VBA to return numeric value instead of text from the UserForm

[karolina1406]

Can someone help me with a User form? I created a form for a Risk Register with 2 command buttons: "Enter new Risk" and "Update existing risk". Both work fine however, when updating existing risk, the value of the risk is not returned to the spreadsheet on as a numeric value and therefore is not taken into calculation of the total Risks value. The spreadsheet is password protected. Risks values are in column R.

Can someone help?

 

[steve the fish]

You can do a few things but heres one:

Range("A1") = Val(TextBox1.Text)

It will fail if the textbox isnt a number though so maybe:

tb = TextBox1.Text

If IsNumeric(tb) Then
    Range("A1") = Val(tb)
Else
    Range("A1") = tb
End If

 

[karolina1406]

You can do a few things but heres one:

Range("A1") = Val(TextBox1.Text)

It will fail if the textbox isnt a number though so maybe:

tb = TextBox1.Text

If IsNumeric(tb) Then
    Range("A1") = Val(tb)
Else
    Range("A1") = tb
End If

thank you Steve... can i ask a silly question? (I am self-tutoring in VBA hence not everything is obvious to me). Should this code you gave me be entered on the Risk Register tab Excel Object or as part of the code in my UserForm?

 

[steve the fish]

Right click on the userform in the VBA project explorer. Press view code. Then its something like:

Private Sub CommandButton1_Click()

tb = TextBox1.Text

If IsNumeric(tb) Then
    Range("a1") = Val(TextBox1.Text)
Else
    Range("a1") = tb
End If

End Sub

depending on what the command button and the text box is called. Did someone else write the code to place the textbox value on the worksheet?

 

[karolina1406]

i have written below code to self populate the "Update existing risk" form based on the selected risk ID:

Private Sub RiskID_Change()


Dim Sh As Worksheet


Set Sh = ThisWorkbook.Sheets("Risk_Register")
Dim i As Long

If Me.RiskID.Value <> "" Then
i = Application.Match(VBA.CLng(Me.RiskID.Value), Sh.Range("H:H"), 0)

If Sh.Range("k" & i).Value = "Open" Then Me.OptionButton1.Value = True
If Sh.Range("k" & i).Value = "Closed" Then Me.OptionButton2.Value = True
If Sh.Range("k" & i).Value = "For escalation" Then Me.OptionButton3.Value = True
Me.RiskType_CB.Value = Sh.Range("m" & i).Value
Me.RiskName_TX.Value = Sh.Range("n" & i).Value
Me.RiskDescription_TX.Value = Sh.Range("o" & i).Value
Me.ImpactDescription_TX.Value = Sh.Range("p" & i).Value
Me.AreaOfImpact_CB.Value = Sh.Range("q" & i).Value
Me.RiskValue_TX.Value = Sh.Range("r" & i).Value
Me.RiskOWner_TX.Value = Sh.Range("t" & i).Value
Me.Impact_CB.Value = Sh.Range("u" & i).Value
Me.Probablility_CB.Value = Sh.Range("v" & i).Value
Me.MitigationAction_TX.Value = Sh.Range("x" & i).Value
Me.PostMitigationValue_TX.Value = Sh.Range("y" & i).Value
Me.ResolveDate_TX.Value = Sh.Range("aa" & i).Value
Me.LastReviewedDate_TX.Value = Sh.Range("z" & i).Value


End If


End Sub
Private Sub UpdateRisk_CB_Click()
Dim Sh As Worksheet
Set Sh = ThisWorkbook.Sheets("Risk_Register")
Dim n As Long


n = Application.Match(VBA.CLng(Me.RiskID.Value), Sh.Range("H:H"), 0)


Sh.Unprotect "kk"
If Me.LastReviewedDate_TX.Value = "" Then
MsgBox "Please enter Last Review Date as dd/mm/yy format", vbCritical
Exit Sub
End If


Sh.Range("m" & n).Value = Me.RiskType_CB
Sh.Range("n" & n).Value = Me.RiskName_TX
Sh.Range("o" & n).Value = Me.RiskDescription_TX
Sh.Range("p" & n).Value = Me.ImpactDescription_TX
Sh.Range("q" & n).Value = Me.AreaOfImpact_CB
Sh.Range("r" & n).Value = Me.RiskValue_TX
Sh.Range("t" & n).Value = Me.RiskOWner_TX
Sh.Range("u" & n).Value = Me.Impact_CB
Sh.Range("v" & n).Value = Me.Probablility_CB
Sh.Range("x" & n).Value = Me.MitigationAction_TX
Sh.Range("y" & n).Value = Me.PostMitigationValue_TX
Sh.Range("z" & n).Value = Me.LastReviewedDate_TX
Sh.Range("aa" & n).Value = Me.ResolveDate_TX


If Me.OptionButton1.Value = True Then
Sh.Range("k" & n).Value = "Open"
End If

If Me.OptionButton2.Value = True Then
Sh.Range("k" & n).Value = "Closed"
End If

If Me.OptionButton3.Value = True Then
Sh.Range("k" & n).Value = "For Escalation"
End If


MsgBox "Updated Successfully", vbInformation

All works perfectly fine other than, as said, returned Risk Value is returned to the excel as a text rather than a number

 

[steve the fish]

So this part?

Sh.Range("r" & n).Value = Me.RiskValue_TX

If you are sure its always numeric then just wrap the textbox with Val.

Sh.Range("r" & n).Value = Val(Me.RiskValue_TX.Text)

 

[karolina1406]

So this part?

Sh.Range("r" & n).Value = Me.RiskValue_TX

If you are sure its always numeric then just wrap the textbox with Val.

Sh.Range("r" & n).Value = Val(Me.RiskValue_TX.Text)

OMG! You're a star! it works perfectly!" Thank you very much!!!

 

[karolina1406]

btw, is there as easy way as this one to return a date in a dd-mmm-yy format? This is less of my issue with the form now however, if I can improve this as well - that would be great

 

[steve the fish]

Try using it the same way but wrapping with CDate rather than Val.