Worksheet CodeName as type Worksheet

[RobOrBob]

I have a .xlsm that has data feeds pushed into a specific workbook/sheet and uses Worksheet_Change to perform various actions as the data changes.

So my VBA has to be very specific about the workbook/sheet references as the active worksheet may be another in the workbook or even a different workbook entirely.

The following is a test piece that demonstrates the problem (it clears the texts of some dummy form buttons).

Rather than protecting the worksheet, I assumed that by setting the '(Name)' property (aka CodeName) it would protect the code from changes to the display worksheet name 'Name' (which the user can change from the tab):

In VBA Project>Modules>Module1 I have these declarations:

Option Explicit
    Public wb As Workbook
    Public ws As Worksheet

In VBA Project>Microsoft Excel Objects>ThisWorkbook I have this code:

Private Sub Workbook_Open()
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("Interface")
    ws.Buttons.Text = ""
End Sub

On opening the workbook I get a 'Run-time error '9' Subscript out of range' on the Set ws line; if I change it to:

Set ws = wb.Worksheets("Sheet1")

.... then when I open the workbook again, all is well.

Any ideas?

 

[StephenCrump]

The error message is because there is no worksheet with a name (not codename) "Interface"

You just need to say:

Set ws = Interface

 

[kevin9999]

You've actually got the sheet code name & sheet tab name back to front. In your example, "Interface" is the code name of the sheet and "Sheet1" is the tab name. Using your code, try:

Private Sub Workbook_Open()
    Dim ws as Worksheet
    Set ws = Interface
    ws.Buttons.Text = ""
End Sub

 

[RobOrBob]

The error message is because there is no worksheet with a name (not codename) "Interface"

You just need to say:

Set ws = Interface

Thanks for the reply.

That works but does that not default to Interface in the current active workbook (which I'm trying to avoid)?

 

[RobOrBob]

You've actually got the sheet code name & sheet tab name back to front. In your example, "Interface" is the code name of the sheet and "Sheet1" is the tab name. Using your code, try:

Private Sub Workbook_Open()
    Dim ws as Worksheet
    Set ws = Interface
    ws.Buttons.Text = ""
End Sub

Thank you for your response.

I think thats what I originally said, I should have given the actual texts to clarify the context.

But as per my reply to Stephen, I don't see how

Set ws = Interface

doesn't reference the current active workbook as its not qualified.

I'm not sure I even understand how Interface seems to be automatically defined either.

 

[StephenCrump]

That works but does that not default to Interface in the current active workbook (which I'm trying to avoid)?

An unqualified reference to a sheet by its name will refer to the ActiveWorkbook

But a sheet's codename will refer to the workbook with the code in it, ThisWorkbook.

Give it a try!

 

[RobOrBob]

An unqualified reference to a sheet by its name will refer to the ActiveWorkbook

But a sheet's codename will refer to the workbook with the code in it, ThisWorkbook.

Tested and it does exactly that! Who would have thunk it!

Many thanks to you and Kevin.

 

[StephenCrump]

I'm glad we could help