VBA InputBox

[Rashed.R]

Hi All,

I am facing problem in taking input from user with VBA InputBox:
1. I want to take input (Cross Rate) from the user.
2. The Cross Rates are
i. EUR/USD
ii. USD/JPY
iii. GBP/USD
iv. USD/CAD
v. AUD/USD
vi. USD/CHF
vii. USD/BDT
3. The Cross Rates are Numbers with decimals. For example: EUR/USD = 1.3475, USD/JPY = 89.57, GBP/USD = 1.5358, USD/CAD = 1.0559, AUD/USD = 0.8888, USD/CHF = 1.0857, USD/BDT = 69.63
4. At first, the user must be given the option whether he wants to input cross rate. If user chooses to input cross rate, an input box is to prompt to take user input.
5. If the user chooses Cancel Button of the input box, ask the user whether he wants to exit. If chooses to exit, EXIT SUB. If user chooses to continue, an input box is to prompt to take user input.
6. If user enters nothing (Zero lenth string) and/or chooses OK Button of the InputBox, Prompt user nothing is entered and prompt an input box to take user input
7. If user enters Other than Numbers, Prohibit user to input. Ask user to re-input Cross Rate (Numbers with decimals)
The above sequence must be present in taking input for each of the below cross rates:
i. EUR/USD
ii. USD/JPY
iii. GBP/USD
iv. USD/CAD
v. AUD/USD
vi. USD/CHF
vii. USD/BDT
8. After user inputs valid Cross Rate, input data in certain cells in two Workbooks. The first workbook contains the macro. The macro is to open the second workbook and paste the input into certain cells.
I am currently using the folllowing code. This would give you hint in what sequence the program is to run.

Sub CrossRate()
Dim iEUR, iJPY, iGBP, iCAD, iAUD, iCHF, iCHF1 As Integer
Dim iBDT As String
Dim response As Long
response = MsgBox(prompt:="Are You Sure to Input Cross Rate?", Buttons:=vbYesNo, Title:="Foreign Exchange Position")
If response = vbYes Then
iEUR = Application.InputBox("Enter EUR/USD Rate", "Cross Rate Input-EUR/USD")
Sheets("Ledger").Activate
Range("E27").Value = iEUR
Range("I27").Value = iEUR
Range("E29").Value = iEUR
Range("I29").Value = iEUR
Range("E31").Value = iEUR
Range("I31").Value = iEUR
Range("E33").Value = iEUR
Range("I33").Value = iEUR
Range("E43").Value = iEUR
Range("I43").Value = iEUR
iJPY = Application.InputBox("Enter USD/JPY Rate", "Cross Rate Input-USD/JPY")
Sheets("Ledger").Activate
Range("E61").Value = iJPY
Range("I61").Value = iJPY
iGBP = Application.InputBox("Enter GBP/USD Rate", "Cross Rate Input-GBP/USD")
Sheets("Ledger").Activate
Range("E23").Value = iGBP
Range("I23").Value = iGBP
Range("E25").Value = iGBP
Range("I25").Value = iGBP
iCAD = Application.InputBox("Enter USD/CAD Rate", "Cross Rate Input-USD/CAD")
Sheets("Ledger").Activate
Range("E35").Value = iCAD
Range("I35").Value = iCAD
iAUD = Application.InputBox("Enter AUD/USD Rate", "Cross Rate Input-AUD/USD")
Sheets("Ledger").Activate
Range("E37").Value = iAUD
Range("I37").Value = iAUD
iCHF = Application.InputBox(prompt:="Enter USD/CHF Rate", Title:="Cross Rate Input-USD/CHF", Type:=1 + 2)
Sheets("Ledger").Activate
Range("E59").Value = iCHF
Range("I59").Value = iCHF
iBDT = Application.InputBox(prompt:="Enter B.B. Weighted Average Rate-USD/BDT Rate", Title:="Weighted Average Rate Input-USD/BDT", Type:=2)
Sheets("Ledger").Activate
Range("L4").Value = iBDT
Range("F67").Select
ActiveWorkbook.Save
If Not IsFileOpen("E:\BB_SUBMISSION-FE26\ANNEXURE-A.xls") Then
Workbooks.Open "E:\BB_SUBMISSION-FE26\ANNEXURE-A.xls"
End If
Workbooks("ANNEXURE-A.xls").Activate
Sheets("Ledger").Activate
Range("E26").Value = iEUR
Range("I26").Value = iEUR
Range("E28").Value = iEUR
Range("I28").Value = iEUR
Range("E30").Value = iEUR
Range("I30").Value = iEUR
Range("E32").Value = iEUR
Range("I32").Value = iEUR
Range("E42").Value = iEUR
Range("I42").Value = iEUR
Range("E64").Value = iJPY
Range("I64").Value = iJPY
Range("E68").Value = iJPY
Range("I68").Value = iJPY
Range("E22").Value = iGBP
Range("I22").Value = iGBP
Range("E24").Value = iGBP
Range("I24").Value = iGBP
Range("E34").Value = iCAD
Range("I34").Value = iCAD
Range("E36").Value = iAUD
Range("I36").Value = iAUD
Range("E66").Value = iCHF
Range("I66").Value = iCHF
Range("L4").Value = iBDT
Range("F73").Select
ActiveWorkbook.Save
ElseIf response = vbNo Then Exit Sub
End If
End Sub