Link to home
Create AccountLog in
Avatar of gpchicago08

asked on

EXCEL: Sheet CodeName question

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!
Avatar of Patrick Matthews
Patrick Matthews
Flag of United States of America image

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.
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.
Avatar of Norie

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account