Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a function to hide rows
First and foremost, any suggestions would be greatly appreciated!
I'm trying to use a function to hide a row or rows based upon the results of a given cell. I would use a sub but I need it to update the number of hidden cells each time the value in the cell changes. So far I've tried something like: Function Hide_Rows(Input_Cell as Range) Dim InputRow = InputCell.Row If InputCell = (some number) Then Rows(InputRow+1).Hidden = True End If.... The function compiles fine, but the row doesn't get hidden when the function is implemented. Any ideas, suggestions, comments on how to fix this? Thanks. Ryan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a function to hide rows
Ryan,
Put this code in the Thisworkbook module. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target = <Enter the value you are searching for Then Range(Target.Address).Select Selection.EntireRow.Hidden = True End If "Ryan" wrote: First and foremost, any suggestions would be greatly appreciated! I'm trying to use a function to hide a row or rows based upon the results of a given cell. I would use a sub but I need it to update the number of hidden cells each time the value in the cell changes. So far I've tried something like: Function Hide_Rows(Input_Cell as Range) Dim InputRow = InputCell.Row If InputCell = (some number) Then Rows(InputRow+1).Hidden = True End If.... The function compiles fine, but the row doesn't get hidden when the function is implemented. Any ideas, suggestions, comments on how to fix this? Thanks. Ryan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a function to hide rows
If you are attempting at using the function from the spreadsheet side, this
would be known as a UDF or User Defined Function. In this case, performing any such actions is not allowed as UDFs are merely meant to return a value, not to perform any actions. On the other hand, if you are calling on this function from another method (Sub or Function) within VBA, then performing actions is perfectly legal. Now, I see 3 main issues with your Function below. First, the whole purpose of a function unlike a subprocedure is to return a value (via Let statement, which can be omitted and normally is omitted) or a reference to an object (via Set statement). I therefore ask you, what is it that this function is suppose to be returning to the caller of this function? To have the function return a value, it needs to be using the following syntax <FunctionName = <Value OR Set <FunctionName = <Object Now onto the 2nd issue I see. What worksheet are you attempting to hide the rows on? Is this suppose to hide the rows on the parent worksheet of the "Input_Cell" range object? If so, try the following statement Input_Cell.Parent.Rows(InputRow+1).Hidden=True However, the above statement still isn't valid as the argument for the Rows property is a read only range object, so we much use the Range object instead. However, we need to modify the above input into the range object as the above is still invalid. The input must take on the format of the following in string format. <FirstRowNumber":"<LastRowNumber Such as the following: CStr(InputRow+1) & ":" & CStr(InputRow+1) Therefore, your statement will now look like: Input_Cell.Parent.Range(CStr(InputRow+1) & ":" & CStr(InputRow+1)).Hidden=True Main thing you should be able to take from this, don't leave your code to the possibility of ambiguity, if reasonably possible. I prequalify just about all of the various variables, methods, objects and anything else involved. There's only a few things I don't prequalify, such as the data type conversion functions cause if I attempt to prequalify them functions, VBA errors out on me. I learned this ambiguity lesson a long while back the hard way (years), thus why I'm a stickler on this rule. This is also one such reason why I use "Option Explicit" at the top of every single module in VBA. This was one of my first lessons learned when I started working with VBA. With prequalifying the various things in code, you not only avoid ambiguity, but you also avoid the priority issues that you may face, if using this code on multiple computers. Yes, I have faced that issue head on too. Some systems would allow me to use "Date" variable of the VBA class, but others wouldn't allow me to use that statement by itself, so I had to modify the code to be "VBA.Date", so as it would work on all computers. -- Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "Ryan" wrote in message ... First and foremost, any suggestions would be greatly appreciated! I'm trying to use a function to hide a row or rows based upon the results of a given cell. I would use a sub but I need it to update the number of hidden cells each time the value in the cell changes. So far I've tried something like: Function Hide_Rows(Input_Cell as Range) Dim InputRow = InputCell.Row If InputCell = (some number) Then Rows(InputRow+1).Hidden = True End If.... The function compiles fine, but the row doesn't get hidden when the function is implemented. Any ideas, suggestions, comments on how to fix this? Thanks. Ryan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a function to hide rows
Thanks, that works great but doesn't really accomplish what I'm trying to do.
I don't want the sheet to change every time <Enter the value you are searching for is inputted, only when the value of a certain cell is changed. Basically, what I have is a row with validated cell in in and 8 hidden rows immediately following. The validated cell has a list of values 1-8 and depending on what value you choose from this list, that many rows below hide or unhide. Any change you have words on wisdom as how to accomplish this? Ryan "JRForm" wrote: Ryan, Put this code in the Thisworkbook module. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target = <Enter the value you are searching for Then Range(Target.Address).Select Selection.EntireRow.Hidden = True End If "Ryan" wrote: First and foremost, any suggestions would be greatly appreciated! I'm trying to use a function to hide a row or rows based upon the results of a given cell. I would use a sub but I need it to update the number of hidden cells each time the value in the cell changes. So far I've tried something like: Function Hide_Rows(Input_Cell as Range) Dim InputRow = InputCell.Row If InputCell = (some number) Then Rows(InputRow+1).Hidden = True End If.... The function compiles fine, but the row doesn't get hidden when the function is implemented. Any ideas, suggestions, comments on how to fix this? Thanks. Ryan |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a function to hide rows
Words of wisdom much appreciated. Finally got is working properly thanks to
everyone's help. Thanks again!! Ryan "Ronald Dodge" wrote: If you are attempting at using the function from the spreadsheet side, this would be known as a UDF or User Defined Function. In this case, performing any such actions is not allowed as UDFs are merely meant to return a value, not to perform any actions. On the other hand, if you are calling on this function from another method (Sub or Function) within VBA, then performing actions is perfectly legal. Now, I see 3 main issues with your Function below. First, the whole purpose of a function unlike a subprocedure is to return a value (via Let statement, which can be omitted and normally is omitted) or a reference to an object (via Set statement). I therefore ask you, what is it that this function is suppose to be returning to the caller of this function? To have the function return a value, it needs to be using the following syntax <FunctionName = <Value OR Set <FunctionName = <Object Now onto the 2nd issue I see. What worksheet are you attempting to hide the rows on? Is this suppose to hide the rows on the parent worksheet of the "Input_Cell" range object? If so, try the following statement Input_Cell.Parent.Rows(InputRow+1).Hidden=True However, the above statement still isn't valid as the argument for the Rows property is a read only range object, so we much use the Range object instead. However, we need to modify the above input into the range object as the above is still invalid. The input must take on the format of the following in string format. <FirstRowNumber":"<LastRowNumber Such as the following: CStr(InputRow+1) & ":" & CStr(InputRow+1) Therefore, your statement will now look like: Input_Cell.Parent.Range(CStr(InputRow+1) & ":" & CStr(InputRow+1)).Hidden=True Main thing you should be able to take from this, don't leave your code to the possibility of ambiguity, if reasonably possible. I prequalify just about all of the various variables, methods, objects and anything else involved. There's only a few things I don't prequalify, such as the data type conversion functions cause if I attempt to prequalify them functions, VBA errors out on me. I learned this ambiguity lesson a long while back the hard way (years), thus why I'm a stickler on this rule. This is also one such reason why I use "Option Explicit" at the top of every single module in VBA. This was one of my first lessons learned when I started working with VBA. With prequalifying the various things in code, you not only avoid ambiguity, but you also avoid the priority issues that you may face, if using this code on multiple computers. Yes, I have faced that issue head on too. Some systems would allow me to use "Date" variable of the VBA class, but others wouldn't allow me to use that statement by itself, so I had to modify the code to be "VBA.Date", so as it would work on all computers. -- Sincerely, Ronald R. Dodge, Jr. Master MOUS 2000 "Ryan" wrote in message ... First and foremost, any suggestions would be greatly appreciated! I'm trying to use a function to hide a row or rows based upon the results of a given cell. I would use a sub but I need it to update the number of hidden cells each time the value in the cell changes. So far I've tried something like: Function Hide_Rows(Input_Cell as Range) Dim InputRow = InputCell.Row If InputCell = (some number) Then Rows(InputRow+1).Hidden = True End If.... The function compiles fine, but the row doesn't get hidden when the function is implemented. Any ideas, suggestions, comments on how to fix this? Thanks. Ryan |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a function to hide rows
Ryan,
Let me see if I understand your request. You are searching a column for a specific value and when/if you find it you want to hide the row. Also you want to hide the rows that are associated with it. Is this correct? "Ryan" wrote: Thanks, that works great but doesn't really accomplish what I'm trying to do. I don't want the sheet to change every time <Enter the value you are searching for is inputted, only when the value of a certain cell is changed. Basically, what I have is a row with validated cell in in and 8 hidden rows immediately following. The validated cell has a list of values 1-8 and depending on what value you choose from this list, that many rows below hide or unhide. Any change you have words on wisdom as how to accomplish this? Ryan "JRForm" wrote: Ryan, Put this code in the Thisworkbook module. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target = <Enter the value you are searching for Then Range(Target.Address).Select Selection.EntireRow.Hidden = True End If "Ryan" wrote: First and foremost, any suggestions would be greatly appreciated! I'm trying to use a function to hide a row or rows based upon the results of a given cell. I would use a sub but I need it to update the number of hidden cells each time the value in the cell changes. So far I've tried something like: Function Hide_Rows(Input_Cell as Range) Dim InputRow = InputCell.Row If InputCell = (some number) Then Rows(InputRow+1).Hidden = True End If.... The function compiles fine, but the row doesn't get hidden when the function is implemented. Any ideas, suggestions, comments on how to fix this? Thanks. Ryan |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using a function to hide rows
Ryan
With DV dropdown in A1 and values of 0 through 8 try this........... Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub On Error GoTo endit Application.EnableEvents = False Rows("2:9").Hidden = True Rows(1 & ":" & Target.Value + 1).Hidden = False endit: Application.EnableEvents = True End Sub Select 0 from list and no rows unhide, 1 unhides row 2, 2 unhides 2:3 etc. Gord Dibben MS Excel MVP On Thu, 25 Oct 2007 10:46:02 -0700, Ryan wrote: Thanks, that works great but doesn't really accomplish what I'm trying to do. I don't want the sheet to change every time <Enter the value you are searching for is inputted, only when the value of a certain cell is changed. Basically, what I have is a row with validated cell in in and 8 hidden rows immediately following. The validated cell has a list of values 1-8 and depending on what value you choose from this list, that many rows below hide or unhide. Any change you have words on wisdom as how to accomplish this? Ryan "JRForm" wrote: Ryan, Put this code in the Thisworkbook module. Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target = <Enter the value you are searching for Then Range(Target.Address).Select Selection.EntireRow.Hidden = True End If "Ryan" wrote: First and foremost, any suggestions would be greatly appreciated! I'm trying to use a function to hide a row or rows based upon the results of a given cell. I would use a sub but I need it to update the number of hidden cells each time the value in the cell changes. So far I've tried something like: Function Hide_Rows(Input_Cell as Range) Dim InputRow = InputCell.Row If InputCell = (some number) Then Rows(InputRow+1).Hidden = True End If.... The function compiles fine, but the row doesn't get hidden when the function is implemented. Any ideas, suggestions, comments on how to fix this? Thanks. Ryan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide rows function | Excel Discussion (Misc queries) | |||
Creating a function to auto hide rows | Excel Worksheet Functions | |||
Enabling option „Format rows“ to hide/unhide rows using VBA-code? | Excel Discussion (Misc queries) | |||
Specify which rows to NOT hide, and have excel hide the rest | Excel Programming | |||
hiding columns or rows not using the hide function | Excel Worksheet Functions |