Code unable to debug, how to run continuously?

[lazyengineer]

Hey guys! I have outlined my code below.

So basically I have two different things happening here, one part is hiding rows based off of a selected dropdown setting, the next hides/shows pictures of signatures based off of a name selection dropdown. I know both of these pieces of code work, but for some reason I can't get them to run continuously, and I can't even debug them, f8 doesn't allow me to run through them. Any idea why? Any help would be appreciated. Thanks guys!

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Employee_select As String

    Select Case Range("OF_CHANNELS").value
        Case Is = "# OF CHANNELS"
'First case select shows rows to prevent case overlap
            Sheets("HARTLAND").Rows("83:350").Hidden = False
'Second case select hides rows
            Sheets("HARTLAND").Rows("73:350").Hidden = False
        Case Is = "1"
            Sheets("HARTLAND").Rows("83:350").Hidden = False
            Sheets("HARTLAND").Rows("73:350").Hidden = True
        Case Is = "2"
            Sheets("HARTLAND").Rows("73:350").Hidden = False
            Sheets("HARTLAND").Rows("83:350").Hidden = True
        Case Is = "3"
            Sheets("HARTLAND").Rows("83:350").Hidden = False
            Sheets("HARTLAND").Rows("93:350").Hidden = True
        Case Is = "4"
            Sheets("HARTLAND").Rows("83:350").Hidden = False
            Sheets("HARTLAND").Rows("103:350").Hidden = True
        Case Is = "5"
            Sheets("HARTLAND").Rows("83:350").Hidden = False
            Sheets("HARTLAND").Rows("113:350").Hidden = True
        Case Is = "6"
            Sheets("HARTLAND").Rows("83:350").Hidden = False
            Sheets("HARTLAND").Rows("123:350").Hidden = True

    End Select
'Shows employee signature based off of selected name
    'JIM WAGNER
    If Range("Employee_Select").value = "JIM WAGNER" Then
    Sheets("HARTLAND").Shapes("JIM_WAGNER").Visible = True
        Sheets("HARTLAND").Shapes("Blank").Visible = True
        Sheets("HARTLAND").Shapes("blank1").Visible = True
        Sheets("HARTLAND").Shapes("blank2").Visible = True
        Sheets("HARTLAND").Shapes("blank3").Visible = True
    Else
        Sheets("HARTLAND").Shapes("JIM_WAGNER").Visible = False
    End If
    'MIKE FLEISCHMANN
    If Range("Employee_Select").value = "MIKE FLEISCHMANN" Then
        Sheets("HARTLAND").Shapes("MIKE_FLEISCHMANN").Visible = True
        Sheets("HARTLAND").Shapes("blank").Visible = True
        Sheets("HARTLAND").Shapes("blank1").Visible = True
        Sheets("HARTLAND").Shapes("blank2").Visible = True
        Sheets("HARTLAND").Shapes("blank3").Visible = True
    Else
        Sheets("HARTLAND").Shapes("MIKE_FLEISCHMANN").Visible = False
    End If
    'ROBIN HENNEN
    If Range("Employee_Select").value = "ROBIN HENNEN" Then
        Sheets("HARTLAND").Shapes("ROBIN_HENNEN").Visible = True
        Sheets("HARTLAND").Shapes("blank").Visible = True
        Sheets("HARTLAND").Shapes("blank1").Visible = True
        Sheets("HARTLAND").Shapes("blank2").Visible = True
        Sheets("HARTLAND").Shapes("blank3").Visible = True
    Else
        Sheets("HARTLAND").Shapes("ROBIN_HENNEN").Visible = False
    End If
    'SHANNON BURTON
    If Range("Employee_Select").value = "SHANNON BURTON" Then
        Sheets("HARTLAND").Shapes("SHANNON_BURTON").Visible = True
        Sheets("HARTLAND").Shapes("blank").Visible = True
        Sheets("HARTLAND").Shapes("blank1").Visible = True
        Sheets("HARTLAND").Shapes("blank2").Visible = True
        Sheets("HARTLAND").Shapes("blank3").Visible = True
    Else
        Sheets("HARTLAND").Shapes("SHANNON_BURTON").Visible = False
    End If
    'JEREMY SOMMERS
    If Range("Employee_Select").value = "JEREMY SOMMERS" Then
        Sheets("HARTLAND").Shapes("JEREMY_SOMMERS").Visible = True
        Sheets("HARTLAND").Shapes("blank").Visible = True
        Sheets("HARTLAND").Shapes("blank1").Visible = True
        Sheets("HARTLAND").Shapes("blank2").Visible = True
        Sheets("HARTLAND").Shapes("blank3").Visible = True
    Else
        Sheets("HARTLAND").Shapes("JEREMY_SOMMERS").Visible = False
    End If
    'KEN MUELLER
    If Range("Employee_Select").value = "KEN MUELLER" Then
        Sheets("HARTLAND").Shapes("KEN_MUELLER").Visible = True
        Sheets("HARTLAND").Shapes("blank").Visible = True
        Sheets("HARTLAND").Shapes("blank1").Visible = True
        Sheets("HARTLAND").Shapes("blank2").Visible = True
        Sheets("HARTLAND").Shapes("blank3").Visible = True
    Else
        Sheets("HARTLAND").Shapes("KEN_MUELLER").Visible = False
    End If
    'ANDREW WOJDYLA
    If Range("Employee_Select").value = "ANDREW WOJDYLA" Then
        Sheets("HARTLAND").Shapes("ANDREW_WOJDYLA").Visible = True
        Sheets("HARTLAND").Shapes("blank").Visible = True
        Sheets("HARTLAND").Shapes("blank1").Visible = True
        Sheets("HARTLAND").Shapes("blank2").Visible = True
        Sheets("HARTLAND").Shapes("blank3").Visible = True
    Else
        Sheets("HARTLAND").Shapes("ANDREW_WOJDYLA").Visible = False
    End If
    'DAVID KLISH
    If Range("Employee_Select").value = "DAVID KLISH" Then
        Sheets("HARTLAND").Shapes("DAVID_KLISH").Visible = True
        Sheets("HARTLAND").Shapes("blank").Visible = True
        Sheets("HARTLAND").Shapes("blank1").Visible = True
        Sheets("HARTLAND").Shapes("blank2").Visible = True
        Sheets("HARTLAND").Shapes("blank3").Visible = True
    Else
        Sheets("HARTLAND").Shapes("DAVID_KLISH").Visible = False
    End If
    'BOB KRAMER
    If Range("Employee_Select").value = "BOB KRAMER" Then
        Sheets("HARTLAND").Shapes("BOB_KRAMER").Visible = True
        Sheets("HARTLAND").Shapes("blank").Visible = True
        Sheets("HARTLAND").Shapes("blank1").Visible = True
        Sheets("HARTLAND").Shapes("blank2").Visible = True
        Sheets("HARTLAND").Shapes("blank3").Visible = True
    Else
        Sheets("HARTLAND").Shapes("BOB_KRAMER").Visible = False
    End If
    If Range("Employee_Select").value = "Show objects" Then
        Sheets("HARTLAND").Shapes("blank").Visible = False
        Sheets("HARTLAND").Shapes("blank1").Visible = False
        Sheets("HARTLAND").Shapes("blank2").Visible = False
        Sheets("HARTLAND").Shapes("blank3").Visible = False
    End If
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

End Sub

[TMS]

Put the word Stop as the first line of code in the Change event handler. Then, when you make a change, it will stop and you can use F8 to step through the code.

Regards, TMS

[lazyengineer]

TMS,

If I put the word stop there, will this run continuously? I basically need this running in the background at all times so when the selection is made, it will still update. Could explain more as to where the "change event handler is"?
Thanks for the help.

[TMS]

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Employee_select As String

Stop             ' for diagnostic purposes.

' rest of code
' vvvvv

If you have a Worksheet Change event handler, such as your code, it will fire for EVERY change made on the worksheet. It's then up to you to decide whether or not you are interested in the change that has been made. And, if you are, what do you want to do about it.

So, yes, the Worksheet Change event handler will (does) run continuously but, for diagnostic purposes, the Stop statement will halt execution so that you can step through and see what is happening. However, what IS happening is that for each and every change made to ANY cell on the worksheet, the event will fire and check the values of certain Named Ranges. It will then hide or unhide specific rows.

The code looks a little repetitive and not very scaleable but I'm sure it does what you want.

Regards, TMS

[lazyengineer]

Thank you so much, you've seriously been so much help, thanks for explaining it to me. I managed to get it working, do I have a clue how? Absolutely not. It just started working for some reason and I don't even know what I changed. The explanation you gave was very thorough though and I appreciate that, keep up the good work!

[TMS]

You're welcome. Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.