align radio buttons to row instead of using coordinates?

nodding dino

Board Regular
Joined
Aug 5, 2003
Messages
94
Hi,

Does anyone know if it is possible to align radio buttons to the row *without* manually entering the coordinates OR dragging into position.

The reason is, I have a varying number of rows in a block of data and would like to create a macro that will add 3 radio buttons alongside each row. Becuase the number of rows will vary I will use a loop, and as such will not know the correct coordinates to align properly, what I really need is to say something like 'add radio button to H9'

any ideas?

Thanks!
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

nodding dino

Board Regular
Joined
Aug 5, 2003
Messages
94
a piccie might help explain it a bit better...!
smoke and mirrors.xls
ABCDEFGH
1SupplierNameInvoiceDateTotalAmountVATAmountSupplierCodeCertifyAuthoriseDispute
2ASupplierNameHere07010518.102.70XNNNNRadio1Radio2Radio3
3AnotherSupplierName07010514112.172101.81XNNNNRadio1Radio2Radio3
4andanother07010518.102.70XNNNN
Sheet3
 

Andy Pope

Active Member
Joined
Dec 31, 2003
Messages
349
Hi nodding dino,

Here some code that will add a optionbutton to a sheet using the ranges location to position it.

Code:
Sub Test()
'
' Add Optionbutton, form Forms toolbar, to sheet
'
    Dim objRadio1 As OptionButton
    
    Set objRadio1 = AddRadioFormsType(Range("H9"), "optTest1", "Hello")
    
End Sub

Function AddRadioFormsType(MyRng As Range, Name As String, Text As String) As OptionButton
'
' Add Option button sized to specified range
'
    Set AddRadioFormsType = MyRng.Parent.OptionButtons.Add( _
                            MyRng.Left, MyRng.Top, MyRng.Width, MyRng.Height)
    AddRadioFormsType.Name = Name
    AddRadioFormsType.Caption = Text
    
End Function

Cheers
Andy
 

nodding dino

Board Regular
Joined
Aug 5, 2003
Messages
94
FANTASTIC!!!

I was just about to bump the thread after failing miserably to get any further with the problem for hours...when I saw your reply!!!

KUDOS ANDY! :pray:
 

nodding dino

Board Regular
Joined
Aug 5, 2003
Messages
94

ADVERTISEMENT

OK...slight hiccup...!

I now have my three buttons on every row of data, but it only lets you select one of the many buttons - not just in the row of three but on the entire worksheet.

I need to be able to select one radio button on *each* row...

any thoughts?
 

Andy Pope

Active Member
Joined
Dec 31, 2003
Messages
349

ADVERTISEMENT

Hi,

In order to get around the problem here is some revised code that uses the option buttons from the Control toolbox. These controls have a Groupname property.

Code:
 Sub Test()
'
' Add Optionbutton, form Forms toolbar, to sheet
'
    Dim objRadio1 As OLEObject
   
    Set objRadio1 = AddRadioButton(Range("H9"), "optTest091", "One", "Group1")
    Set objRadio1 = AddRadioButton(Range("I9"), "optTest092", "Two", "Group1")
    Set objRadio1 = AddRadioButton(Range("J9"), "optTest093", "Three", "Group1")
    Range("I9") = True     ' selects second option button
   
    Set objRadio1 = AddRadioButton(Range("H10"), "optTest101", "One", "Group2")
    Set objRadio1 = AddRadioButton(Range("I10"), "optTest102", "Two", "Group2")
    Set objRadio1 = AddRadioButton(Range("J10"), "optTest103", "Three", "Group2")
    Range("J10") = True     ' select third option button
   
End Sub

Function AddRadioButton(MyRng As Range, Name As String, Text As String, Groupname As String) As OLEObject
'
' Add Option button sized to specified range
'
    Set AddRadioButton = MyRng.Parent.OLEObjects.Add( _
                    ClassType:="Forms.OptionButton.1", Link:=False, DisplayAsIcon:=False, _
                    Left:=MyRng.Left, Top:=MyRng.Top, Width:=MyRng.Width, Height:=MyRng.Height)
    
    With AddRadioButton
        .Name = Name
        .LinkedCell = MyRng.Cells(1, 1).Address
        With .Object
            .Caption = Text
            .Groupname = Groupname
            .Value = False
        End With
    End With
    
End Function

FYI to make the forms controls group by row you would need to enclose the option buttons within a frame control.
 

nodding dino

Board Regular
Joined
Aug 5, 2003
Messages
94
Andy, thats great!

I have amended the code slightly so that it sits in a loop and runs for the required amount of rows. The only problem seems to be with some of the formatting...the TRUE/FALSE state was showing from behind the option buttons, so I have added a line to change this text to white to get around that problem!

Would it be possible for you to explain this line to me?

Code:
Left:=MyRng.Left, Top:=MyRng.Top, Width:=MyRng.Width, Height:=MyRng.Height

I understand that you are setting some of the properties that position the button within the cell, I just wonder what other options I can use instead, for example, if I remove the caption "One" (and use a heading instead) can I then reduce the size assigned to where the text would go and instead centralise the button within the cell?

thanks for sticking with it Andy...we're on the home straight!!!
 

Andy Pope

Active Member
Joined
Dec 31, 2003
Messages
349
Hi,

This code sizes the option button to be exactly the same size as the range MyRng.

Code:
Left:=MyRng.Left, Top:=MyRng.Top, Width:=MyRng.Width, Height:=MyRng.Height

This revision will autosize the buttons to their captions and then center them within the cell range.

Code:
Sub Test()
'
' Add Optionbutton, form Forms toolbar, to sheet
'
    Dim objRadio1 As OLEObject
    Dim rngTemp As Range
    Dim strName As String
    Dim strBaseName As String
    Dim intCount As Integer
    Dim strHeading(3) As String
    
    strHeading(1) = "Heading One"
    strHeading(2) = "Heading Two"
    strHeading(3) = "Heading Three"
    
    strBaseName = "optTest"
    For Each rngTemp In Range("H9:J9")
        intCount = intCount + 1
        strName = strBaseName & Format(rngTemp.Row, "00") & intCount
        Set objRadio1 = AddRadioButton(rngTemp, strName, strHeading(intCount), "Group1")
        ' center option button within cell
        With objRadio1
            .Object.BackColor = 255 ' red background so you can see buttons
            .Object.AutoSize = True
            .Top = rngTemp.Top + ((rngTemp.Height - .Height) / 2)
            .Left = rngTemp.Left + ((rngTemp.Width - .Width) / 2)
        End With
    Next
    
End Sub

Cheers
Andy
 

nodding dino

Board Regular
Joined
Aug 5, 2003
Messages
94
just to finish this thread (in case anyone ever finds it in a search!)...I struggled to integrate the last lot of Andy's code into my own, but got around the problem another (albeit not as elegant!) way...

I removed the caption from the button creation code and set the columns that they would be created in to a very small width, having created all the option buttons (via a loop) I added the column titles and formatted the columns to the correct width. The result of this is a series of option buttons without a visible caption (this effectively keeps the width of the button to the size of the circle itself), but aligned to the very left of the cell. I then simply recorded a macro of me moving the button into the centre of the cell and then copied this single line of code into a second loop.

hope that makes sense....!

A BIG thankyou to Andy! :biggrin:
 

Forum statistics

Threads
1,148,053
Messages
5,744,528
Members
423,881
Latest member
Nguyen Vu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top