align radio buttons to row instead of using coordinates?

[nodding dino]

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!

 

[nodding dino]

a piccie might help explain it a bit better...!

smoke and mirrors.xls
	A	B	C	D	E	F	G	H
1	SupplierName	InvoiceDate	TotalAmount	VATAmount	SupplierCode	Certify	Authorise	Dispute
2	ASupplierNameHere	070105	18.10	2.70	XNNNN	Radio1	Radio2	Radio3
3	AnotherSupplierName	070105	14112.17	2101.81	XNNNN	Radio1	Radio2	Radio3
4	andanother	070105	18.10	2.70	XNNNN
Sheet3

 

[Andy Pope]

Hi nodding dino,

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

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]

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! ray:

 

[nodding dino]

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?

 

[nodding dino]

^^bump^^






(thanks!)

 

[Andy Pope]

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.

 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]

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?

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]

Hi,

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

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.

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]

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!