Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Repetitive IF statement
Excellent, thanks very much..!
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Repetitive macros | Excel Discussion (Misc queries) | |||
Repetitive Numbers | Excel Discussion (Misc queries) | |||
Repetitive keystroke | Excel Discussion (Misc queries) | |||
repetitive | Excel Discussion (Misc queries) | |||
Repetitive IF THEN Statement | Excel Programming |