PDA

View Full Version : Hide rows based on results of a drop down box on another sheet.



tcrjmom
04-16-2014, 08:00 AM
Hello to all,

I'm a very limited on my knowledge of vb code but have been working at it so, I'm hoping someone will help me accomplish the following:

I would like code to hide rows on one sheet (Order Form) based on the results of a drop down box on another sheet (Entry Form). If the drop down box result is a 2 then I want the rows hidden, if the result of the drop down is 1 then I don't want anything to happen (no rows to be hidden). The worksheets and workbook are locked when it’s sent out to be used so I also need a code to unlock the workbook so the rows can be hidden. I've searched throughout several sites and have found code and tested them, but none of it seems to work and I don't know why. Below is the code that I currently have:

Private Sub Worksheet_Calculate()
Dim MyRange As Range
Set MyRange = Sheets("Entry Form").Range("Z2")
If MyRange("Z2").Value = "2" Then
Sheets("Order Form").Rows("22:22,25:27,30:32,36:38").EntireRow.Hidden = True
Else
Sheets("Order Form").Rows("22:22,25:27,30:32,36:38").EntireRow.Hidden = False
End If
End Sub

I'm not sure if I even have the code placed correctly in the worksheet. I currently have the code placed on the Entry Form worksheet, but have also tried it as a module - still didn't work. Please advise.

Thank you, in advance, for your assistance.

Regards,

patel
04-16-2014, 09:30 AM
Private Sub Worksheet_Change()
instead of

Private Sub Worksheet_Calculate()

tcrjmom
04-16-2014, 09:44 AM
Thank you Patel, but it still did not work. :-(

patel
04-16-2014, 11:41 AM
attach please a sample file for testing

HaHoBe
04-20-2014, 02:15 AM
Hi, tcrjmom,

code goes behind the worksheet Entry Form (Right Click on Worksheet Tab, Vew Code, paste code into the code window), code will be triggered if on Sheet Entry Form the Drop Down for Z2 is changed:


Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("Z2")) Is Nothing Then
Sheets("Order Form").Range("22:22,25:27,30:32,36:38").EntireRow.Hidden = Range("Z2").Value = 2
End If
End Sub

Ciao,
Holger