Find and Delete a specified value IF found in cell in ANY Sheets in a WorkBook

[Gary Keramidas]

you don't specify where the listbox was created, form forms toolbar or control
toolbox.

i assumed the forms toolbar.


Sub Macro3()
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
If wks.Range("J59").Value = Worksheets("sheet1").Shapes("List Box 4").Value Then
wks.Range("J59").Value = ""
End If
Next wks

End Sub

 

[Corey]

I have numerous sheets in a workbook, and have sometimes a numerical value in Cell J59.

I have some code that does some other work for me, but now i need to add a piece of VBA that will
look through ALL sheets int he workbook to see IF Listbox4.value is in Cell J59.
If it is then Delete the value, if not found then do nothing.

I am trying this to no avail:
===========================
Dim wks As Worksheet
For Each wks In ThisWorkbook.Worksheets
On Error Resume Next
If wks.Range("J59").Value = ListBox4.Value Then
wks.Range("J59").Value = ""
End If
Next wks
===========================
ctm....

 

[ctm]

Here is my entire code thus far:
Private Sub CommandButton3_Click()
Application.ScreenUpdating = False
If ListBox4.Value <> "" Then
If MsgBox("Are you Sure you want to Permanently DELETE" & vbCrLf & vbCrLf &
vbTab & "No. " & vbTab & UserForm3.ListBox4.Value & vbCrLf & vbCrLf & "
Once DELETED it can NOT be Reversed ?", vbYesNo, "....") = vbNo Then
Exit Sub
Else
Dim rngFound As Range
On Error Resume Next
Sheets("Data").Visible = True
Sheets("Data").Unprotect
With Worksheets("Data").Range("A:A")
Set rngFound = .Find(What:=ListBox4.Value, After:=.Cells(1),
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False, Matchbyte:=False)
rngFound.EntireRow.Delete xlShiftUp
Sheets("Data").protect
Sheets("Data").Visible = False
Call finder
End With
End If
Else
MsgBox "You DID NOT Select a Number from the List the be Deleted !!",
vbInformation, "...."
Exit Sub
End If

' Find Listbox4.value in Range("J59") in EACH Sheet Except for
Sheets("Enter-Exit page") <=====


Sheets("Enter-Exit Page").Select
MsgBox "No. " & ListBox4.Value & " has been Removed.", vbInformation,
"...."
Unload Me
Application.ScreenUpdating = True
End Sub