none
Macro Pop-up FInd box

    Question

  • Hello,

    We use Excel 2010 in our workplace and some of our vendors send us excel sheets that have up to 30 or more worksheets within Excel. When I first started with the company a few years ago there was a very helpful excel macro we used that has a very useful feature. When you open any excel sheet it would "popup" a find box and change the function to search the entire workbook by default. This has been working great up until Windows 8.1. Since having installed Windows 8.1 on several workstations it has an unusual feature. When any excel sheet is opened the NUM LOCK and and SCROLL LOCK functions turn off. It only does this on Windows 8.1 (I confirmed the same behavior on two different workstations). Here is the macro:

    Option Explicit
    Sub Workbook_Open()
    Application.CommandBars.ExecuteMso "FindDialogExcel"
    SendKeys "%t"               'Opens Options section
    SendKeys "%hW{Enter}"       'Selects Workbook option
    SendKeys "{TAB 11}"         'Moves to Find What box
    End Sub

    Any ideas?


    • Edited by w0ls0n Sunday, December 01, 2013 2:56 PM left off the version number of Excel
    Sunday, December 01, 2013 2:52 PM

Answers

  • Hello,

    I gogoled 'excel 2010 turn on numlock and I found a partial workaround. Here it is:

    Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Long
    Private Const NumlockKey = 144
    Option Explicit
    Sub Workbook_Open()
    Application.CommandBars.ExecuteMso "FindDialogExcel"
    SendKeys "%t"               'Opens Options section
        DoEvents
    SendKeys "%hW{Enter}"       'Selects Workbook option
        DoEvents
    SendKeys "{TAB 11}"         'Moves to Find What box
        DoEvents
    SendKeys "{NUMLOCK}"
        DoEvents
             If GetKeyState(NumlockKey) = 0 Then
            SendKeys "{NUMLOCK}"
         End If
    End Sub

    I do notice if the numlock key is on it will flash really quick (it will stay on)but if it is numlock is off it stays off. One thing I would REALLY love this macro to do is put the cursor inside the find box. At this point you have to click the find box and then start the search.

    Friday, December 06, 2013 2:01 AM

All replies

  • Hello w0ls0n,

    In case of Excel 2013 you could use the following code for displaying the dialog:

    Application.Dialogs(xlDialogSearch).Show
    
    But in Excel 2010 you can try to use the BringWindowsToTop Windows API function to bring the dialog window on top and send keys to it. Read more about this way in a similar forum thread. Also, as a workaround you can develop your own form.
    Sunday, December 01, 2013 6:18 PM
  • Hi,I am unsure of how to use BringWindowsToTop. I think I just discovered that this is a VBA not a macro. Is that correct?

    Monday, December 02, 2013 4:01 AM
  • Hello w0ls0n,

    Please take a look at the How To Bring an Application Window to the Top with Focus article for more information.

    Monday, December 02, 2013 6:22 AM
  • Which kind of popup do you like to popup? is that like this

    Application.Dialogs(xlDialogFormulaReplace).Show
    

    Best Regards, Nikunj Suthar nikunj.suthar@outlook.com Please remember to "Mark as Answer" the responses that resolved your issue. It is a common way to recognize those who have helped you, and makes it easier for other visitors to find the resolution later.

    Monday, December 02, 2013 12:57 PM
  • Nikunj Suthar,

    To answer your question please see my original post. To elaborate a bit what I would like to happen is when a specific excel sheet is open I would like the find box to popup with the worksheet (rather than sheet) be selected. I would also like the cursor to be inside the find box so whomever can type in the search results. I provided a sample on my first post but the num lock key is disabled on Windows 8.1

    Tuesday, December 03, 2013 2:10 AM
  • Hi,

    Thank you for posting in the MSDN Forum.

    I'm trying to involve some senior engineers into this issue and it will take some time. Your patience will be greatly appreciated.

    Sorry for any inconvenience and have a nice day!

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, December 05, 2013 6:12 AM
  • Hello,

    I gogoled 'excel 2010 turn on numlock and I found a partial workaround. Here it is:

    Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Long
    Private Const NumlockKey = 144
    Option Explicit
    Sub Workbook_Open()
    Application.CommandBars.ExecuteMso "FindDialogExcel"
    SendKeys "%t"               'Opens Options section
        DoEvents
    SendKeys "%hW{Enter}"       'Selects Workbook option
        DoEvents
    SendKeys "{TAB 11}"         'Moves to Find What box
        DoEvents
    SendKeys "{NUMLOCK}"
        DoEvents
             If GetKeyState(NumlockKey) = 0 Then
            SendKeys "{NUMLOCK}"
         End If
    End Sub

    I do notice if the numlock key is on it will flash really quick (it will stay on)but if it is numlock is off it stays off. One thing I would REALLY love this macro to do is put the cursor inside the find box. At this point you have to click the find box and then start the search.

    Friday, December 06, 2013 2:01 AM
  • Hello w0ls0n,
    We do not recommend using 'SendKeys' as we have often seen issues with it.
    So, I was trying to research if there is another way of invoking the Find and Replace dialog with "Workbook" scope.
    Unfortunately, Excel has nothing to select the Workbook scope.

    Although we don't recommend, during my research I found if we replace SendKeys "{TAB 11}" by SendKeys "%n", it worked for me intermittently.

    Thanks!

    Thursday, January 16, 2014 4:06 PM