[RESOLVED] Excel Userform problems!

[direktoren]

Hi

I've been trying to make a currency converter, but I can't get it to function correctly!?

What I have is;

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 sSheet As Worksheet
Dim oOption As MSForms.Control
 
Set vSheet = ActiveWorkbook.Worksheets("Valuta")
Set dSheet = ActiveWorkbook.Worksheets("Data")
Set sSheet = ActiveWorkbook.Worksheets("Start")
 
Application.ScreenUpdating = False
 
For Each oOption In Me.Frame1.Controls
  If TypeName(oOption) = Left(oOption.Caption, 3) = "opt" Then
    With oOption
        If .Value = True Then
            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
                                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
  End If
Next oOption
 
For Each oOption In Me.Frame2.Controls
  If TypeName(oOption) = Left(oOption.Caption, 3) = "opt" Then
    With oOption
        If .Value = True Then
            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
                                vSheet.Activate
                                vSheet.Cells(x, 2).Copy
                                dSheet.Activate
                                dSheet.Range(Cells(2, i), Cells(2, i).End(xlDown)).PasteSpecial Paste:=xlValues, operation:=xlDivide
                                sSheet.Cells(27, 7).Value = oOption.Caption
                    End If
                    Next i
                End With
                End If
            Next x
        End If
    End With
  End If
Next oOption
 
FrmCur.Hide
 
Application.ScreenUpdating = True
 
End Sub

I don't get any errors - but it doesn't do anything either! in other words - its useless!!

Any ideas??

[westconn1]

want to attach your workbook??

pete

[direktoren]

I can't

first off, the data is confidential - and secondly we don't have a zip program at work (for some odd reason), and it is some 7 MB total.

I can try to describe it though
I have 3 sheets;
Start - The only visible sheet where the user is able to make some different selections - including the currency converter.

Data - consist of all the data. columns 6 to 30 consist of numbers, some of which are percentages, these are marked with an pct as the last 3 letters and should not be converted (obviously)

Valuta - 2 columns, one with the currency shortname (e.g. EUR) and the other with the exchange rate.

hope this helps you...

[Comintern]

Your problem is in this line of code:

If TypeName(oOption) = Left(oOption.Caption, 3) = "opt" Then

The TypeName function returns the class name of an object passed to it. So, if oOption is an OptionButton with "Dollars" in the caption, your if statement would look like this:

If "OptionButton" = "Dol" = "opt" Then

This will always evaluate to false, even if you take out the Left call, because the TypeName of the option button will never equal "opt". You should probably use one of the following two tests instead (I'd use the first).

If TypeName(oOption) = "OptionButton" Then
'...or
If Left(oOption[COLOR=Red].Name[/COLOR], 3) = "opt" Then

[direktoren]

Hi

Tried substitutting as you said, but still - nothing happens, no errors, nothing...