Need a permanent way to refer to Excel 2007 worksheets to use for VBA code

[kimBQT7]

Hello,

I am using VBA to program a UserForm for a proposal workbook. The workbook has multiple tabs with related product data on them and the users of this form will be renaming the tabs and changing the order of the tabs/worksheets frequently.

I am wondering if the worksheets in Excel have a permanent name of some sort, a name that never changes, so when I write my VBA code for a specific worksheet, it will always be referring to the same worksheet with its corresponding data- regardless of its position in the workbook or its current "tab name."

Thanks so much for your help!

 

[VoG]

Yes each sheet has a Code Name like Sheet1

If you look in the Project window in the Visual Basic Editor you should see the sheets listed like

Sheet1 (Data)

where Sheet1 is the Code Name and Data is what the sheet has been named.

The Code Name is not affected by renaming or moving sheets.

 

[RoryA]

If you view the sheets in the Project Explorer in the VB Editor you will see they have two names - e.g. Sheet1(Data) where the name in parentheses is the name you see on the sheet tab and the name before it is the code name which can only be changed in code or in the VBEditor. If you use the code name, you should be safe.
Note: the codename is effectively a variable, rather than a string, so you can refer to:

sheet1.range("A1")

rather than:

Sheets("Data").Range("A1")

 

[shg]

You can refer to the CodeName of the sheet. Instead of

Worksheets("Sheet1").Range("A1").Value = "Bob"

use

Sheet1.Range("A1").Value = "Bob"

The CodeName of a sheet appears to the left of the sheet name in the Project Explorer window, and can only be changed in the VBE (or via VBA).

 

[RoryA]

Now that's quite spooky.

 

[Ruddles]

Sort of... I think you mean the sheet's .Codename property - the first name you see for each worksheet in the VBA Project Explorer.

Try this:-

Option Explicit
Sub Shownames()
 
  Dim ws As Worksheet
  
  For Each ws In ThisWorkbook.Worksheets
    Debug.Print "Name: " & ws.Name & ", Codename=" & ws.CodeName
  Next ws

End Sub

Be aware though that the codename can be changed in the VBA editor by going View > Properties Window, so perhaps you should stop your users having access to that.

 

[Ruddles]

Blimi, last off the mark - again!

 

[kimBQT7]

When I use the sheets' code names in my code I get:

"Runtime error 1004 - Method 'Range' of object '_Worksheet' failed"


Using the code name works fine if I just have a single sub in a module. However, when I copy the sub- change the sheet codename- and run the code, I will get the runtime error above ONLY for my second sub in the module.


Here is the code in the module:

Sub Name3100Table()

Dim CPE3100Table As Range

Set CPE3100Table = Sheet3.Range("A5", Range("A5").End(xlToRight).End(xlDown))

ActiveWorkbook.Names.Add Name:="CPE3100_EntireTable", RefersTo:=CPE3100Table

End Sub

Sub NameX3Table()

Dim CPEX3Table As Range

'This is where I get the runtime error > Set CPEX3Table = Sheet4.Range("A5", Range("A5").End(xlToRight).End(xlDown))

ActiveWorkbook.Names.Add Name:="CPEX3_EntireTable", RefersTo:=CPEX3Table

End Sub


Thanks for the quick replies, guys. Any thoughts?

 

[RoryA]

In both subs you are failing to qualify the second Range call. This:

Set CPEX3Table = Sheet4.Range("A5", Range("A5").End(xlToRight).End(xlDown))

should be:

Set CPEX3Table = Sheet4.Range("A5", Sheet4.Range("A5").End(xlToRight).End(xlDown))

 

[kimBQT7]

Rory- you are amazing. That fixed everything, thank you so much!