How to hide and unhide based on drop down list in VBA?

VBAEXCELNew

New Member
Joined
Apr 3, 2023
Messages
38
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
Hi, everyone

i am in a scenario where currently i want to unhide row 19 - 22 based on my drop down list. I wan to show my row 19-22 when i select in this drop down "Dumping/Destruction" or "Donation To Charity" other wise my row 19-22 will be hide, how i can based on this write in my vba on changes and when selecting this drop down a message box appear to prompt the user?
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
I have written a simple vba script however i have mismatch issue can anyone help me out why ?
I have a column J where i wan from j6 to whatever j last row is how can i do it ?

1682403416635.png


VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim keycells As Range
Set keycells = Range("J6:J999")

If Not Application.Intersect(keycells, Range(Target.Address)) Is Nothing Then
If keycells = "Dumping / Destruction" Then
If keycells = "Donation To Charity" Then
Rows("19:22").Hidden = False
Else
Rows("19:22").Hidden = True
End If
End If
End If

   


End Sub
 
Last edited:
Upvote 0
Try


VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim keycells As Range
Dim KLoop As Range
Set keycells = Range("J6:J999")
For Each KLoop In keycells
    If Not Application.Intersect(keycells, Range(Target.Address)) Is Nothing Then
        If KLoop.Value = "Dumping / Destruction" Or KLoop.Value = "Donation To Charity" Then
            Rows("19:22").Hidden = False
            exit for
        Else
            Rows("19:22").Hidden = True
        End If
    End If
Next

End Sub
 
Upvote 0
Try


VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim keycells As Range
Dim KLoop As Range
Set keycells = Range("J6:J999")
For Each KLoop In keycells
    If Not Application.Intersect(keycells, Range(Target.Address)) Is Nothing Then
        If KLoop.Value = "Dumping / Destruction" Or KLoop.Value = "Donation To Charity" Then
            Rows("19:22").Hidden = False
            exit for
        Else
            Rows("19:22").Hidden = True
        End If
    End If
Next

End Sub
Thanks, but i have issue when reselect the dropdown the hidden row wouldnt appear again
 
Upvote 0
Its because you are checking a range. Some of them are true some false so it will switch
 
Upvote 0
My bad, try


VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim keycells As Range
Set keycells = Range("J6:J999")
If Not Application.Intersect(keycells, Range(Target.Address)) Is Nothing Then
    If Target.Value = "Dumping / Destruction" Or Target.Value = "Donation To Charity" Then
        Rows("19:22").Hidden = False
    Else
        Rows("19:22").Hidden = True
    End If
End If

End Sub
 
Upvote 0
My bad, try


VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim keycells As Range
Set keycells = Range("J6:J999")
If Not Application.Intersect(keycells, Range(Target.Address)) Is Nothing Then
    If Target.Value = "Dumping / Destruction" Or Target.Value = "Donation To Charity" Then
        Rows("19:22").Hidden = False
    Else
        Rows("19:22").Hidden = True
    End If
End If

End Sub
Thanks it work, since we are at here do u know when i select this target.value how do we make a message pop out to prompt the user ?
i realize why the row not hiding is due to my drop down list there some spelling or capital letter different LOL
 
Upvote 0
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim keycells As Range
Set keycells = Range("J6:J999")
If Not Application.Intersect(keycells, Range(Target.Address)) Is Nothing Then
    If Target.Value = "Dumping / Destruction" Or Target.Value = "Donation To Charity" Then
        Rows("19:22").Hidden = False
        MsgBox "Please select from bla bla", vbInformation, ThisWorkbook.Name
    Else
        Rows("19:22").Hidden = True
    End If
End If

End Sub
 
Upvote 1
Solution

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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