Results 1 to 9 of 9

Thread: Using Named Ranges in a Macro to open sheets
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jan 2011
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Using Named Ranges in a Macro to open sheets

    Hi

    I am attempting to create a named range that will open individual sheets in a work book via a macro. The current method i am using is when a button is pressed the individual sheet for each person are displayed as in the example below.

    Sheets("21534").Visible = True
    Sheets("20722").Visible = True
    Sheets("21160").Visible = True
    Sheets("22031").Visible = True
    Sheets("1503").Visible = True

    What I would like to do is crate a named range that list the same sheets then call the named range in the macro to display the sheets from the named range. In the example below I create a named range called "Steve" the have the macro open the sheets from the name range. This would allow people who never used VBA to modify the displayed without having to go to the text editor to modify

    Steve
    1503
    20722
    21160
    21534
    22031


    Thanks again for all the help, this forum has pulled my fat out of the fire more times than I would like to admit to.
    Chris

  2. #2
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,597
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Using Named Ranges in a Macro to open sheets

    Try something like this. Define a Named Range

    Name: Steve
    RefersTo: Sheet1!$A$1: INDEX(Sheet1!$A:$A, COUNTA(Sheet1!$A:$A), 1)

    Steve then refers to the list of names that starts on Sheet1!$A$1 and downward.

    Then if you run this macro, it will show all those sheet's who's names are listed in Steve.
    Code:
    Sub ShowSteve()
        Dim oneCell
    
        For Each oneCell in Range("Steve")
            On Error Resume Next
            ThisWorkbook.Sheets(oneCell.Value).Visible = xlSheetVisible
            On Error Goto 0
        Next oneCell
    End Sub
    There is no use of Names that will change sheet visibility without the user invoking a macro.

    Also, you might consider that even your least experienced users are familiar with Excel (at least a little bit) and teaching them how to use the on-board Sheet Visibility controls that Excel provides is as easy as teaching them how to use your sheet visibility named range + macro.
    Last edited by mikerickson; Oct 27th, 2016 at 09:23 AM.

  3. #3
    Board Regular Darren Bartrup's Avatar
    Join Date
    Mar 2006
    Location
    Nottingham (UK)
    Posts
    1,258
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Using Named Ranges in a Macro to open sheets

    This code will check the sheet exists before making it visible.
    Code:
    Sub Test()
    
    
        Dim rCell As Range
        
        For Each rCell In Range("Steve")
            If WorkSheetExists(rCell.Value) Then
                ThisWorkbook.Worksheets(rCell.Value).Visible = True
            End If
        Next rCell
    
    
    End Sub
    
    
    Public Function WorkSheetExists(SheetName As String, Optional WrkBk As Workbook) As Boolean
        Dim wrkSht As Worksheet
        
        If WrkBk Is Nothing Then
            Set WrkBk = ThisWorkbook
        End If
        
        On Error Resume Next
            Set wrkSht = WrkBk.Worksheets(SheetName)
            WorkSheetExists = (Err.Number = 0)
            Set wrkSht = Nothing
        On Error GoTo 0
    End Function
    Using Office 2003 & 2010,

    I'm 1 of the 10 people that don't understand binary. Guess that means the other 1001 do.

    No answer to your post?
    Get someone to read it - does it make sense or does it sound like gibberish?

  4. #4
    Board Regular
    Join Date
    Jan 2011
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Using Named Ranges in a Macro to open sheets

    Hi Mike

    Thanks for the help and I think its close but I still cant get the hidden tabs to become visible. I have defined the Range as "Steve" and using the "Watch" function in the Debug window know that I am getting the correct values in the range and are looping through them.

    Somehow I think I need to use the "index" command to convert the variable "onecell" into the sheet name so the command saying visible = true works. Below is the code as it stands at the moment. Thanks again.


    '
    ActiveWorkbook.Names.Add Name:="Steve", RefersToR1C1:="=Sheet3!R2C4:R6C4"
    ActiveWorkbook.Names("Steve").Comment = ""
    Dim oneCell


    For Each oneCell In Range("Steve")
    On Error Resume Next
    ActiveWorkbook.Sheets(oneCell.Value).Visible = True
    ActiveWorkbook.Sheets(oneCell.Value).Visible = xlSheetVisible
    On Error GoTo 0

    Next oneCell

    End Sub

  5. #5
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,597
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Using Named Ranges in a Macro to open sheets

    What are in those cells?

  6. #6
    Board Regular
    Join Date
    Jan 2011
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Using Named Ranges in a Macro to open sheets

    Hi Mike,
    The range "Steve" is defined below:
    STEVE
    1503
    20722
    21160
    21534
    22031

    There are sheets titled the exact same way that are hidden. When I add the watch using the variable "onecell" the values in the range are displayed in the the watch but I cant get the line of code to recognize the variable as the sheet name. If I replaced onecell with "20722" it works in the code e.g. Sheets("20722").Visible = True

    I can see that the variable has that value. I keep thinking that you have to index the variable so it recognizes the hidden sheet.




  7. #7
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,597
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Using Named Ranges in a Macro to open sheets

    Try
    Code:
    ActiveWorkbook.Sheets(CStr(oneCell.Value)).Visible = xlSheetVisible
    Are these sheets in the ActiveWorkbook?

  8. #8
    Board Regular
    Join Date
    Jan 2011
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Using Named Ranges in a Macro to open sheets

    Yes it is an active work book and these sheet are within it.

    Thank you! Have a pleasant afternoon.
    Chris

  9. #9
    Board Regular
    Join Date
    Jan 2011
    Posts
    76
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Using Named Ranges in a Macro to open sheets

    Mike,
    I works perfectly thanks again your a genius.
    C

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •