+ Reply to Thread
Results 1 to 15 of 15

Shorten Runtime & Modify Output of Search Macro

  1. #1
    Registered User
    Join Date
    07-28-2015
    Location
    Danville, IL USA
    MS-Off Ver
    2013
    Posts
    34

    Shorten Runtime & Modify Output of Search Macro

    Greetings,

    Running Office 2007, 2010, & 2013 on Windows 7 in our network environment here.

    I have a search macro that looks at all worksheets in the workbook except for one that will never contain the string being searched for. The results get displayed in a message box. This workbook will be used by many people who will access it over our internal network.

    Although the macro functions, I'm having some issues with it.

    First, the macro produces no error code and the results message box pops up rather quickly but it looks like the macro never stops running. My cursor stays in "busy" mode unless it is over the results message box. I suspect this is because the results message box is waiting for the click on the OK button. I'm afraid this will confuse the end users. Is there any way to force the cursor back to its default pointer?

    Second, the results message box give the locations of the found strings in absolute references (with the dollar signs). My manager thinks this is "ugly". How can I get rid of the dollar signs?

    Third, my manager would like it if the locations in the results message box could be hyperlinks to the actual cell listed. Is this even possible from a message box? [My manager frequently asks for the impossible. ]


    Here's an image of the results with the cursor spinning:
    Search-n-Cursor.JPG

    Here is my code:
    Please Login or Register  to view this content.
    I'm a novice at VBA, only half-way through my online Introduction to VBA course. I find lots of code via google and then modify it to meet my needs.

    Any assistance would be greatly appreciated.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Shorten Runtime & Modify Output of Search Macro

    What would you have us test your code with?

    Third, my manager would like it if the locations in the results message box could be hyperlinks to the actual cell listed. Is this even possible from a message box?
    Not AFAIAA. I believe that you could do something similar wit a UserForm emulating a MsgBox. Suggest you Google: excel vba hyperlink in msgbox for some suggested approaches.


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Expert
    Join Date
    07-31-2010
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    4,070

    Re: Shorten Runtime & Modify Output of Search Macro

    I agree with TMS, you would probably have to do it with a UserForm. You could probably set it up to create a label for each of your outputs and then apply a hyperlink to each label on click. Not easy stuff to do either.
    If you are happy with my response please click the * in the lower left of my post.

  4. #4
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Shorten Runtime & Modify Output of Search Macro

    Hi PKW57,

    My cursor stays in "busy" mode unless it is over the results message box. I suspect this is because the results message box is waiting for the click on the OK button. I'm afraid this will confuse the end users. Is there any way to force the cursor back to its default pointer?
    It can probably be done with some fancy footwork, but is not recommended. An alternate approach such as a custom UserForm as previously suggested is the way to go.

    Second, the results message box give the locations of the found strings in absolute references (with the dollar signs). My manager thinks this is "ugly". How can I get rid of the dollar signs?
    I hate the '$' signs too. They are easy to remove.
    Please Login or Register  to view this content.
    my manager would like it if the locations in the results message box could be hyperlinks to the actual cell listed. Is this even possible from a message box?
    See the attached file that uses a UserForm with the code that follows. This should get you started with what you want. It is implemented as follows. The User:
    a. Opens a UserForm by Clicking a CommandButton. The UserForm can be opened in several different ways, but I find the CommandButton approach to usually be the most convenient.
    b. Fills in a TextBox with the item to be searched for (similar to your InputBox).
    c. Selects 'Search'
    d. A 'ListBox' in the UserForm is populated with the search results.
    e. 'Double Clicks' a line in the list box to go to that cell (similar to Hyperlink).

    UserForm1 module code:
    Please Login or Register  to view this content.
    Ordinary Code module code (such as Module1):
    Please Login or Register  to view this content.
    There are two ways to implement a UserForm:
    a. Modal - Locks out Excel except for the UserForm.
    b. Modeless - Allows access to Excel Resources.

    Lewis

  5. #5
    Registered User
    Join Date
    07-28-2015
    Location
    Danville, IL USA
    MS-Off Ver
    2013
    Posts
    34

    Re: Shorten Runtime & Modify Output of Search Macro

    Thanks you.

  6. #6
    Registered User
    Join Date
    07-28-2015
    Location
    Danville, IL USA
    MS-Off Ver
    2013
    Posts
    34

    Re: Shorten Runtime & Modify Output of Search Macro

    Thank you.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Shorten Runtime & Modify Output of Search Macro

    You're welcome. Thanks for the rep.

  8. #8
    Registered User
    Join Date
    07-28-2015
    Location
    Danville, IL USA
    MS-Off Ver
    2013
    Posts
    34

    Re: Shorten Runtime & Modify Output of Search Macro

    LJMetzger,

    Thank you for your detailed response. Good grief, how many years will it take me to understand all of this stuff?!

    If I take your code and paste it into my workbook, I get the following error in the Form:

    Compile Error.JPG

    Correct me if I'm wrong but, I think this has something to do with the
    Please Login or Register  to view this content.
    statement?

    The sample file you attached to your response however runs just fine.

    If I comment out the
    Please Login or Register  to view this content.
    statement in either or both the Form and the Module, I get this Runtime Error 424 error:

    Runtime Error.JPG

    I saw that your test worksheets all had
    Please Login or Register  to view this content.
    in them so I added it to all of my other Procedures and Modules. This broke a lot of my other VBA and made no difference in the way your code ran within my workbook.

    I am attaching my workbook for reference. There is quite a bit of code involved and I'm positive there are more elegant ways of doing things but I'm piecing it together from google searches, a VBA reference book, and help from the forums like this one. The attached file is my most current functional revision and has none of the changes I have attempted in my effort to create a global search function.

    150903 QA-RF-1012 QMS Document Map.xlsm

    Thanks again for your time and patience. It is truly appreciated.

    ~ Phil

  9. #9
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Shorten Runtime & Modify Output of Search Macro

    Option Explicit just forces you to declare/define all your variables. This helps to avoid simple coding errors caused by mi-spelling variable names. It can also make the code more efficient as it is useful to actually define the variable type. Some variable types will take up more space than others and some actions will cause a variable to be coerced from one type to another.

    So, for example:
    Please Login or Register  to view this content.
    will define the variable and will prevent
    Please Login or Register  to view this content.
    from complaining, but:
    Please Login or Register  to view this content.
    is better.

    The presence or absence of Option Explicit should not cause an error. The absence will allow potential compilation errors not to be picked up early in development. Its presence will stop some of the trivial errors but, even if the code compiles, it doesn't guarantee that it will run. However, run time errors can just as easily be cause by the presence or absence of the data that you expect. For example, if you use .SpecialCells(xlCellTypeBlanks) to select blank cells and there aren't any, it will cause a run time error.

    Regards, TMS

  10. #10
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,093

    Re: Shorten Runtime & Modify Output of Search Macro

    Your sample workbook doesn't have all the code it needs, specifically, the Global Search Macro. It also doesn't have a form or form code present.

  11. #11
    Registered User
    Join Date
    07-28-2015
    Location
    Danville, IL USA
    MS-Off Ver
    2013
    Posts
    34

    Re: Shorten Runtime & Modify Output of Search Macro

    Yes, the file I attached was my last functional revision.

    Here is the version I modified this morning and ran into problems with.

    150908 QA-RF-1012 QMS Document Map.xlsm

  12. #12
    Registered User
    Join Date
    07-28-2015
    Location
    Danville, IL USA
    MS-Off Ver
    2013
    Posts
    34

    Re: Shorten Runtime & Modify Output of Search Macro

    All,

    So I've been beating on this code and I'm still stumped.


    I modified the Form by defining the variables as objects. See below:

    Please Login or Register  to view this content.
    Now I get an error message saying "Object variable or With block variable not set." The debugger takes me to the module and indicates the problem occurs at:

    Please Login or Register  to view this content.
    However I know the problem is still in the Form because when I open it in the VBA editor all I see is the generic blank form, although if I hit F7 the code I pasted in is there.
    I don't understand what I am supposed to set the variables to! The error message doesn't specify which With statement is causing the issue and some of the variables won't have a value until I enter the search text into the boxes.

  13. #13
    Registered User
    Join Date
    07-28-2015
    Location
    Danville, IL USA
    MS-Off Ver
    2013
    Posts
    34

    Re: Shorten Runtime & Modify Output of Search Macro

    All,

    I just noticed that the sample file sent to me by Mr. Metzger was an Excel 97 - 2003 .xls file.

    I'm running Excel 2013 and my file is saved in the .xlsm format. Could this be the source of the problem? I know there are some differences in VBA from one release of Excel to the next.

    All of my experience with VBA (minuscule as it is) has been using Excel 2013.

  14. #14
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Shorten Runtime & Modify Output of Search Macro

    Trevor - Thank you for helping out.

    PKW57,

    I apologize for not responding sooner to your Sept 8th message, but either ExcelForum never notified that you responded, or I did not notice that I was notified. I assure you that I was not trying to ignore you.

    Either way, see the attached updated copy of your file, that should work correctly as is. I made a couple of small changes to improve the look and feel and to correct a small error. Neither of the changes had anything to do with your problem.

    Your problem was my fault, for not explicitly telling you how to import the code.

    Sometimes you can cut and paste code, such as with an ordinary code module ModUserForm1, or you can export the file (code) from one Workbook, and import the file (code) into another workbook.

    However UserForm modules are different. With UserForm modules you MUST export the file (code) from one Workbook, and import the file (code) into another workbook. This is because UserForms consist of two parts, the UserForm itself, and the UserForm code. You apparently created a blank UserForm1, then copied and pasted the code.

    The error was caused by the BLANK UserForm1 itself, and had nothing to do with .xls or .xlsm, etc.

    Again, please forgive me for the delay.

    Lewis
    Attached Files Attached Files

  15. #15
    Registered User
    Join Date
    07-28-2015
    Location
    Danville, IL USA
    MS-Off Ver
    2013
    Posts
    34

    Re: Shorten Runtime & Modify Output of Search Macro

    Lewis,

    No apologies necessary. I never complain about free help!

    I was completely unaware of the necessity of importing Forms. I believe I've mentioned that I'm only half way through an online intro to VBA course and I haven't worked with forms yet.

    I have to tell you it was driving me nuts. I saved your sample file as a .xlsm and then ran it side by side with your code copied into my file. Of course your sample file ran just fine but when running it from my file I could see all these undefined variables in the Locals window.

    Lesson learned - import Forms! Thanks for the contribution to my VBA education as well as the assistance with the coding.

    I greatly appreciate your time and patience.

    ~ Phil

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Word - modify macro to loop search procedure and then extract pages as pdf
    By adamstarr12 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-20-2016, 06:47 AM
  2. [SOLVED] Modify code at runtime
    By chipnputt in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-06-2013, 07:24 PM
  3. [SOLVED] Modify Macro to search columns before rows
    By m3k1rk in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-16-2013, 11:51 AM
  4. Modify Macro to Distribute Output Entries into Separated Worksheets
    By Seraph122 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-15-2013, 08:25 PM
  5. Modify Macro Search & Replace Formatting for part of cell
    By tkeiffer in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-02-2011, 05:48 PM
  6. Search by more than 2 criteria (help in macro modify)
    By Wiecman in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-08-2010, 05:44 AM
  7. Replies: 1
    Last Post: 08-04-2009, 10:43 PM

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