Worksheet_Change issues

[jcmascolo]

Hello eveyrone!
New to VBA and this forum (thanks for having me!), but encountered a weird issue with Worksheet_Change.
So I have a protected worksheet that I unprotect when macros are running. There are 3 fields where I'm checking for changes. But I'm having an issue with cell E2 where there is a drop down ("English";"Français"). For some odd reason, my CleanData method doesn't react when E2 is changed, but works fine when B6 and D6 are changed.

Here is my code

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
    Case "$E$2"
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Call ResizeButton(Target.Value)
        Call CleanData(Sheet1)
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    Case "$B$6"
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Call ResizeButton(Target.Value)
        Call CleanData(Sheet1)
        Call UnlockCells(Sheet1)
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    Case "$D$6"
        Application.EnableEvents = False
        Application.ScreenUpdating = False
        Call ResizeButton(Target.Value)
        Call CleanData(Sheet1)
        Call UnlockCells(Sheet1)
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    Case Else
        Exit Sub
End Select


End Sub

And my CleanData :

Sub CleanData(priorityWS As Worksheet)
Dim detailsR As Range
Dim nbRows As Long, rowsPrior As Long


Application.EnableEvents = False
Application.ScreenUpdating = False
priorityWS.Unprotect


Set detailsR = priorityWS.Range("$A$22:$E$22")


detailsR.ClearContents
detailsR.Borders.LineStyle = xlLineStyleNone


rowsPrior = 23
nbRows = priorityWS.Range("A" & Rows.Count).End(xlUp).Row
If nbRows >= rowsPrior Then Rows(rowsPrior & ":" & nbRows).Delete


priorityWS.Protect
Application.EnableEvents = True
Application.ScreenUpdating = True

Any help would be great!
Thanks

 

[jcmascolo]

Was this issue resolved? I'm experiencing a similar issue; I have a drop-down value that when changed doesn't trigger my Sub Worksheet_Change at all. I'm also using sql sourced data in the sheet (the macro I'm calling is to refresh the data on cell value change) and protected sheets.

I have inserted both breakpoints and MsgBox and neither one triggers. I've tried it with the sheet protected and unprotected; entering data in other cells to see if the Target is recorded; using the drop-down and entering data manually...

Is there something simple I need to check? One of the earlier problems I beat my head against a wall for too long for was having the background refresh on my queries enabled in a protected worksheet.

Any help would be appreciated.

Not yet. I'll need to change my Excel in order to send it on the forums. Busy on stuff, I'll post asap.