Numerical Values Via User Form Text Box

[Denny57]

I understand that by default a User Form TextBox will populate cells in a worksheet in text format. I appreciate information in textboxes can be formatted in various numeric and currency formats. I need for information to be added to a database which will allow for the values to be recognised by Conditional Formatting in the destination cells.

I have used the following to format the details but this does not meet Conditional Formatting conditions.

Private Sub txtMorning_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Me.txtMorning = Format(Me.txtMorning, "#,##0.0")
End Sub

I believe that using CDbl formatting might assist.

Is this a possible solution or will this still fail?
If it is possible, should thbe code be active at the time the textbox is populated (as above) or if it should happen in the command button module?
If it is possible, please could someone assist with the code I need to convert the keyed information to a suitable format.

I really must look to undertake an Intermediate / advanced VBA course

Many thanks

 

[Micron]

Perhaps it's because Format function returns a string, so your values are text data type. You could try wrapping your format function in any conversion function that provides the number type that you need:

CDbl(Format(Me.txtMorning, "#,##0.0"))
Re: #1 - the code is not running when the control is "populated" it is running when you leave it, and that will happen regardless of what's in it - important distinction.
Re: #2 - I don't think it will matter except that as per above comment, using conversion functions with nulls will raise an error, so you might want to exit sub immediately if cell is null/empty/zls (zero length string, aka "")

 

[Denny57]

Perhaps it's because Format function returns a string, so your values are text data type. You could try wrapping your format function in any conversion function that provides the number type that you need:

CDbl(Format(Me.txtMorning, "#,##0.0"))
Re: #1 - the code is not running when the control is "populated" it is running when you leave it, and that will happen regardless of what's in it - important distinction.
Re: #2 - I don't think it will matter except that as per above comment, using conversion functions with nulls will raise an error, so you might want to exit sub immediately if cell is null/empty/zls (zero length string, aka "")

Micron

Thank you fopr your response

I still have some issues which I am hoping are as a result of incorrect syntax.

Here is a sample of the code

Private Sub txtMorning_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If IsEmpty(txtMorning) Then
Exit Sub

Else
Me.txtMorning = CDbl(Format(Me.txtMorning, "#,##0.0"))
End If
End Sub

Issue 1
As you noted, with Null / Empty Cells the code fails at Me.txtMorning = CDbl(Format(Me.txtMorning, "#,##0.0"))
So I expect that firstly there is something wrong with the Null Check

Issue 2
If there is a numeric value entered into the text box then I still get an incorrect Conditional Formatting result in the WS cells.

Issue 3
Most Confusing is that I have a series of formulas that use the values in the cells (O4,P4 & Q4 are being used to test input at present)
=IF(SUM(O4+P4+Q4)=0," ",(O4+P4+Q4)/3) provides the average of the 3 readings to be input during the day in Cell R4 - Works absolutely fine
However another formula (in the same ws) and which uses the same cells does not recognise the values in these cells. So I really need to find either correct syntax or another way to populate cells inworksheets with numerical values.

Hopefully there may be a simple solution, however, I wish that there was some clearly defined way to use form controls to get numerical values in to Excel as after all it is a tool for calculations. Everywhere I look I can never seem to find clear explanations and examples. As a novice with VBA I find this very frustrating.

Any suggestions or a solution would be most welcome

 

[Denny57]

Micron

Thank you fopr your response

I still have some issues which I am hoping are as a result of incorrect syntax.

Here is a sample of the code

Private Sub txtMorning_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If IsEmpty(txtMorning) Then
Exit Sub

Else
Me.txtMorning = CDbl(Format(Me.txtMorning, "#,##0.0"))
End If
End Sub

Issue 1
As you noted, with Null / Empty Cells the code fails at Me.txtMorning = CDbl(Format(Me.txtMorning, "#,##0.0"))
So I expect that firstly there is something wrong with the Null Check

Issue 2
If there is a numeric value entered into the text box then I still get an incorrect Conditional Formatting result in the WS cells.

Issue 3
Most Confusing is that I have a series of formulas that use the values in the cells (O4,P4 & Q4 are being used to test input at present)
=IF(SUM(O4+P4+Q4)=0," ",(O4+P4+Q4)/3) provides the average of the 3 readings to be input during the day in Cell R4 - Works absolutely fine
However another formula (in the same ws) and which uses the same cells does not recognise the values in these cells. So I really need to find either correct syntax or another way to populate cells inworksheets with numerical values.

Hopefully there may be a simple solution, however, I wish that there was some clearly defined way to use form controls to get numerical values in to Excel as after all it is a tool for calculations. Everywhere I look I can never seem to find clear explanations and examples. As a novice with VBA I find this very frustrating.

Any suggestions or a solution would be most welcome

Micron

I have found a solution to Issues 1 & 3. I found the following Macro which works fine.

Sub ConvertTextToNumber()
With Sheets("Daily Statistics").Range("O4:Q370")
.NumberFormat = "General"
.Value = .Value
End With
End Sub

I just need help with the syntax when the value is Null

 

[Micron]

I guess it's different when dealing with textbox vs cell or range. Try
If txtMorning = "" Then, or
If Me.txtMorning = "" Then, or
If Len(Me.txtMorning) = 0 Then