I seem to have deleted the Find control with vba -can you help me get it back?

[SlinkRN]

I found the code

Application.CommandBars.FindControl(ID:=1849).Execute

on this forum and it worked wonderfully to open the find dialogue box.
I was then trying to get the box to close when I ran the code to return to the start page. I couldn't get it to disappear by using "Hide" or "Visible=false" so I tried the code:

Application.CommandBars.FindControl(ID:=1849).Delete

I am afraid that I somehow deleted that control ID because now the execute code returns a Run-time error '91' Object variable or With block variable not set.
Anyone know how I can undo that Delete code that I ran? Thanks, Slink

 

[GWteB]

Try this ...

Application.CommandBars.FindControl(ID:=1849).Reset

 

[SlinkRN]

Thanks GWteB. Unfortunately, that brings up that same run-time error 91.

 

[GWteB]

Perhaps ...

Application.CommandBars("Edit").Reset

 

[SlinkRN]

Beautiful!!!! That worked. Thank you SO much!!! Do you happen to know what word would close the find dialogue box in vba? I 'd like it to close when they click on the "Return to Start Page" button. That code only brings them to a certain worksheet and unfortunately, the find box comes along with it. I would like it to close rather than relying in them to close it. Not a show stopper but would be nice. Thanks so much for your help!

 

[GWteB]

I use little or no native UI elements from Excel in my code and therefore have no idea. If you're able to explain what you're trying to achieve and would post your code in this thread, we may be able to advise you on the best approach.

 

[SlinkRN]

I use little or no native UI elements from Excel in my code and therefore have no idea. If you're able to explain what you're trying to achieve and would post your code in this thread, we may be able to advise you on the best approach.

Thanks GWteB, The process I've already achieved is to allow the user to enter a search term which my code then searches the whole workbook for and returns a listbox with the names of the sheets that the search item is present on. Once they choose the page they want and click on Select, it brings them to that sheet. That all works fine but it is more helpful if it will continue to allow them to search for that same term on the sheet that they choose.
I capture the term they first entered on Sheet "List" in cell B2 so that I can bring that into the next bit of code as necessary. So, I guess what I need is just some code that would bring in sheets("List").range("B2") into some sort of search process for the active sheet. Ideally, it would allow for "Next" "Next" "Next" as long as they want to keep searching that page. Then it would close if they click on "Return to Start Page" button (which is just code that brings them back to a start page cell A1.

 

[GWteB]

You are welcome.

it is more helpful if it will continue to allow them to search for that same term on the sheet that they choose.

You could use a tailor made dialog for that. That way you have full control. Don't know whether you are familiar with userforms. Anyway, the code below assumes the presence of a Userform1, containing two text boxes (TbxSearchFor & TbxFound) and two command buttons (CbtnPrevious & CbtnNext). Example code for both opening and closing the dialog is also provided. See if this works for you.

This goes in the code-behind module of Userform1:

Option Explicit

Private Type TLocals
    SearchFor       As String
    CurrentSheet    As Worksheet
End Type
Private this As TLocals

Private Sub TbxSearchFor_Change()
    this.SearchFor = Me.TbxSearchFor.Value
End Sub

Private Sub UserForm_Activate()
    If this.CurrentSheet Is Nothing Then
        Err.Raise vbObjectError + 380, Me.Name, "Userform needs to be properly initialized."
    End If
    CbtnNext_Click
End Sub

Public Sub Initialize(ByVal argSht As Worksheet, ByVal argSearchText As String)
    this.SearchFor = argSearchText
    Me.TbxSearchFor.Value = argSearchText
    Set this.CurrentSheet = argSht
    argSht.Activate
    Me.TbxFound.BackStyle = fmBackStyleTransparent
    Me.TbxFound.Locked = True
End Sub

Private Sub CbtnNext_Click()
    Dim Rng As Range
    Set Rng = CustomFind(this.SearchFor, xlNext)
    ShowResult Rng
End Sub

Private Sub CbtnPrevious_Click()
    Dim Rng As Range
    Set Rng = CustomFind(this.SearchFor, xlPrevious)
    ShowResult Rng
End Sub

Private Sub CbtnNext_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    CbtnNext_Click
End Sub

Private Sub CbtnPrevious_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    CbtnPrevious_Click
End Sub

Private Function CustomFind(ByVal argTxt As String, ByVal argDirection As XlSearchDirection) As Range
    Dim Rng As Range
    Set Rng = this.CurrentSheet.Cells.Find(What:=argTxt, After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
                                           SearchDirection:=argDirection, MatchCase:=False, SearchFormat:=False)
    this.CurrentSheet.Activate
    If Not Rng Is Nothing Then
        Rng.Activate
        Set CustomFind = Rng
    End If
End Function

Private Sub ShowResult(ByVal argRng As Range)
    If Not argRng Is Nothing Then
        Me.TbxFound.ForeColor = vbBlack
        Me.TbxFound.Value = argRng.Value
        Me.Caption = "Find ...  [" & this.CurrentSheet.Name & " - " & Replace(argRng.Address, "$", "") & "]"
    Else
        Me.TbxFound.ForeColor = vbRed
        Me.TbxFound.Value = "NOT FOUND!"
        Me.Caption = "Find ... NOT FOUND!"
    End If
End Sub

Example code:

Option Explicit

Public DlgFind As UserForm1

Public Sub OpenFindDialog()

    Dim oWs         As Worksheet
    Dim SearchFor   As String

    Set oWs = ThisWorkbook.Sheets("sheet3")                         ' <<< sheet to search on
    SearchFor = ThisWorkbook.Sheets("List").Range("B2").Value       ' <<< text to find

    Set DlgFind = New UserForm1
    
    DlgFind.Initialize oWs, SearchFor
    DlgFind.Show vbModeless
End Sub

Public Sub CloseFindDialog()
    On Error Resume Next
    DlgFind.Hide
    Unload DlgFind
    Set DlgFind = Nothing
End Sub

 

[SlinkRN]

Thanks so much! Your code looks awesome and thanks for the idea. I wound up just creating a simple user form without using any functions. Some day I need to look into functions but for now they are not in my wheel house. I really appreciate your help with this issue. You helped me figure out how to get around using the application find by creating my own userform for find. Works great now! Thanks again! Slink

 

[GWteB]

You are welcome and thanks for letting me know.