conditional formating

[jmathew]

I have a workbook with hundreds of option buttons in it. Is there any way to write vb code to change and/or tell what the cell link is to the option button. For example when you right click on the option button and then click on format control; and then in the tab Control there is a cell link box. Is there a way to reset this or manually assign?

 

[erik.van.geit]

Hi,

you can do that using something like

ActiveSheet.Shapes("Option Button 1").OLEFormat.Object.LinkedCell = "$A$1"

you will need a loop to change all optionbuttons
(I suppose you've put them in frames to group them)
is there some pattern to follow ?

kind regards,
Erik

 

[Damon Ostrander]

Hi jmathew,

Here is a macro that will list all the option buttons (Forms type only) on a worksheet and the linked cell for each.

Sub ListOptionButtons()

   Dim OBsheet    As Worksheet
   Dim ListSheet  As Worksheet
   Dim OB         As OptionButton
   Dim iRow       As Long
   
   Set OBsheet = ActiveSheet
   Set ListSheet = Worksheets.Add(after:=OBsheet)
   
   With ListSheet
      .[A1] = "Index"
      .[B1] = "Name"
      .[C1] = "Caption"
      .[D1] = "Cell Link"
      iRow = 2
      For Each OB In OBsheet.OptionButtons
         .Cells(iRow, "A") = OB.Index
         .Cells(iRow, "B") = OB.Name
         .Cells(iRow, "C") = OB.Caption
         .Cells(iRow, "D") = OB.LinkedCell
         iRow = iRow + 1
      Next OB
      .Rows(1).Font.Bold = True
      .Columns("A:D").AutoFit
   End With
   
End Sub

[/code]

 

[jmathew]

Thanks. This is very useful for checking to see if they are coded correctly.

My problem is I have to make changes to them and I have to go into the properties and change them and it is taking forever. Is there any way around that? The option buttons are in frames. There are two option buttons for each frame. The column which displays the value of the option button is in column Q which has the formula =IF($IV6=1,"1","0"), etc.

 

[erik.van.geit]

please provide some feedback on my post

you told us you wanted to change the linkedcell
I posted code to do that: did you test it ?

I asked if there is a pattern to follow
example:
1st button A1
2nd button A11
3rd button A21

greetings,
Erik