Compile error: Procedure too large. Executing macro via hyperlink.

rain4u,

Give this a try:

Please Login or Register  to view this content.

Hope this helps,
~tigeravatar

One problem I noticed with this awesome code. Even if you click anywhere outside E7 to I29 are it still tries to run macro. Can this be limited to this range only? I have been very structured with my other codes and this fits in very well. Learning every day here!

Cheers
Rain

rain4u,

One problem I noticed with this awesome code. Even if you click anywhere outside E7 to I29 are it still tries to run macro. Can this be limited to this range only?

Anywhere outside? It should only try to run the code in columns 5 (E) through 11 (K). That is in this part here:

Please Login or Register  to view this content.

I didn't know if you used saturday and sunday so I included those anyway. It sounds like you don't use them, so you can just delete those lines.

Hope this helps,
~tigeravatar

Thank you. Nope. Using till Friday only, so I will delete those lines. When I click on something really random like AF301 it still runs macro. It looks like it executes this line Application.Run "copy_master_to_hyperlink_all_sites"

Any ideas?
Rain

rain4u,

Ah, ok. I see what the problem is. I put this line in the wrong spot:

Please Login or Register  to view this content.

Instead of being in the Worksheet_SelectionChange event, it should be in the HyperlinkByDay function, at the top:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

That problem is now solved.Macros execute only if I click within E7 to I29. Thank you for that.

Can you help me with this last request. You know this line
If ThisCell.Row >= 7 And ThisCell.Row <= 29 Then Run "copy_master_to_hypelink_all_sites"

Can we make here a conditional rule to choose between 4 codes instead launching this "copy_master_to_hypelink_all_sites"

The 4 codes are following
Application.Run "copy_master_to_hypelink_all_sites"
Application.Run "copy_master_to_hypelink_Basildon"
Application.Run "copy_master_to_hypelink_Burnley"
Application.Run "copy_master_to_hypelink_Ilkeston"

I have x4 OptionButtons on the sheet.
OptionButton1
OptionButton2
OptionButton3
OptionButton4

Not to be confused with tickboxes. By OptionButtons I mean the ones that you can click and little dot will appear inside. The ones when only 1 can be clicked i.e. as soon as you click any other box the little dot will move to that clicked box.

Now I would like to add this feature. When clicked to any cell listed in the code it would first check which OptionBox is currently ticked to determine which code to launch.

OptionButton1 - Application.Run "copy_master_to_hypelink_all_sites"
OptionButton2 - Application.Run "copy_master_to_hypelink_Basildon"
OptionButton3 - Application.Run "copy_master_to_hypelink_Burnley"
OptionButton4 - Application.Run "copy_master_to_hypelink_Ilkeston"

then it would carry on launching macros as per addressed target

Please Login or Register  to view this content.

Example:
Lets say OptionButton2 is currently ticked. If I would now click cell E8
it would first see that OptionButton2 is ticked and it runs Application.Run "copy_master_to_hypelink_Basildon"
then as per target code it would carry on with Application.Run "hyperlink_backlog_DNB_monday"



Would you be so kind to assist me with this?
Cheers
Rain

rain4u,

The answer you're looking for would be rather specific, which is good. However, I need to know what kind of option buttons you're using: ActiveX controls or Form controls. I also need to know if the option button caption (the text for the option button) is the same as the function you want to run, or if there is no caption and the option buttons are just next to cells that contain that text, or if you use a linked cells. It might be easiest to upload a sample workbook so I can take a look at what you're using.

~tigeravatar

Hi Tiger
I'm bit newbie in this. Getting better and better though. In order not to give you a wrong answer I just copied the sheet out of the workbook. Attached to this post. option buttons are up there "All sites", "Basildon", "Brunley" and "Ilkeston"

Thanks for finding time for me. Its very appreciated.

Cheers
Rain

rain4u,

Attached is a modified version of the file you provided. You were using ActiveX controls and the option button captions had a partial match. I replaced the old line:

Please Login or Register  to view this content.

With this new section:

Please Login or Register  to view this content.

Hope that helps,
~tigeravatar

Thank you very much. Everything works like a charm.
Got it working! Very impressive. I like the way you have tied everything neatly together even going as far as taking the parts of the macro names from the button names. It will make my life easier when I will try to tweak few things and help my colleagues at work so it will work with their accounts as well. Just when you think you have realized the excels limits, it comes back and surprises you. I think the sky is the limit with excel. I think I will never learn all its functions.
Thank you Tiger for sharing your time and knowledge. Simply awesome.

Cheers
Rain

rain4u,

Add this line in the Worksheet_SelectionChange event:

Please Login or Register  to view this content.

So that is looks like this:

Please Login or Register  to view this content.

~tigeravatar

Thank you. This fixed it.