Excel VBA Cell Format

Question

I am making a simple tracking spreadsheet for myself with a userform interface, learning from lots of different online tutorials as I've never coded before. Using the excel ribbon, I have set certain cells with a certain format, one for dates (Day Month Year) & one for currency ([$$-en-AU]-#,##0.00;[Red]-[$$-en-AU]-#,##0.00).

My issue is: whenever I enter data into textbox6, textbox20 textbox21 & textbox22, then close the userform, transferring all the entered data into the worksheet. The data entered into the above textboxes overrides how the cell is formatted. Is there any way to lock the cell format or make it use a certain format regardless of what the user enters into the text box?

This is the code that is used when adding the data into the sheet & unloading the userform: Private Sub cmdAdd_Click() 'Copy input values to sheet. Dim rw As Integer Dim ws As Worksheet Dim lrow As Long Dim LO As ListObject Dim LEO As Range Set ws = Worksheets("2020_Data") Set LO = ws.ListObjects("Table1") 'lrow = ws.Cells(Rows.Count, "B") .End(xlUp). .Offset(1, 0) .Row With LO.Range.Columns(2) Set LEO = .Find(what:="", after:=.Cells(1), LookIn:=xlValues, _ searchorder:=xlByRows, searchdirection:=xlNext) If Not LEO Is Nothing Then lrow = LEO.Row ' MsgBox LEO.Row 'First empty row at column B End If End With 'rw = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookInValues) .Row + 1 With ws .Cells(lrow, "B") = Me.TextBox6 .Cells(lrow, "C") = Me.TextBox21 .Cells(lrow, "F") = Me.ComboBox2 .Cells(lrow, "G") = Me.ComboBox3 .Cells(lrow, "I") = Me.TextBox16 .Cells(lrow, "H") = Me.TextBox17 .Cells(lrow, "J") = Me.TextBox18

Please include the code you have tried using in your question and explain the problems you ran across using it. — braX
HI braX, i haven't tried any code as yet, only using the cell number/date format option in the excel ribbon. I have edited my question to reflect this. Thanks — new11
It is almost certainly an issue with the code though, specifically the part that dumps the values from the textboxes. If I had to guess, you're doing Range(A2) = textbox instead of Range(A2).Value = textbox.Value — Harry
Hi Brax, I've edited my original question to include the code that is used when the userform is unloaded. Would you mind pointing out where I went wrong, please? — new11

Harry Harry · Accepted Answer · 2020-02-17T09:58:53

Like I said in my previous comment, the problem is: .Cells(lrow, "B") = Me.TextBox6

As a rule of thumb, when you want to modify only the value of a cell with VBA, you should use .Cells(...).Value Otherwise, you'll likely override some properties (in this case, formatting)

Using Me.TextBox.6 is not ideal, and I prefer being explicit as well in this case (even if this will work just fine). So, all in all:

.Cells(lrow, "B").Value = Me.Textbox6.Value

Excel VBA Cell Format

1 Answers