Input variables on Sheet1 being called by a VB program on Sheet 2

  • Thread starter Thread starter RU42
  • Start date Start date
R

RU42

Tom provided me with some code that helps me hide rows in a sheet
based on a user input on that specific sheet. The code looks like
this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
if Target.count > 1 then exit sub
if Target.Address = "$A$15" then
Rows(20).Resize(10).Hidden = True
if Target.Value >=1 and Target.Value <= 5 then _
Rows(20).Resize(Target.Value*2).Hidden = False
End if
End Sub

I would like 2 variations on this code:
1. Where the user input is on a separate sheet
2. Where the 'hidden' statements are on a different sheet.

Example:
1. The active sheet is Sheet 2 and want the Target>Address to be
from Sheet 1. (I assume this would be the 3rd line of code (if
Target.Address=$A$15)

2. I want the input (if Target.Address=$A$15) is the active sheet to
effect the Hidden (lines 5, 6, and 7) on a different Sheet.

I played around with something like:
if Target.Address =Sheet1$A$15 but I know that is incorrect. Any help
would be appreciated.

RU
 
Hi

try this

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Count > 1 Then Exit Sub
If Target.Address = "$A$15" Then
Sheet2.Rows(20).Resize(10).Hidden = True
If Target.Value >= 1 And Target.Value <= 5 Then _
Sheet2.Rows(20).Resize(Target.Value * 2).Hidden = False
End If
End Sub

This is the new code

Sheet2.Rows(20).Resize(10).Hidden = True

Where sheet2 is the sheet you want to hide the row on
based on the input on sheet1, or where ever
the 'worksheet_change' macro resides.

Hope this helps

Richard Daniels
 
Back
Top