How to execute a macro when user selects radiobutton

Hi,

I have a button named "Convert" that performs some formatting. I have few radio buttons below in which the user can select his option.

When user selects radiobutton1 and click the Convert button radiobutton1 function should execute. similarly if radiobutton 2 is selected i want that function to excute on clicking the convert button.

I tried assigning macro to radiobutton it excutes on radio button click.

But i want it to execute only after clicking on the convert button.

Try something like the following code (tested using Excel 2003):

In the UserForm1 code module:

Private Sub CommandButtonConvert_Click()
  Call ProcessConvert
End Sub

In an ordinary code module:

Sub ProcessConvert()

  'NOTE: The '= True' is NOT NEEDED, but I like to use it for readability.

  If UserForm1.OptionButton1 = True Then
    Call RadioButton1ConvertRoutine
  ElseIf UserForm1.OptionButton2 = True Then
    Call RadioButton2ConvertRoutine
  End If

End Sub

Sub RadioButton1ConvertRoutine()
  MsgBox "RadioButton1ConvertRoutine() stub"
End Sub

Sub RadioButton2ConvertRoutine()
  MsgBox "RadioButton2ConvertRoutine() stub"
End Sub

Thanks. Is it possible to user the optionbutton on cells without the userform

No, you can't use 'Option Buttons' on cells without the Userform to the best of my knowledge.

However, You can SIMULATE 'Option Buttons' by using Shapes, and assign macros to the shapes.
See the attached workbook written and tested using Excel 2003, which I expanded to 3 Simulated Option Buttons, and uses code very
similar to what existed with the Userform.


In the 'ThisWorkbook' module an optional initialization can be used:

Private Sub Workbook_Open()
  Call SimulatedOptionButtonInitialization
End Sub

I arbitrarily divided the rest of the code into two modules:
ModSimulatedOptionButtons - which simulates the Option Buttons and interfaces with the Worksheet.
ModConvert - which contains code similar the original.

Code for ModSimulatedOptionButtons follows:

Option Explicit

Private SimulatedOptionButton1 As Boolean
Private SimulatedOptionButton2 As Boolean
Private SimulatedOptionButton3 As Boolean

Private Const SimulatedOptionButton1_CELL = "E9"
Private Const SimulatedOptionButton2_CELL = "E10"
Private Const SimulatedOptionButton3_CELL = "E11"

'''''''''''''''''''''''''''''''''''''''''''''''
'Get Routines - available for use by outside routines
'''''''''''''''''''''''''''''''''''''''''''''''
Function GetSimulatedOptionButton1Value() As Boolean
  GetSimulatedOptionButton1Value = SimulatedOptionButton1
End Function

Function GetSimulatedOptionButton2Value() As Boolean
  GetSimulatedOptionButton2Value = SimulatedOptionButton2
End Function

Function GetSimulatedOptionButton3Value() As Boolean
  GetSimulatedOptionButton3Value = SimulatedOptionButton3
End Function


'''''''''''''''''''''''''''''''''''''''''''''''
'Set Routines
'''''''''''''''''''''''''''''''''''''''''''''''
Private Sub SetAllSimulatedOptionButtonsFalse()
  SimulatedOptionButton1 = False
  SimulatedOptionButton2 = False
  SimulatedOptionButton3 = False
  Range(SimulatedOptionButton1_CELL) = False
  Range(SimulatedOptionButton2_CELL) = False
  Range(SimulatedOptionButton3_CELL) = False
End Sub


Sub SetSimulatedOptionButton1()
  If SimulatedOptionButton1 = True Then
    'do nothing - if true - already selected
  Else
    Call SetAllSimulatedOptionButtonsFalse
    SimulatedOptionButton1 = True
    Range(SimulatedOptionButton1_CELL) = True
  End If
  
End Sub
Sub SetSimulatedOptionButton2()
  If SimulatedOptionButton2 = True Then
    'do nothing - if true - already selected
  Else
    Call SetAllSimulatedOptionButtonsFalse
    SimulatedOptionButton2 = True
    Range(SimulatedOptionButton2_CELL) = True
  End If
  
End Sub
Sub SetSimulatedOptionButton3()
  If SimulatedOptionButton3 = True Then
    'do nothing - if true - already selected
  Else
    Call SetAllSimulatedOptionButtonsFalse
    SimulatedOptionButton3 = True
    Range(SimulatedOptionButton3_CELL) = True
  End If
  
End Sub


'''''''''''''''''''''''''''''''''''''''''''''''
'Other Routines
'''''''''''''''''''''''''''''''''''''''''''''''
Sub SimulatedOptionButtonInitialization()
  'Excel VBA initializes all Booleans to 'False'
  'If you want to initialize a value to 'True' this
  'routine should contain the 'True' value for the one
  'Simulated Option button in the GROUP you want true.
  'This Function is called from the Workbook_Open()
  'code area in 'ThisWorkbook'.

  Range(SimulatedOptionButton1_CELL) = False
  Range(SimulatedOptionButton2_CELL) = False
  Range(SimulatedOptionButton3_CELL) = False
  Call SetSimulatedOptionButton2
End Sub

Code for ModConvert follows:

Sub ProcessConvert()

  'NOTE: The '= True' is NOT NEEDED, but I like to use it for readability.
  'With 3 choices - 'Select Case' is probably better than an 'If'

  If GetSimulatedOptionButton1Value() = True Then
    Call RadioButton1ConvertRoutine
  ElseIf GetSimulatedOptionButton2Value() = True Then
    Call RadioButton2ConvertRoutine
  ElseIf GetSimulatedOptionButton3Value() = True Then
    Call RadioButton3ConvertRoutine
  End If

End Sub

Sub RadioButton1ConvertRoutine()
  MsgBox "RadioButton1ConvertRoutine() stub"
End Sub

Sub RadioButton2ConvertRoutine()
  MsgBox "RadioButton2ConvertRoutine() stub"
End Sub

Sub RadioButton3ConvertRoutine()
  MsgBox "RadioButton3ConvertRoutine() stub"
End Sub

I added another sheet to the Workbook, which has 'Shapes' that have the
look and feel of UserForm Option Buttons. See the attached workbook.

I did this by adding two Oval 'Shapes' that look like circles for each
simulated Option Button required. The name of each 'outer circle' is irrelevant.
The name of each 'inner (filled in) circle' is IMPORTANT because it is used by
the 'Event Handler' (Macro) to make the 'inner circle' visible or NOT visible.
I assigned the same Macro to each inner/outer circle pair.

I had to rename a couple of the 'Shapes' from their original names which looked
like "Oval 8" to "SimulatedOptionButton1B". The following code is included in
'ModZTest' in the workbook.

First I had to identify all the 'Shape' names:

Sub LookAtShapeCharacteristics()
  'This was used to look through the 'Shapes' to find
  'the 'Shapes' I was interested in (3 small filled in circles).
  '
  'The Original Names were 'Oval 7', 'Oval 8', and 'Oval 9'.

  Dim Sh As Object

  For Each Sh In ActiveSheet.Shapes
    Debug.Print
    Debug.Print "Shape Name:    " & Sh.Name
    Debug.Print "Shape Type:    " & Sh.Type
    Debug.Print "Shape Id:      " & Sh.ID
    Debug.Print "Shape Locked:  " & Sh.Locked
    Debug.Print "Shape Visible: " & Sh.Visible
    Debug.Print "Shape Top:     " & Sh.Top
    Debug.Print "Shape Left:    " & Sh.Left
    Debug.Print "Shape Height:  " & Sh.Height
    Debug.Print "Shape Width:   " & Sh.Width
    Debug.Print "Shape ZOrderPosition:  " & Sh.ZOrderPosition
    Debug.Print "Shape AlternativeText: " & Sh.AlternativeText
    
  Next Sh
  
End Sub

Then I had to rename the 'inner circle' 'Shapes'.

Sub ModifyShapes()
  'This was used to modify the Names of the 3 small filled in circles
  'to 'SimulatedOptionButton1B', 'SimulatedOptionButton2B' and 'SimulatedOptionButton3B'.
  '
  'The names of the LARGE CIRCLES are irrelevant.
  '
  'The changes can be verified by running LookAtShapeCharacteristics() again.
  
  ActiveSheet.Shapes("Oval 7").Name = "SimulatedOptionButton1B"
  ActiveSheet.Shapes("Oval 8").Name = "SimulatedOptionButton2B"
  ActiveSheet.Shapes("Oval 9").Name = "SimulatedOptionButton3B"
End Sub