I have created several worksheets with VBA code that hide columns that are not referenced in a drop down list of months. The sheets work as intended when the drop down list changed manually in each worksheet. Using VBA in the workbook module, I have synchronized the dropdowns in each sheet to mimic each other and I would like the macros in each sheet to run when any one dropdown selection has been changed. My code is below:
Private Sub Worksheet_Change(ByVal target As Range)
If target.Address = "$B$3" Then
ActiveSheet.Unprotect
Application.ScreenUpdating = False
Application.EnableEvents = False
StartColumn = 17
LastColumn = 112
iRow = 1
For i = StartColumn To LastColumn
If Cells(iRow, i).Value <> "X" Then
Cells(iRow, i).EntireColumn.Hidden = False
Else
Cells(iRow, i).EntireColumn.Hidden = True
End If
Next i
Application.ScreenUpdating = True
Application.EnableEvents = True
ActiveSheet.Protect
End If
End Sub
This macro only work's when the dropdown in this worksheet's cell B3 is changed manually.
I have tried to change "If target.Address = "$B$3" Then" to "If Not Intersect (Target, Me.Range("B3")) Is Nothing Then" which isn't working.
I have also tried to change the Worksheet_Change to Worksheet_Calculate and change "If target.Address = "$B$3" to
Private Sub Worksheet_Calculate()
Dim target As Range
Set target = Range("B3")
If Not Intersect(target, Range("B3")) Is Nothing Then
which is also not working. Any help would be appreciated. Thanks in advance
Bookmarks