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

 

[AlphaFrog]

What does ResizeButton do? Perhaps it's failing or ending code execution when E2 is changed so CleanData isn't called. Just speculating.

 

[jcmascolo]

In fact, ResizeButton works fine. Here's the code for it.

Sub ResizeButton(Value As String)

Application.EnableEvents = False
Application.ScreenUpdating = False
Sheet1.Unprotect


If Value = "English" Then
    Sheet1.CommandButton1.Caption = "Verify"
    Sheet1.CommandButton1.AutoSize = False
    Sheet1.CommandButton1.Height = 30
    Sheet1.CommandButton1.Left = 354
    Sheet1.CommandButton1.Width = 99.75
End If


If Value = "Français" Then
    Sheet1.CommandButton1.Caption = "Vérifier"
    Sheet1.CommandButton1.AutoSize = False
    Sheet1.CommandButton1.Height = 30
    Sheet1.CommandButton1.Left = 354
    Sheet1.CommandButton1.Width = 99.75
End If


Sheet1.Protect
Application.EnableEvents = True
Application.ScreenUpdating = True

Excuse my french

 

[AlphaFrog]

Can you confirm that when E2 is changed, ResizeButton runs but CleanData does not?

Does range A23:A? have data when you change E2? Otherwise, the rows do not get cleared unless there is data in column A.

 

[jcmascolo]

Can you confirm that when E2 is changed, ResizeButton runs but CleanData does not?

Does range A23:A? have data when you change E2? Otherwise, the rows do not get cleared unless there is data in column A.

I confirm that when A23:A has data and E2 is changed to "English" or "Français", CleanData does not clear A23:A, breakpoints don't trigger but Msgboxes do. Additionally, CleanData works fine in other instances, namelly the following :

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Application.EnableEvents = False
Application.ScreenUpdating = False


Sheet1.Unprotect
Sheet1.Range("$D$6").ClearContents
Sheet1.Range("$B$6").ClearContents
Call CleanData(Sheet1)
Sheet1.Protect


Application.DisplayAlerts = False
ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & ThisWorkbook.Name, _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True


End Sub

Hoping this help!

 

[AlphaFrog]

Can you elaborate on "breakpoints don't trigger but Msgboxes do" ? What exactly did you try and the effects.

 

[jcmascolo]

Can you elaborate on "breakpoints don't trigger but Msgboxes do" ? What exactly did you try and the effects.

Of course.
In CleanData, I added Msgbox("Hello world") at various points to test if Excel would go through the code, and it did. On the other hand, I added breakpoints (red dots in the margin with a left-click) and these did not stop my code.

Hoping this answers.
Thanks

 

[AlphaFrog]

Of course.
In CleanData, I added Msgbox("Hello world") at various points to test if Excel would go through the code, and it did. On the other hand, I added breakpoints (red dots in the margin with a left-click) and these did not stop my code.

Hoping this answers.
Thanks

Msgboxes work but not breakpoints? I don't see how that could be. Did you have them both at the same time? When the msgboxes worked, did it also clear the rows?

I'll take a look at your workbook if you want to upload it to a file share site and post the link.

 

[jcmascolo]

Msgboxes work but not breakpoints? I don't see how that could be. Did you have them both at the same time? When the msgboxes worked, did it also clear the rows?

I'll take a look at your workbook if you want to upload it to a file share site and post the link.

In fact, there is a data validation in E2. When I enter "English" via keystrokes on my keyboard, if works fine, but when I change it with mouse-clicks, Resize button runs but CleanData runs but does not clearcontents of my range of delete rows 23 down.

I unfortunately cannot share my workbook as the data from rows 22 down are pulled via an SQL query and the database is on my server. I'll try to modify it though and send it over.

I also tried the following and nothing (If Target.Address = Range("$E$2").Address Then).

Private Sub Worksheet_Change(ByVal Target As Range)If Target.Address = Range("$E$2").Address Then
        Call ResizeButton(Target.Value)
        Call CleanData(Sheet1)
End If


Select Case Target.Address
    Case "$E$2"
        Call ResizeButton(Target.Value)
        Call CleanData(Sheet1)
    Case Target.Address = Range("$E$2").Address
        Call ResizeButton(Target.Value)
        Call CleanData(Sheet1)
    Case "$B$6"
        Call ResizeButton(Target.Value)
        Call UnlockCells(Sheet1)
    Case "$D$6"
        Call ResizeButton(Target.Value)
        Call UnlockCells(Sheet1)
    Case Else
        Exit Sub
End Select


End Sub

Guess I'll keep in touch.

 

[foser]

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.