Date formats in userform text boxes

mmmreece

New Member
Joined
Apr 8, 2015
Messages
22
Hi . I know that if I format a text box to Format(textbox1, "MM/DD/YYYY") it will enter the value as a date if textbox1 is initialised as textbox1 = Date but what I would like to do is have textbox1 show the date on the userform as "DDD dd/mm/yyyy" but when entered into a cell enters it as a date into the spreadsheet. I currently have a command button to enter called CMB_Enter which has the formula (one of the many) of Range ("A" & NewRow).value = textbox1.value. I'm having difficulty in achieving this. One workaround I have is to have an invisible textbox showing the date in format "mm/dd/yyyy" but surely there should be a way to display a userform textox as one format and then enter the actual value from that textbox to a cell.

Also there is another issue I have here. Sometimes I need to enter a different date into the textbox but if the textbox is formatted in a different way then I don't know how to enter a different date!
I've tried to be concise here but if what I'm saying doesn't make any sense let me know and I'll try and re-word it. Unfortunately googling for this just brings me up too much irrelevant stuff for me to find anything
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,289
Office Version
  1. 2019
Platform
  1. Windows
Hi,

try following & see if does what you want

VBA Code:
Private Sub CMB_Enter_Click()


With Range("A" & NewRow)
    .Value = DateValue(Mid(Me.TextBox1.Value, 4))
    .NumberFormat = "mm/dd/yyyy"
End With

End Sub

this assumes that your textbox date is displaying in format "DDD dd/mm/yyyy" - If no valid date entered, code will error.

Dave
 

Watch MrExcel Video

Forum statistics

Threads
1,119,015
Messages
5,575,560
Members
412,676
Latest member
Davejf81
Top