macro to call up sheets

[bramnizzle]

Okay, first off, this group rocks. To everyone who contributes; your
advice is priceless. Thanks!

I have a journaling workbook for my office. A user inputs some
information onto a template sheet. At the end of the day, the
template gets saved as a new sheet and is hidden so that when the user
opens the workbook the next day, a fresh template is available and
yesterday's sheet is there, but hidden. I need a macro that allows
the user to search the hidden sheets. Perhaps a userform or pop up
window that has a list of the hidden sheets. The user can then select
a sheet and then the macro opens the hidden sheet so the user can view/
print.

Example...

-user opens "PMJournal.xls" and it goes right to Sheets("Journal")
-user inputs some information into the fields
-user clicks a button and a macro (which I already have) copies
"Journal" and renames it with that days date (i.e. 6.25.2007) and then
hides it.
-after exit, "Journal" is cleared for new use when the workbook is
opened again

....now, some time later, the user needs to go back to
Sheets("6.25.2007"). Without making the user do Format -> Sheet ->
Unhide...I was hoping for a macro that something pops up, gives a list
of the hidden sheets so that when you click on one, it unhides that
sheet.

I would assume the hidden sheets need to be listed somewhere, but
maybe I'm making it more complicated than it has to be.

Thanks in advance!

 

[Guest]

You can modify this code to do what you want:

http://www.j-walk.com/ss/excel/tips/tip48.htm

 

[Bernie Deitrick]

Add a userform with a listbox and a commandbutton. Then, in a regular codemodule, add this macro
(Assuming your userform is named UserForm1):

Sub ShowUnhideUserform()
Load UserForm1
UserForm1.Show
End Sub

In the userforms codemodule, add this

Private Sub CommandButton1_Click()
ThisWorkbook.Worksheets(UserForm1.ListBox1.Value).Visible = True
Unload UserForm1
End Sub


Private Sub UserForm_Initialize()
Dim mySht As Worksheet
Dim HiddenSheets() As String

ReDim HiddenSheets(1 To 1)

For Each mySht In ThisWorkbook.Worksheets
If Not mySht.Visible Then
ReDim Preserve HiddenSheets(1 To UBound(HiddenSheets) + 1)
HiddenSheets(UBound(HiddenSheets)) = mySht.Name
End If
Next mySht

Me.ListBox1.List = HiddenSheets

End Sub

You can add textboxes with instructions, etc. but this will give you the basic code. You would
also need code to re-hide the sheet at some point.

HTH,
Bernie
MS Excel MVP