Generate sheet by running macro through hidden sheets

[Cycs1516]

Hello peeps,

I need some help here. When the COMMS & GO box are checked, and I click on Generate Form, it will move to the "COMMSGO" Sheet. I manage to do until here. But however, I just it to show only the "COMMSGO" sheet and the rest hidden. Same goes if i do it for other sheets. How do I run macro on the hidden sheets if I hide it, and only appear the sheet that I want when the "Generate Form" is pressed. I have searched through the forum, but I didnt manage to find any solution.

Help is much appreciated and thank you!

[/IMG]


My formula is as below.

Sub SelectSheet1()
Dim sheetName As String
If CheckBox1.Value = True Then sheetName = Range("B6").Value
If CheckBox2.Value = True Then sheetName = sheetName & Range("B7").Value
If CheckBox3.Value = True Then sheetName = sheetName & Range("B8").Value
If CheckBox4.Value = True Then sheetName = sheetName & Range("B9").Value
Worksheets(sheetName).Select
End Sub

Sub SelectSheet2()
Dim sheetName As String
If CheckBox1.Value = False Then
If CheckBox2.Value = True Then sheetName = Range("B7").Value
If CheckBox3.Value = True Then sheetName = sheetName & Range("B8").Value
If CheckBox4.Value = True Then sheetName = sheetName & Range("B9").Value
Worksheets(sheetName).Select
End If
End Sub

Sub SelectSheet3()
Dim sheetName As String
If CheckBox1.Value = False Then
If CheckBox2.Value = False Then
If CheckBox3.Value = True Then sheetName = Range("B8").Value
If CheckBox4.Value = True Then sheetName = sheetName & Range("B9").Value
Worksheets(sheetName).Select
End If
End If
End Sub

Sub SelectSheet4()
Dim sheetName As String
If CheckBox1.Value = False Then
If CheckBox2.Value = False Then
If CheckBox3.Value = False Then
If CheckBox4.Value = True Then sheetName = Range("B9").Value
Worksheets(sheetName).Select
End If
End If
End If
End Sub

Sub SelectSheet5()
Dim sheetName As String
If CheckBox1.Value = True Then sheetName = Range("B6").Value
If CheckBox2.Value = False Then
If CheckBox3.Value = False Then
If CheckBox4.Value = True Then sheetName = sheetName & Range("B9")
Worksheets(sheetName).Select
End If
End If
End Sub

Sub RunAll()
Call SelectSheet1
Call SelectSheet2
Call SelectSheet3
Call SelectSheet4
Call SelectSheet5
End Sub

 

[Fluff]

Hi & welcome to MrExcel
How about

Sub SelectSht()

   Dim ShtNm As String
   Dim Ws As Worksheet
   Dim Cnt As Long
   Dim cbx As OLEObject
   
   For Each Ws In Worksheets
      If Ws.Name <> "Generate Form" Then Ws.Visible = xlSheetHidden
   Next Ws
   Cnt = 5
   For Each cbx In ActiveSheet.OLEObjects
      If TypeName(cbx.Object) = "CheckBox" Then
         Cnt = Cnt + 1
         If cbx.Object = True Then ShtNm = ShtNm & Range("B" & Cnt).Value
      End If
   Next cbx
   With Sheets(ShtNm)
      .Visible = True
      .Activate
   End With
End Sub

This replaces all your code & assumes that you are using ActiveX checkboxes

 

[Cycs1516]

Omg, thanks fluff, it worked perfectly. But is there anyway to hide the "Generate Form" Tab also after generating. Many thanks!

 

[Fluff]

Just add this

   Sheets("Generate Form").Visible = xlSheetHidden

Before the End Sub

 

[Cycs1516]

Thank you! It had been solved. Many thanks and best wishes to you!

 

[Fluff]

Glad to help & thanks for the feedback