Hide or disable buttons until dropdown is filled

NoobXL

New Member
Joined
Apr 8, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi everyone,

I am still working on a form. I have 7 buttoms which open dropdown menus (unhide rows). At the moment you can open them all (also without filling in the first dropdown). I would like to change this. You can only use the buttom (to unhide rows) if you filled in the former dropdown menu.

This is is what I tried:

If Worksheet.Range("G172").Value = "" Then
ButtomAssignmentSN().Enabled = False

Else
ButtomAssignmentSN().Enabled = True

End If

End Sub


For Buttom:

Public Sub ButtomAssignmentSN()
Rows("174:176").Select
If Selection.EntireRow.Hidden = True Then
Selection.EntireRow.Hidden = False
Else
Selection.EntireRow.Hidden = True

End If

End Sub


I receive the following message:
1619000968723.png


I will also upload the minisheet with the Private Sub Worksheet_Change(ByVal Target As Range) and below code.

VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Range("F16:F16"), Range(Target.Address)) Is Nothing Then

Call ShowHide

ElseIf Not Application.Intersect(Range("G30:G30"), Range(Target.Address)) Is Nothing Then

Call ShowHide_HRLine

ElseIf Not Application.Intersect(Range("I26:I26"), Range(Target.Address)) Is Nothing Then

Call ShowOption4

ElseIf Not Application.Intersect(Range("F16:F16"), Range(Target.Address)) Is Nothing Then

Call ShowOption5

ElseIf Not Application.Intersect(Range("F26:F26"), Range(Target.Address)) Is Nothing Then

Call ShowOption6

ElseIf Not Application.Intersect(Range("B53:B53"), Range(Target.Address)) Is Nothing Then

Call ShowPivot

ElseIf Not Application.Intersect(Range("F50:F50"), Range(Target.Address)) Is Nothing Then

Call ShowOption2a

ElseIf Not Application.Intersect(Range("B149:B149"), Range(Target.Address)) Is Nothing Then

Call ShowOption7

ElseIf Not Application.Intersect(Range("J23:J23"), Range(Target.Address)) Is Nothing Then

Call ShowOption8

ElseIf Not Application.Intersect(Range("B135:B135"), Range(Target.Address)) Is Nothing Then

Call ShowOption7

ElseIf Not Application.Intersect(Range("K16:K16"), Range(Target.Address)) Is Nothing Then

Call ShowOption10

End If

    If Target.Address = "$G$172" Then
    Range("H172").Value = ""
    
End If
    
    If Target.Address = "$G$172" Then
    Range("I172").Value = ""

End If
    
    If Target.Address = "$G$172" Then
    Range("H175").Value = ""
    
End If

    If Target.Address = "$G$172" Then
    Range("I175").Value = ""
    
End If

    If Target.Address = "$G$172" Then
    Range("H178").Value = ""

End If

    If Target.Address = "$G$172" Then
    Range("I178").Value = ""
    
End If

    If Target.Address = "$G$172" Then
    Range("H181").Value = ""

End If

    If Target.Address = "$G$172" Then
    Range("I181").Value = ""
    
End If

    If Target.Address = "$G$172" Then
    Range("H184").Value = ""
    
End If

    If Target.Address = "$G$172" Then
    Range("I184").Value = ""
    
End If

    If Target.Address = "$G$172" Then
    Range("H187").Value = ""
    
End If

    If Target.Address = "$G$172" Then
    Range("I187").Value = ""
    
End If

    If Target.Address = "$G$172" Then
    Range("H190").Value = ""
    
End If

    If Target.Address = "$G$172" Then
    Range("I190").Value = ""
    
End If

    If Target.Address = "$G$172" Then
    Range("H193").Value = ""
    
End If

    If Target.Address = "$G$172" Then
    Range("I193").Value = ""

End If
 
    If Target.Address = "$F$14" Then
    Range("G172").Value = ""

End If
    
    If Target.Address = "$F$16" Then
    Range("G172").Value = ""

End If
    
    If Target.Address = "$F$26" Then
    Range("G172").Value = ""
    
End If


If Worksheet.Range("G172").Value = "" Then
    ButtomAssignmentSN().Enabled = False
    
    Else
    ButtomAssignmentSN().Enabled = True
    
  End If

End Sub


Public Sub ButtomAssignmentSN()
Rows("174:176").Select
If Selection.EntireRow.Hidden = True Then
Selection.EntireRow.Hidden = False
Else
Selection.EntireRow.Hidden = True

End If

End Sub

Public Sub ButtonAssignmentSN2()
Rows("177:179").Select
If Selection.EntireRow.Hidden = True Then
Selection.EntireRow.Hidden = False
Else
Selection.EntireRow.Hidden = True

End If

End Sub

Public Sub ButtonAssignmentSN3()
Rows("180:182").Select
If Selection.EntireRow.Hidden = True Then
Selection.EntireRow.Hidden = False
Else
Selection.EntireRow.Hidden = True

End If

End Sub

Public Sub ButtonAssignmentSN4()
Rows("183:185").Select
If Selection.EntireRow.Hidden = True Then
Selection.EntireRow.Hidden = False
Else
Selection.EntireRow.Hidden = True

End If

End Sub

Public Sub ButtonAssignmentSN5()
Rows("186:188").Select
If Selection.EntireRow.Hidden = True Then
Selection.EntireRow.Hidden = False
Else
Selection.EntireRow.Hidden = True

End If

End Sub


Public Sub ButtonAssignmentSN6()
Rows("189:191").Select
If Selection.EntireRow.Hidden = True Then
Selection.EntireRow.Hidden = False
Else
Selection.EntireRow.Hidden = True

End If

End Sub


Public Sub ButtonAssignmentSN7()
Rows("192:195").Select
If Selection.EntireRow.Hidden = True Then
Selection.EntireRow.Hidden = False
Else
Selection.EntireRow.Hidden = True

End If

End Sub

Public Sub ButtomAssignmentSN8()
Rows("174:195").Select
If Selection.EntireRow.Hidden = True Then
Selection.EntireRow.Hidden = True
Else
Selection.EntireRow.Hidden = True

End If

End Sub
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top