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!”
- 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?