Solved: Need help with adding yes/no msgbox to Find Procedure

Procedure Finds the Next Customer name containing the word "Brice" and changes it to either "Brice Tool" or "Brice Seat", depending on PO# format
Rule: "BRICE TOOL" PO#'s always contain either dashes or periods or RGB or some of each and BRICE SEAT never contains any of those.

As my code is now it makes the change when it finds and checks the criteria of the match. What I need is to give the user the option of making the change or not.

Edit: A better description is I need a yes/no/cancel msgbox
- Yes = make change -- then allow the next click of the button to find the next match.
- No = do not make change -- but allow the next click of the button to find the next match
--Cancel -- Exit the Procedure

Sample Data - Also a workbook attached with the macro.
27684 BRICE 05/14/12 04-26-12 should be BRICE TOOL
27625 BRICE TOOL 05/08/12 04.26.12
27052 BRICE TOOL 02/16/12 02.02RGB
27017 BRICE 02/13/12 02.02 RGB should be BRICE TOOL
26372 BRICE 11/18/11 P074119 should be BRICE SEAT
26114 BRICE TOOL 10/21/11 10.20.RGB
23023 BRICE 09/02/10 067992 should be BRICE SEAT
[vba]
Private Sub CommandButton1_Click()
'Use PO# format to change BRICE to BRICE TOOL or BRICE SEAT
Dim StrtAdrss As String
Dim Found As Range
Dim stringToFind
Dim Rng As Range
Dim LastRow As Long

stringToFind = "BRICE"

StrtAdrss = ActiveCell.Address
With ActiveSheet
LastRow = .Cells.SpecialCells(xlCellTypeVisible).Find("*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

Set Rng = .Range(.Cells(16, 1), .Cells(LastRow, 5)).SpecialCells(xlCellTypeVisible)

End With
If ActiveCell.Row > 15 Then
ActiveCell.EntireRow.Cells(3).Activate
Set Found = Rng.Find(What:=stringToFind, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not Found Is Nothing Then
' match was found

If InStr(Found.EntireRow.Cells(5).Value, ".") >= 1 _
Or InStr(Found.EntireRow.Cells(5).Value, "-") >= 1 _
Or InStr(Found.EntireRow.Cells(5).Value, "RGB") >= 1 Then
Found.EntireRow.Cells(3).Value = "BRICE TOOL"

Else

Found.EntireRow.Cells(3).Value = "BRICE SEAT"

End If

Do Until Found.Row > 15 And StrtAdrss <> Found.Address

Set Found = Rng.FindNext(Found)
Loop

ActiveWindow.ScrollRow = Found.Row - 5'allows me to view 5 rows above
Found.EntireRow.Cells(5).Activate

Else
MsgBox "No Match Found"

End If
Else
MsgBox "Please select a row greater than 15"
End If
End Sub

[/vba]

try

[vba]
Private Sub CommandButton1_Click()
'Use PO# format to change BRICE to BRICE TOOL or BRICE SEAT
Dim StrtAdrss As String
Dim Found As Range
Dim stringToFind
Dim Rng As Range
Dim LastRow As Long

stringToFind = "BRICE"

StrtAdrss = ActiveCell.Address

With ActiveSheet

LastRow = .Cells.SpecialCells(xlCellTypeVisible).Find("*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

Set Rng = .Range(.Cells(16, 1), .Cells(LastRow, 5)).SpecialCells(xlCellTypeVisible)

End With

If ActiveCell.Row > 15 Then

ActiveCell.EntireRow.Cells(3).Activate

Set Found = Rng.Find(What:=stringToFind, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not Found Is Nothing Then
' match was found
confirm = MsgBox("Make changes?", vbYesNo)
If confirm = vbYes Then
If InStr(Found.EntireRow.Cells(5).Value, ".") >= 1 _
Or InStr(Found.EntireRow.Cells(5).Value, "-") >= 1 _
Or InStr(Found.EntireRow.Cells(5).Value, "RGB") >= 1 Then
Found.EntireRow.Cells(3).Value = "BRICE TOOL"

Else

Found.EntireRow.Cells(3).Value = "BRICE SEAT"

End If

D:
Do Until Found.Row > 15 And StrtAdrss <> Found.Address

Set Found = Rng.FindNext(Found)
Loop

ActiveWindow.ScrollRow = Found.Row - 5
Found.EntireRow.Cells(5).Activate

Else
GoTo D:
End If

Else
MsgBox "No Match Found"

End If


Else

MsgBox "Please select a row greater than 15"

End If

End Sub

[/vba]

This often happens, after I post, it's then that my brain fog starts to clear. :banghead:

Anyway I got it figured out :)

Hope I didn't take up much of anyones time. And thanks
[VBA]
Private Sub CommandButton1_Click()
'Use PO# format to change BRICE to BRICE TOOL or BRICE SEAT
Dim StrtAdrss As String
Dim Found As Range
Dim stringToFind
Dim Rng As Range
Dim LastRow As Long
Dim i As Integer

stringToFind = "BRICE"

StrtAdrss = ActiveCell.Address
With ActiveSheet
LastRow = .Cells.SpecialCells(xlCellTypeVisible).Find("*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row

Set Rng = .Range(.Cells(16, 1), .Cells(LastRow, 5)).SpecialCells(xlCellTypeVisible)

End With
If ActiveCell.Row > 15 Then
ActiveCell.EntireRow.Cells(3).Activate
Set Found = Rng.Find(What:=stringToFind, _
After:=ActiveCell, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not Found Is Nothing Then
' match was found

If InStr(Found.EntireRow.Cells(5).Value, ".") >= 1 _
Or InStr(Found.EntireRow.Cells(5).Value, "-") >= 1 _
Or InStr(Found.EntireRow.Cells(5).Value, "RGB") >= 1 Then

Found.EntireRow.Cells(3).Activate

i = MsgBox("Click YES to change: " & Found.Value & " to: BRICE TOOL" & _
" Click NO if you do wish to make any change, or Cancel to exit the procedure", _
vbQuestion + vbYesNoCancel, "The Name BRICE was found.")

Select Case i
Case vbYes
Found.EntireRow.Cells(3).Value = "BRICE TOOL"
Case vbNo
Found.EntireRow.Cells(3).Activate
GoTo ExitNow
Case vbCancel
Found.EntireRow.Cells(3).Activate
GoTo ExitNow
End Select


Else

Found.EntireRow.Cells(3).Activate

i = MsgBox("Click YES to change: " & Found.Value & " to: BRICE SEAT" & _
" Click NO if you do wish to make any change, or Cancel to exit the procedure", _
vbQuestion + vbYesNoCancel, "The Name BRICE was found.")

Select Case i
Case vbYes
Found.EntireRow.Cells(3).Value = "BRICE TOOL"
Case vbNo
Found.EntireRow.Cells(3).Activate
GoTo ExitNow
Case vbCancel
Found.EntireRow.Cells(3).Activate
GoTo ExitNow
End Select

Found.EntireRow.Cells(3).Value = "BRICE SEAT"

End If

Do Until Found.Row > 15 And StrtAdrss <> Found.Address

Set Found = Rng.FindNext(Found)
Loop

'ActiveWindow.ScrollRow = Found.Row - 5
Found.EntireRow.Cells(5).Activate

Else
MsgBox "No Match Found"

End If
Else
MsgBox "Please select a row greater than 15"
End If
ExitNow:
End Sub

[/VBA]

Awe Defcon 3, I didn't see you as I was in the middle of posting

Thank you much sir

Let me check that out real quick and I'll let you know.

No worries I just saw your post and made it really quick with your provided code not tested but pretty sure will work. I forgot to add cancel but I guess you figure it out :) cheers.

Quote:

---

Originally Posted by defcon_3

No worries I just saw your post and made it really quick with your provided code not tested but pretty sure will work. I forgot to add cancel but I guess you figure it out :) cheers.

---

HI again Defcon 3

Yes your right, your code does work, just to improve it a little I added the line: Found.EntireRow.Cells(3).Activate before your msgbox so that the cell in question is selected before the routine pauses.

As you noticed I added Cancel, but effectivly with the way I coded it , it is the same as choosing No.

- I now will study the circumstances more and try to abbreviate my code, if I can see how to, by closely looking over your code.
- Even if I don't use your style of msgbox right now, I know I will somewhere down the road, as it's a more streamlined approach.
- I surely appreciate your time :friends: