I was working on a little application which has a spin button on the worksheet. That spin button is linked to a cell and works just fine for my purposes. After a while though, it became apparent that I needed to use some VBA to prohibit the user from changing the value in the cell in certain circumstances. No problem, I'll just monitor the worksheet_change event via VBA and make sure that the user can't change the cell value if certain conditions are met!
Not so fast though… this solution works just fine when the user types a value in the cell, but when you increment the cell value using a worksheet control, the VBA doesn't fire!
If you're interested in testing this for yourself:
- Create a new workbook
- Create a forms spin button on the worksheet (Developer Tabà Insertà Spin button)
- Link it to cell A1
- Right click the Sheet1 tab and choose "View Code"
-
Paste the following in the window:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
MsgBox "Cell value changed!"
End If
End Sub
- Go back to the worksheet and use the spin button
The value in cell A1 will change, but nothing else. But yet if you type a value in cell A1, you'll get a message.
I've tested this now using the spin button & scroll bar controls, both forms and activeX, in Excel 2007 and 2010. Same results… or rather lack thereof…
I'm a little surprised that this is the case. I understand why a recalculation of worksheet formulas wouldn't trigger the worksheet_change event… the actual formula didn't change, after all, but the results did. To me though, this is different. I'm changing a physical value in the worksheet, and I would expect that this change would trip the event.
I guess I'll have to find another way to deal with this. Not a big deal, but I'm curious… would you expect a change in the worksheet triggered by a control to fire the worksheet_change event?
11 thoughts on “Worksheet_Change event and linked controls”
You can simply add this event as workaround:
Private Sub SpinButton1_Change()
Worksheet_Change Range(SpinButton1.LinkedCell)
End Sub
You're right, no worksheet event seems to fire when you click the spin button. I even tried the SheetChange Event. Per Excel Help:
"Occurs when cells on the worksheet are changed by the user or by an external link."
They could expand that to include buttons, or add a new event (I prefer the latter).
As a workaround, the spin button control has Max and Min values you can specify, so the spin button doesn't go out of range. That would work if you know the range.
Or you could write code and assign it to the button (if a Form control), or use the SpinButton Change Event (if an ActiveX control).
Anounymous, thanks. Easy enough to do, for sure, I'm just still curious why we need to.
JP, waht event would you add for that.. and why would you prefer a new event to just making the controls kick off the worksheet_change event?
The Change event is triggered in excel when you Enter the cell in an edit mode come out of it by hitting the Enter key or the tick mark on the formula bar
The change event is not triggered is the cell value changes by some other means.
Ex in cell A1 TYPE = A2, change a2 the event for A1 will not fire
like wise in cell A1 TYPE =RAND()
say F9 the change event is not fired (the calculate event is)
So the change event does not detect a change in the value of a cell.
It simply detect the "enter" key
Sorry Sam, I'm not sure I agree with that 100%.
I do agree that the change event doesn't fire on the recalculation of a formula. That's as it should be. The formula itself has not changed, just the value it is returning to the view.
But what does trigger a change is more than just coming out of edit mode.
Writing a value to a cell using VBA triggers the event. There is no Edit Mode involved in that transaction. Likewise, selecting data from a data validation drop down also triggers the event. That one to me is probably the most telling... if that triggers an event, why doesn't the spin button (or any other forms/activeX controls)?
<<… if that triggers an event, why doesn’t the spin button (or any other forms/activeX controls)?<<
Perhaps because you can assign macros to the controls to do whatever you want. WorkSheet_Change is an awkward beast because it gets triggered whether you're within your target range or not. If you have multiple ranges, each requiring a different treatment, you have to test for each range in the same routine. I find that having each control with it's own macros leads to cleaner code.
I just happened across this inconvenience while trying to trigger a change event from a form control. I managed to 'get around' this by a slightly lame; but effective manner. I used the linked cell value and multiplied that by 1 (in a hidden cell) to trigger a calc event instead...
Works.
Oli
Thanks Oli, can you explain it a little more with an example (if possible),
Thanks in advance
Sunny, I'm not sure if Oli still monitors this post. It may better to post in the Excelguru forums at http://www.excelguru.ca/forums
Cheers!
How about to change the increment value dynamically?
I have 1 sheet that will monitor my date per weekly or monthly. if is in monthly mode then change the increment value to be 30 days, if is weekly then change to 7 days. Would be great if you can help me with this...
Hi Victor,
I think it would probably be best to post that question in the forums at http://www.excelguru.ca/forums