CODEWITHSUNDEEP
excelvba
new11
new11

Reputation: 55

Excel VBA Cell Format

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

Upvotes: 1

Views: 343

Answers (1)

Hawawa
Hawawa

Reputation: 141

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

Upvotes: 1

Related Questions