Excel 2007 : Assign Macro to Cell Range?

[stevetothink]

I have the following macro assigned to a key stroke. I would like to assign it to a double click for a cell range. Is that possible?

For example, if I double click A2 it will run this macro across Row 2. If I double click A500, it will run this macro across row 500, etc...

Does anyone know how to do this?

Thanks,
Steve

Please Login or Register  to view this content.

[royUK]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

[MarvinP]

Hi stevetothink,

I have a very good and quick answer but you need to do this before I can give it to you.

Edit your original post by clicking on the green EDIT button below it. Then click on "Go Advanced" below the message area. Then select all the text in the message that is code. With the text selected click on the "#" Icon above the advanced message area. This will put code tags around your text and

Please Login or Register  to view this content.

Save you changes.

We will see you have complied with the formum rules and then we can answer your question.

[stevetothink]

Hi stevetothink,

I have a very good and quick answer but you need to do this before I can give it to you.

Edit your original post by clicking on the green EDIT button below it. Then click on "Go Advanced" below the message area. Then select all the text in the message that is code. With the text selected click on the "#" Icon above the advanced message area. This will put code tags around your text and

Please Login or Register  to view this content.

Save you changes.

We will see you have complied with the formum rules and then we can answer your question.

Thanks guys. Sorry about the violation. I'm still learning the rules around here. I'm looking forward to seeing your solutions to my problem.

Steve

[MarvinP]

Hey Steve - good job on finding Code Tags.

Here is the answer for your question.
Code behind an individual worksheet is called Event Code. This code can be triggered to run, depending on the event that is being performed on that sheet. In the IDE/VBA editor you need to double click on the Sheet Name in the VBA Project window. This will then show a blank VBA screen. At the top where it says "(General)" , click this dropdown and select "WorkSheet". Now the dropdown to the right lists all the possibe events that can be triggered on this sheet. Drop down this list and select "BeforeDoubleClick". This will now, in the VBA code, give you a Private Sub that will be triggered by a double click anywhere on the worksheet.

You want to only do your code if you double click on a cell between A2 and A500. To do this you need this code:

Please Login or Register  to view this content.

Target is the cell you clicked on and it is checked against A2 to A500. If the Target is in this range, you do the code. If it isn't in this range it will do nothing.

Read http://www.cpearson.com/excel/Events.aspx for a good introduction to Event code.

I hope this helps.

[stevetothink]

Hey Steve - good job on finding Code Tags.

Here is the answer for your question.
Code behind an individual worksheet is called Event Code. This code can be triggered to run, depending on the event that is being performed on that sheet. In the IDE/VBA editor you need to double click on the Sheet Name in the VBA Project window. This will then show a blank VBA screen. At the top where it says "(General)" , click this dropdown and select "WorkSheet". Now the dropdown to the right lists all the possibe events that can be triggered on this sheet. Drop down this list and select "BeforeDoubleClick". This will now, in the VBA code, give you a Private Sub that will be triggered by a double click anywhere on the worksheet.

You want to only do your code if you double click on a cell between A2 and A500. To do this you need this code:

Please Login or Register  to view this content.

Target is the cell you clicked on and it is checked against A2 to A500. If the Target is in this range, you do the code. If it isn't in this range it will do nothing.

Read http://www.cpearson.com/excel/Events.aspx for a good introduction to Event code.

I hope this helps.

That's awesome. Thank you very much for your help. I really appreciate it.

Steve

[MarvinP]

Hi Steve

You shouldn't Quote the previous post as it just makes our forum harder to read. If you would....
Edit your post and remove the Quoted text. It will save my old eyes from trying to read the same thing twice. I also fall into that groundhog day thing and think I've already read that someplace before if you quote the post above. (Rule #12 I find)

Thanks and glad you're learning Event Programming. I remember being scolded for using ByRef instead of ByVal (or vice versa) in my code by one of the real gurus. I just found a good explaination of what that does at:
http://www.tushar-mehta.com/publish_...%20ByRef.shtml