+ Reply to Thread
Results 1 to 7 of 7

Possible to call Find & Replace dialogue box within Workbook?

Hybrid View

  1. #1
    Forum Contributor unley's Avatar
    Join Date
    11-27-2008
    Location
    South Australia
    MS-Off Ver
    MS Office 2007
    Posts
    253

    Possible to call Find & Replace dialogue box within Workbook?

    Yes, another homework for you guys. I have found the code to open the "Find and Replace" dialogue box but is it possible to open it with "within:" Workbook instead of Sheet?

    This is the code that open up the dialogue box

    Sub FineMe()
            Application.CommandBars("Edit").Controls("Find...").Execute
            
    End Sub
    You know, there are some lazy people that I have to dealt with who can't be bother of clicking on "Workbook" to find what they want.

    Thank you in advance
    Last edited by unley; 07-20-2009 at 11:49 PM.

  2. #2
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,342

    Re: Possible to call Find & Replace dialogue box within Workbook?

    Do you mean that it will Execute when the work book is opened?

    try...in the VB Editor > Excel objects > thisworkbook > module
    Private Sub Workbook_Open()
    Application.CommandBars("Edit").Controls("Find...").Execute
    end sub
    hope it helps
    If the solution helped please donate to RSPCA

    Site worth visiting: Rabbitohs

  3. #3
    Forum Contributor unley's Avatar
    Join Date
    11-27-2008
    Location
    South Australia
    MS-Off Ver
    MS Office 2007
    Posts
    253

    Re: Possible to call Find & Replace dialogue box within Workbook?

    Thank you for replying pike but it's the same dialogue box I have in the first post but already after choosing Option.lol

    But I would like it to open with the choice of Workbook in "Within:" box instead of Sheet

    I'm going to assign macro with a button so the boss can just click on the button and the "Find and Replace" dialogue box will appear, showing Workbook in "Within:" box

    Thanks

  4. #4
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,342

    Re: Possible to call Find & Replace dialogue box within Workbook?

    do you mean like...
    Application.SendKeys ("^f{Enter}")

  5. #5
    Forum Contributor unley's Avatar
    Join Date
    11-27-2008
    Location
    South Australia
    MS-Off Ver
    MS Office 2007
    Posts
    253

    Re: Possible to call Find & Replace dialogue box within Workbook?

    No, this code does'nt open "Find and Replace" dialogue box with "Workbook" in Within: text box.
    But I have found a suitable one while surfed the net. This will open a dialogue box that will search for your text right through the workbook

    Sub FindEverywhere()
    ' Prompt the user for the searchText
    Dim searchText As String
    searchText = InputBox("Find what:", "Search All Worksheets")
    
    ' If the searchText is NOT an empty string, begin the search
    If Not searchText = "" Then
    Dim r As Range
    Dim findNext As Integer
    findNext = vbYes
    
    Dim i As Integer
    ' Use For loop, not a For Each loop, as it's the only way to handle hidden/macro sheets!
    For i = 1 To Sheets.Count
    ' Loop through each sheet in the workbook. This does NOT change the activate/visible sheet.
    ' That will come later if we're still searching (findNext=vbYes). I'd prefer a method to
    ' "break" out of the For loop, but VBA doesn't have one?
    If Sheets.Item(i).Visible = xlSheetVisible Then
    ' This sheet is visible, so let's work with it (you HAVE to skip macro sheets, so just skip all hidden sheets)
    Dim sheet As Worksheet
    Set sheet = Sheets.Item(i) ' don't do this until you're sure this sheet is Visible!
    
    ' The firstFind and looped vars are used to determine if we've looped thru every find on this page.
    Dim firstFoundCell As String
    firstFoundCell = ""
    Dim looped As Boolean
    looped = False
    
    Do While findNext = vbYes And Not looped
    ' This is necessary to search on the right sheet (?), but it also means we'll switch to every sheet,
    ' including ones which do NOT contain searchText. So, if you search the whole Workbook, you'll end up looking
    ' at the last sheet, whether or not it contains the searchText. :-(
    Sheets(sheet.Name).Select
    
    ' Here's the search!
    Set r = Cells.Find(searchText, ActiveCell, xlValues, xlPart, xlByRows, xlNext, False)
    If r Is Nothing Then
    ' Nothing was found. Exit this WHILE loop (the FOR loop will still go to the next sheet).
    Exit Do
    Else
    r.Activate ' Activate (select) the cell that was found
    ' Check/Set the firstFoundCell and the looped variable.
    If firstFoundCell = "" Then
    sheet.Activate
    firstFoundCell = r.Address
    Else
    ' We've found at least one cell already. Check to see if we've looped back to the firstFoundCell.
    If r.Address = firstFoundCell Then
    looped = True
    Else
    End If
    End If ' end of checking for firstFoundCell
    
    ' If we haven't looped around, prompt the user to findNext. (If we have looped, the WHILE loop will finish, causing the FOR loop to go to the next sheet.)
    If Not looped Then
    findNext = MsgBox("Find Next?", vbYesNo)
    End If
    End If
    Loop ' end of Do While loop
    End If
    Next i
    
    If findNext = vbYes Then
    MsgBox ("No more matches found!")
    End If
    End If
    End Sub
    So thank you pike for your time and I hope this can be useful for anyone who wanted this, cheers

  6. #6
    Forum Expert pike's Avatar
    Join Date
    12-11-2005
    Location
    Alstonville, Australia
    MS-Off Ver
    2016
    Posts
    5,342

    Re: Possible to call Find & Replace dialogue box within Workbook?

    Oh.... thats what you ment ..

    a very good example

  7. #7
    Forum Contributor unley's Avatar
    Join Date
    11-27-2008
    Location
    South Australia
    MS-Off Ver
    MS Office 2007
    Posts
    253

    Re: Possible to call Find & Replace dialogue box within Workbook?

    cheers mate

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1