danmclane
04-15-2013, 02:55 PM
Hello gang. first post.
I have some great code that places multiple check boxes into a sheet.
I love this code because it works with a selection.
I need to change the code to create grouped rows of radio buttons, and to also link the radio buttons to cells on a seperate sheet.
Here is the code I have for making check boxes:
Sub AddCheckBoxes()
On Error Resume Next
Dim c As Range, myRange As Range
Set myRange = Selection
For Each c In myRange.Cells
ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height).Select
With Selection
.LinkedCell = c.Address
.Characters.Text = ""
.Name = c.Address
End With
c.Select
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & c.Address & "=TRUE"
.FormatConditions(1).Font.ColorIndex = 4 'change for other color when ticked
.FormatConditions(1).Interior.ColorIndex = 4 'change for other color when ticked
.Font.ColorIndex = 2 'cell background color = White
End With
Next
myRange.Select
End Sub
here is some code to make the radio buttons but it does work with a selection (hard coded for rang), and also does not link the radio buttons to seperate sheet cells:
Sub fill_range_with_optionbuttons5()
Dim rownr As Long, i As Integer
Dim counter As Long
Dim rng As Range
Dim txt(4) As String
Dim nm(4) As String
'replace each occurence of 22 by 800
Set rng = Range("A1:d22") 'changed to d22
txt(1) = "R"
txt(2) = "V"
txt(3) = "a" 'added
txt(4) = "b" 'added
nm(1) = "RRR" 'single R or V impossible "R1" is invalid name (= cellreference)
nm(2) = "VVV"
nm(3) = "zzz" 'added
nm(4) = "xxx" 'added
Application.ScreenUpdating = False
Rows("1:22").RowHeight = 18
For rownr = 1 To 22
counter = 0
For i = 1 To 4 'change here
counter = counter + 1
Set rng = Cells(rownr, i)
ActiveSheet.OLEObjects.Add(ClassType:="Forms.OptionButton.1", Link:=False, _
DisplayAsIcon:=False, Left:=rng.Left, Top:=rng.Top, Width:=rng.Width, Height:=rng.Height).Name = nm(i) & rownr
With ActiveSheet.Shapes(nm(i) & rownr).OLEFormat.Object.Object
.Caption = txt(i)
'.LinkedCell = Cells(rownr, 3).Address
'Cells(rownr, 3) = False
.GroupName = "grp" & rownr
End With
Next i
Next rownr
Application.ScreenUpdating = True
End Sub
I need someone smarter than me to help me out here.
To sum up what I need: select cell range in excel, create grouped radio buttons for each of the rows (variable from 2 - infinity), link those buttons to a seperate sheet (say links).
hope you can help
I have some great code that places multiple check boxes into a sheet.
I love this code because it works with a selection.
I need to change the code to create grouped rows of radio buttons, and to also link the radio buttons to cells on a seperate sheet.
Here is the code I have for making check boxes:
Sub AddCheckBoxes()
On Error Resume Next
Dim c As Range, myRange As Range
Set myRange = Selection
For Each c In myRange.Cells
ActiveSheet.CheckBoxes.Add(c.Left, c.Top, c.Width, c.Height).Select
With Selection
.LinkedCell = c.Address
.Characters.Text = ""
.Name = c.Address
End With
c.Select
With Selection
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=" & c.Address & "=TRUE"
.FormatConditions(1).Font.ColorIndex = 4 'change for other color when ticked
.FormatConditions(1).Interior.ColorIndex = 4 'change for other color when ticked
.Font.ColorIndex = 2 'cell background color = White
End With
Next
myRange.Select
End Sub
here is some code to make the radio buttons but it does work with a selection (hard coded for rang), and also does not link the radio buttons to seperate sheet cells:
Sub fill_range_with_optionbuttons5()
Dim rownr As Long, i As Integer
Dim counter As Long
Dim rng As Range
Dim txt(4) As String
Dim nm(4) As String
'replace each occurence of 22 by 800
Set rng = Range("A1:d22") 'changed to d22
txt(1) = "R"
txt(2) = "V"
txt(3) = "a" 'added
txt(4) = "b" 'added
nm(1) = "RRR" 'single R or V impossible "R1" is invalid name (= cellreference)
nm(2) = "VVV"
nm(3) = "zzz" 'added
nm(4) = "xxx" 'added
Application.ScreenUpdating = False
Rows("1:22").RowHeight = 18
For rownr = 1 To 22
counter = 0
For i = 1 To 4 'change here
counter = counter + 1
Set rng = Cells(rownr, i)
ActiveSheet.OLEObjects.Add(ClassType:="Forms.OptionButton.1", Link:=False, _
DisplayAsIcon:=False, Left:=rng.Left, Top:=rng.Top, Width:=rng.Width, Height:=rng.Height).Name = nm(i) & rownr
With ActiveSheet.Shapes(nm(i) & rownr).OLEFormat.Object.Object
.Caption = txt(i)
'.LinkedCell = Cells(rownr, 3).Address
'Cells(rownr, 3) = False
.GroupName = "grp" & rownr
End With
Next i
Next rownr
Application.ScreenUpdating = True
End Sub
I need someone smarter than me to help me out here.
To sum up what I need: select cell range in excel, create grouped radio buttons for each of the rows (variable from 2 - infinity), link those buttons to a seperate sheet (say links).
hope you can help