View & edit textbox in userform

[ShenYee]

hello,

Can you help me to identify and solve my problem?
I have multiple textbox in userform and all of it was Vlookup to Combobox1. I arrange the format of each Textbox according to number, percentage and date.
it was all good but when i want to edit or change the value in textbox, it doesn't type accordingly or the same as the problem above.
What i would like to happen is VIEW and EDIT.
In View when I type the ID number of employee the textboxes get the value by using vlookup in the workbook cell (All good)

Code:
'FORMAT TO DATE
Private Sub TextBox17_Change()
TextBox17.Text = Format(TextBox17.Value, "dd-mmm-yy")
End Sub

'FORMAT TO PERCENTAGE
Private Sub TextBox18_Change()
TextBox18 = Format(TextBox18.Value, "0.00%")
End Sub



When Edit, "_Change()" cannot edit the previous value accordingly. I know that to edit accordingly i have to use "_Exit(ByVal Cancel As MSForms.ReturnBoolean)", but this cannot view the format as i wanted ("dd-mmm-yy" & "0.00%") when Vlookup is applied.

In short explanation, I want to view the value at txtboxes accordingly as formatted but can also edit the value accordingly.

Hope you can help me

ShenYee

 

[Fluff]

Hi & welcome to MrExcel
Have you tried a afterupdate event

Private Sub TextBox17_AfterUpdate()

 

[ShenYee]

Thank you for your response and yes i also tried that to replace _Change() and the problem is when i click Combobox1, the value of percentage becomes demical and also it cannot change or edit the value accordingly.

 

[Fluff]

Rather than trying to format on a change event, do the format before you enter the value like

Private Sub DTPicker1_Change()
Me.TB1.Value = format(Me.DTPicker1.Value, "d-mmm-yy")
End Sub

I'm using a datepicker for ease, but you can do the same thing from a combobox. You can then use the afterupdate for editing.

 

[ShenYee]

 

[Fluff]

You cannot display images from your computer, you will need to upload to a file share site

 

[ShenYee]

Nothing happened in your code. The thing is when I open this userform and select the ID number in combobox1, all textboxes filled with value from Vlookup (VIEW).
(EDIT) when i change or input some of the value in textbox and i can type from left to right and not jumping at the end.

 

[ShenYee]

https://www.dropbox.com/home?preview=Capture01.JPG

This is what happened when I want to edit the recent value

 

[ShenYee]

https://www.dropbox.com/home?preview=Capture01.JPG

This is what happened when I want to edit the recent value

And this is the code i am using in userform (sample only in Textbox18)

Private Sub ComboBox1_Change()Me.TextBox16.Value = Application.VLookup(Me.ComboBox1.Value, Worksheets("MAIN").Range("B9:EC10008"), 16, 0)
Me.TextBox17.Value = Application.VLookup(Me.ComboBox1.Value, Worksheets("MAIN").Range("B9:EC10008"), 17, 0)
Me.TextBox18.Value = Application.VLookup(Me.ComboBox1.Value, Worksheets("MAIN").Range("B9:EC10008"), 18, 0)
Me.TextBox20.Value = Application.VLookup(Me.ComboBox1.Value, Worksheets("MAIN").Range("B9:EC10008"), 20, 0)
Me.TextBox21.Value = Application.VLookup(Me.ComboBox1.Value, Worksheets("MAIN").Range("B9:EC10008"), 21, 0)
Me.TextBox22.Value = Application.VLookup(Me.ComboBox1.Value, Worksheets("MAIN").Range("B9:EC10008"), 22, 0)

End Sub

'FORMAT TO PERCENTAGE
Private Sub TextBox18_Change()
TextBox18 = Format(TextBox18.Value, "0.00%")
End Sub

'FORMAT TO DATE
Private Sub TextBox17_Change()
TextBox17.Text = Format(TextBox17.Value, "dd-mmm-yy")
End Sub

'LINE 1
Private Sub TextBox16_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox16 = Format$(TextBox16.Text, "#,##0.00")
End Sub
'LINE 2
Private Sub TextBox20_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox20 = Format(TextBox20, "#,##0.00")
On Error GoTo InvalidTypes:
TextBox22.Value = CStr(CDbl(TextBox16.Text) + CDbl(TextBox20.Text) - CDbl(TextBox21.Text))
Exit Sub
InvalidTypes:
TextBox22.Value = "0"
End Sub
Private Sub TextBox21_Exit(ByVal Cancel As MSForms.ReturnBoolean)
TextBox21 = Format(TextBox21, "#,##0.00")
On Error GoTo InvalidTypes:
TextBox22.Value = CStr(CDbl(TextBox16.Text) + CDbl(TextBox20.Text) - CDbl(TextBox21.Text))
Exit Sub
InvalidTypes:
TextBox22.Value = "0"
End Sub
Private Sub TextBox22_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo InvalidTypes:
TextBox22.Value = CStr(CDbl(TextBox16.Text) + CDbl(TextBox20.Text) - CDbl(TextBox21.Text))
TextBox22 = Format(TextBox22, "#,##0.00")
Exit Sub
InvalidTypes:
TextBox22.Value = "0"
End Sub

 

[mikerickson]

One way to do this would be to give use the Tag property to hold that textbox's number format.

Private Sub Userform_Intialize()
    ' ...
    TextBox17.Tag = "dd-mmm-yy"
    ' ...
End Sub

And use that in the combobox change event

Private Sub ComboBox1_Change()

For I = 16 to 22
    With Me.Controls("TextBox" & i)
        .Value = Format(Application.VLookup(Me.ComboBox1.Value, Worksheets("MAIN").Range("B9:EC10008"), 17, 0), .Tag)
    End With
Next I

End Sub