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.
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")
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
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")
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
