Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Input variables on Sheet1 being called by a VB program on Sheet 2
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use two parameters in input of popup Variables - Gary's Student | Excel Worksheet Functions | |||
Array for varying 2 out of many input variables for complicated fx | Excel Discussion (Misc queries) | |||
How can I have have one column's input depend on the variables in a second one? | Excel Worksheet Functions | |||
where can i get a software program called Microfit? | Excel Discussion (Misc queries) | |||
Help required with setting up a pivot table with the source on sheet1 to have the pivot table created on sheet called "report" | Excel Programming |