Add a "Find" Button to Excel (VBA?)

[NewYears1978]

This probably sounds stupid because I can just CTRL-F but my other employees are not understanding of Excel. Right now I have macros in my sheet that apparently turn MATCH CASE and MATCH CELL on, and leaves them on.

So when doing CTRL-F it defaults with both of those on..then you can't find data. I can't determine where this happens to simply turn it off, but I wanted to make a FIND button anyways because they often don't remember how to hit "CTRL-F" either.

So..simple solution would be a button that just activates a box to type a value and find it without case or cell sensitivity.

Any pointers? I searched google and here but found nothing like it.

Thanks in advance.

Edit
(Looks like match case saves into the document..explains why it was doing that...woops. Still would like button though)

[apo]

Hi..

Maybe something like this using FindNext..

Try the attached..

Private Sub CommandButton1_Click()
    Dim str As String, sh1 As Worksheet, c, x As Long, y As Long
    Set sh1 = Sheets("Sheet1")
    With sh1.Cells
        str = "*" & InputBox("Find Value") & "*"
        Set c = .Find(str, LookIn:=xlValues)
        x = Application.CountIf(sh1.Cells, str)
        y = x - 1
        If Not c Is Nothing Then
            firstaddress = c.Address
            Do
                Application.Goto c
                Msgtext = MsgBox("Found: " & x & " Matches" & vbNewLine & vbNewLine & _
                                 "Remaining: " & y & vbNewLine & vbNewLine & "Goto Next?", vbYesNo, "Itterate Through Found Values")
                If Msgtext = vbNo Then
                    Exit Sub
                Else
                    GoTo 1
                End If
1
                Set c = .FindNext(c)
                y = y - 1
                If y = 0 Then Application.Goto c: Exit Sub
            Loop While Not c Is Nothing And c.Address <> firstaddress
        End If
    End With
End Sub

or.. even better .. this will open the Find Dialogue via a CommandButton..

Private Sub CommandButton2_Click()
 Application.CommandBars("Edit").Controls("Find...").Execute
End Sub

[NewYears1978]

Sorry for the late reply, been swamped and not able to work on my forms. Trying this out thank you very much.


Worked great, thank you

[NewYears1978]

Sorry to double post, needed to bring this post back.

Solution works perfect but I had forgot to ask, when searching I want to ignore columns A and B..is this possible? IN other words only search in columns C+ (or even better if possible search ONLY in column G and J?)

Thanks in advance..looking at code to see if I can sort it out myself meanwhile

[apo]

Hi..

Sorry for the late reply..

Private Sub CommandButton1_Click()
    Dim str As String, sh1 As Worksheet, c, x As Long
    Set sh1 = Sheets("Sheet1")
     With sh1.Range("G:G", "J:J")
        str = "*" & Cells(2, 2).Value & "*"
        Set c = .Find(str, LookIn:=xlValues)
         sh1.Cells(2, 3).Value = Application.CountIf(sh1.Range("G:G", "J:J"), str)
        x = sh1.Cells(2, 3).Value
        If Not c Is Nothing Then
            firstaddress = c.Address
            Do
                Application.Goto c
                Msgtext = MsgBox("Found: " & sh1.Cells(2, 3).Value & " Matches" & vbNewLine & vbNewLine & _
                "Remaining: " & x & vbNewLine & vbNewLine & "Goto Next?", vbYesNo, "Itterate Through Found Values")
                If Msgtext = vbNo Then
                    Exit Sub
                Else
                    GoTo 1
                End If
1
x = x - 1
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> firstaddress
        End If
    End With
End Sub