Error message when saving an edit on userform

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
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.

VBA Code:
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
 

Attachments

  • 6114.jpg
    6114.jpg
    21.5 KB · Views: 4

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
Maybe you also need this ?

VBA Code:
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



Code:
 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

New Member
Joined
Nov 6, 2018
Messages
13

ADVERTISEMENT

What line does it debug on?
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
The Msgbox only has the OK button. There is no debug button.
 

JoshWolski

New Member
Joined
Nov 6, 2018
Messages
13

ADVERTISEMENT

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

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
Yes i understand but it loops through without error
 

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,935
Office Version
  1. 2007
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,687
Messages
5,637,832
Members
416,984
Latest member
dee10

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top