VBA to Hide/Unhide Sheets

[shades]

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?

[BlueCactus]

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.

[shades]

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.