Home » Forums » Open Source Learning Center » Question and Answer

Hide and unhide rows in one worksheet based on the value from a drop down list from another worksheet

Submitted by HP on 31 May, 2012 - 14:22

I need to hide and unhide rows in one worksheet based on the value chosen from a drop down list from another worksheet in the same workbook. Please help.

AttachmentSize
SampleFSWPChk.xlsm602.83 KB
‹ Database Search and Output Combine total items ›
»

A step in the right direction, perhaps...

Submitted by joy.eliz.young on 1 June, 2012 - 17:53.

Hi,

I'm on a friend's computer that doesn't have excel, so this is from memory as I can't open my files. The syntax won't be perfect, but the logic will work for you. Your file has several check boxes, correct? I've done this sort of thing within a user form. It took a little extra time, but I named the check boxes in a way that corresponded with values in a particular column in the spreadsheet that required the show/hide rows.

I then used:

Dim ckbox as checkbox, Lookup as string, ShowHideRange as range
set ShowHideRange as sheets(1).range("ShowHideRange")
For each ckbox in sheets(1).oleobjects
If ckbox.value=true then
Lookup = ckbox.name
'(or some portion of the name like
'Lookup = left(ckbox.name, 2)

'Lookup is the value looked for in ShowHideRange.
'Then:

With ShowHideRange.cells
(using Nick's VBA tip #4 "Finding things Using VBA"
Lookup is equivalent to FoundIt
then replace the bold command with
rows.hidden=true)
End with
End If
Next ckbox

I like to use naming conventions for my objects that correspond in some way to something within the file, or the file name itself. Hope that helps.

Joy

»

how to seperate drop down list and rows in two sheets

Submitted by Anonymous on 4 June, 2012 - 13:59.

Thanks for the reply. I will try it out.

I was able to use the simple code below to hide the rows based on a drop down list in the same worksheet. However, I would like to keep the drop down list in sheet1 and rows to be hidden in sheet2. Can someone please help:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim changed As Range
Set changed = Intersect(Target, Range("c18"))
If Not changed Is Nothing Then
Range("A49:A164").EntireRow.Hidden = True
Select Case Target.Value
Case "FOF"
Range("A73:A98").EntireRow.Hidden = False
Case "Direct"
Range("A49:A72").EntireRow.Hidden = False
Case "Feeder"
Range("A99:A129").EntireRow.Hidden = False
Case "Blocker"
Range("A130:A164").EntireRow.Hidden = False
Case ""
Range("A49:A164").EntireRow.Hidden = False

End Select
Range("c18").Select
End If

End Sub

»