ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,832
- Office Version
- 2007
- Platform
- Windows
Hi,
Ive been looking at a userform today & noticed 2 things.
Ist thing.
When the userform is open and i make an edit to the text within the fields i naturally click on SAVE CHANGES FOR THIS CUSTOMER.
I see the saved confirmation message & i am then taken back to the database.
Ive noticed that all changes made on the userform reflect correctly on the database APART from the field labelled PAID which is column O on the database,it put the value in that cell as £0.00
Example.
Database shows paid £50.00,i open up the userform and i see say £50.00 so i then change it to £60.00,save etc & back to database and i see £0.00
2nd thing.
Similar to the above but font size.
Userform field labeled Invoice Number,on database it is column P
The font size should be 16 but it saves it as 11
I believe i have supplied the correct part of the code if you could be so kind.
Thanks very much.
Ive been looking at a userform today & noticed 2 things.
Ist thing.
When the userform is open and i make an edit to the text within the fields i naturally click on SAVE CHANGES FOR THIS CUSTOMER.
I see the saved confirmation message & i am then taken back to the database.
Ive noticed that all changes made on the userform reflect correctly on the database APART from the field labelled PAID which is column O on the database,it put the value in that cell as £0.00
Example.
Database shows paid £50.00,i open up the userform and i see say £50.00 so i then change it to £60.00,save etc & back to database and i see £0.00
2nd thing.
Similar to the above but font size.
Userform field labeled Invoice Number,on database it is column P
The font size should be 16 but it saves it as 11
I believe i have supplied the correct part of the code if you could be so kind.
Thanks very much.
Code:
[/COLOR]Private Sub UpdateRecord_Click()
Dim C As Range
Dim i As Integer
Dim Msg As String
If Not IsComplete(Form:=Me) Then Exit Sub
Dim IsNewCustomer As Boolean
'New Part
Dim ctrl As MSForms.Control
For Each ctrl In Me.Controls
If TypeOf ctrl Is MSForms.TextBox Then ctrl.BackColor = RGB(255, 255, 255)
Next ctrl
'End New part
If Me.NewRecord.Caption = "CANCEL" Then
With Sheets("DATABASE")
Set C = .Range("A:A").Find(What:=txtCustomer.Value, _
After:=.Range("A5"), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With
If Not C Is Nothing Then
MsgBox "Customer already Exists, file did not update"
Cells(C.Row, "Q").Value = TextBox1 '<<<<<<<<<<<<<<
Exit Sub
End If
End If
IsNewCustomer = CBool(Me.UpdateRecord.Tag)
Msg = "CHANGES SAVED SUCCESSFULLY"
If IsNewCustomer Then
'New record - check all fields entered
r = startRow
Msg = "NEW CUSTOMER SAVED TO DATABASE"
ws.Range("A6").EntireRow.Insert
ResetButtons Not IsNewCustomer
Me.NextRecord.Enabled = True
End If
On Error GoTo myerror
Application.EnableEvents = False
'Add / Update Record
For i = 1 To UBound(ControlNames)
With Me.Controls(ControlNames(i))
'check if date value
If IsDate(.Text) Then
ws.Cells(r, i).Value = DateValue(.Text)
ElseIf i = 15 Then
If .Text = "" Then
MsgBox .Name & " is empty!"
Exit Sub
End If
ws.Cells(r, i).Value = Val(.Text)
Else
ws.Cells(r, i).Value = UCase(.Text)
End If
ws.Cells(r, i).Font.Size = 11
End With
Next i
If IsNewCustomer Then
Call ComboBoxCustomersNames_Update
Range("A6:Q6").Interior.ColorIndex = 6
With Sheets("DATABASE")
If .AutoFilterMode Then .AutoFilterMode = False
x = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A5:Q" & x).Sort Key1:=.Range("A6"), Order1:=xlAscending, Header:=xlGuess
Range("A6:Q6").Borders.LineStyle = xlContinuous
Range("A6:Q6").Borders.Weight = xlThin
End With
End If
ThisWorkbook.Save
'tell user what happened
MsgBox Msg, 48, Msg
Set C = Nothing
myerror:
Application.EnableEvents = True
'something went wrong tell user
If Err > 0 Then MsgBox (Error(Err)), 48, "Error"
Unload Me
End Sub[COLOR=#333333]