[RESOLVED] Excel Userform problems!
Hi
I've been trying to make a currency converter, but I can't get it to function correctly!?
What I have is;
VB Code:
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??
Re: Excel Userform problems!
want to attach your workbook??
pete
Re: Excel Userform problems!
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...
Re: Excel Userform problems!
Your problem is in this line of code:
VB 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:
VB Code:
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).
VB Code:
If TypeName(oOption) = "OptionButton" Then
'...or
If Left(oOption[COLOR=Red].Name[/COLOR], 3) = "opt" Then
Re: Excel Userform problems!
Hi
Tried substitutting as you said, but still - nothing happens, no errors, nothing...