looping optionbuttons in userform??

[direktoren]

Hi all

I've added a currency converter (as a pop-up userform) to one of my existing macros. At first I only used 1 base value (eg. it could only convert USD -> EUR, not the other way). Now I would like to make it go both ways - and I have 5 different currencies.

What I thought would work is this;

Private Sub CommandButton1_Click()
Dim x As Long
Dim i As Long
Dim j As Long
Dim vSheet As Worksheet
Dim dSheet As Worksheet
Dim oOption As MSForms.OptionButton
 
Set vSheet = ActiveWorkbook.Worksheets("Valuta")
Set dSheet = ActiveWorkbook.Worksheets("Data")
 
Application.ScreenUpdating = False
 
For Each oOption In FrmCur.Frame1
    With oOption
        If .Value = True Then
            For x = 2 To 6
                If Opt1.Caption = vSheet.Cells(x, 1).Value Then
                With vSheet
                    For i = 6 To dSheet.Cells(1, 5).End(xlToRight).Column
                    If Not Right(.Cells(1, i).Value, 3) = "pct" Then
                                vSheet.Activate
                                vSheet.Cells(x, 2).Copy
                                dSheet.Activate
                                dSheet.Range(Cells(2, i), Cells(2, i).End(xlDown)).PasteSpecial Paste:=xlValues, operation:=xlMultiply
                                
                    End If
                    Next i
                End With
                End If
            Next x
        End If
    End With
Next oOption
 
For Each oOption In FrmCur.Frame2
    With oOption
        If .Value = True Then
            For x = 2 To 6
                If Opt1.Caption = vSheet.Cells(x, 1).Value Then
                With vSheet
                    For i = 6 To dSheet.Cells(1, 5).End(xlToRight).Column
                    If Not Right(.Cells(1, i).Value, 3) = "pct" Then
                                vSheet.Activate
                                vSheet.Cells(x, 2).Copy
                                dSheet.Activate
                                dSheet.Range(Cells(2, i), Cells(2, i).End(xlDown)).PasteSpecial Paste:=xlValues, operation:=xlDivide
                                
                    End If
                    Next i
                End With
                End If
            Next x
        End If
    End With
Next oOption
 
FrmCur.Hide
 
Application.ScreenUpdating = True
 
End Sub

However this line;

For Each oOption In FrmCur.Frame2

is giving me problems. I know I can use it on a spreadsheet (browsing OLEObjects) - so can't I also use in a userform?

Any thoughts are welcome

[DKenny]

direktoren

When looping through any set of controls in a frame you must use the Controls collection for the frame. This means that you will need to Dim your object variable generically as a control rather than specifically as an OptionButton. You can use the TypeName function to then limit to the type of control you want the loop to handle.

For example, this piece of code will not work

Dim oOpt As OptionButton
    
    For Each oOpt In Me.Frame1.OptionButtons
        'Do some stuff
    Next oOpt

But this code will

Dim oCont As Control
 
    For Each oCont In Me.Frame1.Controls
        If TypeName(oCont) = "OptionButton" Then
            'Do some stuff
        End If
    Next oCont

So in your code, you will need to change the DIM statement for the oOption object to

Dim oOption As MSForms.Control

[direktoren]

Hi Declan

Thanks for the quick response - as usual...and as usual I must have done something wrong again! I don't get any errors now, but nothing happens either I think this piece might be causing the nothingness to happen!;

For x = 2 To 6
                If .Caption = vSheet.Cells(x, 1).Value Then
                With vSheet
                    For i = 6 To dSheet.Cells(1, 5).End(xlToRight).Column
                    If Not Right(.Cells(1, i).Value, 3) = "pct" Then

But I can't see what the problem should be... Perhaps the

.caption = vSheet.Cells(x,1).value

part?

Nicolaj