So I have created a userform called Assets which contain multiple text and combo boxes. Presently I have only been playing with Textbox31 which on the attached example is the first textbox beneath Amount. I can use the following code in each textbox for data entry, and that works great.
If IsNumeric(Assets.TextBox31.Value) Then
lDecPoint = InStr(Assets.TextBox31.Value, ".")
If lDecPoint > 0 And Len(Assets.TextBox31.Value) - lDecPoint >= 2 Then
Assets.TextBox31.Value = Left(Assets.TextBox31.Value, lDecPoint + 2)
End If
End If
But I would like to reduce the code and have the code within the Textbox on the userform call a routine, that will allow it to change the name for each textbox. So I have created the following sub routine:
Sub convertTextbox()
Dim newTextBoxName As String
MsgBox TbNum
newTextBoxName = "Assets.TextBox" & TbNum
MsgBox newTextBoxName
'If IsNumeric(newTextBoxName.Value) Then
'lDecPoint = InStr(newTextBoxName.Value, ".")
'If lDecPoint > 0 And Len(newTextBoxName.Value) - lDecPoint >= 2 Then
'newTextBoxName.Value = Left(newTextBoxName.Value, lDecPoint + 2)
'End If
'End If
If IsNumeric(Assets.TextBox31.Value) Then
lDecPoint = InStr(Assets.TextBox31.Value, ".")
If lDecPoint > 0 And Len(Assets.TextBox31.Value) - lDecPoint >= 2 Then
Assets.TextBox31.Value = Left(Assets.TextBox31.Value, lDecPoint + 2)
End If
End If
End Sub
In the above code you will see the first If statement commented out. Since when I try to run it I get an error: Compile error: Invalid Qualifier.
I have tried a number of different solutions and suggestion that I have found searching online, but so far none of them work.
I would also like to make it when the user is entering numbers into the textbox that the user would not have to enter the decimal point and that each time they enter a number it would then shift it to the left. For example if the enter 90 it would show as .90, but if then enter 390, then it would show as 3.90. Presently they must enter the decimal point manually. If they do not then it does not display any decimal point, so if they entered 390 and no decimal point and hit enter, it would only show 390 in the textbox.
I hope I have made this as clear as possible.
See the attached workbook as an example.
Thank you for any suggestions.
Bookmarks