Excel VBA Prevent Selection Change to refresh/execute on the click of each cell

0
votes

I created the subroutine below with the intention of executing the macro when an item is selected from a data validation list drop-down on cell(2,3). I template to compare Forecast vs. Actuals and a Variance section. Forecast is on the top, Actuals is in the middle and Variance is at the bottom. Each sections contains the same list of sub-market names. The idea is if cell (2,3) is blank then all the data is viewed. If an item is selected then all rows are hidden except for the sub-market selected. The end-user will see the forecast, actuals and variance rows with its titles and headers on the screen to allow for simple analysis and make it a user-friendly tool. It works fine but the subroutine seems to execute/refresh any time any cell is selected either by mouse or by keyboard. Furthermore, the subroutine will not execute when selecting an item on the drop down in cells(2,3). It will only execute when clicking on the sheet again anywhere.

I have been reading for over two hours trying to find a solution on how to prevent the execution/refresh of the subroutine on each click of the sheet. The goal is for the subroutine to execute only when an item is selected on the drop down in cells(2,3) or when it is cleared in cells(2,3). I will continue reading but I figured to post the question to see if I can get an answer while I am researching. Thank you in advanced for any assistance provided.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


Dim Wk2 As Worksheet
Set Wk2 = Sheets("By SubMarket")

Application.ScreenUpdating = False

With Wk2

Set Target = Cells(2, 3)

'If the cell with the drop-down data validation list is blank then unhide all rows -- effectively show all data
If Target = "" Then

Wk2.Cells.Rows.Hidden = False

Else
'Goal:  Hide all rows and only leave the rows related to the submarket selected on the drop down for each section
'       This is down so the end-user can drill down and view a specific market easily FCST vs. Actual vs. Variance
Wk2.Cells.Rows.Hidden = False

'define the header row of the Forecast section
'define the first row of data in the Forecast section
'define the last row of data in the Forecast section

Dim hRowFcst As Long
Dim fRowFcst As Long
Dim lRowFcst As Long

hRowFcst = Cells.Find("FCST", LookAt:=xlWhole).Offset(1, 0).Row
fRowFcst = Cells.Find("FCST", LookAt:=xlWhole).Offset(2, 0).Row
lRowFcst = Cells.Rows(hRowFcst).Find("State", LookAt:=xlWhole).End(xlDown).Row

'define the header row of the Actuals section
'define the first row of data in the Actuals section
'define the last row of data in the Actuals section
Dim hRowAct As Long
Dim fRowAct As Long
Dim lRowAct As Long

hRowAct = Cells.Find("ACT", LookAt:=xlWhole).Offset(1, 0).Row
fRowAct = Cells.Find("ACT", LookAt:=xlWhole).Offset(2, 0).Row
lRowAct = Cells.Rows(hRowAct).Find("State", LookAt:=xlWhole).End(xlDown).Row

'define the header row of the Variance section
'define the first row of data in the Variance section
'define the last row of data in the Variance section

Dim hRowVar As Long
Dim fRowVar As Long
Dim lRowVar As Long

hRowVar = Cells.Find("Over/(Under)", LookAt:=xlWhole).End(xlUp).Row
fRowVar = Cells.Rows(hRowVar).Offset(1, 0).Row
lRowVar = Cells.Find("Over/(Under)", LookAt:=xlWhole).Offset(-1, 0).Row


'define the Total row of the Forecast section
'define the Total row of data in the Actuals section
'define the Total row of data in the Variance section
Dim tRowFcst As Long
Dim tRowAct As Long
Dim tRowVar As Long

tRowFcst = Cells.Rows(lRowFcst).Offset(1, 0).Row
tRowAct = Cells.Rows(lRowAct).Offset(1, 0).Row
tRowVar = Cells.Rows(lRowVar).Offset(1, 0).Row


Dim StateCol As Long
Dim SubMktCol As Long
'Dim Month Col As Long -- See how to make choosing a period dynamic <will define later>

StateCol = Cells.Find("State", LookAt:=xlWhole).Column
SubMktCol = Cells.Find("Sub-Market", LookAt:=xlWhole).Column

'Define the range of all the submarkets listed in the Forecast section
'Define the range of all the submarkets listed in the Actuals section
'Define the range of all the submarkets listed in the Variance section
Dim FSubMktRg As Range
Dim ASubMktRg As Range
Dim VSubMktRg As Range

Set FSubMktRg = Range(Cells(fRowFcst, SubMktCol), Cells(lRowFcst, SubMktCol))
Set ASubMktRg = Range(Cells(fRowAct, SubMktCol), Cells(lRowAct, SubMktCol))
Set VSubMktRg = Range(Cells(fRowVar, SubMktCol), Cells(lRowVar, SubMktCol))

'
  Dim HideRg As Range
  For Each cell In Application.Union(FSubMktRg, ASubMktRg, VSubMktRg)
    If cell <> Cells(2, 3) Then
        If HideRg Is Nothing Then
            Set HideRg = cell
        Else
            Set HideRg = Union(HideRg, cell)
        End If
    End If
  Next
  HideRg.EntireRow.Hidden = True
  'Hides miscelaneous rows between each section
  Range(Cells(tRowFcst, 1), Cells(hRowAct, 1).Offset(-2, 0)).EntireRow.Hidden = True
  Range(Cells(tRowAct, 1), Cells(hRowVar, 1).Offset(-2, 0)).EntireRow.Hidden = True
  Cells(tRowVar, 1).EntireRow.Hidden = True



End If

End With



Application.ScreenUpdating = True


End Sub
2
excelvba

2 Answers

2
votes

If you want to respond only when cell C2 is changed then use a different Event

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Cells(2, 3), Target) Is Nothing Then Exit Sub
    Application.EnableEvents = False
        MsgBox "A change has been made to cell(2,3)"
    Application.EnableEvents = True
End Sub
0
votes

You can use Target which is the active cell that triggerd the worksheet change event:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    On Error GoTo EXIT_SUB
    If Sh.name = Worksheets("Your Sheet Name").name Then
        If Target.Address = .Range("rngYourDropdownRange").Address Then
            myMacro
        ElseIf Target.Address = .Range("rngAnotherRangeIHave").Address Then
            mySecondMacro
        End If
    End If