Change event does not fire

Hello!
I have a worksheet with checkboxes. Each checkbox is linked to a cell. When 
checkboxes are checked/unchecked the cell changes, but the Change event for 
the sheet does NOT fire! Is that how it is supposed to be??? I wanted to use 
this method instead of Checkbox Click event, because I have lots of 
checkboxes. Also it seem to work before, but not anymore. Am I confusing 
something?

Please help! Thanks a lot in advance.

P.S: Alternativly I can have another cell with formula linked to the 
checkbox cell. This way Calculation event works. But it's not best practice :)
0
Utf
6/7/2010 6:06:11 PM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
3394 Views

Similar Articles

[PageSpeed] 29

Lena,

If I use this in excel 2003 the change event is triggered:

Option Explicit
Dim enableEvents As Boolean

Private Sub CheckBox1_Click()
If enableEvents = True Then Exit Sub
enableEvents = True
Range("A1") = 2
enableEvents = False
End Sub

You don't say exactly what a checkbox being "linked" to a cell means.
Posting your code would be best.

This is from Chip Pearson's website:

"For the Change event in particular, it should be noted that this is
triggered when a cell is changed by user action or by other VBA code, but is
not raised if the value of a cell is changed as a result of formula
calculation."

HTH

"Lena" <Lena@discussions.microsoft.com> wrote in message
news:143C03B1-8C20-4F9D-B8EF-388663E832B1@microsoft.com...
> Hello!
> I have a worksheet with checkboxes. Each checkbox is linked to a cell.
When
> checkboxes are checked/unchecked the cell changes, but the Change event
for
> the sheet does NOT fire! Is that how it is supposed to be??? I wanted to
use
> this method instead of Checkbox Click event, because I have lots of
> checkboxes. Also it seem to work before, but not anymore. Am I confusing
> something?
>
> Please help! Thanks a lot in advance.
>
> P.S: Alternativly I can have another cell with formula linked to the
> checkbox cell. This way Calculation event works. But it's not best
practice :)


0
Project
6/7/2010 6:48:09 PM
I don't have any code for checkbox itself. I use LinkedCell which is setup in 
the propeties of the checkbox and changes the value from TRUE/FALSE whenever 
the checkbox is checked/unchecked. I'm trying to catch an event when this 
cell changes it's value.
I just use a test code for now for Worksheet_Change event and it doesn not 
work when the cell is changed by the checkbox.

It does work however when I have another cell with the formula linking to my 
LinkedCell of the checkbox. Then I can use Worksheet_Calculate event. But I 
don't want to create another column just for that.

I have around 40 checkboxes and I don't want to use the Checkbox Click event 
for each of them.

"Project Mangler" wrote:

> Lena,
> 
> If I use this in excel 2003 the change event is triggered:
> 
> Option Explicit
> Dim enableEvents As Boolean
> 
> Private Sub CheckBox1_Click()
> If enableEvents = True Then Exit Sub
> enableEvents = True
> Range("A1") = 2
> enableEvents = False
> End Sub
> 
> You don't say exactly what a checkbox being "linked" to a cell means.
> Posting your code would be best.
> 
> This is from Chip Pearson's website:
> 
> "For the Change event in particular, it should be noted that this is
> triggered when a cell is changed by user action or by other VBA code, but is
> not raised if the value of a cell is changed as a result of formula
> calculation."
> 
> HTH
> 
> "Lena" <Lena@discussions.microsoft.com> wrote in message
> news:143C03B1-8C20-4F9D-B8EF-388663E832B1@microsoft.com...
> > Hello!
> > I have a worksheet with checkboxes. Each checkbox is linked to a cell.
> When
> > checkboxes are checked/unchecked the cell changes, but the Change event
> for
> > the sheet does NOT fire! Is that how it is supposed to be??? I wanted to
> use
> > this method instead of Checkbox Click event, because I have lots of
> > checkboxes. Also it seem to work before, but not anymore. Am I confusing
> > something?
> >
> > Please help! Thanks a lot in advance.
> >
> > P.S: Alternativly I can have another cell with formula linked to the
> > checkbox cell. This way Calculation event works. But it's not best
> practice :)
> 
> 
> .
> 
0
Utf
6/7/2010 8:13:08 PM
I'm not quite sure what you want to do, but instead of using 40 distinct _change
events, you could use a single _change event in a class module (for every
checkbox assigned to a specific group of checkboxes).

The "grouping" is done when the workbook opens.

This goes in a General module:

Option Explicit
Dim ChkBoxes() As New Class1
Sub Auto_Open()

    Dim CBXCount As Long
    Dim OLEObj As OLEObject
        
    CBXCount = 0
    For Each OLEObj In ThisWorkbook.Worksheets("sheet1").OLEObjects
        If TypeOf OLEObj.Object Is MSForms.CheckBox Then
            CBXCount = CBXCount + 1
            ReDim Preserve ChkBoxes(1 To CBXCount)
            Set ChkBoxes(CBXCount).CBXGroup = OLEObj.Object
        End If
    Next OLEObj
    
End Sub

And then when you're in the VBE, do Insert|Class Module
The name of this class module is Class1 (it's important to match what's in the
code):

Then paste this in the newly opened code window:

Option Explicit
Public WithEvents CBXGroup As MSForms.CheckBox
Private Sub CBXGroup_Change()
    With CBXGroup
        MsgBox .Name & vbLf & .Value
    End With
End Sub

You can read more info at John Walkenbach's site:
http://spreadsheetpage.com/index.php/file/multiple_userform_buttons_with_one_procedure



Lena wrote:
> 
> I don't have any code for checkbox itself. I use LinkedCell which is setup in
> the propeties of the checkbox and changes the value from TRUE/FALSE whenever
> the checkbox is checked/unchecked. I'm trying to catch an event when this
> cell changes it's value.
> I just use a test code for now for Worksheet_Change event and it doesn not
> work when the cell is changed by the checkbox.
> 
> It does work however when I have another cell with the formula linking to my
> LinkedCell of the checkbox. Then I can use Worksheet_Calculate event. But I
> don't want to create another column just for that.
> 
> I have around 40 checkboxes and I don't want to use the Checkbox Click event
> for each of them.
> 
> "Project Mangler" wrote:
> 
> > Lena,
> >
> > If I use this in excel 2003 the change event is triggered:
> >
> > Option Explicit
> > Dim enableEvents As Boolean
> >
> > Private Sub CheckBox1_Click()
> > If enableEvents = True Then Exit Sub
> > enableEvents = True
> > Range("A1") = 2
> > enableEvents = False
> > End Sub
> >
> > You don't say exactly what a checkbox being "linked" to a cell means.
> > Posting your code would be best.
> >
> > This is from Chip Pearson's website:
> >
> > "For the Change event in particular, it should be noted that this is
> > triggered when a cell is changed by user action or by other VBA code, but is
> > not raised if the value of a cell is changed as a result of formula
> > calculation."
> >
> > HTH
> >
> > "Lena" <Lena@discussions.microsoft.com> wrote in message
> > news:143C03B1-8C20-4F9D-B8EF-388663E832B1@microsoft.com...
> > > Hello!
> > > I have a worksheet with checkboxes. Each checkbox is linked to a cell.
> > When
> > > checkboxes are checked/unchecked the cell changes, but the Change event
> > for
> > > the sheet does NOT fire! Is that how it is supposed to be??? I wanted to
> > use
> > > this method instead of Checkbox Click event, because I have lots of
> > > checkboxes. Also it seem to work before, but not anymore. Am I confusing
> > > something?
> > >
> > > Please help! Thanks a lot in advance.
> > >
> > > P.S: Alternativly I can have another cell with formula linked to the
> > > checkbox cell. This way Calculation event works. But it's not best
> > practice :)
> >
> >
> > .
> >

-- 

Dave Peterson
0
Dave
6/7/2010 9:16:33 PM
Reply:

Similar Artilces:

Question about Excel (Office) auditing/changes
I have a document that will need to be posted on a website available for users to download & change/approve. The approval process will not make any changes at all to the document. However, I do need to know they've opened the document. Is there a way to get the name/machine/ip or something regarding who opens a file and when they open it? If so, I would like to do something like that, and either write it to something in the spreadsheet or as an audit trail in a database. If there's no way to do this, I am considering writing a small app that requires them to sign in (somet...

Simple Spreadsheet random changes dates and colum colors?
Hi. I have a simple spreadsheet, almost daily recent random dates in one of the columsn change from 1 to 6 days and also the color changes in the row change to red to black or black to red? The background is that it is a 'manual' spread sheet for tracking items. When an item is returned, we set the whole row red for that item using the color pallete on the toolbar. That's about it. The only formula on the sheet is a formula that subtracts a date in a colum by todays date to get a difference in days. We manual copy and paste this into rows when an item is late to show how many da...

How to change the budget dates?
Hi there, My appologies if this is a stupid question for some of you but I'm stuck. I live in New Zealand and here the financier year starts on the 1st of May and ends on the 30st of April next year. My question is: How can I change the budgeting period to these dates in Money 2001? Theoretically shouldn't be different to any other Money version, isn't it? I reckon that this is a click away but after hours spent to find a way to do it I gave up and thought to ask you guys for help. I have Money2001 that came with my computer and I find it quite good provided that I don&#...

Macro for selection change
I want Goal Seek to update my spreadsheet automatically when certain values are changed. Can someone walk me through how to do this in the subroutine. I need specific instructions! Thanks! Chris, Try the macro recorder. Walk through the Goal Seek steps while recording. Then you can run the macro in the Workbook_Change event. Or you can probably create the required answer in the independent cell (the one Goal Seek is changing) with a modified version of the formula in the dependent cell. This would be if there are no circumstances where you would manually enter a value into the cell. -...

How do I get excel to accept (c) as text and not change to copyri.
How do I enter the text (c) in Excel without having it changed into the copyright symbol? Hi Daffyd, Try: Tools | Autocorrect | Select (c) | Delete | OK --- Regards, Norman "daffyd" <daffyd@discussions.microsoft.com> wrote in message news:8CCC3C1A-6F19-4F62-B934-8A71F236A4FD@microsoft.com... > How do I enter the text (c) in Excel without having it changed into the > copyright symbol? Go into the Tools Menu. Look for AutoCorrect. In the bottom half of the AutoCorrect Tab, look at the list for Replace text as you type. Delete the entry for (c). tj "da...

Publisher 2000 changes default font
Having loaded Publisher 2000 onto a Win98 m/c I have discovered fonts used by Groupwise have changed. This implies that P2000 has changed the default font somewhere - any ideas please? How do I change the fonts in GroupWise http://support.novell.com/cgi-bin/search/searchtid.cgi?/10061270.htm -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.mvps.org/msauer/ news://msnews.microsoft.com "PS" <anonymous@discussions.microsoft.com> wrote in message news:1be4401c45220$f2b1b4a0$a601280a@phx.gbl... > Having loaded Publisher 2000 onto a Win98 m/c I have > discover...

changing Size of a cell without affecting the others in the same column
Dear All, May I ask how can I change the size of some cells, let say changin the width of A1 cell without affecting the one below it (A2). Or if that's not feasible. can I create a table like sheet that could cover on some part of the existing worksheet to "archieve" a similar effect(i.e Changing the size of one cell without affecting the one below it.) Thanks so much for your kindly help again. Cheers, Simon Lee You cannot do exactly what you want. A couple of workrounds :- 1. Use 2 or more columns instead of 1 so longer data spreads across the width of all columns. F...

Adding/changing registry keys
I used to do stuff periodically with registry keys from Excel. Are there any issues with more modern versions of Windows and permissions? Are there areas of the registry that I can use/can't use Does a user have to have administrator rights for my code to write to the registrty? Thanks M Broadly speaking, you should restrict yourself to the HKEY_CURRENT_USER region of the registry. Leave LOCAL_MACHINE and CLASSES_ROOT alone and let Windows or other applications use those regions. If you screw up the registry, it is much easier to fix things up in CURRENT_USER than ...

Change outbound server in header to fix 550 Can't verify your host name error
The headers on the outbound emails show the internal DNS name of our exchange server; obviously this won't resolve properly at the destination. How/where in Exchange 5.5 can I force the IMC to use a real fqdn on outbound mail? Thanks! Frinky You can do this in TCP/IP properties\Advanced\DNS tab of machine. And yes, this is not just for Exchange, so you may consider forwarding all outgoing mail to some relay server (your firewall or ISP's server). Professor Frink wrote: > The headers on the outbound emails show the internal DNS name of our > exchange server; obviously this...

why do changes only save locally on networked excel file?
why do changes only save locally on networked excel file? ...

Changing hyperlinks in Excel Worksheet
I have to work on a very large Excel workbook in which most of the cells are linked to another Excel file by hyperlinks. I have to update the file once a month by changing the hyperlinks cell by cell to a updated source file. It's so time-consuming. I am sure there must be a smarter way to do it. I believe that Macro may do but I am not good at macro at all. I appreciate if anyone could give me any sort of suggestions. Thanks. Colona Maybe someone can answer your question exactly, but as another approach. In the spreadsheet you change each month set a cell aside for the...

Drag msg to explorer folder -> change name?
Hello, It is possible to drag a message from outlook 2000 to a normal explorer folder and it will be named subject.msg. Nice. But I keep all my mesaages on a project in one folder. This is ok as long as each message has a different subject, or when I drag all messages at once to the folder, in this case OL automatically names the files subject 1.msg subject 2.msg etc. However, I file mails on a daily basis, so I keep egtting 'file already exists, overwrite?" . Is it possible to modify the way outlook creates the name for the file, for instance date-from-subject.msg. This would ...

How can I stop charts from refreshing when changing source data?
My problem is, that I am working with a lot of data and when I change some of the ranges all charts in my view refreshes and it takes much time. My pc is aP4 3GHz, 2GB RAM so that should not be the bottleneck. Is there any way to force the charts not to update all the time? ...

Count on cell for each time it is changed
I would like to count the number of times a cell has been amended/ changed e.g cell b2 has the word red it it, in cell c2 i would like a counter for everytime the cell in b2 has changed, at the end of the day you get final number. Is this at all possible? Jelinek, You can do it by putting the following VBA macro in your sheet: Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$B$2" Then Cells(2, 3) = Cells(2, 3) + 1 End If End Sub Art "Jelinek" wrote: > I would like to count the number of times a cell has bee...

[Newbie] How to change folder locations for Active Directory and Exchange?
I've just set up a Windows 2003 server for our small business, and I'd like to have the user's folders and the exchange folders (especially the public ones) reside on a seperate drive than the C:\ drive. Is there a way to change the default location for these folders? I'm going to run out of space on my C:\ drive soon because I wasn't planning on using it just for the OS, not for storage. On Thu, 12 May 2005 11:06:43 -0700, "Marty Christion" <anon@yahoo.com> wrote: >I've just set up a Windows 2003 server for our small business, and I'd l...

changing the edit box contents
Hi, I have an edit box in a view, and i handle the ON_EN_UPDATE message in my view that is received when a char is pressed in the edit box so i can validate the char. I also have a list ctrl, that when an item is double cicked, it needs to place the char that it reprosents into the edit box. To do this, i used c_myEdit.Get / SetWindowText(...) to get the old text, append a char, then set the text, which works, except the ON_EN_UPDATE message isnt sent so i cant validate the net char ! Any ideas? How can i send a character to an edit box so that ON_EN_UPDATE still fires? <code> void ...

Events from GROUPBOX
Hello All I need to receive mouse events from a child CWnd, created with class "BUTTON" and flags WS_VISIBLE | WS_CHILD | BS_GROUPBOX | BS_NOTIFY ( a GROUPBOX, in .RC statements). I tried to override OnWndMsg, PreTranslateMessage, and then I TRACEd received messages. I got only WM_GETDLGCODE, WM_PAINT, WM_NCPAINT,WM_ERASEBKGND, and then (a regime) sequences of WM_NCHITTEST. I must handle this last by myself? I don't think there are any events from a group box. It is a static control. Overriding OnWndMsg is usually a Really Bad Idea, and overriding PreTranslateMessage is usua...

Article published by Microsoft reg. 'Event' custom entity
Recently, I found a great article published by Microsoft that contains a sample code on how to create a custom entity, event. I thought that I bookmarked it but cannot find it. Has anyone seen it and can provide a hyperlink? I will really appreciate it. http://msdn2.microsoft.com/en-us/library/aa682866.aspx you'll probably find it in the above link "mkatsev" wrote: > Recently, I found a great article published by Microsoft that contains a > sample code on how to create a custom entity, event. I thought that I > bookmarked it but cannot find it. Has anyone seen...

hide/change color of selected row headings
Is it possible to hide the row heading numbers for selected rows (i.e. rows 51 and greater) for just the selected sheet? I'd like to have a color with no row heading number appear that matches the fill color I select for the adjacent cells. Secondly, any ideas as to how I can prevent the user from scolling further down than a certain point (i.e. row 51). Thanks in advance. Joe Row headings are either on or off, you can't hide some. However, you can achieve a similar effect by hiding *all* headings (Tools/Options/General) and putting the numbers 1:51 in A1:A51. to limit scro...

OnLoad Event Error
Hey All, My client is getting an odd error during the Account Form's OnLoad event. There's an extensive amount of JScript code in it, but all was working on the CRM Server during testing, but when the User open's the Account, they receive this Msg: Error: Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record. Part of the code queries a filtered view in the CRM db, could it be that some users do not have permissions for this view? Has anyone seen this before? Error seems to be from the ADO provider. Without the cod...

changing a name in mulit parts of a document
I am setting up a word document. This will be used as a template In this document I wish to insert the same "name" throughtout this document. Is it possible to change the name in one spot and all of the others change automatically thanks See http://gregmaxey.mvps.org/Repeating_Data.htm -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>...

Function cell references change by themselves
Spreadsheet created in Excel 2000, now opened in Excel 2002. OS Windows 200 Professional A SUM function =SUM(A2:D4) is in column I. There are other numbers in cumns F and G. When info is entered in columns A to D, then in F and G, the Function cell references change themselves so the it reads =SUM(A2:G2). We are moving betweent he cells using either the tab button or the arrow keys. Please can you suggest what is happening and how to resolve it. ...

change exchange name suffix
Hi We need to change our exchange server 'name' suffix. The situation is that we have a local DNS domain 'bs.si' where all our company computers and server are (Win2k srv AD) We have also external domain 'bs-group.si' that is also for delivering mail (mx record). The Exchange server name is 'posta' Now when someone connects to Exchange server from outside via port 25 it responds like '220 posta.bs.si ...', but I want that the response will be like '220 posta.bs-group.si'. So that mail that is coming from our server will be seen like that i...

Changing password #2
Hi, If I change a domain user's password, will that user's Exchange Server 2003 mailbox's pssword be changed too? I mean, does Active Directory share the same password with Exchange Server 2003? Thanks! Exchange never had passwords separate from domain, not even in Exchange 4-5.5 era. So you would change just one password, which belongs to AD account. Michael wrote: > Hi, > > If I change a domain user's password, will that user's > Exchange Server 2003 mailbox's pssword be changed too? > I mean, does Active Directory share the same password > w...

Dynamic chart: Changing Ranges
Hi: I have a set of data, going across by months, and going down into different categories that I have to graph using stacked column charts. Since the data is going to be in the same columns, just going down a category...I'd like to know if there is a way for me to add some kind of drop-down to the chart so that users can just drop-down to CATEGORY 1, and see the chart for CATEGORY 1, without me having to build 10 different charts that i'll have to update everytime i add new data. I would appreciate any advice or feedback. Hello: Check out Jon Peltier's website. It looks ...