Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Run a macro when clicking a cell.
Hi,
I'm sure this has been discussed before but I'm a complete novice on Excel so any help is appreciated. I've created a Calendar Control and can call the macro up either by clicking a button or right-clicking the mouse. When I click on a date in the Calendar, the date populates the selected cell. However, what I'd also like to do is to run the macro when I click on a specific cell. I've seen another post which said copy the cell and paste the picture then you can assign the macro to the cell but the date populates the cell you clicked on prior to the macro running so that's no good. Can anyone advise either how to run the macro by clicking the cell so that the date populates the clicked cell, or maybe a double-click which I've seen in another post. I can email my workbook to anyone to have a look if required. Kind regards. K-Rod. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Run a macro when clicking a cell.
You can set up a cell to call a macro when it is clicked, but there is a
problem. The macro will be called even if you run across the cell with the arrow keys or ENTER key. That is why a double click is usually reccommended. Try this worksheet event macro: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Set t = Target Set a1 = Range("A1") If Intersect(t, a1) Is Nothing Then Exit Sub Cancel = True Call hello End Sub Because it is worksheet code, it is very easy to install and automatic to use: 1. right-click the tab name near the bottom of the Excel window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you have any concerns, first try it on a trial worksheet. If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu2007h " wrote: Hi, I'm sure this has been discussed before but I'm a complete novice on Excel so any help is appreciated. I've created a Calendar Control and can call the macro up either by clicking a button or right-clicking the mouse. When I click on a date in the Calendar, the date populates the selected cell. However, what I'd also like to do is to run the macro when I click on a specific cell. I've seen another post which said copy the cell and paste the picture then you can assign the macro to the cell but the date populates the cell you clicked on prior to the macro running so that's no good. Can anyone advise either how to run the macro by clicking the cell so that the date populates the clicked cell, or maybe a double-click which I've seen in another post. I can email my workbook to anyone to have a look if required. Kind regards. K-Rod. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Run a macro when clicking a cell.
On 28 Apr, 22:59, Gary''s Student
wrote: You can set up a cell to call a macro when it is clicked, but there is a problem. *The macro will be called even if you run across the cell with the arrow keys or ENTER key. *That is why a double click is usually reccommended. *Try this worksheet event macro: Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) Set t = Target Set a1 = Range("A1") If Intersect(t, a1) Is Nothing Then Exit Sub Cancel = True Call hello End Sub Because it is worksheet code, it is very easy to install and automatic to use: 1. right-click the tab name near the bottom of the Excel window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you have any concerns, first try it on a trial worksheet. If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu2007h " wrote: Hi, I'm sure this has been discussed before but I'm a complete novice on Excel so any help is appreciated. I've created a Calendar Control and can call the macro up either by clicking a button or right-clicking the mouse. *When I click on a date in the Calendar, the date populates the selected cell. *However, what I'd also like to do is to run the macro when I click on a specific cell. I've seen another post which said copy the cell and paste the picture then you can assign the macro to the cell but the date populates the cell you clicked on prior to the macro running so that's no good. Can anyone advise either how to run the macro by clicking the cell so that the date populates the clicked cell, or maybe a double-click which I've seen in another post. I can email my workbook to anyone to have a look if required. Kind regards. K-Rod.- Hide quoted text - - Show quoted text - Hi, Thanks for the responses. I can't get either of these to work though. When I double-click a cell I get the error "Compile error. Sub or function not defined". Do I need to link this into my calendar in some way? Thanks for your help. K-Rod. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Run a macro when clicking a cell.
Did you see my post with the macro and the link to Ron de Bruin's site?
If so, have you gone to Ron's site for info? Gord Dibben MS Excel MVP On Wed, 30 Apr 2008 09:57:02 -0700 (PDT), wrote: I can't get either of these to work though. When I double-click a cell I get the error "Compile error. Sub or function not defined". Do I need to link this into my calendar in some way? Thanks for your help. K-Rod. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Run a macro when clicking a cell.
On 1 May, 00:35, Gord Dibben <gorddibbATshawDOTca wrote:
Did you see my post with the macro and the link to Ron de Bruin's site? If so, have you gone to Ron's site for info? Gord Dibben *MS Excel MVP On Wed, 30 Apr 2008 09:57:02 -0700 (PDT), wrote: I can't get either of these to work though. When I double-click a cell I get the error "Compile error. *Sub or function not defined". Do I need to link this into my calendar in some way? Thanks for your help. K-Rod.- Hide quoted text - - Show quoted text - Hi, Yes I visited the link and copied the code but get a runtime error. Can anyone help out? I can send my workbook via email if someone would be so kind as to have a look at it? As I mentioned, I know nothing about VB and just want to get this calendar working. Thanks, K-Rod. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Run a macro when clicking a cell.
See here for step by step instructions.
http://www.fontstuff.com/vba/vbatut07.htm If you scroll to the page bottom, there are downloads available. "Runime" error could arise for a number of reasons. If you want you can upload the workbook to one of these sites then post back with the URL http://www.freefilehosting.net/ http://savefile.com/ Gord On Sat, 3 May 2008 02:59:43 -0700 (PDT), wrote: Hi, Yes I visited the link and copied the code but get a runtime error. Can anyone help out? I can send my workbook via email if someone would be so kind as to have a look at it? As I mentioned, I know nothing about VB and just want to get this calendar working. Thanks, K-Rod. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Run a macro when clicking a cell.
On May 3, 6:09*pm, Gord Dibben <gorddibbATshawDOTca wrote:
See here for step by step instructions. http://www.fontstuff.com/vba/vbatut07.htm If you scroll to the page bottom, there are downloads available. "Runime" error could arise for a number of reasons. If you want you can upload the workbook to one of these sites then post back with the URL http://www.freefilehosting.net/http://savefile.com/ Gord On Sat, 3 May 2008 02:59:43 -0700 (PDT), wrote: Hi, Yes I visited the link and copied the code but get a runtime error. Can anyone help out? *I can send my workbook via email if someone would be so kind as to have a look at it? *As I mentioned, I know nothing about VB and just want to get this calendar working. Thanks, K-Rod.- Hide quoted text - - Show quoted text - Hi Gord, Thanks for getting back to me. I've followed those step by step instructions and I can get the calendar to pop up either with right-click or button. What I want to do now is have it pop up with a double-click on a particular cell, or cells. I've uploaded the workbook here as advised. http://www.freefilehosting.net/download/3gk65 Thanks again. K-Rod. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Run a macro when clicking a cell.
All you uploaded was a Class Module.........*.cls
Using the the Excel Calendar Workbook from Martin's site. Right-click on Sheet1 tab and "View Code" Copy/paste this into that sheet module. Private Sub Worksheet_BeforeDoubleClick _ (ByVal Target As Range, Cancel As Boolean) OpenCalendar Cancel = True End Sub Alt + q to return to the Excel window. Double-click on any cell. Gord On Mon, 5 May 2008 13:05:37 -0700 (PDT), wrote: Hi Gord, Thanks for getting back to me. I've followed those step by step instructions and I can get the calendar to pop up either with right-click or button. What I want to do now is have it pop up with a double-click on a particular cell, or cells. I've uploaded the workbook here as advised. http://www.freefilehosting.net/download/3gk65 Thanks again. K-Rod. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Run a macro when clicking a cell.
On 5 May, 23:41, Gord Dibben <gorddibbATshawDOTca wrote:
All you uploaded was a Class Module.........*.cls Using the the Excel Calendar Workbook *from Martin's site. Right-click on Sheet1 tab and "View Code" Copy/paste this into that sheet module. Private Sub Worksheet_BeforeDoubleClick _ (ByVal Target As Range, Cancel As Boolean) * * *OpenCalendar * * * * *Cancel = True End Sub Alt + q to return to the Excel window. Double-click on any cell. Gord On Mon, 5 May 2008 13:05:37 -0700 (PDT), wrote: Hi Gord, Thanks for getting back to me. I've followed those step by step instructions and I can get the calendar to pop up either with right-click or button. *What I want to do now is have it pop up with a double-click on a particular cell, or cells. I've uploaded the workbook here as advised. http://www.freefilehosting.net/download/3gk65 Thanks again. K-Rod.- Hide quoted text - - Show quoted text - Hi Gord, I'm still getting the error. How do I upload the full workbook please? Thanks, K-Rod. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
clicking all the way to another cell | Excel Worksheet Functions | |||
Run a macro after clicking into cell | Excel Worksheet Functions | |||
Using Macro to sort without clicking on macro button | Excel Discussion (Misc queries) | |||
Colour by clicking cell | New Users to Excel | |||
Run macro from clicking on a cell | Excel Discussion (Misc queries) |