Worksheet_Change issues

jcmascolo

New Member
Joined
Feb 26, 2015
Messages
6
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

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 :
Code:
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 :)
 
Last edited:

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,381
What does ResizeButton do? Perhaps it's failing or ending code execution when E2 is changed so CleanData isn't called. Just speculating.
 

jcmascolo

New Member
Joined
Feb 26, 2015
Messages
6
In fact, ResizeButton works fine. Here's the code for it.

Code:
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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,381
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

New Member
Joined
Feb 26, 2015
Messages
6

ADVERTISEMENT

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 :

Code:
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!
 
Last edited:

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,381
Can you elaborate on "breakpoints don't trigger but Msgboxes do" ? What exactly did you try and the effects.
 

jcmascolo

New Member
Joined
Feb 26, 2015
Messages
6

ADVERTISEMENT

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

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,381
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

New Member
Joined
Feb 26, 2015
Messages
6
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).

Code:
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

New Member
Joined
Mar 2, 2015
Messages
1
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,777
Messages
5,598,020
Members
414,204
Latest member
Excelmee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top