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!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$20" Then
If Target.Value = "No" Then
Hideforeign
Else
ShowForeign
End If
End If
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!

on the sheets, is there a column with some indicator as to whether a row
should be shown or not?

In the follwing code, sheets 2 through 10, rows hidden or shown depending on
the value in A being "Yes"

Sub ShowHideForeign(show As Boolean)
'
' ShowHideForeign Macro
' Hides or shows all items in workbook pertaining to foreign activities
'
'
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim sh As Long
Dim rwindex As Long
For sh = 2 To 10
rwindex = 2 ' assume row 1 are headings
Set ws = Worksheets("Sheet" & sh)
Do Until ws.Cells(rwindex, 1) = ""
If ws.Cells(rwindex, "A") = "YES" Then
ws.Cells(rwindex, "A").EntireRow.Hidden = show
End If
rwindex = rwindex + 1
Loop
Next
Application.ScreenUpdating = True
End Sub


change the code in the worksheet event to this :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$20" Then
If Target.Value = "No" Then
ShowHideForeign False
Else
ShowHideForeign True
End If
End If
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!

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!

Patrick and all,

Thanks for your responses. No, there is currently no an indicator in
the rows to be hidden/shown. I suppose I could add a character in a
hidden column to indicate if the row should be hidden/shown based on
the response, although I'm not sure if that just adds more complexity.

I'm still a little confused though. Could you tell me why my existing
code, when run, just generates a pop-up that lists all of the available
macros? I'd like to understand where the flaws are in my coding, in
addition to re-writing it a different way if needed.

Part One

Worksheet "I-1" has a Yes/No dropdown box. This code, stored in the
I-1 worksheet (as opposed to ThisWorkbook or a general module), checks
whether or not the Yes/No value has changed and, if so, it will call
the appropriate macro as to whether to hide or show rows on other
worksheets.

Part Two

Worksheet "I-1" now has a second drop-down box, giving the user the
ability to select whether certain data summarized on a different
worksheet would be rounded to the nearest hundred, thousand, million,
or no rounding. Again, this code is stored in the I-1 worksheet (as
opposed to ThisWorkbook or a general module).

I'd appreciate your feedback as to why this doesn't work and
alternative codings / module placement. Thanks in advance!

The code for the two subs are as follows:

-------------------

Option Explicit
Private Sub Foreign_Change(ByVal Target As Range)
' Checks to see if worksheet I-1 foreign activities option has
changed
' Cell D26 is a Yes/No dropdown box to indicate if company has
foreign activity.
Dim ForeignYesNo As Range
Set ForeignYesNo = Range("D26")
If Not Application.Intersect(Target, ForeignYesNo) Is Nothing
Then
' If I-1 cell D28 has changed, then show foreign rows if value of
D28 is "Yes"
' Hide foreign rows if value of D26 is "No"
If Target.Value = "No" Then
Run "Hideforeign"
Else: Run "ShowForeign"
End If
End If
End Sub

Private Sub Rounding_Change(ByVal Target As Range)
' Checks to see if worksheet I-1 rounding option has changed
Dim RoundingOptionChange As Range
Dim RoundingValue As Integer
Set RoundingOptionChange = Range("B16")
If Not Application.Intersect(Target, RoundingOptionChange) Is
Nothing Then
' Selects rounding option based on user input in B16
' Cell B16 has a drop-down box, showing None, Hundreds, Thousands
or Millions
If Target.Value = "Hundreds" Then
RoundingValue = -1
Else
If Target.Value = "Thousands" Then
RoundingValue = -2
Else
If Target.Value = "Millions" Then
RoundingValue = -3
Else: RoundingValue = 0
End If
End If
End If
End If
End Sub

------------------

Patrick Molloy wrote:
on the sheets, is there a column with some indicator as to whether a row
should be shown or not?

In the follwing code, sheets 2 through 10, rows hidden or shown depending on
the value in A being "Yes"

Sub ShowHideForeign(show As Boolean)
'
' ShowHideForeign Macro
' Hides or shows all items in workbook pertaining to foreign activities
'
'
Application.ScreenUpdating = False
Dim ws As Worksheet
Dim sh As Long
Dim rwindex As Long
For sh = 2 To 10
rwindex = 2 ' assume row 1 are headings
Set ws = Worksheets("Sheet" & sh)
Do Until ws.Cells(rwindex, 1) = ""
If ws.Cells(rwindex, "A") = "YES" Then
ws.Cells(rwindex, "A").EntireRow.Hidden = show
End If
rwindex = rwindex + 1
Loop
Next
Application.ScreenUpdating = True
End Sub


change the code in the worksheet event to this :

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$20" Then
If Target.Value = "No" Then
ShowHideForeign False
Else
ShowHideForeign True
End If
End If
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!