Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to hide and unhide cells (SC).
I'm designing a form where Cell A5, which is named Off_Num. This cell
has Values (Select One, 1 to 20) in a dropdown, and Rows 9 to 28 are hidden. Now if 1 is selected in Off_Num cell then, the code should Unhide Row 9. And if 2 is selected then Row(9:10) should be unhidden so on, till if 20 is selected the Rows(9:28) should be unhidden. And if Select One option is selected that Rows(9:28) should get hidden again. Please help me with this. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to hide and unhide cells (SC).
One way:
Put this in your worksheet code module: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Range("Off_Num") If Not Intersect(.Cells, Target) Is Nothing Then Application.ScreenUpdating = False Rows("9:28").Hidden = True If IsNumeric(.Value) Then Rows("9:9").Resize(CLng(.Value)).Hidden = False End If Application.ScreenUpdating = True End If End With End Sub In article om, wrote: I'm designing a form where Cell A5, which is named Off_Num. This cell has Values (Select One, 1 to 20) in a dropdown, and Rows 9 to 28 are hidden. Now if 1 is selected in Off_Num cell then, the code should Unhide Row 9. And if 2 is selected then Row(9:10) should be unhidden so on, till if 20 is selected the Rows(9:28) should be unhidden. And if Select One option is selected that Rows(9:28) should get hidden again. Please help me with this. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to hide and unhide cells (SC).
On Mar 31, 7:06 pm, JE McGimpsey wrote:
One way: Put this in your worksheet code module: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Range("Off_Num") If Not Intersect(.Cells, Target) Is Nothing Then Application.ScreenUpdating = False Rows("9:28").Hidden = True If IsNumeric(.Value) Then Rows("9:9").Resize(CLng(.Value)).Hidden = False End If Application.ScreenUpdating = True End If End With End Sub In article om, wrote: I'm designing a form where Cell A5, which is named Off_Num. This cell has Values (Select One, 1 to 20) in a dropdown, and Rows 9 to 28 are hidden. Now if 1 is selected in Off_Num cell then, the code should Unhide Row 9. And if 2 is selected then Row(9:10) should be unhidden so on, till if 20 is selected the Rows(9:28) should be unhidden. And if Select One option is selected that Rows(9:28) should get hidden again. Please help me with this.- Hide quoted text - - Show quoted text - Thanks a lot this works just as i wanted. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to hide and unhide cells (SC).
On Mar 31, 10:53 pm, wrote:
On Mar 31, 7:06 pm, JE McGimpsey wrote: One way: Put this in your worksheet code module: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Range("Off_Num") If Not Intersect(.Cells, Target) Is Nothing Then Application.ScreenUpdating = False Rows("9:28").Hidden = True If IsNumeric(.Value) Then Rows("9:9").Resize(CLng(.Value)).Hidden = False End If Application.ScreenUpdating = True End If End With End Sub In article om, wrote: I'm designing a form where Cell A5, which is named Off_Num. This cell has Values (Select One, 1 to 20) in a dropdown, and Rows 9 to 28 are hidden. Now if 1 is selected in Off_Num cell then, the code should Unhide Row 9. And if 2 is selected then Row(9:10) should be unhidden so on, till if 20 is selected the Rows(9:28) should be unhidden. And if Select One option is selected that Rows(9:28) should get hidden again. Please help me with this.- Hide quoted text - - Show quoted text - Thanks a lot this works just as i wanted.- Hide quoted text - - Show quoted text - Now i am back with another question How can i write the same code if i have to unhide a set of rows instead of a single row. Similar to above, if 1 is selected in cell Mod_Num then rows (5 to 10) should get unhidden and in 2 is selected rows (5 to 15) will get unhidden so on till i enter 5 to unhide the entire range of rows that are hidden. is this possible?? Thanks for your help. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Code to hide and unhide cells (SC).
Hi Ram,
See at least one response il your later thread. --- Regards, Norman "Ram" wrote in message oups.com... On Mar 31, 10:53 pm, wrote: On Mar 31, 7:06 pm, JE McGimpsey wrote: One way: Put this in your worksheet code module: Private Sub Worksheet_Change(ByVal Target As Excel.Range) With Range("Off_Num") If Not Intersect(.Cells, Target) Is Nothing Then Application.ScreenUpdating = False Rows("9:28").Hidden = True If IsNumeric(.Value) Then Rows("9:9").Resize(CLng(.Value)).Hidden = False End If Application.ScreenUpdating = True End If End With End Sub In article om, wrote: I'm designing a form where Cell A5, which is named Off_Num. This cell has Values (Select One, 1 to 20) in a dropdown, and Rows 9 to 28 are hidden. Now if 1 is selected in Off_Num cell then, the code should Unhide Row 9. And if 2 is selected then Row(9:10) should be unhidden so on, till if 20 is selected the Rows(9:28) should be unhidden. And if Select One option is selected that Rows(9:28) should get hidden again. Please help me with this.- Hide quoted text - - Show quoted text - Thanks a lot this works just as i wanted.- Hide quoted text - - Show quoted text - Now i am back with another question How can i write the same code if i have to unhide a set of rows instead of a single row. Similar to above, if 1 is selected in cell Mod_Num then rows (5 to 10) should get unhidden and in 2 is selected rows (5 to 15) will get unhidden so on till i enter 5 to unhide the entire range of rows that are hidden. is this possible?? Thanks for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro or VB code to hide and unhide Label box | Excel Discussion (Misc queries) | |||
Slow VBA code....Hide/Unhide Loop | Excel Worksheet Functions | |||
VB Code to hide and unhide rows | Excel Discussion (Misc queries) | |||
Hide/Unhide in Code; Protect, Unlock, etc. | Excel Programming | |||
Code to hide and unhide columns, with certain condition? | Excel Programming |