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 :afrog:

[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