Hello
I am writing a macro to find-replace certain values in formulas on a dashboard based on the value in a drop-down menu. This is built from similar code I have used before. However when I change the drop-down value, try to run the macro from the VBE window, or try to debug nothing happens. When I press F5 in the VBE window the macro list dialog window pops up and no macros are visible. Here is the code:
Application events are on, so it is not that. Any help or advice would be greatly appreciated. Thank you.
I am writing a macro to find-replace certain values in formulas on a dashboard based on the value in a drop-down menu. This is built from similar code I have used before. However when I change the drop-down value, try to run the macro from the VBE window, or try to debug nothing happens. When I press F5 in the VBE window the macro list dialog window pops up and no macros are visible. Here is the code:
Code:
Option Explicit
Private Sub Worksheet_Change1(ByVal Target As Excel.Range)
Dim rng As Range
Dim Ws As Worksheet
Dim OldTab As String
Dim NewTab As String
Set rng = ThisWorkbook.Worksheets("Dashboard").Range("BusinessUnit")
If Target.Count > 1 Then Exit Sub
If Intersect(Target, rng) Is Nothing Then Exit Sub
OldTab = ThisWorkbook.Worksheets("Key").Range("selOldTab").Value
NewTab = ThisWorkbook.Worksheets("Key").Range("selNewTab").Value
ThisWorkbook.Worksheets("Dashboard").Range("B9:H35").Replace OldTab, NewTab
ThisWorkbook.Worksheets("Key").Range("selNewTab").Copy
ThisWorkbook.Worksheets("Key").Range("selOldTab").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
Application events are on, so it is not that. Any help or advice would be greatly appreciated. Thank you.