When Re-Opening A UserForm Number Formatting Is Lost - Please Help

[bensko]

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.

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

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

 

[westconn1]

not sure that it will fix your problem, but i would set the numberformat before assigning the value

an alternative is to format the text to the textbox

.txtValue = format(WSProject.Cells(Thisrow, 24).Text, "currency")
or define your own

 

[bensko]

That did not appear to work - is there a way to remove formatting when retrieving the data back to the form so that instead of $ 100.00 it just shows 100 that way when it's sent back to the sheet, it correctly applies the formatting ("accounting").

 

[bensko]

I worked it out by removing the formatting from the cell before retrieving the data back into the form. Thanks Westconn, you triggered the idea...!

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Application.ScreenUpdating = True
[B]Application.Goto Range("X" & ActiveCell.Row)
Selection.NumberFormat = "General"[/B]
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