Force Save and Unsave of Workbooks

---

Brief: Sometimes event macros are of great help. These two macros are example of that. While one macro protects a workbook from saving any changes, other macro force changes to be saved, if the former macro is present in the workbook. Both can be considered hacks of excel programming or a trick, but they’re always useful and as an excel programmer you should know these two fundas.

Macro #1: Make Workbook unacceptable to any changes.
Pre-requisite: Event Programming, VBA and Excel
Logic: Use of Workbook’s Saved property and Workbook’s BeforeSave event. As soon as excel encounter any changes, it changes Saved property value to FALSE so when you attempt to save the workbook, excel checks this property value and save the workbook, if it founds to be FALSE but if it is TRUE, then it ignores the operation.
Now we use BeforeSave event and turn this property value to TRUE everytime BeforeSave event fired. So this makes workbook immune to any changes.
Code:
‘PLACE THIS CODE IN THISWORKBOOK, NOT IN SHEETS OR MODULE
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
‘CHEATING APPLICATION BY FORCING SAVED PROPERTY TO BE TRUE
    ThisWorkbook.Saved = True
End Sub

Macro #2. Force changes to be saved in such workbooks (as mentioned above).
Pre-requisite: Event Programming, VBA and Excel
Logic: Disable Events, Save the workbook and then enable events again. Yes, it is that simple and here is its code.
Code:
 Sub ForceSave()
    On Error Resume Next
‘THIS WILL DISABLE ALL EVENTS INCLUDING Workbook_BeforeSave.
    Application.EnableEvents = False
‘SAVE THE WORKBOOK
    ActiveWorkbook.Save
‘ENABLE THE EVENTS AGAIN
    Application.EnableEvents = True
    On Error GoTo 0
End Sub

Note: You fill find workbooks with Macro#1 generally VBE password protected, in such case if you want to use Macro#2, either write it in Personal workbook and assign a shortcut key or assign it to a toolbar button.

---

Resize Form using Worksheet Event macro

---

Query Source  : Excel Macros Google Group
Solution Type   : Forms and VBA
Query by          : Kanwaljit
Solution by       : Ashish Jain (Microsoft Certified Application Specialist;Lead Trainer, Success Electrons)

---

Query / Problem:
I have a User Form which needs to take a specified size depending upon the data source. Data Source is in Range DataS1 and DataS2. The following range (named as “Dimensions”) shows the dimensions which the User Form needs to have when ever we use DataS1 or DataS2.UserFormSpec    DataS1      DataS2
FormWidth                   50            100
FormHeight                  40             80

Cell A1 of Sheet1 contains a dropdown containing the names of the datasources, i.e., DataS1 and DataS2. The following code is used to show the UserForm.

Private Sub MyForm_Click()
        UserForm.Show
End Sub

What is wish is that when I select DataS1 or DataS2 from Cell A1, the UserForm width and height changes according to the numbers in range “Dimensions”.

---

Solution:

Brief Description: Solution requires use of Event Macros, VBA, Forms, Object Hierarchy. Even if you don’t know refer below to solve such problems in a very easy manner.
As you want to re-size the form whenever value in Cell A1 of Sheet1 changes, you mustuse event macro to achieve this. Since event macros are automatically fired in response to associated events. Following are some examples of excel events for which event macros are already in the MS Excel applications:
      Name of Event                           |             Event Macro
1. Opening of Workbook              |          Workbook_Open()
2. Double click a cell                     |          Worksheet_BeforeDoubleClick
3. Activate a Sheet                        |          Worksheet_Activate()
4. Pivot Table Update                  |           Worksheet_PivotTableUpdate
5. Change Cell Value                     |            Worksheet_SelectionChange
etc etc etc

Since, each event macro is associated with an event, it is also associated with a particular object. Like, if defined then a workbook event macro is associated to the workbook where it is defined. So if you’ve written a workbook event macro in Workbook A then it won’t affect Workbook B. Same for Sheets, pivot tables etc.

Full Solution to the Query:
1. Open the desired Excel file.
2. Go to VBE by pressing Alt+F11.
3. Under Microsoft Excel Objects for this workbook, Double Click on Sheet1 to open it’s code window.
4. Paste the Code Available here in it.

5. Test your Macro by changing the value of Cell A1.

---

 Example: 

---

ViewColor Entirerow based on a column’s value

---

Query Source   : Excel Macros Google Group
Solution Type   : Event Macro and Data Validation
Query by          : Avinash Daga
Solution by       : Ashish Jain (Microsoft Certified Application Specialist;Lead Trainer, Success Electrons)

---

Query / Problem:

I have two buttons on column P which called active and deactivate.When I click active it should change color of  that row as green,means that project is active and once that is completed i can deactivate that by changing it to original text color. This is working for raw 1 but when I am dragging it down to make it work for all rows I am not able to do so.Please help me to have that thing changed for each row. 
Link to Problem File

---

 Solution:

Instead of using buttons a better solution is to include Data Validation (as you’ve used it in other columns) and Worksheet Event Macro (Worksheet_Change). Here is how you can do this:
Data Validation
1. Select Cell P2.
2. Go to Data –> Validation –> Settings.
3. Change Allow to List and Source to Active,Deactive. Click Ok.
4. Copy the cell P2 using Fill series/Fill Down or any other method you wish.

Use Worksheet Event Macro
Event macros should be placed only in the code window of target object. Since in this case our target is Sheet 3, we will place our macro in Sheet 3 only. This is because event macros only listens the events with which they are attached to. Here is event macro for this problem.

---

View VBA Macro Code Here:

---

Example: How and where the macro is to be placed?
Link to Solution File.

---