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.