Hi all,
I have a multipage userform - 8 pages with multiple frames within each page. I have text boxes and comboboxes within each frame. Each textbox, combobox, frame and multipage has a uniquename. The user is able to input data and retrieve data that has been saved - it is a multisession form - in the sense that the user will not be able to complete and submit in one session.
Some of the texboxes contain text and some contain numbers. A subset of those that contain numbers further have some that can contain percentages. The main page multipage(0) has textboxes that summarises the rest of pages - hence they are locked and i for sure need the numbers to be comma separated. However i do not want the users to be able to enter comma in the rest of the pages. The percentages can be a fraction - for instance 0.42 not going beyond 2 decimal places.
Is there a way i can format all the textboxes within various frames and pages using the "words like" phrase. I will do it manually if i must but i am certain there is a way using VBA.
so far, below is the code i have been trying but it works on either or basis. If i use one it ensures no commas can be entered but does not show me fraction percentage values.
I searched this forum and have perused code snipped provided by Dave and it ensures that users arent able to to enter text in textboxes where numbers are expected.
Private Sub OnlyNumbers()
Dim cContrl As Control
With Me.ActiveControl
Set cContrl = .Pages(.Value).Controls(strCtrlName)
With cContrl
If .Value Like "*[!0-9]*" And .Value <> vbNullString Then
MsgBox "Invalid Entry! Only Numbers are Allowed.", vbInformation, "Invalid Entry"
.Value = vbNullString
End If
End With
End With
End Sub
Display More
I am using the following code to enable formatting but unfortunately it is formatting all the text boxes including those where a fractional percentages may appear. so a percentage figure like 0.42 is being represented as 0.00 and 0.94 like 1.00
Since i am to retrieve the data back from the spreadsheet when the user resumes their session, i must format the data to maintain the formatting...
Dim cCont As Control
For Each cCont In SQW_Budget_Tool.Controls
If TypeName(cCont) = "TextBox" Then
cCont = Format(cCont, "###0")
With cCont
If .Value = "0" Then cCont = Format(cCont, "0;-0;;@")
End With
End If
Next cCont
Display More
Any ideas?