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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

930 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now