Solved

VBA - Using CodeName of Worksheet as String to Active Sheet

Posted on 2011-03-15
13
1,464 Views
Last Modified: 2012-05-11
Experts,

I have some code that is working fine, however the reference to each worksheet is based on the visible sheet name in the workbook. I want to change this to the CodeName “ (Name) property” in the VBE so that the sheets are referenced regardless of what the visible sheet is called.

In this example, you will see that I have the sheets in Excel named “2011 … something” which means that when the tabs are updated to 2012, I will get an error and then need to change the code.

The first example runs correctly as follows:

SetupToPrint “2011 Agent YTD ALL Sales”

I have changed the (Name) property value to EverythingAgentOnlySalesToPrint

How can I pass this string so that in the private sub “SetupToPrint”, EverythingAgentOnlySalesToPrint is activated and then the rest of the routine is ran on that worksheet?

In the old format, to activate the sheets in Sub SetupToPrint the following was used:
Sheet(sh).Activate.

I have tried just passing the string as:
sh.activate but I compile error: Invalid qualifier

Also, as you can see on line 6 and 7, I tried to pass the sheet name with the addition of .Name, such as “GroupSalesToPrint.Name" however these did not work either.

In the screenshot,
 Arrowsthe black arrows work as 1 goes into 2, however I want to make the blue arrows work by executing SetupToPrint with the active sheet being the (Name) which is called.

Code is attached below.

Also - I asked this question, which was similar, I wanted to use the (Name) instead of the sheetname, but it was for an array:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26846232.html

Thank you in advance!
Kevin

Sub SetPageBreaks()

    Application.ScreenUpdating = False

        SetupToPrint "EverythingAgentOnlySalesToPrint" '"2011 Agent YTD ALL Sales"
'        SetupToPrint "GroupSalesToPrint.Name"               '"2011 Agent YTD Sales Group"
'        SetupToPrint "MyBlueSalesToPrint.Name"              '"2011 Agent YTD Sales My Blue"
'        SetupToPrint "MedicareSalesToPrint"                 '"2011 Agent YTD Sales Medicare"
'        SetupToPrint "SalesByProductToPrint"                '"Sales by Product"
    
    Application.ScreenUpdating = True
    
End Sub

Private Sub SetupToPrint(sh As String)
    
    Sheet(sh).Activate
    Call SetPrintAreaToPivotTable
    Call SetPageBreakToXNumberOfRows
    Call PageBreakUnderlining

End Sub


Private Sub SetPrintAreaToPivotTable()

    With ActiveSheet
        lPTcells = .PivotTables("PivotTable1").DataBodyRange.Cells.Count
        Set rngTopLeft = .PivotTables("PivotTable1").RowRange.Cells(1)
        Set rngBotRight = .PivotTables("PivotTable1").DataBodyRange.Cells(lPTcells)
        strPTAddress = rngTopLeft.Address & ":" & rngBotRight.Address 'strPT address don't exist!
        .PageSetup.PrintArea = strAddress
    End With

End Sub

Private Sub SetPageBreakToXNumberOfRows()

    Dim LastRow As Long
        Dim Row_Index As Long
        Dim RW As Long
    
        RW = 48                 'How many rows do you want between each page break
        
        With ActiveSheet
            .ResetAllPageBreaks 'Remove all PageBreaks
            
            LastRow = .Cells(Rows.Count, "D").End(xlUp).Row 'Search for the last row with data in Column D
            
            For Row_Index = RW + 2 To LastRow Step RW
                .HPageBreaks.Add Before:=.Cells(Row_Index, 1)
            Next
    End With

End Sub

Private Sub PageBreakUnderlining()
    
    Dim pb As HPageBreak
    
        With ActiveSheet
        For Each pb In .HPageBreaks
        LastRow = pb.Location.Offset(-1, 0).Row

        With .Rows(LastRow).Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlThin
      End With
    Next pb
  End With
End Sub

Open in new window

0
Comment
Question by:redrumkev
  • 7
  • 6
13 Comments
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
ID: 35142205
Worksheet code names are not used the same way as tab names. You reference the code name as an instantiated object:

Private Sub SetupToPrint(sh As Worksheet)
   
    sh.Activate
    Call SetPrintAreaToPivotTable
    Call SetPageBreakToXNumberOfRows
    Call PageBreakUnderlining

End Sub

Kevin
0
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 500 total points
ID: 35142216
So instead of:

        SetupToPrint "EverythingAgentOnlySalesToPrint" '"2011 Agent YTD ALL Sales"

You would have:

        SetupToPrint "EverythingAgentOnlySalesToPrint", EverythingAgentOnlySalesToPrint

Kevin
0
 
LVL 23

Author Comment

by:redrumkev
ID: 35142224
Kevin,

So if I am calling the visible "sheetname" then I use (sh As String)
but for the code name "(Name)" I should use (sh As Worksheet)

Kevin
0
 
LVL 81

Assisted Solution

by:zorvek (Kevin Jones)
zorvek (Kevin Jones) earned 500 total points
ID: 35142244
Yes.

Kevin
0
 
LVL 23

Author Comment

by:redrumkev
ID: 35142290
THANK YOU!!!

I changed it to:

Sub SetPageBreaks()

    Application.ScreenUpdating = False

        SetupToPrint EverythingAgentOnlySalesToPrint
        SetupToPrint GroupSalesToPrint
        SetupToPrint MyBlueSalesToPrint
        SetupToPrint MedicareSalesToPrint
        SetupToPrint SalesByProductToPrint
    
    Application.ScreenUpdating = True
    
End Sub

Open in new window


followed by:

Private Sub SetupToPrint(sh As Worksheet)
   
    sh.Activate
    Call SetPrintAreaToPivotTable
    Call SetPageBreakToXNumberOfRows
    Call PageBreakUnderlining

End Sub

Open in new window


And it executed!!

Thank you so much, I think I might of went overboard on my question explanation and screenshots, especially seeing that it was only removing the quotes surrounding the codename and then changing the As from string to worksheet!

Regards,
Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35142315
:-)

Just for fun and to better understand the process, here is a routine to find the worksheet object using the code name when you don't know the code name when writing the code.

Public Function GetWorksheetFromCodeName( _
      ByVal CodeName As String _
   ) As Worksheet

' Return the worksheet with the requested code name.

   Dim FocusSheet As Object
   
   For Each FocusSheet In ThisWorkbook.Worksheets
      If FocusSheet.CodeName = CodeName Then
         Set GetWorksheetFromCodeName = FocusSheet
         Exit Function
      End If
   Next FocusSheet

End Function

Kevin
0
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.

 
LVL 23

Author Comment

by:redrumkev
ID: 35142345
Kevin,

So how would I use this code, do I paste it as a new module?

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35142371
I provided the code only as an exercise in understanding how to treat tab names versus code names.

I would never use such a routine myself unless I needed to mess around with worksheet objects only knowing the code names and didn't know the code names when I was writing the code. The only scenario I can think of is code that actually manipulates the VBA project which is a rather risky activity to being with.

That said, it's a regular function so you can place it anywhere.

Kevin
0
 
LVL 23

Author Comment

by:redrumkev
ID: 35142415
Kevin,

Ok - that makes sense and it is does show the differences.

I finally learned how to reference Sheets("2010Budget").Activate or other ways of just activating each sheet. Then someone changed it from 2010 to 2011 and it was "out of range".

So that started me down this entire codename path. And it looks like utilizing the codename will make for a more robust sub, so I think I will base my new subs on the codename rather than the sheetname!

Thank you once again!
Kevin
0
 
LVL 23

Author Comment

by:redrumkev
ID: 35152076
Kevin,

Sorry to add on to a close question, but can you look at the following:

When doing a VLookup inside my VBA, I want to change the lookup range from sheet name to the codename for the sheet, how would I reference this?

I have tired putting the codename in place of the sheet name. I tired listing the codename as a string variable and then putting that into the formula as the lookup sheet.

Thank you,
Kevin
'Working code, but not using codename of the lookup sheet
Set rng1 = Range("C2", Range("C2").End(xlDown)).Offset(, -1)
    rng1.FormulaR1C1 = "=vlookup(RC[-1],'Correct Agent ID-Name'!C1:C2,2,FALSE)"

'Sheet codename is: CorrectedAgentIDName

'The following does not work, brings up the file box, "looking for the worksheet/workbook, as Excel doesn't know where CorrectedAgentIDName is located.
Set rng1 = Range("C2", Range("C2").End(xlDown)).Offset(, -1)
    rng1.FormulaR1C1 = "=vlookup(RC[-1],'CorrectedAgentIDName'!C1:C2,2,FALSE)"

Open in new window

0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35152268
This gets a little tricky. You have to create the address text in VBA before assigning the formula. You should also always qualify any range references to avoid default behavior from generating unwanted results.

Set rng1 = CorrectedAgentIDName.Range("C2", Range("C2").End(xlDown)).Offset(, -1)
rng1.FormulaR1C1 = "=vlookup(RC[-1]," & CorrectedAgentIDName.[C1:C2].Address(External:=True) & ",2,FALSE)"

Good thing you are in the "Kevin" clan or I would make you create a new question ;-)

Kevin
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35152285
Correction:

rng1.FormulaR1C1 = "=vlookup(RC[-1]," & CorrectedAgentIDName.[C1:C2].Address(External:=True, ReferenceStyle:=xlR1C1) & ",2,FALSE)"

Kevin
0
 
LVL 23

Author Comment

by:redrumkev
ID: 35161085
Kevin,
 
I knew this "Kevin" clan had some benefits. We need a brochure to list what comes with membership!

The formula is not being inputted correctly, I have asked a new question on this, which is available here:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_26895116.html

THANK YOU!
Kevin
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

705 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

17 Experts available now in Live!

Get 1:1 Help Now