Solved

VBA - Using CodeName of Worksheet as String to Active Sheet

Posted on 2011-03-15
13
1,616 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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
 
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
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 Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

726 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