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

[Lara1]

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:

Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim FirstRow As Double
Dim TotalRows As Double
Dim Row As Double
 
TotalRows = Worksheets("Calculation").UsedRange.Rows.Count
FirstRow = 10
 
For Row = FirstRow To TotalRows Step 1
        If Target.Address = Worksheets("Calculation").Cells(Row, 5) Then
           CaAdjustmentButton.Enabled = True
           CaAdjustmentButton.BackColor = "&H0000000FF&"
        Else: Exit Sub
        End If
Next Row
 
End Sub
 
 

[MikeTheBike]

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:

Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim FirstRow As Double
Dim TotalRows As Double
Dim Row As Double
 
TotalRows = Worksheets("Calculation").UsedRange.Rows.Count
FirstRow = 10
 
For Row = FirstRow To TotalRows Step 1
        If Target.Address = Worksheets("Calculation").Cells(Row, 5) Then
           CaAdjustmentButton.Enabled = True
           CaAdjustmentButton.BackColor = "&H0000000FF&"
        Else: Exit Sub
        End If
Next Row
 
End Sub
 
 

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

Private Sub Worksheet_Change(ByVal Target As Range)
 
    Dim FirstRow As Integer
    Dim TotalRows As Long
 
    TotalRows = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    FirstRow = 10
 
    If .Column = 5 And Target.Row >= FirstRow And Target.Row <= TotalRows Then
        CaAdjustmentButton.Enabled = True
        CaAdjustmentButton.BackColor = 255
    Else
        CaAdjustmentButton.Enabled = False
        CaAdjustmentButton.BackColor = -2147483633
    End If
 
End Sub

Note the use of Integer/Long data types for Row counters !!

HTH


MTB

[Lara1]

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

Private Sub Worksheet_Change(ByVal Target As Range)
 
    Dim FirstRow As Integer
    Dim TotalRows As Long
 
    TotalRows = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    FirstRow = 10
 
    If .Column = 5 And Target.Row >= FirstRow And Target.Row <= TotalRows Then
        CaAdjustmentButton.Enabled = True
        CaAdjustmentButton.BackColor = 255
    Else
        CaAdjustmentButton.Enabled = False
        CaAdjustmentButton.BackColor = -2147483633
    End If
 
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

[MikeTheBike]

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!).

Private Sub Worksheet_Change(ByVal Target As Range)
 
    Dim FirstRow As Integer
    Dim TotalRows As Long
    TotalRows = ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
    FirstRow = 10
 
    If Target.Column = 5 And Target.Row >= FirstRow And Target.Row <= TotalRows Then
        CaAdjustmentButton.Enabled = True
        CaAdjustmentButton.BackColor = 255
    Else
        CaAdjustmentButton.Enabled = False
        CaAdjustmentButton.BackColor = -2147483633
    End If
 
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

[Lara1]

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.