Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide sheets
Hi all. I have a list of sheets within the workbook in the range
Sheet1 A1:A10. How can I have vba hide the sheets that are listed in that range? Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide sheets
Sub testData()
Dim whatSheet As Worksheet For i = 1 To 10 Set curCell = Worksheets("Sheet1").Cells(i, 1) If Not IsEmpty(curCell.Value) Then Set whatSheet = Worksheets(curCell.Value) whatSheet.Visible = xlSheetVeryHidden End If Next i End Sub "Steve" wrote: Hi all. I have a list of sheets within the workbook in the range Sheet1 A1:A10. How can I have vba hide the sheets that are listed in that range? Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide sheets
Try something like this:
Sub Test() Dim sht As Worksheet Dim rng As Range Dim myshtnames As Range Set sht = Worksheets("Sheet1") Set myshtnames = sht.Range("A1:A10") For Each rng In myshtnames shtnam = rng.Value Worksheets(shtnam).Visible = False Next End Sub -- If this posting was helpful, please click on the Yes button. Regards, Michael Arch. "Mike" wrote: Sub testData() Dim whatSheet As Worksheet For i = 1 To 10 Set curCell = Worksheets("Sheet1").Cells(i, 1) If Not IsEmpty(curCell.Value) Then Set whatSheet = Worksheets(curCell.Value) whatSheet.Visible = xlSheetVeryHidden End If Next i End Sub "Steve" wrote: Hi all. I have a list of sheets within the workbook in the range Sheet1 A1:A10. How can I have vba hide the sheets that are listed in that range? Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide sheets
Dim Cell As Range
On Error Resume Next For Each Cell In Range("A1:A10") Sheets(Cell.Value).Visible = xlSheetHidden Next Cell ' or you can use: ' Sheet1.Visible = xlSheetVeryHidden "Steve" wrote: Hi all. I have a list of sheets within the workbook in the range Sheet1 A1:A10. How can I have vba hide the sheets that are listed in that range? Thanks! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide sheets
I named the range of cells containing the worksheet names "MyList" and used
the following code: Sub HideAway() Dim w As Worksheet Dim r As Range Dim i As Integer Dim strSheet As String Set r = Range("MyList") For i = 1 To r.Cells.Count strSheet = r.Cells(i) For Each w In ThisWorkbook.Worksheets If w.Name = strSheet Then w.Visible = xlSheetHidden End If Next w Next i Set w = Nothing Set r = Nothing End Sub -- Kevin Backmann "Steve" wrote: Hi all. I have a list of sheets within the workbook in the range Sheet1 A1:A10. How can I have vba hide the sheets that are listed in that range? Thanks! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide sheets
I meant to say, or you can use:
Sheets(Cell.Value).Visible = xlSheetVeryHidden ....depending on whether or not you want your users to be able to unhide them via Format menu. "Charlie" wrote: Dim Cell As Range On Error Resume Next For Each Cell In Range("A1:A10") Sheets(Cell.Value).Visible = xlSheetHidden Next Cell ' or you can use: ' Sheet1.Visible = xlSheetVeryHidden "Steve" wrote: Hi all. I have a list of sheets within the workbook in the range Sheet1 A1:A10. How can I have vba hide the sheets that are listed in that range? Thanks! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hide sheets
Something like this maybe...
Sub HideSheets() Dim X As Long On Error Resume Next For X = 1 To 10 If Len(Cells(X, "A").Value) 0 Then Worksheets(Cells(X, "A").Value).Visible = False End If Next End Sub Note: The On Error Resume Next is to protect against a sheet name that does not exist. Rick "Steve" wrote in message ... Hi all. I have a list of sheets within the workbook in the range Sheet1 A1:A10. How can I have vba hide the sheets that are listed in that range? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide sheets | Excel Worksheet Functions | |||
Hide Sheets | Excel Programming | |||
Hide Sheets | Excel Programming | |||
Hide all Sheets | Excel Programming | |||
Hide all sheets but selected sheets - an example | Excel Programming |