EXCEL: Sheet CodeName question

Posted on 2013-01-04
Last Modified: 2013-01-07
I want this code to run on specific worksheets only.  I can filter the sheet names, but I need to populate a combobox with info and I cannot seem to get the codename into the With statement...

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

ShCode = Application.Sheets(Sh.Name).CodeName
MsgBox (ShCode)
If Sh.Name = "Jan-1-15" Then
MsgBox (Sh.Name)
With ShCode.cboSelectPP
  .AddItem "One"
  .AddItem "Two"
  .AddItem "Three"
End With
End If
End Sub

Open in new window

It bombs at line 7....
Do I need to Dim the ShCode variable in some way?  Cannot figure it out.  Thanks!
Question by:gpchicago08
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38745796
First off, ALWAYS declare your variables, even if you are explicitly declaring them as Variant.  You'll thank me later :)

On line 2, you are assigning a string value to the ShCode variable.  With...End With only works with objects.
LVL 14

Expert Comment

ID: 38745901
This line doesn't make too much sense:-
ShCode = Application.Sheets(Sh.Name).CodeName

First off, as matthewpatrick says, ALWAYS declare your variables. Like,
Dim ShCode As String
So, we know that it is a string. Your code seeks to assign the code name of the worksheet Sh to it which, as I said, doesn't make too much sense. However, before we get there, where do you get the worksheet Sh from?

This code is wrong:-
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
True, you could have this procedure in a normal code module where it would be perfectly OK, but I suspect that you have this as an event procedure in the code sheet of a worksheet where the procedure was set up correctly by Excel and you can't add any parameters to it. Moreover, the sheet which was just activated is the ActiveSheet. Therefore you already know which sheet is active and don't need any parameter to tell you. Remember not to change the format of event procedures that Excel sets up for you.

So, the line of code we discuss here should run like this,
ShCode = Application.Sheets(ActiveSheet.Name).CodeName
which is a very complicated and round-about way of saying something rather simple, like
ShCode = ActiveSheet.CodeName

Every sheet has a name and a code name. You can use the code name to refer to that sheet in your code. In other words, you don't need to assign a code name - like ShCode - to a sheet because it already has one. On the other hand, code like Worksheets(ShCode) would fail if ShCode is a code name. You need the sheet's Name (not CodeName) to specify the item in the worksheets collection. So, ShCode  = ActiveSheet.CodeName is correct syntax but quite useless. I suggest you leave the code name alone for the time being and use the syntax Worksheets(ShName) where ShName is the name of a worksheet.

I suspect, however, that you had something quite different in mind. Perhaps this:-

Dim ShCode As Worksheet
Set ShCode = ActiveSheet

This, too, doesn't make too much sense as becomes apparent in the way I wrote it. ActiveSheet is already a placeholder. However, if you intend to change the active sheet later then creating an object variable while you know which sheet it is makes perfect sense. Your Dim statement makes clear what you have in mind. Note that all Dim statements should appear at the top of your procedure, not interspersed with other code. Also note that you need to precede the assignment with the word Set when you assign a value to an object variable.

Now you would be able to use the object ShCode in With statements.
With ShCode.cboSelectPP
This code might actually work if there is a control by the name of cboSelectPP in the worksheet ShCode.
LVL 33

Accepted Solution

Norie earned 500 total points
ID: 38745996
I don't think you can use the codenames of the worksheets like you want.

A reference to the worksheet that's been activated is passed to the workbook level event SheetActivate.

Can't you use that?

Perhaps something like this.
Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    If Sh.Name = "Jan-1-15" Then

        With Sh.OLEObjects("cboSelectPP").Object
            .AddItem "One"
            .AddItem "Two"
            .AddItem "Three"
        End With

    End If
End Sub

Open in new window


Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
how to add loop into this VBA 3 29
Excel error  #DIV/0! 7 18
Excel - conditional formatting on several columns 9 35
Msgbox tickler 10 25
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question