Here's a similar solution to Patrick's. You would assign the ShowOrHide macro
to the drop-down box. This will run when the drop-down box changes, but not
if you were to manually change Cell D20 (bypassing the drop-down box).

Hope it helps.

Regards,
James

Sub ShowOrHide()
Select Case ThisWorkbook.Worksheets("Sheet1").Range("D20").Val ue
Case "Yes"
HideForeignRows True
Case "No"
HideForeignRows False
End Select
End Sub

Sub HideForeignRows(blnShowRows As Boolean)
Dim wkb As Workbook

Set wkb = ThisWorkbook

wkb.Worksheets("Sheet1").Range("A33").EntireRow.Hi dden = blnShowRows

wkb.Worksheets("Sheet2").Range("A30:A31,A42:A43,A4 7,A50").EntireRow.Hidden =
blnShowRows
wkb.Worksheets("Sheet3").Range("A22").EntireRow.Hi dden = blnShowRows

Set wkb = Nothing
End Sub

"Punsterr" wrote:

This is my first attempt at vba programming, so I'm sure there are some
obvious flaws in my code below. I'd appreciate your help.

I have a workbook with multiple worksheets. On the first worksheet,
one of the lines asks "Does the unit sell internationally?" followed by
a Yes/No drop-down box.

The goal is to hide unnecessary rows in various worksheets in the
workbook if the unit doesn't sell internationally. If the dropdown is
Yes, then it calls the "Show" macro. If the dropdown is No, then it
calls the "Hide" macro.

Issue 1: I have this first routine stored within the "Sheet1", the
worksheet that contains the cell value being tested. Running the
following code, I'm actually getting a pop-up box listing all of the
macros in the workbook... so I'm assuming that's an error. Do you see
anything obviously wrong with this code?

Private Sub Worksheet_Change(ByVal Target As Range)
' Checks to see if worksheet "Sheet1" cell D20 has changed
' Cell D20 is a Yes/No dropdown box to indicate if unit sells
internationally.
If Not Application.Intersect(Worksheets("Sheet1").Range(" D20"),
Target) Is Nothing Then
' If I-1 cell D20 has changed, then show foreign rows if value of
D20 is "Yes"
' Hide foreign rows if value of D20 is "No"
If Target.Value = "No" Then
Run "Hideforeign"
Else: Run "ShowForeign"
End If
End If
End Sub



Issue 2: Here are the Show/Hide macros. Can this be written more
efficiently?

Sub HideForeign()
'
' HideForeign Macro
' Hides all items in workbook pertaining to foreign activities
'
'
Application.ScreenUpdating = False

Sheets("Sheet1").Range("a33").EntireRow.Hidden = True

Sheets("Sheet2").Range("a30,a31,a42,a43,a47,a50"). EntireRow.Hidden =
True
Sheets("Sheet3").Range("a22").EntireRow.Hidden = True
' There are another 15 sheets with various rows to hide

Application.ScreenUpdating = True
End Sub

Sub ShowForeign()
'
' ShowForeign Macro
' Shows all items in workbook pertaining to foreign activities
'
'
Application.ScreenUpdating = False

Sheets("Sheet1").Range("a33").EntireRow.Hidden = False

Sheets("Sheet2").Range("a30,a31,a42,a43,a47,a50"). EntireRow.Hidden =
False
Sheets("Sheet3").Range("a22").EntireRow.Hidden = False
' Again, another 15 sheets with EntireRow.Hidden set to False.

Application.ScreenUpdating = True
End Sub


Thanks, group!