Repetitive IF statement
 

Excuse my extremely novice post, I've been looking through google
groups to find the answer to my problem but haven't got very far..

I want to simplify the below IF statement:

Private Sub CommandButton11_Click()

' Selection of component type
If OptionButton1 = True Then
Sheets("GIT XP CLIENT STATS").Visible = True
Sheets("GROUP IT SUPPORTED XP CLIENT").Visible = True
Else
If OptionButton2 = True Then
Sheets("GIT SILO STATS").Visible = True
Sheets("GROUP IT SUPPORTED SILO SERVER").Visible = True
Else
If OptionButton3 = True Then
Sheets("GIT FARM STATS").Visible = True
Sheets("GROUP IT SUPPORTED SERVER FARM").Visible = True
Else
If OptionButton4 = True Then
Sheets("NGIT XP CLIENT STATS").Visible = True
Sheets("NON-GIT SUPPORTED XP CLIENT").Visible = True
Else
If OptionButton5 = True Then
Sheets("NGIT SILO STATS").Visible = True
Sheets("NON-GIT SUPPORTED SILO SERVER").Visible = True
Else
If OptionButton6 = True Then
Sheets("NGIT FARM STATS").Visible = True
Sheets("NON-GIT SUPPORTED SERVER FARM").Visible = True
Else
If OptionButton7 = True Then
Sheets("SW XP CLIENT STATS").Visible = True
Sheets("SHRINK WRAPPED XP CLIENT").Visible = True
Else
If OptionButton8 = True Then
Sheets("SW SILO STATS").Visible = True
Sheets("SHRINK WRAPPED SILO SERVER").Visible = True
Else
If OptionButton9 = True Then
Sheets("SW FARM STATS").Visible = True
Sheets("SHRINK WRAPPED SERVER FARM").Visible = True
Else
If OptionButton10 = True Then
Sheets("BUNDLE STATS").Visible = True
Sheets("BUNDLE").Visible = True
Else
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
Sheets("START").Visible = False
ActiveSheet.Range("A1").Select
ActiveCell = "Component Name: " & TextBox1.Text
End Sub

This above Sub works but is very long winded. This also only populates
the active sheet with the text entered into the textbox in cell A1 - I
want to populate a cell on each sheet.

I am unsure how to do this as the sheet name changes depending on the
selected option button.

Any help is much appreciated..

Repetitive IF statement
 

"This also only populates the active sheet with the text entered into the
textbox in cell A1 - I want to populate a cell on each sheet."
You can always loop through the sheets (For Each WS in
ThisWorkboook.Worksheets) to populate the cells. If you mean you only want
to populate the cells on the visible sheets, you can do it this way:
Dim WS as Worksheet
.... ' put your current code here except the last 2 lines
For Each WS in ThisWorkbook.Worksheets
If WS.Visible then WS.Range("A1").Value = "Componene Name: " &
TextBox1.Text
Next WS

As for simplifying the existing code: That is tough since no matter how you
approach it you need to "map" your buttons to particular sheets by name. You
have to do this by typing all the names into code unless you could set up the
buttons and the sheets so there is a "connection" between their names. For
example, if your sheets were named "1: GIT XP CLIENT STATS", "1: GROUP IT
SUPPORTED XP CLIENT", "2: GIT SILO STATS", "2: GROUP IT SUPPORTED SILO
SERVER", etc. then you could use the fact that the number on the button
matched the number on the sheet to have VBA figure out which sheets to make
visible with something like the following:

Dim ThisControl as Control, ThisSheet as Worksheet, ThisNumber as String
For Each ThisControl in UserForm1.Controls
' Look for the option buttons:
If ThisControl.Name liike "OptiionButton#" Then
' Extract the number from the button's name:
ThisNumber = Replace(ThisControl.Name,"OptionButton","")
' Look for the corresponding worksheets:
For Each ThisSheet in ThisWorkbook.Worksheets
' The following line sees if the sheet name begins with the
current button #
' and uses the answer (true, false) to set the sheet visible
ThisSheet.Visible = ThisSheet.Name like ThisNumber & ":*"
Next ThisSheet
End If
Next ThisControl

Simpler, but would require you to change the sheet names.
--
- K Dales


" wrote:

Excuse my extremely novice post, I've been looking through google
groups to find the answer to my problem but haven't got very far..

I want to simplify the below IF statement:

Private Sub CommandButton11_Click()

' Selection of component type
If OptionButton1 = True Then
Sheets("GIT XP CLIENT STATS").Visible = True
Sheets("GROUP IT SUPPORTED XP CLIENT").Visible = True
Else
If OptionButton2 = True Then
Sheets("GIT SILO STATS").Visible = True
Sheets("GROUP IT SUPPORTED SILO SERVER").Visible = True
Else
If OptionButton3 = True Then
Sheets("GIT FARM STATS").Visible = True
Sheets("GROUP IT SUPPORTED SERVER FARM").Visible = True
Else
If OptionButton4 = True Then
Sheets("NGIT XP CLIENT STATS").Visible = True
Sheets("NON-GIT SUPPORTED XP CLIENT").Visible = True
Else
If OptionButton5 = True Then
Sheets("NGIT SILO STATS").Visible = True
Sheets("NON-GIT SUPPORTED SILO SERVER").Visible = True
Else
If OptionButton6 = True Then
Sheets("NGIT FARM STATS").Visible = True
Sheets("NON-GIT SUPPORTED SERVER FARM").Visible = True
Else
If OptionButton7 = True Then
Sheets("SW XP CLIENT STATS").Visible = True
Sheets("SHRINK WRAPPED XP CLIENT").Visible = True
Else
If OptionButton8 = True Then
Sheets("SW SILO STATS").Visible = True
Sheets("SHRINK WRAPPED SILO SERVER").Visible = True
Else
If OptionButton9 = True Then
Sheets("SW FARM STATS").Visible = True
Sheets("SHRINK WRAPPED SERVER FARM").Visible = True
Else
If OptionButton10 = True Then
Sheets("BUNDLE STATS").Visible = True
Sheets("BUNDLE").Visible = True
Else
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
Sheets("START").Visible = False
ActiveSheet.Range("A1").Select
ActiveCell = "Component Name: " & TextBox1.Text
End Sub

This above Sub works but is very long winded. This also only populates
the active sheet with the text entered into the textbox in cell A1 - I
want to populate a cell on each sheet.

I am unsure how to do this as the sheet name changes depending on the
selected option button.

Any help is much appreciated..

Repetitive IF statement
 

Excellent, thanks very much..!

Repetitive IF statement
 

One way:

Private Sub CommandButton11_Click()

Select Case

Case OptionButton1 = True


Sheets("GIT XP CLIENT STATS").Visible = True
Sheets("GROUP IT SUPPORTED XP CLIENT").Visible = True

Case OptionButton2 = True

Sheets("GIT SILO STATS").Visible = True
Sheets("GROUP IT SUPPORTED SILO SERVER").Visible = True

Case OptionButton3 = True

Sheets("GIT FARM STATS").Visible = True
Sheets("GROUP IT SUPPORTED SERVER FARM").Visible = True

Case OptionButton4 = True

Sheets("NGIT XP CLIENT STATS").Visible = True
Sheets("NON-GIT SUPPORTED XP CLIENT").Visible = True

Case OptionButton5 = True

Sheets("NGIT SILO STATS").Visible = True
Sheets("NON-GIT SUPPORTED SILO SERVER").Visible = True

Case OptionButton6 = True

Sheets("NGIT FARM STATS").Visible = True
Sheets("NON-GIT SUPPORTED SERVER FARM").Visible = True

Case OptionButton7 = True

Sheets("SW XP CLIENT STATS").Visible = True
Sheets("SHRINK WRAPPED XP CLIENT").Visible = True

Case OptionButton8 = True

Sheets("SW SILO STATS").Visible = True
Sheets("SHRINK WRAPPED SILO SERVER").Visible = True

Case OptionButton9 = True

Sheets("SW FARM STATS").Visible = True
Sheets("SHRINK WRAPPED SERVER FARM").Visible = True

Case OptionButton10 = True

Sheets("BUNDLE STATS").Visible = True
Sheets("BUNDLE").Visible = True

End Case

Sheets("START").Visible = False
ActiveSheet.Range("A1").Select
ActiveCell = "Component Name: " & TextBox1.Text
End Sub