Absolutely!
What you're looking for is a specific type of macro (or subroutine)
called an "Event".
There are lots of kinds of events.
the "workbook_open" event runs when a workbook is open.
the "workbook_activate" or "workbook_deactivate" events run when you
switch between windows.
What you describe would be the "Worksheet_Change" event.
To create this, right click on the sheet "tab" and select "view code".
this will take you to the VBA code module for THIS SHEET.
Now, depending on the layout of your screen, at the top-left of the
code sheet, there is a pulldown.
right now, the only selections are "(general)" and "worksheet".
select "worksheet".
Now, I think the "default" event is the "SelectionChange" event. This
would run whenever you click (or arrow)
from one cell to another. probably not ideal for what you want.
In the upper RIGHT pulldown, select "Change".
It will create a line:
Private Sub Worksheet_Change(ByVal Target As Range)
Notice that when this runs, it receives a Range object called
"Target".
in your sub, you'll want to test this Target to see if is the cell
you're watching.
Like:
If (Target.Address = "$C$6") then
'run code or execute sub
End If
Now, keep in mind that if your SUB changes the value of THIS cell, it
will call the change event AGAIN.
Like:
If (Target.Address = "$C$6") then
Range(Target.Address) = Ucase(Target.Value)
End If
works Great for changing the cell value to upper case, but it will be
stuck in a continuous loop
because the macro keeps changing the cell contents, so it calls the
macro again!
To get past this, a useful Method is the EnableEvents method.
If (Target.Address = "$C$6") then
Application.EnableEvents = false
Range(Target.Address) = Ucase(Target.Value)
Application.EnableEvents = true
End If
this will turn off the Event Handler, change the cell, then turn it
back on.
this should give you some toys to play with and explore!!!
good luck,
Paul
On Aug 25, 5:46 am, vbiad <a.dijks...@vbi.nl> wrote:
> Hello Experts,
>
> I am looking for a way to start a macro by clicking on a specific cell
> in the spreadsheet. The macro have to run each time the value in the
> cell is changed.
> Is this possible???
>
> Thank in advance for the help
>
> Kind regard
>
> Hans Dijkstra
--~--~---------~--~----~------------~-------~--~----~
----------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 5,200 subscribers worldwide and receive many nice notes about the
learning and support from the group. Our goal is to have 10,000 subscribers by
the end of 2009. Let friends and co-workers know they can subscribe to group at
http://groups.google.com/group/excel-macros/subscribe
-~----------~----~----~----~------~----~------~--~---
