By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,853 Members | 2,246 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,853 IT Pros & Developers. It's quick & easy.

VBA Excel SheetChange event - nothing happens when i make the change

P: 11
I'm trying to write a procedure that will enable button on a sheet (and colour it red), but ONLY when changes are made to cells in column E of that sheet (from row 10 to the bottom of the used-range).

However, at present, when I edit a cell in column E, nothing happens to the button (it is currently grey and disabled).

I've tried using Microsoft's VBA online help, and also my bible (Excel VBA for dummies) but haven't found any clues so far. Can anyone help?

I've written the code in the sheet, rather than a separate module. And I've done it as a sheet-change procedure, as follows:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.  
  3. Dim FirstRow As Double
  4. Dim TotalRows As Double
  5. Dim Row As Double
  6.  
  7. TotalRows = Worksheets("Calculation").UsedRange.Rows.Count
  8. FirstRow = 10
  9.  
  10. For Row = FirstRow To TotalRows Step 1
  11.         If Target.Address = Worksheets("Calculation").Cells(Row, 5) Then
  12.            CaAdjustmentButton.Enabled = True
  13.            CaAdjustmentButton.BackColor = "&H0000000FF&"
  14.         Else: Exit Sub
  15.         End If
  16. Next Row
  17.  
  18. End Sub
  19.  
  20.  
Jan 24 '08 #1
Share this Question
Share on Google+
4 Replies


Expert 100+
P: 634
I'm trying to write a procedure that will enable button on a sheet (and colour it red), but ONLY when changes are made to cells in column E of that sheet (from row 10 to the bottom of the used-range).

However, at present, when I edit a cell in column E, nothing happens to the button (it is currently grey and disabled).

I've tried using Microsoft's VBA online help, and also my bible (Excel VBA for dummies) but haven't found any clues so far. Can anyone help?

I've written the code in the sheet, rather than a separate module. And I've done it as a sheet-change procedure, as follows:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.  
  3. Dim FirstRow As Double
  4. Dim TotalRows As Double
  5. Dim Row As Double
  6.  
  7. TotalRows = Worksheets("Calculation").UsedRange.Rows.Count
  8. FirstRow = 10
  9.  
  10. For Row = FirstRow To TotalRows Step 1
  11.         If Target.Address = Worksheets("Calculation").Cells(Row, 5) Then
  12.            CaAdjustmentButton.Enabled = True
  13.            CaAdjustmentButton.BackColor = "&H0000000FF&"
  14.         Else: Exit Sub
  15.         End If
  16. Next Row
  17.  
  18. End Sub
  19.  
  20.  
Hi

There are maybe a couple of thing, but I think this line

If Target.Address = Worksheets("Calculation").Cells(Row, 5) Then

perhaps should be

If Target.Address = Worksheets("Calculation").Cells(Row, 5).Address Then


However, I think this may be better ??

Expand|Select|Wrap|Line Numbers
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.  
  3.     Dim FirstRow As Integer
  4.     Dim TotalRows As Long
  5.  
  6.     TotalRows = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
  7.     FirstRow = 10
  8.  
  9.     If .Column = 5 And Target.Row >= FirstRow And Target.Row <= TotalRows Then
  10.         CaAdjustmentButton.Enabled = True
  11.         CaAdjustmentButton.BackColor = 255
  12.     Else
  13.         CaAdjustmentButton.Enabled = False
  14.         CaAdjustmentButton.BackColor = -2147483633
  15.     End If
  16.  
  17. End Sub
Note the use of Integer/Long data types for Row counters !!

HTH


MTB
Jan 24 '08 #2

P: 11
Thanks again for helping out, Mike. Not quite there yet, but maybe you'll know why?

If Target.Address = Worksheets("Calculation").Cells(Row, 5) Then

perhaps should be

If Target.Address = Worksheets("Calculation").Cells(Row, 5).Address Then
I tried this but it didn't seem to do the trick, so I figured I'd abandon my code and try out yours.


However, I think this may be better ??

Expand|Select|Wrap|Line Numbers
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.  
  3.     Dim FirstRow As Integer
  4.     Dim TotalRows As Long
  5.  
  6.     TotalRows = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
  7.     FirstRow = 10
  8.  
  9.     If .Column = 5 And Target.Row >= FirstRow And Target.Row <= TotalRows Then
  10.         CaAdjustmentButton.Enabled = True
  11.         CaAdjustmentButton.BackColor = 255
  12.     Else
  13.         CaAdjustmentButton.Enabled = False
  14.         CaAdjustmentButton.BackColor = -2147483633
  15.     End If
  16.  
  17. End Sub
Line 6 appears to have a big space at the end of it, before the .Row. The VBE didn't like that so i ran .Row onto the end of SpecialCells(xlCellTypeLastCell)

But it still isn't quite working. If I make any changes in the sheet (not just in column E), I get an error message saying that .Column (line 9) is an invalid reference.

I've tried changing the reference to say Activesheet.Column. Now if I make any changes in the sheet (not just in column E), I get the error message "Run-time error 438. Object doesn't support this property or method".

Any clues?


Note the use of Integer/Long data types for Row counters !!
Point taken, I'll watch out for this in future. Thanks
Jan 24 '08 #3

Expert 100+
P: 634
Hi again

There seems to be something spooky going on

I havd noticed to large gap between the ')' and .Row but could not eliminate it inside the code tags !!

What I hadn't noticed was the missing Taget that should be (definitly was when I pasted it!!).

So this is what it should be (maybe!).
Expand|Select|Wrap|Line Numbers
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.  
  3.     Dim FirstRow As Integer
  4.     Dim TotalRows As Long
  5.     TotalRows = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
  6.     FirstRow = 10
  7.  
  8.     If Target.Column = 5 And Target.Row >= FirstRow And Target.Row <= TotalRows Then
  9.         CaAdjustmentButton.Enabled = True
  10.         CaAdjustmentButton.BackColor = 255
  11.     Else
  12.         CaAdjustmentButton.Enabled = False
  13.         CaAdjustmentButton.BackColor = -2147483633
  14.     End If
  15.  
  16. End Sub
No, the extra spaces are still there, but at least the missing Target is now there. That should eliminate the error, hopefully.

MTB
Jan 24 '08 #4

P: 11
Yup, the missing target was the problem. I'd even figured it out myself just before you posted (!!!). The code now runs perfectly, and you are a star.
Jan 25 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.