-
VBA to Hide/Unhide Sheets
Sadly XL on Mac does not appear to allow keyboard shortcuts for Hide Worksheet (on Windows ALT + O + H + H) nor for Unhide Worksheet (ALT + O + H + U). And even in the Customize toolbar there doesn't seem to be any way to access these two commands (Format > Sheet > Hide/Unhide).
So, VBA code seems to be the way to go. It is simple to Hide the activesheet as follows. which then can be assigned to a keyboard shortcut.
[VBA]Sub HideSheet()
ActiveWindow.SelectedSheets.Visible = False
End Sub[/vba]
With Unhide it isn't as easy. I am having trouble unhiding, not being able to bring up the dialog box that lists the worksheets that are hidden. This is my initial attempt; it brings up a list of hidden worksheets, and then the person can type in that name. I suppose I could develop a UserForm, but UF can be a pain in XL on the Mac (from my experience).
[vba]
Sub UnHideSheet()
Dim ws As Worksheet
Dim myList As String
For Each ws In ActiveWorkbook.Worksheets
If ws.Visible = False Then
myList = ws.Name
MsgBox myList
End If
Next ws
Dim mySht As String
mySht = InputBox("What Sheet do you want to unhide?")
Sheets(mySht).Visible = True
End Sub
[/VBA]
Anyone have any other ideas?
Software: LibreOffice 3.3 on Mac OS X 10.6.5
(retired Excel 2003 user, 3.28.2008 )
Humanware: Older than dirt
--------------------
old, slow, and confused
but at least I'm inconsistent!
Rich
-
OK shades, create yourself a UserForm with one ListBox and one CommandButton. Then use the following as the UserForm code:
[vba]Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub ListBox1_Click()
If Not Worksheets(ListBox1.ListIndex + 1).Visible Then
Worksheets(ListBox1.ListIndex + 1).Visible = True
End If
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim i As Integer
Me.Width = Me.Width + 1
Me.Height = Me.Height + 1
CommandButton1.Caption = "Done"
With ListBox1
.ColumnCount = 2
.ColumnWidths = "200;100"
For i = 1 To Worksheets.Count
.AddItem Worksheets(i).Name
If Worksheets(i).Visible Then
.List(i - 1, 1) = "visible"
Else
.List(i - 1, 1) = "hidden"
End If
Next i
End With
End Sub
[/vba]
When you run the form, it will list the names of all sheets along with their visible/hidden status (note that I have not addressed xlVeryHidden). Click on a hidden sheet name, and it will unhide that sheet.
-
Thanks for the reply - looks like it will fill the bill. I haven't had a chance to work on this - work has been very intense, and I don't see a break for at least 3 weeks.
Software: LibreOffice 3.3 on Mac OS X 10.6.5
(retired Excel 2003 user, 3.28.2008 )
Humanware: Older than dirt
--------------------
old, slow, and confused
but at least I'm inconsistent!
Rich
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules