Using Named Ranges in a Macro to open sheets

cthompson

Board Regular
Joined
Jan 31, 2011
Messages
80
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

<tbody>
</tbody>


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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Hi Mike,
The range "Steve" is defined below:
STEVE
1503
20722
21160
21534
22031

<tbody>
</tbody>

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.




<colgroup><col style="width:96pt" width="128"> </colgroup><tbody>
</tbody>
 
Upvote 0
Try
Code:
ActiveWorkbook.Sheets(CStr(oneCell.Value)).Visible = xlSheetVisible
Are these sheets in the ActiveWorkbook?
 
Upvote 0
Yes it is an active work book and these sheet are within it.

Thank you! Have a pleasant afternoon.
Chris
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top