Change format of VBA userform textbox data

tjdickinson

New Member
Joined
Jun 26, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
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
VBA Code:
.Unprotect Password:="password"
and
VBA Code:
.Protect Password:="password"
and replace "password" with the worksheet password?
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

fadee2

Active Member
Joined
Nov 7, 2020
Messages
367
Office Version
  1. 2019
Platform
  1. Windows
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:

VBA Code:
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.

1624709436543.png


hth.....
 

tjdickinson

New Member
Joined
Jun 26, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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:
VBA Code:
  .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

New Member
Joined
Jun 26, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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:

VBA Code:
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
VBA Code:
Private Sub Eval_Date_Exit(ByVal Cancel As MSForms.ReturnBoolean)

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

End Sub
and
VBA Code:
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:
VBA Code:
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'.
1624729770862.png
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

this will disable the error, but it works across the whole workbook
VBA Code:
Application.ErrorCheckingOptions.NumberAsText = False
 

tjdickinson

New Member
Joined
Jun 26, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
this will disable the error, but it works across the whole workbook
VBA Code:
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

Well-known Member
Joined
May 20, 2004
Messages
3,337
Office Version
  1. 2010
Platform
  1. Windows
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

Active Member
Joined
Nov 7, 2020
Messages
367
Office Version
  1. 2019
Platform
  1. Windows
Or try the following
VBA Code:
.Cells(5, iCol).Value = Me.Eval_Points.Value
  .Cells(5, iCol).NumberFormat = "General"


hth....
 

Forum statistics

Threads
1,144,422
Messages
5,724,221
Members
422,544
Latest member
joanwalter

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top