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

Heder_Santos

New Member
Joined
Jun 5, 2015
Messages
12
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,999
Try one of these:
Code:
    USDQuot = Application.InputBox("Prompt", "Title", Replace(LastUSDQuot, ".", ","))
    USDQuot = InputBox("Prompt", "Title", LastUSDQuot)
 

Heder_Santos

New Member
Joined
Jun 5, 2015
Messages
12
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
 
Last edited:

Forum statistics

Threads
1,082,342
Messages
5,364,783
Members
400,815
Latest member
gangstar67

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top