I have a UserForm that users will Double click a row to open and modify customer data. The problem is for the field containing order value (which does not get changed), when it is called into the form, it retains the "accounting" number format, but when the user clicks enter to close the form and refresh the row, the formatting appears to still be "accounting" but the dollar sign and numbers are pushed together and aligned to the left. I end up having to go into that specific cell (for rows that have been modified) and manually re-enter or refresh the cell to show the true "accounting" format.
Just below is the double click code & row 24 is the issue.
And further below is the Form Code - As you can see, I have it refreshing the order value cell, but it only works if I over ride the existing values. If I leave the existing value, that's when formatting gets altered.
I have tried various ways to refresh that cell with code, but no luck so far.
Appreciate any help with this...
Ben
Just below is the double click code & row 24 is the issue.
And further below is the Form Code - As you can see, I have it refreshing the order value cell, but it only works if I over ride the existing values. If I leave the existing value, that's when formatting gets altered.
Code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Application.ScreenUpdating = True
Dim WSProject As Worksheet 'Added WS to project to indicate it's a worksheet
Set WSProject = Sheets("PROJECTS")
Dim ThisCol As Integer
'Dim Thisrow As Integer
ThisCol = Target.Column
Thisrow = Target.Row
If ThisCol = 1 Then
With frmData
.ComboPrefix.Value = WSProject.Cells(Thisrow, 2).Text
.txtProject = WSProject.Cells(Thisrow, 3).Text
.txtOrderDate = WSProject.Cells(Thisrow, 10).Text
.txtCustomerName = WSProject.Cells(Thisrow, 11).Text
.txtCustomerID = WSProject.Cells(Thisrow, 12).Text
.txtCustomerNo = WSProject.Cells(Thisrow, 13).Text
.txtCustAddress = WSProject.Cells(Thisrow, 14).Text
.txtCity = WSProject.Cells(Thisrow, 15).Text
.ComboState = WSProject.Cells(Thisrow, 16).Text
.txtZipCode = WSProject.Cells(Thisrow, 17).Text
.txtCustFirstName = WSProject.Cells(Thisrow, 18).Text
.TxtCustLastName = WSProject.Cells(Thisrow, 19).Text
.txtPhoneNumber = WSProject.Cells(Thisrow, 20).Text
.txtEmail = WSProject.Cells(Thisrow, 21).Text
.txtEquipment = WSProject.Cells(Thisrow, 22).Text
.txtParts = WSProject.Cells(Thisrow, 23).Text
.txtValue = WSProject.Cells(Thisrow, 24).Text
.txtPoNo = WSProject.Cells(Thisrow, 25).Text
.txtStartDate = WSProject.Cells(Thisrow, 26).Text
.txtNotes = WSProject.Cells(Thisrow, 27).Text
.txtPrevCustomer = WSProject.Cells(Thisrow, 28).Text
.txtPrevAddress = WSProject.Cells(Thisrow, 29).Text
'' ChangeData is = False when variable is set now change it to true so it can be used in userform code
ChangeData = True
.show
End With
End If
End Sub
Code:
Private Sub cmdEnter_Click()
ActiveSheet.Unprotect Password:="MASTER"
Application.ScreenUpdating = False '' set to false to prevent seeing screen flicker
Dim MyQuoteNr As Integer
Dim MyLen As Integer
Dim i As Integer
'
Dim MyOrderDate As Date
Dim MyStartDate As Date
Dim WSProject As Worksheet 'Added WS to project to indicate it's a worksheet
Dim ODlrow As Long
Set WSProject = Sheets("PROJECTS")
''' Check to see if changedata = true ''
If ChangeData = True Then '' True value carried over from sheet3 code
With frmData
WSProject.Cells(Thisrow, 2).Value = .ComboPrefix
WSProject.Cells(Thisrow, 3).Value = .txtProject
WSProject.Cells(Thisrow, 10).Value = .txtOrderDate
WSProject.Cells(Thisrow, 10).NumberFormat = "mm/dd/yy;@"
WSProject.Cells(Thisrow, 11).Value = .txtCustomerName
WSProject.Cells(Thisrow, 12).Value = .txtCustomerID
WSProject.Cells(Thisrow, 13).Value = .txtCustomerNo
WSProject.Cells(Thisrow, 14).Value = .txtCustAddress
WSProject.Cells(Thisrow, 15).Value = .txtCity
WSProject.Cells(Thisrow, 16).Value = .ComboState
WSProject.Cells(Thisrow, 17).Value = .txtZipCode
WSProject.Cells(Thisrow, 18).Value = .txtCustFirstName
WSProject.Cells(Thisrow, 19).Value = .TxtCustLastName
WSProject.Cells(Thisrow, 20).Value = .txtPhoneNumber
WSProject.Cells(Thisrow, 21).Value = .txtEmail
WSProject.Cells(Thisrow, 22).Value = .txtEquipment
WSProject.Cells(Thisrow, 23).Value = .txtParts
WSProject.Cells(Thisrow, 24).Value = .txtValue
WSProject.Cells(Thisrow, 24).NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)"
WSProject.Cells(Thisrow, 25).Value = .txtPoNo
WSProject.Cells(Thisrow, 26).Value = .txtStartDate
WSProject.Cells(Thisrow, 26).NumberFormat = "mm/dd/yy;@"
WSProject.Cells(Thisrow, 27).Value = .txtNotes
WSProject.Cells(Thisrow, 28).Value = .txtPrevCustomer
WSProject.Cells(Thisrow, 29).Value = .txtPrevAddress
End With
Application.Goto Range("X" & ActiveCell.Row)
ActiveCell.Formula = ActiveCell.Formula
Range("A1").Select
I have tried various ways to refresh that cell with code, but no luck so far.
Appreciate any help with this...
Ben