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.

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

Thanks you.

Thank you.

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

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

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.

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.

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

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