Input variables on Sheet1 being called by a VB program on Sheet 2
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
-----Original Message-----
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
TargetAddress 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
.
|