Code help needed for Option Buttons to control Subs

[Brian]

I have a User form with (20) option buttons on it. It does what I want by
only being able to check 1 option button.

Option Button Names:
OB_601
OB_602 - OB_619
OB_620

What I need help with is when user checks the correct option button, that
button exicutes code located in a module.

Code in Module: There are (20) Subs named like below
Sub String_01()
Sub String_02() - Sub String_19()
Sub String_20()

I think the code for each Option button is something like this, because
there default position is False and they only show true when selected.

If OB_601 = True
then call Sub String_01()
If OB_602 = True
then call Sub String_02()
If OB_603 = True
then call Sub String_03()

Etc....
End Sub

 

[OssieMac]

Hi Brian,

While in Design mode if you right click on the option button and select View
Code it will take you to the VBA editor for the forms and create a Sub / End
sub. I would put the code in there like the following instead of in another
module .

Note that If Me.OB_601 is the same as
If Me.OB_601 = True

Private Sub OB_601_Click()
If Me.OB_601 Then
'Your code here
End If
End Sub

Private Sub OB_602_Click()
If Me.OB_602 Then
'Your code here
End If
End Sub

Private Sub OB_603_Click()
If Me.OB_603 Then
'Your code here
End If
End Sub

However, you can call the code in a standard module if that is what you want
to do but you will still need a sub for each of the option buttons. Example
as follows.

Private Sub OB_601_Click()
If Me.OB_601 Then
Call String_01
End If
End Sub

You normally only put code in a separate module if the same code is being
used for all the options with only minor changes dependant on the particular
button. You would call the sub and pass the option button as a parameter.
Without seeing all of your code it is hard to advise if this is the better
way.

 

[Brian]

What I did was created 20 different macros. Each macro is quite intailed in
what it does. I have 4 worksheets in a workbook called forms. Each of the 4
Worksheets has 20 pages in it. Depending on the option button chosen it
formats each of of the 4 worksheets for that number of pages.

Example: User picks OB_605, that tells me that each of of the 4 worksheets
needs 5 pages, so it calls sub String_05. That macro formats the 4 worksheet
for 5 pages each and hides the rest. Then it sets the print area, etc..

That way when the workbook is printed it only shows and prints the nuber of
page needed.

Here is one of the marcos I recorded: I bet there is even a short way of
cleaning up this code.

'************************************************************
'01 String "Update Forms"
'***********************************************************
Sub String_01()

With Workbooks("Installer Forms.xlsm").Sheets("Install Pack Con")

ActiveWindow.ScrollWorkbookTabs Position:=xlLast
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Batt Chg Rpt").Select
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1:O48").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$O$48"
Range("O3:O4").Select
ActiveCell.FormulaR1C1 = "1"
Rows("49:960").Select
Selection.EntireRow.Hidden = True
ActiveWindow.SmallScroll Down:=-42
Range("G6:H7").Select
Sheets("Pilot Cell Chg Rpt").Select
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1:G67").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$G$67"
ActiveWindow.SmallScroll Down:=60
Rows("68:1340").Select
Selection.EntireRow.Hidden = True
ActiveWindow.SmallScroll Down:=-66
Range("B11:C11").Select
Sheets("Press Test Rpt").Select
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1:I1").Select
ActiveCell.FormulaR1C1 = "PRESSURE TEST RECORD"
Range("A1:I75").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$I$75"
ActiveWindow.SmallScroll Down:=60
Rows("76:1499").Select
ActiveWindow.SmallScroll Down:=-3
ActiveWindow.LargeScroll Down:=-21
ActiveWindow.SmallScroll Down:=60
Rows("76:1500").Select
Selection.EntireRow.Hidden = True
ActiveWindow.LargeScroll Down:=-1
Range("B11:E11").Select
Sheets("Batt Strap Res Rpt").Select
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1:J69").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$J$69"
ActiveWindow.SmallScroll Down:=66
Rows("70:1380").Select
Selection.EntireRow.Hidden = True
ActiveWindow.LargeScroll Down:=-1
Range("H11:J11").Select
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Install Pack Con").Select
End With

End Sub


Thanks

 

[Brian]

Would this code work or do I need to have 20 seperate subs like the one you
showed me?

Private Sub OB_601-620_Click()
If Me.OB_601 Then
Call String_01
End If
If Me.OB_602 Then
Call String_02
End If
If Me.OB_603 Then
Call String_03
End If
If Me.OB_604 Then
Call String_04
End If
End Sub

Thanks for your help

 

[OssieMac]

Hi Brian,

I don't know of any way of grouping the code into one sub. You will need
separate subs for each button.

I have often thought that there should be an event to detect a change in the
group status and a Target parameter to identify the button that is True.

 

[Brian]

here is something interesting. I was just palying around with the code you
posted and came up with this by accident. Believe it or not it works perfect.

In main User Form Code Window:
'*******************************************************
'Update_Update_Installer_Forms_10 Control Button
'*******************************************************
Private Sub Update_Installer_Forms_10_Click()
'Located in M10 Misc Codes
Call Update_Installer_Forms4
End Sub


In Misc Codes Module:
'************************************************************
'Update_Installer_Forms_10 Control Button
'Update from User Form to Installer Forms
'Updates Battery Sheets based on Qty Choosen
'************************************************************
Sub Update_Installer_Forms4()

'Battery_String_01
If UserForm1.Battery_String_Qty_601 Then
Call Battery_String_01
End If

Code for 02-19 is here

'Battery_String_20
If UserForm1.Battery_String_Qty_620 Then
Call Battery_String_20
End If
End Sub

In the Battery String Code Module:
'************************************************************
'01 String "Update Forms"
'***********************************************************
Sub String_01()

With Workbooks("Installer Forms.xlsm").Sheets("Install Pack Con")

ActiveWindow.ScrollWorkbookTabs Position:=xlLast
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Batt Chg Rpt").Select
Cells.Select
Selection.EntireRow.Hidden = False
Range("A1:O48").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$O$48"

Rest of code posted in previous post + 19 More code subs.

 

[OssieMac]

Hi again Brian,

Not certain that I understand correctly. Is Update_Installer_Forms_10 a
command button?

If so and that is the way you want to go by making the selection then
clicking a command button then you can use a loop and Run command and
concatenate the sub names to be called.

For i = 601 To 620
On Error GoTo NoSelection
If UserForm1("Battery_String_Qty_" & i) Then
On Error GoTo 0 'Reset error trapping
Run "Battery_String_" & i
Exit For
End If
Next i

Exit Sub

NoSelection:
MsgBox "No buttons selected." & vbLf _
& "Processing terminated."
Exit Sub

However, the above code uses sub names Battery_String_601 to
Battery_String_620. That is the method I would go with and keep the numeric
part of the names the same but you could change to the following and just use
the last 2 digits like the following.

Dim i As Long

For i = 601 To 620
On Error GoTo NoSelection
If UserForm1("Battery_String_Qty_" & i) Then
On Error GoTo 0 'Reset error trapping
Run "Battery_String_" & Mid(i, 2)
Exit For
End If
Next i

Exit Sub

NoSelection:
MsgBox "No buttons selected." & vbLf _
& "Processing terminated."
Exit Sub