Work sheet names and VBA macros in Excel 1

[MarkusLAndersson]

Hi,

I have a question about worksheet names and VBA macros in Excel. Is it possible to change the name of a worksheet in Excel (the name that can be seen in the bottom of the page) whiteout changing the VBA macros connected to them?

Thanks in advance/ Markus

 

[msmith]

Markus,

Yes! Many of the better Excel VBA programming books recommend this, since your macro code will break, otherwise, if the user changes a sheet tab name. Here's how:

In the Visual Basic Editor (VBE), select the worksheet your macro code interacts with from the Project window. Notice that the Name property for the worksheet (Properties window) is the same as the current sheet tab name. Highlight this name and change it to whatever you want. Now your code can reference the worksheet as it would any other object. Here is some example code, where the worksheet name has been changed to Main as instructed above:

Sub MyMacro()
Dim Total As Single

   Total = [b]Main[/b].Cells(4,1).Value
   ...
   ...
End Sub

Regards,
Mike

 

[MarkusLAndersson]

Thanks for applying but it did not really answer my question. I have already made my VBA code, which is referring to "Sheet1", "Sheet2" and so on. I would like to change my "Sheet1" to "Calculation" and "Sheet2" to "Data" without changing my code. Is this possible?

 

[Regg]

Normally code is not Worksheet or even Workbook dependent unless you make it so. Even then, internally, Excel remembers the original name for certain calls. The best test is to rename the sheet and run your macro to see what happens.

 

[msmith]

Markus,

If you have code like

Worksheets("Sheet1").Range("A1")

in your macros, and you change the worksheet tab from Sheet1 to Calculation your macro will certainly choke. However, you should be able to do a Find/Replace to make the process of changing the sheet name in your code somewhat less painful.


Regards,
Mike