Excel VBA Double Click or Hyperlink That Filters For Value In Another Workbook?

[jgb727]

Dear All,
Need some help with this code. Let me paint the picture - I have a database full of client data.
Workbook 1 (Active) (Multiple Sheets for each store)



Column A10:A45,0000 = Unique Client's ID For Each Client
Column B10:B45:000 = Client's Last Name
Column C10:C45,000 = Client's First Name
Column D:E = Client Contact Data for each row based on unique client ID

Workbook 2 (Closed and saved in a shared drive) (master sheet with all transaction history):


Column A10:A45,0000 = Unique Client's ID For Each Client
Column B10:B45:000 = Client's Last Name
Column C10:C45,000 = Client's First Name
Column D:E = Client Transaction History for each row based on unique client ID

I want to create a macro that will allow me to double click on a client's unique ID in Workbook 1 and this will open up Workbook 2 and filter for that client's transactions. EX- In workbook1 - i double click the client ID "XYZ" and then workbook 2's open's and Column A is filtered to only show client ID "XYZ". I also want to error proof it, just in case the client doesn't appear in Workbook 2 for whatever reason.
Finally, I only want to open Workbook 2 if it's closed. If it's open, i just want to switch from workbook 1 to workbook2. With each double click in Workbook 1, the filters in workbook 2 should be reset and then re-filtered for the double click value.

Thanks for all your help and support. I look forward to some responses.
Best,
JB

[p45cal]

Can you confirm that you only want to eyeball (not change) the data in Workbook 2 and never want to bring any of that data into Workbook 1?

It's easy enough to set up an ODBC connection to bring in data from Workbook 2, whether it's open or not, and just change the parameters in vba according to what's in the double-clicked cell.

[jgb727]

Hi,

I just want to eyeball the data and nothing else.

[p45cal]

OK. To save me/us faffing about with files I think you have (and thinking wrongly), can you supply 2 small files;
one with the sheet that you want to double-click on, with say 10 or 20 rows of client ids, including some some not in the master file
and another file being like the master workbook on the shared drive with say 100 or so rows of data, most matching the IDs in the other file.

Then I can properly test and supply code most like what you'll need and that you'll only have to tweak minimally.

Does the master file have only one sheet?

Oh, and if the file's initially closed, do you want it to remain open, or do you want it closed again after each double-click?

[jgb727]

Hi Again,

Master file does only have one sheet. I would love if the file can remain open.

[p45cal]

Tomorrow (UK time)…

[p45cal]

jgb727, you've cross-posted this on other sites (that's OK) but without supplying links:
http://stackoverflow.com/questions/37884417/double-click-or-hyperlink-that-filters-for-value-in-another-workbook
http://answers.microsoft.com/en-us/office/forum/office_2010-customize/excel-vba-double-click-or-hyperlink-that-filters/452d1fc0-9621-4dd9-ba23-5d73d8f871f8
http://www.mrexcel.com/forum/excel-questions/947992-excel-visual-basic-applications-double-click-hyperlink-filters-value-another-workbook.html

I realise you're new here and at the other sites above, but there are etiquette/rules to abide by in these forums.
Have a read of http://www.excelguru.ca/content.php?184
Your problem doesn't just carry a 5 minute solution so this is even more important.
Would you do the 'right thing', and include links to your cross posts at all the sites you have cross posted to (so that each site can cross reference the others to see progress) and if there are more sites than I've come across could you include them here?

In the attached is a bit of code to play with/get you started in workbook1. It's in two places: in a code module and in sheet1's code-module.
I changed the files so that they conformed to the spec. in msg#1.
I haven't bothered to error trap a client not existing in workbook2, it just filters everything out.
You will have to tweak the code before trying to use it (see comments in the code for where).

ps. Your first message looks more like a job specification rather than a plea for help; these forums are usually to help, not really a source of free coding.
pps. Do pay attention to the etiquette thing - you may find help quickly dries up if you don't.

[jgb727]

P45cal,

Thank you so much for your help and direction. Also, I appreciate you helping me find my way in the forums. The code looks pretty good, but I need to do some tinkering. I will follow up if I have any more questions, but you did a great job putting me on the right path.

Thanks buddy!