Another quick question! Thanks!

  • Thread starter Thread starter Damil4real
  • Start date Start date
D

Damil4real

I have a workbook with 9 worksheets. These sheets are named different
names, but let’s just say they are named sheet1 – sheet9. Sheet1 is
named “Main” while the other ones are different.

When you open the workbook, I want only sheet1 (sheet1 named “Main)
showing and all the other sheets hidden.

I want a macro in sheet1 (sheet1 is named “Main) that when clicked
will pop up a box asking something like: click 1 to view sheet2; click
2 to view sheet3…etc), when the number is selected, I want the macro
to hide all other sheets and unhide sheet3 for viewing.

Thanks!
 
You could use a worksheet_change macro tied to a particular cell with a drop
down of 1-? or just enter the number 3 using select case and the macro could
unhide that sheet and goto it

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
I have a workbook with 9 worksheets. These sheets are named different
names, but let’s just say they are named sheet1 – sheet9. Sheet1 is
named “Main” while the other ones are different.

When you open the workbook, I want only sheet1 (sheet1 named “Main)
showing and all the other sheets hidden.

I want a macro in sheet1 (sheet1 is named “Main) that when clicked
will pop up a box asking something like: click 1 to view sheet2; click
2 to view sheet3…etc), when the number is selected, I want the macro
to hide all other sheets and unhide sheet3 for viewing.

Thanks!
 
Right click sheet tab of Main>view code>copy/paste this> type in 1-? in cell
a1

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$A$1" Then Exit Sub
If IsNumeric(Target) And Target _
<= Sheets.Count And Target <> 1 Then
For Each ws In Worksheets
If ws.Name <> "Main" Then ws.Visible = False
Next ws
Sheets("Sheet" & Target).Visible = True
End If
End Sub
 
Quick and easy question but not so quick and easily done.

Other than Sheet1 being named "Main" do you have unique names for the other
sheets?

When unhiding Sheet3(or whatever it is named) would you want "Main" to be
hidden along with the others?

Excel has two names for each sheet. The name you give it and a code name.

Would you want to hide/unhide by code name or by sheet name?


Gord Dibben MS Excel MVP
 
Quick and easy question but not so quick and easily done.

Other than Sheet1 being named "Main" do you have unique names for the other
sheets?

When unhiding Sheet3(or whatever it is named) would you want "Main" to be
hidden along with the others?

Excel has two names for each sheet.  The name you give it and a code name.

Would you want to hide/unhide by code name or by sheet name?

Gord Dibben  MS Excel MVP
Click to expand...

Sheet1 is named "Main"
Sheet2 is named "Cost Adjustment"
Sheet3 is named "Price Adjustment"
Sheet4 is named "Fund List"
Sheet5 is named "Fund list 2"
Sheet6 is named "Incoming Funds"
Sheet7 is named "Outgoing Funds"
Sheet8 is named "Price List"
Sheet9 is named "Fund Adjustment"

When hiding Sheet3, Main should say visible, but all other sheets
hidden.

Would you want to hide/unhide by code name or by sheet name?

I don't understand this question. I just want to look at the workbook
and see only two sheets "Main" & which ever other sheets was picked to
be visible.

Thanks for your assisted

Thanks!
 
Sheet1 is named "Main"
Sheet2 is named "Cost Adjustment"
Sheet3 is named "Price Adjustment"
Sheet4 is named "Fund List"
Sheet5 is named "Fund list 2"
Sheet6 is named "Incoming Funds"
Sheet7 is named "Outgoing Funds"
Sheet8 is named "Price List"
Sheet9 is named "Fund Adjustment"

When hiding Sheet3, Main should say visible, but all other sheets
hidden.

Would you want to hide/unhide by code name or by sheet name?

I don't understand this question. I just want to look at the workbook
and see only two sheets "Main" & which ever other sheets was picked to
be visible.

Thanks for your assisted

Thanks!
Click to expand...

Ron, you're a genius! Your code worked great when the sheets are named
sheet1, sheet2, ...etc, but doesn't work when the sheet names I have
in my previous response.

I can use that code, but is there any other way it can work with the
above sheet names?

Thanks!
 
Back
Top