assign double click event for multiple cells - easier way?

[jousley]

Hey all. I have a column of values from C4:C42. Each value in each cell is the name of another worksheet in the workbook. when you double click the cell - it unhides the corresponding worksheet. I have been successful in coding each cell individually - but I am hoping there is an easier way to do this. Here's what I got:

Please Login or Register  to view this content.

Is there an easier way than having to do this all the way to C42? All help is appreciated.
Thanks
J

[CK76]

Try...

Please Login or Register  to view this content.

[Mumps1]

Another option:
Place this macro in the code module for the "RevenueALL" sheet.

Please Login or Register  to view this content.

[Zer0Cool]

Can you post a sample of the file, would make reviewing and testing easier for those trying to help.

When you say coding each cell individually, what do you mean? Do you mean you have the above snippet repeated where C4 becomes C5, C6, etc to C24?

You can use Target instead of statically naming the cell, as target is the cell that was doubleclicked. IE: if you double click C4, target = C4. If the cells have the sheet name in them (exact) then you can replace sheet names where needed in your code with target.value. IE: if you double click C4, then target.value = C4's content, which should be the desired sheet name.

Also, what is the reason behind hiding/showing sheets in this fashion? I ask as hidden sheets are great for preventing accidental changes on a relatively static sheet, for example a sheet used as the source for many drop down validation lists on another sheet. If however you are just using it as a means of navigational aide (preventing people from needing to scroll through tabs to select a sheet), then you could entirely ditch hiding and just use hyperlinks to each sheet, you wouldn't even need a macro for that. Likewise on each sheet you could put a link back to the "Table of Contents" sheet with all the links. You could, if you wanted, also set a workbook_close event to simply activate the table of contents sheet when they are finished so it always opens for others to the TOC sheet.

[jousley]

CK76 & Mumps1 - thank you! both code snippets worked just fine - I am adding these to my personal reference notebook for future use.
Zer0Cool - great reading between the lines - I am using this for navigation - and wasn't even aware of the hyperlink - which makes this much easier to modify. I will be moving this direction.

thanks again all!
J

[Mumps1]

You are very welcome. . Glad to help.