Vba userform: format cells in text box

[Mr.Magic]

Hi all,

I'm writing codes for a userform, in which people have to enter various data in various text boxes. E.g. numbers, %'s, dates or text.

What I now want is that
- when they have to enter a date, they can only type the date in the form: dd/mm/yyyy
- For numbers, they can enter or e.g. 2000 or 2000,50, in this case in my table it should appear as 2.000,00 or 2.000,50 respectively
- for %, that in the textbox the '%' symbol is already present, and that they have to type the number before the % symbol

Would that be possible...?

Already thanks a lot!!!!




The codes I now have is the following:


Private Sub Userform_Initialize()

TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
TextBox6.Value = ""
TextBox7.Value = ""
TextBox8.Value = ""
TextBox9.Value = ""
TextBox10.Value = ""
TextBox11.Value = ""
TextBox12.Value = ""
TextBox13.Value = ""
ComboBox1.Clear
ComboBox2.Clear

With ComboBox1
.AddItem "EUR"
.AddItem "USD"
.AddItem "GBP"
.AddItem "JPY"
.AddItem "CHF"
.AddItem "PLZ"
.AddItem "SEK"
.AddItem "DKK"
End With

With ComboBox2
.AddItem "No"
.AddItem "Flex"
.AddItem "Fix"
End With

TextBox1.SetFocus

End Sub



Private Sub CommandButton1_Click()

Unload Me

End Sub




Private Sub OKCommandButton_Click()

Sheets(4).Activate

emptyRow = WorksheetFunction.CountA(Range("A:A")) + 2

'Export Data to worksheet
Cells(emptyRow, 2).Value = TextBox1.Value
Cells(emptyRow, 3).Value = ComboBox1.Value
Cells(emptyRow, 4).Value = TextBox2.Value
Cells(emptyRow, 5).Value = TextBox3.Value
Cells(emptyRow, 6).Value = TextBox4.Value
Cells(emptyRow, 7).Value = TextBox5.Value
Cells(emptyRow, 8).Value = ComboBox2.Value
Cells(emptyRow, 18).Value = TextBox6.Value
Cells(emptyRow, 19).Value = TextBox7.Value
Cells(emptyRow, 20).Value = TextBox8.Value
Cells(emptyRow, 21).Value = TextBox9.Value
Cells(emptyRow, 22).Value = TextBox10.Value
Cells(emptyRow, 23).Value = TextBox11.Value
Cells(emptyRow, 24).Value = TextBox12.Value
Cells(emptyRow, 25).Value = TextBox13.Value

Cells(emptyRow, 1).Value = UserForm2.Caption

Unload Me

End Sub

 

[JamesW]

Hi there,

As far as I am aware there is no way to put the percent symbol in a textbox from the start (excel 2003).

Try this though:

Private Sub CommandButton1_Click()
    Cells(6, 1).Value = TextBox1.Value
    Cells(7, 1).Value = Format(TextBox2.Value, "0000.00")
    Cells(8, 1).Value = TextBox3.Value
End Sub



Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Not TextBox1.Value Like "##/##/####" Then
        MsgBox "Date is in the incorrect format, must be ##/##/####"
    End If
    TextBox1.SetFocus
End Sub


Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    TextBox3.Value = Format(Val(TextBox3) / 100, "0.00%")
End Sub