Sheet selection macro

[chobanne]

Hello,

Can someone please help me to make this macro if it is possible at all. I tried alone but i m not good enough in this.

At start only sheet 1 is unhide and active, and other sheets (2 to 7) are hide.

I want macro in sheet1 which will when i start it, create some popup window in which i got a question to choose one of the six options (option A, option B... to option F). Chossing one of them and clicking on the next button in popup window the current sheet1 will be hide and the appropriate other sheet will be unhide and active.

It will go like this Choosing optionA macro will hide sheet 1 and unhide sheet2 , Choosing optionB macro will hide sheet 1 and unhide sheet3 .etc to.... Choosing optionF macro will hide sheet 1 will unhide sheet7.

Thank you for your time

 

[Micron]

From where will the user choice be captured? Combo box? Textbox? Option buttons?
If you want a pop without having to build a form, I suggest to use Input box and just have user enter one letter if the prompt would not be too long for input box.

 

[Snakehips]

Maybe something like this might form the basis for a solution?

Sub Choose()
On Error Resume Next
'set Options relating to sheet number and include sheet name ?
OptA = "      Opion A - " & Sheets(2).Name
OptB = "      Opion B - " & Sheets(3).Name
OptC = "      Opion C - " & Sheets(4).Name
OptD = "      Opion D - " & Sheets(5).Name
OptE = "      Opion E - " & Sheets(6).Name
OptF = "      Opion F - " & Sheets(7).Name

'Get user selection
SelectOpt:
Letter = InputBox(OptA & vbCrLf & OptB & vbCrLf & OptC & vbCrLf & OptD & vbCrLf & OptE & vbCrLf & OptF & vbCrLf _
"Please Enter The Option Letter For The Sheet You Wish To Open   ")
Letter = Trim(Letter)
SNum = Asc(Trim(UCase(Letter))) - 63
'test is single letter in range
If Len(Letter) > 1 Or SNum > 7 Or SNum < 2 Then
MsgBox "Please enter a single letter A - F"
GoTo SelectOpt:
End If
Application.ScreenUpdating = False
'make selection visible
Sheets(SNum).Visible = True
'hide sheet(1)
Sheets(1).Visible = False
Application.ScreenUpdating = True
On Error GoTo 0
End Sub

 

[Micron]

IF it should ever be that a user unhides a sheet and saves and closes wb in that state, that code will show the selected sheet but the visible sheet will remain visible, yes?
I was thinking that after input validation, first set the input name (or a number relating to its name such as 2 = Sheet2) visible then just loop over the sheets and hide where sheet name <> input name. That way, any sheets that were unhidden by the user will be hidden. IMO, no point in providing code for that only to find out it doesn't fit the input method that hasn't been revealed, but that's just me.

 

[chobanne]

Sub Choose() On Error Resume Next 'set Options relating to sheet number and include sheet name ? OptA = " Opion A - " & Sheets(2).Name OptB = " Opion B - " & Sheets(3).Name OptC = " Opion C - " & Sheets(4).Name OptD = " Opion D - " & Sheets(5).Name OptE = " Opion E - " & Sheets(6).Name OptF = " Opion F - " & Sheets(7).Name 'Get user selection SelectOpt: Letter = InputBox(OptA & vbCrLf & OptB & vbCrLf & OptC & vbCrLf & OptD & vbCrLf & OptE & vbCrLf & OptF & vbCrLf _ "Please Enter The Option Letter For The Sheet You Wish To Open ") Letter = Trim(Letter) SNum = Asc(Trim(UCase(Letter))) - 63 'test is single letter in range If Len(Letter) > 1 Or SNum > 7 Or SNum < 2 Then MsgBox "Please enter a single letter A - F" GoTo SelectOpt: End If Application.ScreenUpdating = False 'make selection visible Sheets(SNum).Visible = True 'hide sheet(1) Sheets(1).Visible = False Application.ScreenUpdating = True On Error GoTo 0 End Sub

I got error mesage when i start macro like this compile error syntax error and the line bellow is bolded

Letter = InputBox(OptA & vbCrLf & OptB & vbCrLf & OptC & vbCrLf & OptD & vbCrLf & OptE & vbCrLf & OptF & vbCrLf _
"Please Enter The Option Letter For The Sheet You Wish To Open ")

 

[chobanne]

IF it should ever be that a user unhides a sheet and saves and closes wb in that state, that code will show the selected sheet but the visible sheet will remain visible, yes?
I was thinking that after input validation, first set the input name (or a number relating to its name such as 2 = Sheet2) visible then just loop over the sheets and hide where sheet name <> input name. That way, any sheets that were unhidden by the user will be hidden. IMO, no point in providing code for that only to find out it doesn't fit the input method that hasn't been revealed, but that's just me.

Actually, my idea for macro was as what i wrote. If there is some other solution, its ok for me

 

[Micron]

If there is some other solution, its ok for me

If your sheets are named like "Sheet1" (no spaces) and you want to hide all but the user input (option) and if the user can input a number from 2 to 7 (corresponds with Sheet1 thru Sheet7) then perhaps this:

Public Sub ShowAsheet()
Dim i As Integer, strIn As String
Dim ws As Worksheet

strIn = InputBox("Enter a value from 2 to 7", "CHOOSE A SHEET TO VIEW")
If Not strIn = "" Then
   i = CInt(strIn)
   If i > 1 And i < 8 Then
      Application.ScreenUpdating = False
      Sheets("Sheet" & i).Visible = True 'must ensure 1 sheet is visible, else code will error
      For Each ws In Sheets
         If ws.Name <> "Sheet" & i Then
            ws.Visible = False
         Else
            ws.Visible = True
         End If
      Next
   Else
      MsgBox "Only numbers from 2 to 7 are allowed."
      Exit Sub
   End If
Else
   MsgBox "No number value was provided."
   Exit Sub
End If
Application.ScreenUpdating = True

End Sub

This could also be coded to use the code name of the sheet (what you see in the vb objects pane; e.g. Sheet1 (Customers), however it's less likely that the user always knows the code name of a sheet.

 

[Snakehips]

I got error mesage when i start macro like this compile error syntax error and the line bellow is bolded

Letter = InputBox(OptA & vbCrLf & OptB & vbCrLf & OptC & vbCrLf & OptD & vbCrLf & OptE & vbCrLf & OptF & vbCrLf _
"Please Enter The Option Letter For The Sheet You Wish To Open ")

@chobanne My apologies. Somehow, a comma had gone missing. Below is revised with comma included.

Sub Choose()
On Error Resume Next
'set Options relating to sheet number and include sheet name ?
OptA = "      Opion A - " & Sheets(2).Name
OptB = "      Opion B - " & Sheets(3).Name
OptC = "      Opion C - " & Sheets(4).Name
OptD = "      Opion D - " & Sheets(5).Name
OptE = "      Opion E - " & Sheets(6).Name
OptF = "      Opion F - " & Sheets(7).Name

'Get user selection
SelectOpt:
Letter = InputBox(OptA & vbCrLf & OptB & vbCrLf & OptC & vbCrLf & OptD & vbCrLf & OptE & vbCrLf & OptF & vbCrLf, _
"Please Enter The Option Letter For The Sheet You Wish To Open   ")
Letter = Trim(Letter)
SNum = Asc(Trim(UCase(Letter))) - 63
'test is single letter in range
If Len(Letter) > 1 Or SNum > 7 Or SNum < 2 Then
MsgBox "Please enter a single letter A - F"
GoTo SelectOpt:
End If
Application.ScreenUpdating = False
'make selection visible
Sheets(SNum).Visible = True
'hide sheet(1)
Sheets(1).Visible = False
Application.ScreenUpdating = True
On Error GoTo 0
End Sub

The above code may not represent the prettiest nor the most comprehensive sheet selection system.
However, it was hoped that it reasonably mirrors your stated requirement and, might provide you with, if not a complete solution, something to work on.
Please do not hesitate to come back to me if you feel that I can be of any further assistance.

 

[chobanne]

Thank you all, that is exactly what i want