VBA SelectionChange with Data Validation

1.8k views Asked by At

I have been doing some research but others have not quite had the same problem as me on this or have been trying to reach a different goal.

I have written a macros (posted below) where the end goal is to hide irrelevant rows based on a prior selection made by the user. Currently the macros will achieve the end goal, but not quite how I'm intending.

If the target cell is empty when selected I can use the drop down arrow to select my option or type it manually. But navigating away from the cell afterwards, either by selecting a new cell or hitting enter, does not trigger the macros. To trigger the macros I have to re-activate the cell.

The problem with this is that I would like to give my user the option to change this cell later, but I can't change the selection without highlighting multiple cells and deleting all information which would affect other inputs around the target cell.

I assume that I am missing some line of code that tells the macros to trigger once the User navigates away from the cell.

Here is my current code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

        If Target.Address = "$D$3" Then


            If Target = "Hide" Then
                Rows("9:13").Select
                Selection.EntireRow.Hidden = True

            ElseIf Target = "Don't Hide" Then
                Rows("9:13").Select
                Selection.EntireRow.Hidden = False

            End If

        End If

End Sub
1

There are 1 answers

0
MSK On

And wouldn't you know it, writing that gave me the idea for the correct inquiry to input to the ol' Google search.

I found my answer at this link: http://www.cpearson.com/excel/WaitFunctions.aspx

My final code looks as such:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Address = "$D$3" Then
    Dim B As Boolean
    B = ThisWorkbook.WaitForUserInput(WaitSeconds:=10, _
        WaitCell:=Worksheets(1).Range("D3"))

        If Target = "Hide" Then
            Rows("9:13").Select
            Selection.EntireRow.Hidden = True

        ElseIf Target = "Don't Hide" Then
            Rows("9:13").Select
            Selection.EntireRow.Hidden = False

        End If

    End If

End Sub