Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

problem shows error after run the code

0

hi

I  face  error  after  run  the  code  in  this  line 

Set Rng = ws.Columns(2).Find(SS, LookAt:=xlWhole)

so  when  select  item  from combobox1(SEARCH CODE)   will populate  values  in textboxes  and  when  press  button(delete) . it  will pop up message  if  I  press ok then  will  delete  the  data in sheet based on selected combobox1  and  shows  the  error "application defined or object  defined error" .

why  show  after  run the  code, what's  the  reason?

Answer
Discuss

Answers

0
Selected Answer

Mussa

I guess you're referring to the UserForm macro for CommandButton2 (please say so next time). The problem is that you're asking the range .Find method to work with a declared variable SS but you set the (undeclared) variable Sel to the value of ComboBox1 (so .Find is working with nothing to search for). Tip: If you use Option Explicit, you will be told if any variable has not been declared.

For your code to use the correct variable, make the first change shown in bold below:

Private Sub CommandButton2_Click()
Dim ws As Worksheet
Dim Rng As Range
Dim SS
Set ws = Sheets("brands")

SS = Me.ComboBox1.Value ' was Sel

If SS <> "" Then
 Set Rng = ws.Columns(2).Find(SS, LookAt:=xlWhole)
 If Not Rng Is Nothing Then

    ws.Cells(Rng.Row, "C") = Me.TextBox3.Value
    ws.Cells(Rng.Row, "D") = Me.TextBox4.Value
    ws.Cells(Rng.Row, "E") = Me.TextBox5.Value
    ws.Cells(Rng.Row, "B") = Me.TextBox2.Value
    Else
    Me.TextBox3.Value = ""
    Me.TextBox4.Value = ""
    Me.TextBox5.Value = ""
 End If
End If

End Sub

There is another problem if you have found a code (e.g. AA126) since the first thing line you execute is: 

ws.Cells(Rng.Row, "B") = Me.TextBox2.Value
but that triggers the sub ComboBox1_Change() which overwrites any values you added in the text boxes in the orange part of your UserForm (and wish to update).

To get around that, I've just moved that line until after the other updates (from TextBox 3. 4 etc.)  You might consider removing that line however (if you want to keep your Codes unique and unaltered).

After re-reading your question, I found the problem with the Delete control. That deletes a row in the worksheet Brands which changes the RowSource for the List of ComboBox1 (and so triggers the the sub ComboBox1_Change) where the problem arises. Not sure why .Find does not work but it can be avoided by the second line in bold below in the Delete sub which sets the FoundCell to "". (The first change just improves the user message):

Private Sub CommandButton3_Click()

    Dim lReply      As VbMsgBoxResult
    Dim ws          As Worksheet
    Dim strFind     As String
    Dim FoundCell   As Range

    strFind = Me.TextBox2.Value

    Set ws = ThisWorkbook.Worksheets("BRANDS")

    If Len(strFind) = 0 Then
        MsgBox "PLEASE WRITE  THE CODE", vbExclamation, "Entry Required"
        Me.TextBox2.SetFocus
        Exit Sub
    Else
        Set FoundCell = ws.Columns(2).Find(What:=strFind, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=True)
        If Not FoundCell Is Nothing Then
            lReply = MsgBox("Are you sure you want to delete variation (Code " & FoundCell.Value & ")?", 276, "Confirm")
            If lReply = vbNo Then Exit Sub
            FoundCell.Value = ""
            FoundCell.EntireRow.Delete

        Else
         MsgBox "Could Not find " & strFind & " On " & ws.Name, 48, "Not Found"
        End If
    End If



    strFind = "VO" & strFind
    On Error Resume Next
    Application.DisplayAlerts = False
    Sheets(strFind).Delete
    Application.DisplayAlerts = True
    On Error GoTo 0

    'Close Form
    Unload Me


End Sub

The revised file attached includes these changes.

Hope this works for you.

Discuss

Discussion

John
thanks, but how revised file  works  for  you  ؟
it  doesn't  work  for  me  . it still shows  the  error. I'm so  confused
Mussa (rep: 48) Oct 11, '21 at 7:24 am
Mussa

Oops! I re-read your question and realised that pressing the Delete button creates a problem but see my revised Answer and file.
John_Ru (rep: 6102) Oct 11, '21 at 8:42 am
great ! thanks  very  much 
Mussa (rep: 48) Oct 11, '21 at 9:02 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login