Excel VBA - Issue with Code running on drop down selection

[smick]

Hi,

I am working to create a pretty simple macro setup that when you change a drop down it uses the entry in that field to either hide or unhide a set of rows. I do it with two macros below

The code itself works fine except for one component. It makes it so that the drop down list can no longer be selected and users cant change the value in that drop down list.

Does anyone know of a way around this? I tried a Wait (As you will see in the code) but it just held with the button selected already and still unable to change.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("D8")) Is Nothing Then
    
'Application.Wait (Now + TimeValue("0:00:10"))
        
    Select Case Range("D8")
        Case "Cash in Hand": NTB_Hidden
        Case "ONLY paying off additional debt": NTB_Hidden
        Case "(Select)": NTB_Hidden
    End Select
    
End If
        
End Sub

Sub NTB_Hidden()

Application.ScreenUpdating = False
On Error Resume Next
    With ActiveSheet
        .Unprotect ("8675309")

Rows("25:46").Select

If Range("D8").Value = Range("P4").Value Then
    Selection.EntireRow.Hidden = False
Else
    Selection.EntireRow.Hidden = True

End If

 .Protect ("8675309")
 Application.ScreenUpdating = True
  
End With

End Sub

 

[Candyman8019]

Add a couple of lines to your first code to disable change events then reenable them afterward:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("D8")) Is Nothing Then
 application.enableevents = false
'Application.Wait (Now + TimeValue("0:00:10"))
       
    Select Case Range("D8")
        Case "Cash in Hand": NTB_Hidden
        Case "ONLY paying off additional debt": NTB_Hidden
        Case "(Select)": NTB_Hidden
    End Select
   
End If
  application.enableevents = True
End Sub

 

[smick]

That didnt do it unfortunately

Add a couple of lines to your first code to disable change events then reenable them afterward:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("D8")) Is Nothing Then
 application.enableevents = false
'Application.Wait (Now + TimeValue("0:00:10"))
      
    Select Case Range("D8")
        Case "Cash in Hand": NTB_Hidden
        Case "ONLY paying off additional debt": NTB_Hidden
        Case "(Select)": NTB_Hidden
    End Select
  
End If
  application.enableevents = True
End Sub

 

[Candyman8019]

The cell that contains your data validation field…does it have the ‘locked’ property checked?

 

[smick]

The cell that contains your data validation field…does it have the ‘locked’ property checked?

No, not locked or hidden. The sheet itself is password protected but I have that code in the second macro that runs to unprotect it before running that one.

 

[Candyman8019]

If you manually unprotected the sheet, does the drop-down come active again? I’m feeling like this might have to do with the macro turning protection back on…may need to allow edit objects or something like that.

 

[smick]

If you manually unprotected the sheet, does the drop-down come active again? I’m feeling like this might have to do with the macro turning protection back on…may need to allow edit objects or something like that.

I apologize for the delay. That didn't change it. Still giving the same issue.

 

[Candyman8019]

in your protect / unprotect statements try specifying the password in this format.

Password:="8675309"

 

[smick]

in your protect / unprotect statements try specifying the password in this format.

Password:="8675309"

Unfortunately it doesnt seem to be having an affect. Is there anyway in the Intersect portion of the Macro to have it occur only on change? It seems that part of the issue is because the drop down starts with a (Select) in there versus a blank drop down.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("D8")) Is Nothing Then
   
   Application.EnableEvents = False
    
    Select Case Range("D8")
       Case "Cash in Hand": NTB_Hidden
       Case "ONLY paying off additional debt": NTB_Hidden
       Case "(Select)": NTB_Hidden
      End Select
    
End If
  
  Application.EnableEvents = True

End Sub

Sub NTB_Hidden()

Application.ScreenUpdating = False
On Error Resume Next
   With ActiveSheet
        .Unprotect Password:="8675309"

Rows("25:46").Select

If Range("D8").Value = Range("P4").Value Then
    Selection.EntireRow.Hidden = False
Else
    Selection.EntireRow.Hidden = True

End If

 .Protect Password:="8675309"
 Application.ScreenUpdating = True
  
End With

End Sub

 

[Candyman8019]

Yes. Rather than worksheet_selectionchange use worksheet_change