InputBox with a Variable: Convert Decimal separator from dot to comma

[Heder_Santos]

Hello everyone!

I'm having an issue with an Inputbox, and I'd really appreciate if someone could help to me solve this...

I have some procedures to calculate values, but the calculations will depend on a value inserted by the user: the daily US Dollar exchange rate.

I've dedicated a cell in a separated worksheet to store the last US Dollar value inserted by the user, and when the user starts a calculation procedure, it loads this last value to a public variable ("LastUSDQuot"), which then is displayed as the default value in an InputBox. The user can change the value whenever he wants, or maintain the last value for convenience (5 other procedures will use this exchange rate as well).

The problem:

I need this value to be displayed in the inputbox with a comma as a decimal separator. The value is stored in the cell with a comma, and when it loads to the variable, it's also displayed with the comma. But when it's showed in the inputbox, there's a dot in there, which can lead to unreal results in the calculations.

Please, how can I solve this programmatically? (I've read some things on changing the windows configurations, but I want the code to work independently from this).

The Code:

Sub A

'Calls Procedure to Insert USD Quotation
Call USDQuot_Load

Sub USDQuot_Load()

'Checks If LastUSDQuot is correctly loaded/stored
If LastUSDQuot = 0 Then LastUSDQuot = ActiveWorkbook.Worksheets("Z_Bases").Range("AI5").Value

'Loads variable "USDQuot" with last value or user inserted value
USDQuot = Application.InputBox("Use "","" (Vírgula) para separar as casas decimais.", "INSIRA A COTAÇÃO DO DÓLAR.", LastUSDQuot)

End Sub

(Back to Sub A)

'Terminates Procedure If "USDQuot" isn't properly loaded.
If USDQuot = False Then
MsgBox "Geração de Novo Relatório Abortada. O Último Relatório Disponível foi Mantido.", , "Operação Cancelada pelo Usuário."
Exit Sub
End If

Thank you for your help!

 

[John_w]

Try one of these:

    USDQuot = Application.InputBox("Prompt", "Title", Replace(LastUSDQuot, ".", ","))
    USDQuot = InputBox("Prompt", "Title", LastUSDQuot)

 

[Heder_Santos]

John, your solution is great, worked perfectly! Thank you!

However, in the meantime between my post and your response, I made a workaround with a Userform, that solved this issue also.

I'm posting it here in case someone may be interested in it:

Sub A()

'Calls Procedure to Insert USD Quotation
Call USDQuot_Load

Sub USDQuot_Load()

'Checks If LastUSDQuot is correctly loaded
LastUSDQuot = ActiveWorkbook.Worksheets("Z_Bases").Range("AI5").Value

'Displays Userform to Insert USD quotation
frm_USD_QUOT.Show

USERFORM PRIVATE SUBS!

Private Sub BTN_CANCEL_Click()

CancelQuotFRM = 1

Unload Me

Exit Sub

End Sub

Private Sub BTN_OK_Click()

'Multiplies TextBox content to convert it to Number
USDQuot = Me.TB_QUOT * 1

Unload Me

End Sub

Private Sub UserForm_Initialize()

Me.TB_QUOT = LastUSDQuot

End Sub

Back to Sub USDQuot_Load()

'Updates LastUSDQuot Cell if USDQuot is different from previous LastUSDQuot
If USDQuot <> LastUSDQuot And CancelQuotFRM = 0 Then
ActiveWorkbook.Worksheets("Z_Bases").Range("AI5") = USDQuot
End If

End Sub

Back to Sub A()

'Terminates Procedure If "USDQuot" isn't properly loaded.
If CancelQuotFRM = 1 Then
MsgBox "Geração de Novo Relatório Abortada. O Último Relatório Disponível foi Mantido.", , "Operação Cancelada pelo Usuário."
CancelQuotFRM = 0
Exit Sub
End If

 

[Heder_Santos]

I'm facing problems to upload an image of the userform....