Change format of VBA userform textbox data

[tjdickinson]

Hi, all,

I have a userform with 5 input fields, four of which are textboxes (the other is a combobox). Two of the fields ask the user to input a date in the format dd/mm/yy, and one of them asks for a number.

Currently, these values are added to the worksheet as text values, even though the cells are formatted as dates/numbers. (Thus it also flags that the input data doesn't correspond to the cell formatting).

I would like the dates to retain the day, month, and year data, but to only show dd/mm in the cell.
I would like the number to display as a number (allowing for decimals and zero).

Here's the code that adds the form data to the worksheet:

Private Sub Add_Eval_Add_Click()
Dim iCol As Long
Dim ws As Worksheet
Set ws = Worksheets("Gradebook")

'find first empty column in database
iCol = ws.Cells.Find(What:="*", SearchOrder:=xlColumns, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Column + 1

'check for an evaluation title
If Trim(Me.Eval_Title.Value) = "" Then
  Me.Eval_Title.SetFocus
  MsgBox "Please enter an evaluation title."
  Exit Sub
End If

'check for a category
If Trim(Me.Eval_Cat.Value) = "" Then
  Me.Eval_Cat.SetFocus
  MsgBox "Please choose a category."
  Exit Sub
End If

'check for an assigned date
If Trim(Me.Eval_Date.Value) = "" Then
  Me.Eval_Date.SetFocus
  MsgBox "Please enter the date the evaluation was assigned."
  Exit Sub
End If

'check for a due date
If Trim(Me.Eval_Due_Date.Value) = "" Then
  Me.Eval_Due_Date.SetFocus
  MsgBox "Please enter the due date."
  Exit Sub
End If

'check for a points value
If Trim(Me.Eval_Points.Value) = "" Then
  Me.Eval_Points.SetFocus
  MsgBox "Please enter the available points."
  Exit Sub
End If

'copy the data to the database
'use protect and unprotect lines,
'     with your password
'     if worksheet is protected
With ws
'  .Unprotect Password:="password"
  .Cells(1, iCol).Value = Me.Eval_Title.Value
  .Cells(2, iCol).Value = Me.Eval_Cat.Value
  .Cells(3, iCol).Value = Me.Eval_Date.Value
  .Cells(4, iCol).Value = Me.Eval_Due_Date.Value
  .Cells(5, iCol).Value = Me.Eval_Points.Value
'  .Protect Password:="password"
End With

'clear the data
Me.Eval_Title.Value = ""
Me.Eval_Cat.Value = ""
Me.Eval_Date.Value = ""
Me.Eval_Due_Date.Value = ""
Me.Eval_Points.Value = ""
Me.Eval_Title.SetFocus
End Sub

P.S. If you have any suggestions to optimise the code, I'm all ears!
P.P.S. Do I understand the code correctly that, if I have the worksheet protected, I can uncomment the lines

.Unprotect Password:="password"

and

.Protect Password:="password"

and replace "password" with the worksheet password?

 

[fadee2]

hi ]tjdickinson,

In VBA textbox values (excluding numbers only) are considered as TEXT and when we populate cells with textbox values, they are transferred as text as well. Same goes for dates, thus formatting cells as Date does not have any effect. have a look at below examples:

Sheets("sheet1").Range("a1") = TextBox1.Value                           'textbox value will be transferred as text
Sheets("sheet1").Range("a1").NumberFormat = "mmm/yyyy"                  'there will be no effect on cell value, since it is text

Sheets("sheet1").Range("a3") = Format(TextBox1.Value, "dd/mm/yyyy")     'textbox value will be transferred as text but formatted as date, the value will remain TEXT

Sheets("sheet1").Range("a2") = CDate(TextBox1.Value)                    'textbox value will be transferred as date
Sheets("sheet1").Range("a2").NumberFormat = "mmm/yyyy"                  'cell format will be changed to mmm and year, the value will remain DATE

Textbox1.value will place your value as text and as a result number format will not have any effect on such value.
format(textbox1.value, "mm/yyyy") will place your value as text but formatted in shape of month and year, but excel will read the cell value as text.
CDate(textbox1.value) will place your value as date and numberformat will change the date format, but the value will remain date.

hth.....

 

[tjdickinson]

hi ]tjdickinson,

In VBA textbox values (excluding numbers only) are considered as TEXT and when we populate cells with textbox values, they are transferred as text as well. Same goes for dates, thus formatting cells as Date does not have any effect. have a look at below examples:

Sheets("sheet1").Range("a1") = TextBox1.Value                           'textbox value will be transferred as text
Sheets("sheet1").Range("a1").NumberFormat = "mmm/yyyy"                  'there will be no effect on cell value, since it is text

Sheets("sheet1").Range("a3") = Format(TextBox1.Value, "dd/mm/yyyy")     'textbox value will be transferred as text but formatted as date, the value will remain TEXT

Sheets("sheet1").Range("a2") = CDate(TextBox1.Value)                    'textbox value will be transferred as date
Sheets("sheet1").Range("a2").NumberFormat = "mmm/yyyy"                  'cell format will be changed to mmm and year, the value will remain DATE

Textbox1.value will place your value as text and as a result number format will not have any effect on such value.
format(textbox1.value, "mm/yyyy") will place your value as text but formatted in shape of month and year, but excel will read the cell value as text.
CDate(textbox1.value) will place your value as date and numberformat will change the date format, but the value will remain date.

View attachment 41652

hth.....

Hi, Fahad, thanks for your reply!

Okay, so I changed the code to include the CDate() function:

  .Cells(3, iCol).Value = CDate(Me.Eval_Date.Value)
  .Cells(4, iCol).Value = CDate(Me.Eval_Due_Date.Value)

When I run the form and fill it in, I put (for example) 26/06 in the first date box and 27/06 in the second date box.

After I click 'Add', the cells receive the values
26/06/2021
27/06/2021
even though I didn't put the year in the form. I would like the year not to display in order to minimise column width.

The Cell Number Format now says 'Text' instead of 'Date' (it was set to 'Date' with custom formatting dd/mm before I added the data). However, the flag in the corner of the cell has gone away, which had been indicating that the cell contents didn't fit the intended format. So I'm not quite sure what's going on there.

I don't quite understand how to use the NumberFormat parameter. I can't call a specific cell, because each instance of the userform adds a new column of data (so the reference is constantly changing). Is there something similar to CDate() for numbers?

 

[tjdickinson]

hi ]tjdickinson,

In VBA textbox values (excluding numbers only) are considered as TEXT and when we populate cells with textbox values, they are transferred as text as well. Same goes for dates, thus formatting cells as Date does not have any effect. have a look at below examples:

Sheets("sheet1").Range("a1") = TextBox1.Value                           'textbox value will be transferred as text
Sheets("sheet1").Range("a1").NumberFormat = "mmm/yyyy"                  'there will be no effect on cell value, since it is text

Sheets("sheet1").Range("a3") = Format(TextBox1.Value, "dd/mm/yyyy")     'textbox value will be transferred as text but formatted as date, the value will remain TEXT

Sheets("sheet1").Range("a2") = CDate(TextBox1.Value)                    'textbox value will be transferred as date
Sheets("sheet1").Range("a2").NumberFormat = "mmm/yyyy"                  'cell format will be changed to mmm and year, the value will remain DATE

Textbox1.value will place your value as text and as a result number format will not have any effect on such value.
format(textbox1.value, "mm/yyyy") will place your value as text but formatted in shape of month and year, but excel will read the cell value as text.
CDate(textbox1.value) will place your value as date and numberformat will change the date format, but the value will remain date.

View attachment 41652

hth.....

Update:

Guided and informed by your suggestions, I found a near solution, but there's still one glitch:

I added an Exit event to each textbox setting the format of the textbox contents

Private Sub Eval_Date_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Eval_Date.Text = Format$(Eval_Date.Text, "dd/mm")

End Sub

and

Private Sub Eval_Points_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Eval_Points.Text = Format$(Eval_Points.Text, "##0.0")

End Sub

Then, in the Click event adding the data to the worksheet, I added the .NumberFormat function after each value is added to the worksheet:

With ws
'  .Unprotect Password:="password"
  .Cells(1, iCol).Value = Me.Eval_Title.Value
  .Cells(2, iCol).Value = Me.Eval_Cat.Value
  .Cells(3, iCol).Value = Me.Eval_Date.Value
  .Cells(3, iCol).NumberFormat = "dd/mm"
  .Cells(4, iCol).Value = Me.Eval_Due_Date.Value
  .Cells(4, iCol).NumberFormat = "dd/mm"
  .Cells(5, iCol).Value = Me.Eval_Points.Value
  .Cells(5, iCol).NumberFormat = "##0.0"
'  .Protect Password:="password"
End With

Now the dates show up correctly in the sheet.

The number (Eval_Points) displays correctly, and the number format in the worksheet shows 'Custom' (rather than 'Text'), which is good, but I still get a flag that says 'Number stored as text'.

 

[diddi]

this will disable the error, but it works across the whole workbook

Application.ErrorCheckingOptions.NumberAsText = False

 

[tjdickinson]

this will disable the error, but it works across the whole workbook

Application.ErrorCheckingOptions.NumberAsText = False

Thanks, diddi! My only concern is whether calling these values in other functions will result in errors, if the values are stored here as text.

Is it possible to apply your code only to the current worksheet (and not the whole workbook)?
If I use this code, where do I put it? In the 'ThisWorkbook' object? or in the current sheet object?

 

[diddi]

use the Val( ) function in VBA code
errorcheckingoptions is an application level value, so it cannot be applied to individual sheets or ranges. sorry

you would be best to place it in WorkBook_Open sub. dont forget to set to true again before exiting the workbook

 

[fadee2]

Or try the following

.Cells(5, iCol).Value = Me.Eval_Points.Value
  .Cells(5, iCol).NumberFormat = "General"

hth....