Error message when saving an edit on userform

[ipbr21054]

Evening,

I see the following message Application defined or user defind error when i make a save whilst userform is open.

I have a userform which saves information to a worksheet.
I open the userform & select a textbox, make an edit to the spelling etc, click the command button Save Changes For This Customer & i then see the MsgBox Changes saved Successfuly.
Pressing ok then shows the error message mentioned above.

If i close the user form then open it again i look at my edit & its saved correctly.
I then take a look at the worksheet where its also saved correctly.


So where is the error or why do i see this error ?

There is no option to debug it as it only has the OK button

Any advice welcome to track it down thanks

 

[JoshWolski]

Please provide a snip of your code and a visualization of the error.

 

[ipbr21054]

Hi,
Attached is the image screen shot.
The command button has two options depending on its status if that makes sense ?

This button on the worksheet has 2 options,let me try & explain.

I open thw orksheet,the command button is shown as SAVE CHANGES FOR THIS CUSTOMER because the fields have values for the customer in question.

Also on the userform there is a command button shown as ADD NEW CUSTOMER TO DATABASE of which when pressed the fields are shown as blank so you can enter the new customers details etc BUT the button SAVE CHANGES FOR THIS CUSTOMER has now changed to SAVE NEW CUSTOMER TO DATABASE.


I believe this is the code you require.

Private Sub UpdateRecord_Click()

Dim c As Range
Dim i As Integer
Dim msg As String
Dim x As Long
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
                ws.Cells(r, "P").Font.Size = 16
        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

 

[ipbr21054]

Maybe you also need this ?

Sub ResetButtons(ByVal Status As Boolean)
    
    With Me.NewRecord
        .Caption = IIf(Status, "CANCEL", "ADD NEW CUSTOMER TO DATABASE")
        .BackColor = IIf(Status, &HFF&, &H8000000F)
        .ForeColor = IIf(Status, &HFFFFFF, &H0&)
        .Tag = Not Status
    Me.ComboBoxCustomersNames.Enabled = CBool(.Tag)
    End With
    
    With Me.UpdateRecord
        .Caption = IIf(Status, "SAVE NEW CUSTOMER TO DATABASE", "SAVE CHANGES FOR THIS CUSTOMER")
        .Tag = Status
    End With
End Sub

 Option Explicit
 Dim ws As Worksheet
 Dim r As Long
 Dim EventsEnable As Boolean
 Const startRow As Long = 6

Private m_ws As Worksheet
Private m_rw As Long

 

[JoshWolski]

What line does it debug on?

 

[ipbr21054]

The Msgbox only has the OK button. There is no debug button.

 

[JoshWolski]

Debug is a term used to describe where your code stops and generates the error you are getting. It gets highlighted in your vba code.

 

[ipbr21054]

Yes i understand but it loops through without error

 

[JoshWolski]

What is this error then that you posted?

 

[ipbr21054]

That only happens if i do it manually on the userform.
There is no option on the screen to debug it.
So by using the F8 i didnt ever get to see it