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
And my CleanData :
Any help would be great!
Thanks
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: