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

[code=vb]
Private Sub Workbook_Open()

Worksheets("Rat es").Activate

'GBP to Euro

Dim varInput As String

varInput = InputBox("Pleas e enter today's rates from GBP to EUR", "Exchange Rates", "e.g. 0.765")

Range("C11").Se lect

If(varInput = "") Then
Selection.Value = Range("C11").Va lue

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("Pleas e enter today's rates from GBP to USD", "Exchange Rates", "e.g. 0.765")

Range("C12").Se lect

If(varInputa = "") Then
Selection.Value = Range("C12").Va lue

Elseif(varInput a = "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("Pleas e enter today's rates from GBP to YEN", "Exchange Rates", "e.g. 0.765")

Range("C13").Se lect

If(varInputb = "") Then
Selection.Value = Range("C13").Va lue

Elseif(varInput b = "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("Pleas e enter today's rates from GBP to CAD", "Exchange Rates", "e.g. 0.765")

Range("C14").Se lect

If(varInputc = "") Then
Selection.Value = Range("C14").Va lue

Elseif(varInput c = "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("Pleas e enter today's rates from GBP to AUD", "Exchange Rates", "e.g. 0.765")

Range("C15").Se lect

If(varInputd = "") Then
Selection.Value = Range("C15").Va lue

Elseif(varInput d = "e.g. 0.765") Then
MsgBox "Please enter a proper value.", vbOkOnly, "System Message"

Else

Selection.Value = varInputd
End If
End Sub
[/code]

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("Rat es").Activate

'GBP to Euro
strDefault = "Please enter today's rates from GBP to EUR"
StartOver:
varInput = InputBox(strDef ault, "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").Va lue = varInput
End If