Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


EXCEL: Sheet CodeName question

Posted on 2013-01-04
Medium Priority
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 93

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 34

Accepted Solution

Norie earned 2000 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

661 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