Radio Buttons Created Dynamically

[David Billigmeier]

I'm trying to program a macro to create x number of radio buttons, depending
on what number I specify for x, and place each one at the top of a column.
When clicked, the radio button would highlight the row below it, and also
un-highlight the column with the previously checked radio button... any ideas?
Thanks

 

[Dave Peterson]

You may want to try Chip Pearson's addin:
http://www.cpearson.com/excel/RowLiner.htm

It's not quite what you want, but may be sufficient.

If you really want the highlighting and option buttons stuff:

Option Explicit
Sub SetupOneTimeOnly()

Dim GrpBox As GroupBox
Dim OptBtn As OptionButton
Dim myRng As Range
Dim myCell As Range
Dim wks As Worksheet
Dim iCtr As Long
Dim MaxBtns As Long

Set wks = Worksheets("Sheet1")

With wks
MaxBtns = Application.InputBox(prompt:="how many OptionButtons", _
Type:=1)
If MaxBtns = 0 Then
Exit Sub
End If

If MaxBtns > .Columns.Count Then
MsgBox "Too many!"
Exit Sub
End If

'clean up existing junk
.GroupBoxes.Delete
.OptionButtons.Delete

Set myRng = .Range("A1").Resize(1, MaxBtns)

With myRng
Set GrpBox = .Parent.GroupBoxes.Add _
(Top:=.Top, Left:=.Left, _
Height:=.Height, Width:=.Width)
End With

With GrpBox
.Caption = ""
.Visible = True 'False
.Name = "GRP_" & myRng.Cells(1).Address(0, 0)
End With

For iCtr = 1 To MaxBtns
With myRng.Cells(iCtr)
Set OptBtn = .Parent.OptionButtons.Add _
(Top:=.Top, Left:=.Left, _
Height:=.Height, Width:=.Width)
OptBtn.Caption = ""
OptBtn.Name = "OPT_" & .Address(0, 0)
OptBtn.OnAction _
= "'" & ThisWorkbook.Name & "'!ChangeAllBtns"
End With
Next iCtr

End With
End Sub
Sub ChangeAllBtns()

Dim OptBtn As OptionButton

With ActiveSheet
Set OptBtn = .OptionButtons(Application.Caller)
.Cells.Interior.ColorIndex = xlNone
End With

OptBtn.TopLeftCell.EntireColumn.Interior.ColorIndex = 6

End Sub

Be aware that any fill color in any cell will be lost and then the selected
column will be shaded.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[David Billigmeier]

Hi Dave,
Thanks, this worked great! I just have one follow up quesiton, how would I
modify the code if I wanted to also create a "Reset" radio button somewhere
on the sheet, that when clicked would un-highlight everything, is that an
easy update?

--
Regards,
Dave

You may want to try Chip Pearson's addin:
http://www.cpearson.com/excel/RowLiner.htm

It's not quite what you want, but may be sufficient.

If you really want the highlighting and option buttons stuff:

Option Explicit
Sub SetupOneTimeOnly()

Dim GrpBox As GroupBox
Dim OptBtn As OptionButton
Dim myRng As Range
Dim myCell As Range
Dim wks As Worksheet
Dim iCtr As Long
Dim MaxBtns As Long

Set wks = Worksheets("Sheet1")

With wks
MaxBtns = Application.InputBox(prompt:="how many OptionButtons", _
Type:=1)
If MaxBtns = 0 Then
Exit Sub
End If

If MaxBtns > .Columns.Count Then
MsgBox "Too many!"
Exit Sub
End If

'clean up existing junk
.GroupBoxes.Delete
.OptionButtons.Delete

Set myRng = .Range("A1").Resize(1, MaxBtns)

With myRng
Set GrpBox = .Parent.GroupBoxes.Add _
(Top:=.Top, Left:=.Left, _
Height:=.Height, Width:=.Width)
End With

With GrpBox
.Caption = ""
.Visible = True 'False
.Name = "GRP_" & myRng.Cells(1).Address(0, 0)
End With

For iCtr = 1 To MaxBtns
With myRng.Cells(iCtr)
Set OptBtn = .Parent.OptionButtons.Add _
(Top:=.Top, Left:=.Left, _
Height:=.Height, Width:=.Width)
OptBtn.Caption = ""
OptBtn.Name = "OPT_" & .Address(0, 0)
OptBtn.OnAction _
= "'" & ThisWorkbook.Name & "'!ChangeAllBtns"
End With
Next iCtr

End With
End Sub
Sub ChangeAllBtns()

Dim OptBtn As OptionButton

With ActiveSheet
Set OptBtn = .OptionButtons(Application.Caller)
.Cells.Interior.ColorIndex = xlNone
End With

OptBtn.TopLeftCell.EntireColumn.Interior.ColorIndex = 6

End Sub

Be aware that any fill color in any cell will be lost and then the selected
column will be shaded.

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[Dave Peterson]

I would put a button (not an optionbutton!) from the forms toolbar on the sheet
(near the optionbuttons???).

Assign it this macro:

Option Explicit
Sub ResetAll()
Dim OptBtn As OptionButton
With ActiveSheet
.Cells.Interior.ColorIndex = xlNone
For Each OptBtn In .OptionButtons
OptBtn.Value = False
Next OptBtn
End With
End Sub

It'll turn off all the optionbuttons you have on the worksheet. The code will
need to be modified if that's a problem.

Hi Dave,
Thanks, this worked great! I just have one follow up quesiton, how would I
modify the code if I wanted to also create a "Reset" radio button somewhere
on the sheet, that when clicked would un-highlight everything, is that an
easy update?