Automate a macros when data in a cell changes (e.g.when using quer

Is there any way that a macro can be activated when the data of a specific 
cell changes?

I assigned a macro to a "check box" button from the Tool Bar "Forms", and 
used the  "cell link" from the "check box" properties, so when the data 
changes in the cell linked the "check box" is marked and the macro will 
start.  So far, the "check box" gets marked when data changes but the macro 
doesn't start. The only way the macro starts is when I click in the "check 
box".

0
jwwjd (4)
6/22/2005 1:24:03 PM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
636 Views

Similar Articles

[PageSpeed] 55

Maybe you could use a worksheet event that will fire when the cell's value
changes.

If it's changed by someone's typing...

I put this code in a general module and assigned the procedure to the checkbox.

Option Explicit
Sub testme(Optional myCBX As CheckBox)

    If myCBX Is Nothing Then
        Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)
    End If

    If myCBX.Value = xlOn Then
        MsgBox "It's on"
    Else
        MsgBox "It's off"
    End If
    
End Sub

Then behind the worksheet module, I put this code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

    Dim myCBX As CheckBox
    
    Set myCBX = Worksheets("sheet1").CheckBoxes("check box 1")

    If Intersect(Target, Me.Range(myCBX.LinkedCell)) Is Nothing Then
        Exit Sub
    End If
    
    Call testme(myCBX)

End Sub

Maybe it'll give you a few ideas you can use.

jwwjd wrote:
> 
> Is there any way that a macro can be activated when the data of a specific
> cell changes?
> 
> I assigned a macro to a "check box" button from the Tool Bar "Forms", and
> used the  "cell link" from the "check box" properties, so when the data
> changes in the cell linked the "check box" is marked and the macro will
> start.  So far, the "check box" gets marked when data changes but the macro
> doesn't start. The only way the macro starts is when I click in the "check
> box".

-- 

Dave Peterson
0
ec357201 (5290)
6/22/2005 11:47:25 PM
I use this code when I want to fire a macro by changing the data in a
specific cell (in my case, cell E4. And the macro fired is called "Update")

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("E4")) Is Nothing Then Application.Run
"Update"

Rightclick sheet tab, view code. Paste this stuff in there.  Make sure you
select the cell you want, and the macro you want.

"Dave Peterson" <ec35720@netscapeXSPAM.com> wrote in message
news:42B9F88D.C7E26348@netscapeXSPAM.com...
> Maybe you could use a worksheet event that will fire when the cell's value
> changes.
>
> If it's changed by someone's typing...
>
> I put this code in a general module and assigned the procedure to the
checkbox.
>
> Option Explicit
> Sub testme(Optional myCBX As CheckBox)
>
>     If myCBX Is Nothing Then
>         Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)
>     End If
>
>     If myCBX.Value = xlOn Then
>         MsgBox "It's on"
>     Else
>         MsgBox "It's off"
>     End If
>
> End Sub
>
> Then behind the worksheet module, I put this code:
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
>
>     Dim myCBX As CheckBox
>
>     Set myCBX = Worksheets("sheet1").CheckBoxes("check box 1")
>
>     If Intersect(Target, Me.Range(myCBX.LinkedCell)) Is Nothing Then
>         Exit Sub
>     End If
>
>     Call testme(myCBX)
>
> End Sub
>
> Maybe it'll give you a few ideas you can use.
>
> jwwjd wrote:
> >
> > Is there any way that a macro can be activated when the data of a
specific
> > cell changes?
> >
> > I assigned a macro to a "check box" button from the Tool Bar "Forms",
and
> > used the  "cell link" from the "check box" properties, so when the data
> > changes in the cell linked the "check box" is marked and the macro will
> > start.  So far, the "check box" gets marked when data changes but the
macro
> > doesn't start. The only way the macro starts is when I click in the
"check
> > box".
>
> -- 
>
> Dave Peterson


0
6/23/2005 1:42:10 AM
Dave Peterson's suggestion worked out smoothly, but only when the data is 
changed manually. Is there a way of doing the same but  when the value is 
affected by a formula? according to the result of the formula (eg. true or 
false)


"jwwjd" wrote:

> Is there any way that a macro can be activated when the data of a specific 
> cell changes?
> 
> I assigned a macro to a "check box" button from the Tool Bar "Forms", and 
> used the  "cell link" from the "check box" properties, so when the data 
> changes in the cell linked the "check box" is marked and the macro will 
> start.  So far, the "check box" gets marked when data changes but the macro 
> doesn't start. The only way the macro starts is when I click in the "check 
> box".
> 
0
jwwjd (4)
6/23/2005 7:15:02 PM
So you have a linked cell that's updated by a formula, too.

If you click the checkbox, doesn't the formula go away?

(This sounds like it could be quite a problem to me.)

Anyway, this worked for me.

The checkbox is on Sheet1 (which has a codename of Sheet1) that has a linked
cell of A1.

This is the code behind Sheet1:

Option Explicit
Public OldA1Value As Variant
Sub Worksheet_Calculate()

    Dim myCBX As CheckBox
    
    If Me.Range("a1").Value = OldA1Value Then
        'do nothing
    Else
        OldA1Value = Me.Range("a1").Value
        Set myCBX = Sheet1.CheckBoxes("check box 1")
        Call testme(myCBX)
    End If

End Sub

This is the code behind ThisWorkbook:

Option Explicit
Private Sub Workbook_Open()
    Sheet1.OldA1Value = Sheet1.Range("a1").Value
End Sub

(when the workbook opens, it gets the initial value of the range)

And this is in a General module:

Option Explicit
Sub testme(Optional myCBX As CheckBox)

    If myCBX Is Nothing Then
        Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)
    End If

    If myCBX.Value = xlOn Then
        MsgBox "It's on"
    Else
        MsgBox "It's off"
    End If
    
End Sub

=====
But it still sounds like a problem if you ever click on that checkbox.

jwwjd wrote:
> 
> Dave Peterson's suggestion worked out smoothly, but only when the data is
> changed manually. Is there a way of doing the same but  when the value is
> affected by a formula? according to the result of the formula (eg. true or
> false)
> 
> "jwwjd" wrote:
> 
> > Is there any way that a macro can be activated when the data of a specific
> > cell changes?
> >
> > I assigned a macro to a "check box" button from the Tool Bar "Forms", and
> > used the  "cell link" from the "check box" properties, so when the data
> > changes in the cell linked the "check box" is marked and the macro will
> > start.  So far, the "check box" gets marked when data changes but the macro
> > doesn't start. The only way the macro starts is when I click in the "check
> > box".
> >

-- 

Dave Peterson
0
ec357201 (5290)
6/23/2005 10:06:00 PM
Thank you very much Dave Peterson. It is amazing how many things excel can do 
with the  proper programing. Thanks for  your help. This descussion room is 
great!!!

I'm using the check box just to activate the macro; but your are right once 
I click in the box my formula is override by either False or true. I'm 
thinking of no touching the check box. 
Thanks again
jwwjd 

"Dave Peterson" wrote:

> So you have a linked cell that's updated by a formula, too.
> 
> If you click the checkbox, doesn't the formula go away?
> 
> (This sounds like it could be quite a problem to me.)
> 
> Anyway, this worked for me.
> 
> The checkbox is on Sheet1 (which has a codename of Sheet1) that has a linked
> cell of A1.
> 
> This is the code behind Sheet1:
> 
> Option Explicit
> Public OldA1Value As Variant
> Sub Worksheet_Calculate()
> 
>     Dim myCBX As CheckBox
>     
>     If Me.Range("a1").Value = OldA1Value Then
>         'do nothing
>     Else
>         OldA1Value = Me.Range("a1").Value
>         Set myCBX = Sheet1.CheckBoxes("check box 1")
>         Call testme(myCBX)
>     End If
> 
> End Sub
> 
> This is the code behind ThisWorkbook:
> 
> Option Explicit
> Private Sub Workbook_Open()
>     Sheet1.OldA1Value = Sheet1.Range("a1").Value
> End Sub
> 
> (when the workbook opens, it gets the initial value of the range)
> 
> And this is in a General module:
> 
> Option Explicit
> Sub testme(Optional myCBX As CheckBox)
> 
>     If myCBX Is Nothing Then
>         Set myCBX = ActiveSheet.CheckBoxes(Application.Caller)
>     End If
> 
>     If myCBX.Value = xlOn Then
>         MsgBox "It's on"
>     Else
>         MsgBox "It's off"
>     End If
>     
> End Sub
> 
> =====
> But it still sounds like a problem if you ever click on that checkbox.
> 
> jwwjd wrote:
> > 
> > Dave Peterson's suggestion worked out smoothly, but only when the data is
> > changed manually. Is there a way of doing the same but  when the value is
> > affected by a formula? according to the result of the formula (eg. true or
> > false)
> > 
> > "jwwjd" wrote:
> > 
> > > Is there any way that a macro can be activated when the data of a specific
> > > cell changes?
> > >
> > > I assigned a macro to a "check box" button from the Tool Bar "Forms", and
> > > used the  "cell link" from the "check box" properties, so when the data
> > > changes in the cell linked the "check box" is marked and the macro will
> > > start.  So far, the "check box" gets marked when data changes but the macro
> > > doesn't start. The only way the macro starts is when I click in the "check
> > > box".
> > >
> 
> -- 
> 
> Dave Peterson
> 
0
jwwjd (4)
6/24/2005 2:06:03 PM
Reply:

Similar Artilces:

blank cell instead of x
I need a formula to have a blank cell instead of a "x" eg: result should be: aa ab ac ad ae af ag ah ai aj ak al am an ao 2 6 3 6 2 5 17 20 15 205 18 6 3 6 2x 5 3 1 5 7 19 12 133 1 5 7x the formula i used is: =IF(AF2>=42,AA2&""&"x",IF(AF2="","",AA2)) on row 3 there are blank cells (null string contains formulas ) when I apply the formu...

Outlook 0x800ccc0d error when Norton e-mail protect enabled: see hosts
This post is made to help others solve this issue, based on my experience. Symptom: - Outlook works perfectly well when Norton Anti-Virus e-mail protection is disabled - Outlook cannot retrieve incoming messages when NAV e-mail protection is enabled, message being: pop3 server not found, error 0x800ccc0d This symptom, and possible solutions, are exactly the object of Symantec support note: http://service1.symantec.com/SUPPORT/nav.nsf/docid/2000020716064206 Please read this note first ! The object of this post is to add another possible solution to this problem. NAV email protection sets up...

Using Relative path for XML data file?
Is there a way to specify a relative path to an XML data file imported into Excel 2003? I am writing a web app that generates report data as XML for the user to download to their local machine. This data is to be consumed by an Excel reporting spreadsheet, which contains display-formatted tables and charts that are mapped to various data fields in an XML Map, which is in turn linked to the xml data file they will download. The idea is the user only needs to download the data for updates, not the whole spreadsheet. However, since I cannot predict the path where the user will store their...

Changing Cells and entering data in them
Thanks for the help again. Big thanks to Steve you've got me this far. I went out and bought a book, but it's like reading a foreign language. I was informed today that I can't have message boxes come up. I need to have the code point at the cells and if they are blank turn which ever one is blank red or if both are then both turn red then pause for each cell to be filled in. Cell F14 "Last Name" then automatically go to Cell F16 "First Name" on tab or enter. Basically if Cell F22 or F23 has an X in it, Cells F14 an F16 turn red and cell F14 has the focus...

Formula without using numbers after decimal in the answer
I have a formula that derives the answer from a figure with a decimal. I don't want to use the figures after the decimal. Is there a way to just use the whole number and omit the numbers after the decimal without having to manually key in all these numbers manually? Thanks, Mustang You can use the INT function. This 'rounds down' any number to th nearest integer, e.g. if A1=2.567, a formula in B2 of =INT(A1) return 2 HTH Bruc -- swatsp0 ----------------------------------------------------------------------- swatsp0p's Profile: http://www.excelforum.com/member.php?...

Change position ID in HR
We would like to change the position ID in human resources. Does anyone have a suggestion on this. You would need to do it behind the scenes using a tool like Query Analyzer. -- Charles Allen, MVP "KT" wrote: > We would like to change the position ID in human resources. Does anyone have > a suggestion on this. careful though when you change it on the background as you need to know all the tables that use this position ID or Position Code and change it there too otherwise all the link would be gone and you end up with orphan records that its just the same as creatin...

Pulling data from separate tabs
When charting in Excel 2002 is there a way to use sets of data from two different tabs within the same worksheet? For example, a spreadsheet contains separate tabs for prior year and current year data. Is there a way to reference the data or label series to pick up data from both? I tried pointing and clicking, and then typing the following as a reference for the axis labels: ='Prior Year'!$B$110:$M$110,'Current Year'!'$B$110:$M$110 but receive an error that I'm referring to an external worksheet. I've used the comma (') in the past to reference breaks ...

How to automate increasing the form cache registry/file etc...
I want to roll out a batch file to make a number of tweaks to CRM The body of it would go REGEDIT /S Kerberosefix.reg REGEDIT /S ForceFormreload.reg REGEDIT /S OutlookFix.reg It would also rename OSA.exe to OSA.bad Remove OSA.exe From the startup menu I need help finding a way to use my batch file to increase the Outlook Form cache from the default 4MB to 50 MB.. This makes CRm more stable and faster for communications. I dont want to manually do this, as it time consuming, are my end users would not be reliable in doing it themselves. I also want to make another batch file or button that...

Show date, time & day of week in one cell
Can I show the date, time and day of week in one cell. I have: 09/03/07 8:30 AM in one cell using the format (Format/Cells/Custom): [$-409]mm/dd/yy h:mm AM/PM;@ Excel refuses to accept ddd for Mon or dddd Monday at the end of the format I want it to read: 09/03/07 8:30 AM Monday in 1 cell. I have Excel 2003. One way: mm/dd/yy h:mm AM/PM dddd;@ In article <Xns99B8A3CAF9130pencilunlistedcom@208.49.82.220>, Burp <burp@beep.comINVALID> wrote: > Can I show the date, time and day of week in one cell. > > I have: > 09/03/07 8:30 AM > in one cell using the for...

default values in a cell
Hello, can you help me please Cell B1 contains a complex mathematical formula which requires (in several places) a number from cell A1. Cell A1 can contain any integer number, but it is usually the same (30). I would like to be able to leave cell A1 empty, and only enter a number when it is not 30 , ie the default value of A1 is 30, unless specified. How do I go about this ? Should I look at conditional formatting, or put lots of IF functions into an already complex formula? Thank as always KK Use 2 cells Modify the complex formula to use B1 rather than A1 ( or any other un-u...

data sort
ok now should be simple >> I need to sort by month on data that is held in format >> day/month so eg 1511 1510 3011 3010 now custom/ends with/ 11... does not work custom/ends with/ ??11.. or *11 does not work either contains 11 does not work (& would also be wrong if data set contained 1011) but still I am stumped so any help would be great cheers Alex I would be inclined to add a new, temporary field of formulas that pull off the right 2 digits, and sort by that: =RIGHT(A1,2) -- Jim Rech Excel MVP ...

I need a macro
I would like a macro to do the following: I would place the the cursor in any cell and this macro would give me the average of all the entries to the left if the selected cell. EX: I place the cursor in cell M12. I want this macro on display in cell M12 the average of all the values from Col C12 to Col L12. Note that there may NOT be entries in all the cells in that range. Also the start point would always be col C .Thanks You can do this easily with a worksheet_selection event in the sheet module but you would probably want to restrict to a certain range or columns and rows or it woul...

Disable Secure Sockets Layer on exchange server when using RPC over HTTP
Hi im trying to enable RPC over HTTP to enable users to establish contact to my Excahger server 2003 over the internet. Now, I dont want to use SSL (security not that important) and i am told by this article that i can disable SSL in windows registry. Quote: Note While RPC over HTTP does not require Secure Sockets Layer, you must modify the registry to enable RPC over HTTP if you do not want to use Secure Sockets Layer. Microsoft recommends that you enable and require Secure Sockets Layer for your RPC over HTTP communications. At this address: http://support.microsoft.com/?id=833401 But i ...

using the journal on outlook
Once I link an email to the journal, can I still find that email in my mail box? I seem to be able to get to it only via the journal. If this is the way it is supposed to be, how do I remove it from the journal and get it back into my mail box? Am I just missing something? -- thanks, Independent Are you linking to the item or putting a copy into the journal item? Also, has the item been archived or not? "Independent" <Independent@discussions.microsoft.com> wrote in message news:868279F2-53C8-403A-97F5-604CEECD873C@microsoft.com... > Once I link an email to the journ...

Business Portal Development
All: I know how to use Rational XDE, VS and the BP SDK to create new entities in BP, but how does one go about chaning labels? For example, if I wanted to change two labels on a requisition mgt screen: Can I just crack open the ASPX page and change the caption property? Thanks, Dwight -- Hi, To edit the lables you can use front page server extensions 2003. "Dwight Specht" wrote: > All: > > I know how to use Rational XDE, VS and the BP SDK to create new entities in > BP, but how does one go about chaning labels? > > For example, if I wanted to ch...

outlook 2007 e-mail
how do you stop e-mails from automatically downloading when you open outlook 2007. i would like to be able to open outlook 2007 and have it wait for me to select 'receive' before downloading e-mails to my system. i know there is a way to do it in outlook 2003 but i do not remember how to do it there and i cannot find how to do it in outlook 2007. -- emerson Uncheck the option to send/receive at a specific interval. --=81 Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. All unsolicited mail sent to my personal account will be deleted w...

How do you change the APR?
How do you change the APR in a Credit Card account? Like if you have to change it from 13.250% to 14.240% Depends on the version but (in M2007) go to the CC Account and click on Change Account Settings. Scroll down to Credit Information and click on Change Credit Details. If you want to change the Minimum payment rate/amount as well, click on the Minimum payment amount and a side window opens. -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. I do not respond to any emails that I have not specifically ...

Call & Place Graphic Based on Cell Value?
Is there any way to call & place a graphic image based upon a cell value? Maybe you can look at J.E. McGimpsey's page: http://www.mcgimpsey.com/excel/lookuppics.html documike wrote: > > Is there any way to call & place a graphic image based upon a cell value? -- Dave Peterson ...

After editing example1.xls and click SAVE, the filename changes to AABBEE.xls
After editing example1.xls and click SAVE, the filename changes to AABBEE.xls. The original file still exist and has been updated but the 'funny' filename also have the same content, and editable. The example1.xls is stored in a server and accessed by many people within the company. Everyone accessing to the file will change the filename unknowingly after saving it. ...

predict future data
Is there a way to create an XY line graph wih plotted data, yet leave room to predict future data on the axes? I can get the graph, but the x and y axes stop at the last data points, and I want those axes continued so that the existing data can be examined and future data predicted and plotted on the same graph, but I am not sure how to accomplish this. Any suggestions would be appreciated. Thanks. Jeff 1) Click on data series in chart, use Add trendline; in Option tab specify some units forward OR 2) Read Help about TREND and FORECAST, and SLOPE and INTERCEPT OR 3) Get crystal bal...

Let me use the Line Color icon on charts
It would speed up a lot of my work if I could use the Line Color icon on Excel charts, the same way I am able to use the Fill Color and Font Color icons. However, when I highlight any chart object, like the Plot Area, Chart Area, or a Series, the Line Color icon is disabled. -- Stuart Bratesman, Jr., MPP Muskie School of Public Service Univ. of Southern Maine Portland, Maine ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If ...

Formula for cross tab data filling
Hi All Excel 2003 How to using formula for data filling as below (Y/N) ? Sheet A Product A Product B Product C System A Y N Y System B Y Y N Sheet B System A Product A System A Product C System B Product A System B Product B moonhkt ...

Does Outlook use the DAV protocol?
I'm an Outlook Express user who wants to switch to Outlook. I received a notice from Microsoft that includes the following: "... as of June 30, 2008, Microsoft is disabling the DAV protocol and you will no longer be able to access your Hotmail Inbox via Outlook Express." Please tell me if this action by Microsoft will affect Outlook in the same manner, or am I free to make the switch. "BudV" <BudVitoff@(NO)att.(SPAM)net> wrote in message news:%230XUDi%23zIHA.2384@TK2MSFTNGP02.phx.gbl... > I'm an Outlook Express user who wants to switch to Outlook...

Help with importing data
Can I have users fill in a form in Access and have that data be transferred and updated to a spreadsheet. Need for fill out several fields and then export to a specific spreadsheet and place that data into the cells that will update that cell (add to the total in that cell) of a spreadsheet. ...

Changing Domain Name through Internet Address Management Wizard
Hi Guys I know its weekend. but i need to post for peace of mind. I have a client using SBS 2008 STD.He isusing remote.mydomain.com address through Internet address management wizard. Now he wants to change to remote.mynewdomain.com as old domain is too long. Is there any way so we can change through IAMW. run the wiz, job done. "Atul Sood" <atulsood@live.com> wrote in message news:uVlbmkd3KHA.4028@TK2MSFTNGP05.phx.gbl... > Hi Guys > > I know its weekend. but i need to post for peace of mind. I have a client > using SBS 2008 STD.He isusing rem...