I have noticed that a lot of the procedures in this forum contain references to Worksheets like Sheets("Sheet1").Activate.
This should never have to be done! You can change the Worksheet Reference Name or "(Name)" with parenthases in the VBE. The Worksheet Reference Name differs from the Worksheet Name in that the Worksheet Name is the Name that you see on the Worksheet Tab in Excel and the Worksheet Reference Name is the Name that can be used in procedures to refer to a specific Worksheet in a Workbook.
Some of the procedures I have seen even have the Worksheets set as a variable like Set mySheet = Worksheets("Sheet1") to allow the programmer to refer to the Worksheet later in his/her code as mySheet.
This is unnecessary, because when you change the Worksheet Reference Name in the VBE it is pretty much the same as setting a Global variable for that sheet.
To change the Worksheet Reference Name, click on the Worksheet object in the VBE and go to the Properties for that sheet. Change the "(Name)" (not "Name") to something like sBudget (or even mySheet). Now you can refer to that Worksheet in any module in your application as sBudget (or mySheet).
This can be, and should be done for all of the Worksheets in a workbook. for example:
Worksheets("Annual Budget").Activate should be sBudget.Activate.
You can also shorten the range references in your code as well:
Isn't that better?!
Now, Have fun going through all of your previous applications and changing your Worksheet references!
In addition you can change the Reference Name at run-time. It is a bit more difficult than just changing it at design-time. I wrote the following procedure that asks you if you want to change the Reference Name for the active Worksheet and then will change it to what you input into the InputBox:
I have marked the line that changes the Reference in red
Sub Change_WS_ReferenceName() Dim msg As String Dim NewRef Dim ws As Worksheet Set ws = ActiveSheet ' Create Message msg = "Do you want to change the VBA Reference Name for: " & vbCrLf msg = msg & ws.Name & vbCrLf & "The VBA Reference Name is:" & vbCrLf msg = msg & ws.CodeName & vbCrLf ' Confirm Change If MsgBox(msg, vbQuestion + vbYesNo, "Change Code Name") = vbNo Then Exit Sub ' Set New Reference NewRef = InputBox("Enter the new VBA Reference Name", "Change Code Name") If NewRef = vbCancel Or NewRef = "" Then Exit Sub ' Change Reference On Error GoTo RefExists: ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName") = NewRef MsgBox "New VBA Reference Name for: " & ws.Name & _ " is now " & ws.CodeName, vbOKOnly, "Code Name Changed" Exit Sub RefExists: On Error GoTo 0 MsgBox "This VBA Reference already exits, please try again.", vbCritical, "Invalid Entry" Call Change_WS_ReferenceName End Sub
Now, if you would like to change all of your Worksheet Reference Name's to contiguous Names:
Sub BatchChange_WSRefName() ' Changes the Reference Names for all Worksheets ' in the active Workbook to Sheet + incrementing integer Dim i As Integer, ws As Worksheet i = 0 ' Change to Temp first to prevent Naming errors For Each ws In ActiveWorkbook.Worksheets i = i + 1 On Error Resume Next ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName") = _ "Temp" & i On Error GoTo 0 Next ws ' Change to Sheet + incrementing integer i = 0 For Each ws In ActiveWorkbook.Worksheets i = i + 1 On Error Resume Next ws.Parent.VBProject.VBComponents(ws.CodeName).Properties("_CodeName") = _ "Sheet" & i On Error GoTo 0 Next ws Set ws = Nothing End Sub
The problem with accessing a VBA project at run-time is that (at least w/ Office XP) you have to "allow" for this to happen. Changing the Worksheets' Reference Names at run-time might cause an error message to appear because the acces to VBA Projects using macros has not been turned on. To do this, do the following:
1. In Excel goto Tools->Options . . . 2. Select the Security tab and click the Macro Security . . . button. 3. In the Trusted Sources tab check the Trust Access to Visual Basic Project checkbox.
That should do it! Now you shouldn't have any problems accessing a VBA Project on your PC.