Excel VBA help needed.

[helraizer1]

Hi folks,

I am making a spreadsheet for a foreign exchange bureau, on the loading of the page it asks the operator to add the day's exchange rates.

I have the following code in Excel VBA

Private Sub Workbook_Open()
 
Worksheets("Rates").Activate
 
'GBP to Euro
 
Dim varInput As String
 
varInput = InputBox("Please enter today's rates from GBP to EUR", "Exchange Rates", "e.g. 0.765")
 
Range("C11").Select
 
If(varInput = "") Then
Selection.Value = Range("C11").Value
 
Elseif(varInput = "e.g. 0.765") Then
MsgBox "Please enter a proper value.", vbOkOnly, "System Message"
 
Else
 
Selection.Value = varInput
 
End If
 
'GBP to American Dollars
 
Dim varInput As String
 
varInputa = InputBox("Please enter today's rates from GBP to USD", "Exchange Rates", "e.g. 0.765")
 
Range("C12").Select
 
If(varInputa = "") Then
Selection.Value = Range("C12").Value
 
Elseif(varInputa = "e.g. 0.765") Then
MsgBox "Please enter a proper value.", vbOkOnly, "System Message"
 
Else
 
Selection.Value = varInputa
End If
 
'GBP to Japanese Yen
 
 
Dim varInputb As String
 
varInputb = InputBox("Please enter today's rates from GBP to YEN", "Exchange Rates", "e.g. 0.765")
 
Range("C13").Select
 
If(varInputb = "") Then
Selection.Value = Range("C13").Value
 
Elseif(varInputb = "e.g. 0.765") Then
MsgBox "Please enter a proper value.", vbOkOnly, "System Message"
 
Else
 
Selection.Value = varInputb
End If
 
'GBP to Canadian Dollars
 
 
 
Dim varInputc As String
 
varInputc = InputBox("Please enter today's rates from GBP to CAD", "Exchange Rates", "e.g. 0.765")
 
Range("C14").Select
 
If(varInputc = "") Then
Selection.Value = Range("C14").Value
 
Elseif(varInputc = "e.g. 0.765") Then
MsgBox "Please enter a proper value.", vbOkOnly, "System Message"
 
Else
 
Selection.Value = varInputc
End If
 
'GBP to Australian Dollars
 
 
 
Dim varInputd As String
 
varInputd = InputBox("Please enter today's rates from GBP to AUD", "Exchange Rates", "e.g. 0.765")
 
Range("C15").Select
 
If(varInputd = "") Then
Selection.Value = Range("C15").Value
 
Elseif(varInputd = "e.g. 0.765") Then
MsgBox "Please enter a proper value.", vbOkOnly, "System Message"
 
Else
 
Selection.Value = varInputd
End If
End Sub
 

That works perfectly, but at the moment if the value is the default value of the InputBox ("e.g. 0.765") it just throws up the error message and then moves on to the next part.

How would I make it so that, without having to repeat the first bit of code (inputbox code) each time, if the elseif statement is executed it gives the error message (msgbox) and then prompts them again to enter a value for the same rate?

Hope that makes sense,
Sam

[ubentook]

Here is one way...

Private Sub Workbook_Open()

Dim varInput As String
Dim varInputa As String
Dim varInputb As String
Dim strDefault As String

Worksheets("Rates").Activate

'GBP to Euro
strDefault = "Please enter today's rates from GBP to EUR"
StartOver:
varInput = InputBox(strDefault, "Exchange Rates", "e.g. 0.765")

If (varInput = "") Then
Exit Sub ' user cancelled

ElseIf Val(varInput) = 0 Then
strDefault = "Please Try Again - Enter Today's GBP/EUR Rate "
GoTo StartOver

Else
Range("C11").Value = varInput
End If