Solved

EXCEL: Sheet CodeName question

Posted on 2013-01-04
3
300 Views
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)
ShCode.cboSelectPP.Clear
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!
0
Comment
Question by:gpchicago08
3 Comments
 
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.
0
 
LVL 14

Expert Comment

by:Faustulus
ID: 38745901
Hi,
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.
0
 
LVL 33

Accepted Solution

by:
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
            .Clear
            .AddItem "One"
            .AddItem "Two"
            .AddItem "Three"
        End With

    End If
    
End Sub

Open in new window

0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

747 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

10 Experts available now in Live!

Get 1:1 Help Now