Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding and unhiding rows
Hello,
I have been trying to work out how to do this by reading previous posts but this has defeated me. I have a sheet called 'inputs'. Based on a value on a cell D30, I want to hide/unhide rows. The possible values for D30 are 1, 2 or 3. If cell value is 1, I want to show all rows If cell value is 2, I want to hide rows 51-61 and show all others, including 32-52 if value 3 has previously been selected. If cell value is 3, I want to hide rows 32-52 and show all others, including 51-61 if value 2 has previously been selected. If there is anyone who knows how to do this, please help. Or if this is just not do-able (for someone with my skills), that would be helpful to know as well. Many thanks if advance! -- Johanna |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding and unhiding rows
Select the sheet tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the right blank portion. Get back to to workbook and try out. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$D$30" Then UsedRange.EntireRow.Hidden = False If Target.Value = 2 Then Range("A51:A61").EntireRow.Hidden = True If Target.Value = 3 Then Range("A32:A52").EntireRow.Hidden = True End If End Sub -- Jacob "Johanna Gronlund" wrote: Hello, I have been trying to work out how to do this by reading previous posts but this has defeated me. I have a sheet called 'inputs'. Based on a value on a cell D30, I want to hide/unhide rows. The possible values for D30 are 1, 2 or 3. If cell value is 1, I want to show all rows If cell value is 2, I want to hide rows 51-61 and show all others, including 32-52 if value 3 has previously been selected. If cell value is 3, I want to hide rows 32-52 and show all others, including 51-61 if value 2 has previously been selected. If there is anyone who knows how to do this, please help. Or if this is just not do-able (for someone with my skills), that would be helpful to know as well. Many thanks if advance! -- Johanna |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding and unhiding rows
Try this Event macro:
Private Sub Worksheet_Change(ByVal Target As Range) Set t = Target Set d30 = Range("D30") If Intersect(t, d30) Is Nothing Then Exit Sub Application.EnableEvents = False Rows.Hidden = False If d30.Value = 2 Then Rows("51:61").Hidden = True End If If d30.Value = 3 Then Rows("32:52").Hidden = True End If Application.EnableEvents = True End Sub Because it is worksheet code, it is very easy to install and automatic to use: 1. right-click the tab name near the bottom of the Excel window 2. select View Code - this brings up a VBE window 3. paste the stuff in and close the VBE window If you have any concerns, first try it on a trial worksheet. If you save the workbook, the macro will be saved with it. To remove the macro: 1. bring up the VBE windows as above 2. clear the code out 3. close the VBE window To learn more about macros in general, see: http://www.mvps.org/dmcritchie/excel/getstarted.htm To learn more about Event Macros (worksheet code), see: http://www.mvps.org/dmcritchie/excel/event.htm -- Gary''s Student - gsnu201001 "Johanna Gronlund" wrote: Hello, I have been trying to work out how to do this by reading previous posts but this has defeated me. I have a sheet called 'inputs'. Based on a value on a cell D30, I want to hide/unhide rows. The possible values for D30 are 1, 2 or 3. If cell value is 1, I want to show all rows If cell value is 2, I want to hide rows 51-61 and show all others, including 32-52 if value 3 has previously been selected. If cell value is 3, I want to hide rows 32-52 and show all others, including 51-61 if value 2 has previously been selected. If there is anyone who knows how to do this, please help. Or if this is just not do-able (for someone with my skills), that would be helpful to know as well. Many thanks if advance! -- Johanna |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding and unhiding rows
Thanks, that worked really well! I am very pleased!
However, now that I have shared it with other people they would like to have it looking better visually. They would like to have a combobox where they can select the values from. Is this possible? I have managed to create a combobox which excel has named 'dropdown91' but was unable to modify the macro to take the values from that box. Many thanks again! -- Johanna G "Jacob Skaria" wrote: Select the sheet tab which you want to work with. Right click the sheet tab and click on 'View Code'. This will launch VBE. Paste the below code to the right blank portion. Get back to to workbook and try out. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$D$30" Then UsedRange.EntireRow.Hidden = False If Target.Value = 2 Then Range("A51:A61").EntireRow.Hidden = True If Target.Value = 3 Then Range("A32:A52").EntireRow.Hidden = True End If End Sub -- Jacob "Johanna Gronlund" wrote: Hello, I have been trying to work out how to do this by reading previous posts but this has defeated me. I have a sheet called 'inputs'. Based on a value on a cell D30, I want to hide/unhide rows. The possible values for D30 are 1, 2 or 3. If cell value is 1, I want to show all rows If cell value is 2, I want to hide rows 51-61 and show all others, including 32-52 if value 3 has previously been selected. If cell value is 3, I want to hide rows 32-52 and show all others, including 51-61 if value 2 has previously been selected. If there is anyone who knows how to do this, please help. Or if this is just not do-able (for someone with my skills), that would be helpful to know as well. Many thanks if advance! -- Johanna |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding and unhiding rows
Instead add a combobox from VewToolBarsControlToolBox... and add the below
code to sheet module and Workbook as below 'Add the below in your Sheet module Dim varData As Variant Private Sub ComboBox1_Change() If varData < ComboBox1.Value Then UsedRange.EntireRow.Hidden = False Select Case ComboBox1.Value Case 2 Range("A51:A61").EntireRow.Hidden = True Case 3 Range("A32:A52").EntireRow.Hidden = True End Select End If End Sub Private Sub Worksheet_Activate() varData = ComboBox1.Value End Sub 'Add the below in Workbook ('ThisWorkbook') Open Private Sub Workbook_Open() Me.Sheets("Sheet3").ComboBox1.Add 1 Me.Sheets("Sheet3").ComboBox1.Add 2 Me.Sheets("Sheet3").ComboBox1.Add 3 End Sub -- Jacob "Johanna Gronlund" wrote: Thanks, that worked really well! I am very pleased! However, now that I have shared it with other people they would like to have it looking better visually. They would like to have a combobox where they can select the values from. Is this possible? I have managed to create a combobox which excel has named 'dropdown91' but was unable to modify the macro to take the values from that box. Many thanks again! -- Johanna G "Jacob Skaria" wrote: Select the sheet tab which you want to work with. Right click the sheet tab and click on 'View Code'. This will launch VBE. Paste the below code to the right blank portion. Get back to to workbook and try out. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$D$30" Then UsedRange.EntireRow.Hidden = False If Target.Value = 2 Then Range("A51:A61").EntireRow.Hidden = True If Target.Value = 3 Then Range("A32:A52").EntireRow.Hidden = True End If End Sub -- Jacob "Johanna Gronlund" wrote: Hello, I have been trying to work out how to do this by reading previous posts but this has defeated me. I have a sheet called 'inputs'. Based on a value on a cell D30, I want to hide/unhide rows. The possible values for D30 are 1, 2 or 3. If cell value is 1, I want to show all rows If cell value is 2, I want to hide rows 51-61 and show all others, including 32-52 if value 3 has previously been selected. If cell value is 3, I want to hide rows 32-52 and show all others, including 51-61 if value 2 has previously been selected. If there is anyone who knows how to do this, please help. Or if this is just not do-able (for someone with my skills), that would be helpful to know as well. Many thanks if advance! -- Johanna |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding and unhiding rows
Ops! Something went wrong. I got an error message: Run time error '424'
object not found. I thought that I followed the instructions but obviously something is missing. Thanks again. -- Johanna "Jacob Skaria" wrote: Instead add a combobox from VewToolBarsControlToolBox... and add the below code to sheet module and Workbook as below 'Add the below in your Sheet module Dim varData As Variant Private Sub ComboBox1_Change() If varData < ComboBox1.Value Then UsedRange.EntireRow.Hidden = False Select Case ComboBox1.Value Case 2 Range("A51:A61").EntireRow.Hidden = True Case 3 Range("A32:A52").EntireRow.Hidden = True End Select End If End Sub Private Sub Worksheet_Activate() varData = ComboBox1.Value End Sub 'Add the below in Workbook ('ThisWorkbook') Open Private Sub Workbook_Open() Me.Sheets("Sheet3").ComboBox1.Add 1 Me.Sheets("Sheet3").ComboBox1.Add 2 Me.Sheets("Sheet3").ComboBox1.Add 3 End Sub -- Jacob "Johanna Gronlund" wrote: Thanks, that worked really well! I am very pleased! However, now that I have shared it with other people they would like to have it looking better visually. They would like to have a combobox where they can select the values from. Is this possible? I have managed to create a combobox which excel has named 'dropdown91' but was unable to modify the macro to take the values from that box. Many thanks again! -- Johanna G "Jacob Skaria" wrote: Select the sheet tab which you want to work with. Right click the sheet tab and click on 'View Code'. This will launch VBE. Paste the below code to the right blank portion. Get back to to workbook and try out. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$D$30" Then UsedRange.EntireRow.Hidden = False If Target.Value = 2 Then Range("A51:A61").EntireRow.Hidden = True If Target.Value = 3 Then Range("A32:A52").EntireRow.Hidden = True End If End Sub -- Jacob "Johanna Gronlund" wrote: Hello, I have been trying to work out how to do this by reading previous posts but this has defeated me. I have a sheet called 'inputs'. Based on a value on a cell D30, I want to hide/unhide rows. The possible values for D30 are 1, 2 or 3. If cell value is 1, I want to show all rows If cell value is 2, I want to hide rows 51-61 and show all others, including 32-52 if value 3 has previously been selected. If cell value is 3, I want to hide rows 32-52 and show all others, including 51-61 if value 2 has previously been selected. If there is anyone who knows how to do this, please help. Or if this is just not do-able (for someone with my skills), that would be helpful to know as well. Many thanks if advance! -- Johanna |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding and unhiding rows
---Check out the combo box name ...I assumed that as ComboBox1
---From workbook press Alt+F11 to launch VBE (Visual Basic Editor). From the left treeview search for the workbook name and click on + to expand it. Within that you should see the following VBAProject(Your_Filename) Microsoft Excel Objects Sheet1(Sheet1) Sheet2(Sheet2) Sheet3(Sheet3) This Workbook Double click 'This WorkBook' and paste the below code to the right code pane. Private Sub Workbook_Open() Me.Sheets("Sheet3").ComboBox1.Add 1 Me.Sheets("Sheet3").ComboBox1.Add 2 Me.Sheets("Sheet3").ComboBox1.Add 3 End Sub Double click on the Sheet where you have the combox control and place the below code (2 Subs) Dim varData As Variant Private Sub ComboBox1_Change() If varData < ComboBox1.Value Then UsedRange.EntireRow.Hidden = False Select Case ComboBox1.Value Case 2 Range("A51:A61").EntireRow.Hidden = True Case 3 Range("A32:A52").EntireRow.Hidden = True End Select End If End Sub Private Sub Worksheet_Activate() varData = ComboBox1.Value End Sub -- Jacob "Johanna Gronlund" wrote: Ops! Something went wrong. I got an error message: Run time error '424' object not found. I thought that I followed the instructions but obviously something is missing. Thanks again. -- Johanna "Jacob Skaria" wrote: Instead add a combobox from VewToolBarsControlToolBox... and add the below code to sheet module and Workbook as below 'Add the below in your Sheet module Dim varData As Variant Private Sub ComboBox1_Change() If varData < ComboBox1.Value Then UsedRange.EntireRow.Hidden = False Select Case ComboBox1.Value Case 2 Range("A51:A61").EntireRow.Hidden = True Case 3 Range("A32:A52").EntireRow.Hidden = True End Select End If End Sub Private Sub Worksheet_Activate() varData = ComboBox1.Value End Sub 'Add the below in Workbook ('ThisWorkbook') Open Private Sub Workbook_Open() Me.Sheets("Sheet3").ComboBox1.Add 1 Me.Sheets("Sheet3").ComboBox1.Add 2 Me.Sheets("Sheet3").ComboBox1.Add 3 End Sub -- Jacob "Johanna Gronlund" wrote: Thanks, that worked really well! I am very pleased! However, now that I have shared it with other people they would like to have it looking better visually. They would like to have a combobox where they can select the values from. Is this possible? I have managed to create a combobox which excel has named 'dropdown91' but was unable to modify the macro to take the values from that box. Many thanks again! -- Johanna G "Jacob Skaria" wrote: Select the sheet tab which you want to work with. Right click the sheet tab and click on 'View Code'. This will launch VBE. Paste the below code to the right blank portion. Get back to to workbook and try out. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$D$30" Then UsedRange.EntireRow.Hidden = False If Target.Value = 2 Then Range("A51:A61").EntireRow.Hidden = True If Target.Value = 3 Then Range("A32:A52").EntireRow.Hidden = True End If End Sub -- Jacob "Johanna Gronlund" wrote: Hello, I have been trying to work out how to do this by reading previous posts but this has defeated me. I have a sheet called 'inputs'. Based on a value on a cell D30, I want to hide/unhide rows. The possible values for D30 are 1, 2 or 3. If cell value is 1, I want to show all rows If cell value is 2, I want to hide rows 51-61 and show all others, including 32-52 if value 3 has previously been selected. If cell value is 3, I want to hide rows 32-52 and show all others, including 51-61 if value 2 has previously been selected. If there is anyone who knows how to do this, please help. Or if this is just not do-able (for someone with my skills), that would be helpful to know as well. Many thanks if advance! -- Johanna |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding and unhiding rows
Thanks for your continuing assistance.
I have followed your instructions step by step and the same message persists. I re-named the combo box to ComboBox1 as it was easier than changing the code so I don't think this is problem. In the code that goes into ThisWorkbook, there are references to 'Sheet3'. Do I need to change that? The sheet where I have my combobox (and where I have pasted the two other subs) is called Sheet3 (Inputs) in the VBE view. If you have any further suggestions of what I might want to try, they would be gratefully received! -- Johanna "Jacob Skaria" wrote: ---Check out the combo box name ...I assumed that as ComboBox1 ---From workbook press Alt+F11 to launch VBE (Visual Basic Editor). From the left treeview search for the workbook name and click on + to expand it. Within that you should see the following VBAProject(Your_Filename) Microsoft Excel Objects Sheet1(Sheet1) Sheet2(Sheet2) Sheet3(Sheet3) This Workbook Double click 'This WorkBook' and paste the below code to the right code pane. Private Sub Workbook_Open() Me.Sheets("Sheet3").ComboBox1.Add 1 Me.Sheets("Sheet3").ComboBox1.Add 2 Me.Sheets("Sheet3").ComboBox1.Add 3 End Sub Double click on the Sheet where you have the combox control and place the below code (2 Subs) Dim varData As Variant Private Sub ComboBox1_Change() If varData < ComboBox1.Value Then UsedRange.EntireRow.Hidden = False Select Case ComboBox1.Value Case 2 Range("A51:A61").EntireRow.Hidden = True Case 3 Range("A32:A52").EntireRow.Hidden = True End Select End If End Sub Private Sub Worksheet_Activate() varData = ComboBox1.Value End Sub -- Jacob "Johanna Gronlund" wrote: Ops! Something went wrong. I got an error message: Run time error '424' object not found. I thought that I followed the instructions but obviously something is missing. Thanks again. -- Johanna "Jacob Skaria" wrote: Instead add a combobox from VewToolBarsControlToolBox... and add the below code to sheet module and Workbook as below 'Add the below in your Sheet module Dim varData As Variant Private Sub ComboBox1_Change() If varData < ComboBox1.Value Then UsedRange.EntireRow.Hidden = False Select Case ComboBox1.Value Case 2 Range("A51:A61").EntireRow.Hidden = True Case 3 Range("A32:A52").EntireRow.Hidden = True End Select End If End Sub Private Sub Worksheet_Activate() varData = ComboBox1.Value End Sub 'Add the below in Workbook ('ThisWorkbook') Open Private Sub Workbook_Open() Me.Sheets("Sheet3").ComboBox1.Add 1 Me.Sheets("Sheet3").ComboBox1.Add 2 Me.Sheets("Sheet3").ComboBox1.Add 3 End Sub -- Jacob "Johanna Gronlund" wrote: Thanks, that worked really well! I am very pleased! However, now that I have shared it with other people they would like to have it looking better visually. They would like to have a combobox where they can select the values from. Is this possible? I have managed to create a combobox which excel has named 'dropdown91' but was unable to modify the macro to take the values from that box. Many thanks again! -- Johanna G "Jacob Skaria" wrote: Select the sheet tab which you want to work with. Right click the sheet tab and click on 'View Code'. This will launch VBE. Paste the below code to the right blank portion. Get back to to workbook and try out. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$D$30" Then UsedRange.EntireRow.Hidden = False If Target.Value = 2 Then Range("A51:A61").EntireRow.Hidden = True If Target.Value = 3 Then Range("A32:A52").EntireRow.Hidden = True End If End Sub -- Jacob "Johanna Gronlund" wrote: Hello, I have been trying to work out how to do this by reading previous posts but this has defeated me. I have a sheet called 'inputs'. Based on a value on a cell D30, I want to hide/unhide rows. The possible values for D30 are 1, 2 or 3. If cell value is 1, I want to show all rows If cell value is 2, I want to hide rows 51-61 and show all others, including 32-52 if value 3 has previously been selected. If cell value is 3, I want to hide rows 32-52 and show all others, including 51-61 if value 2 has previously been selected. If there is anyone who knows how to do this, please help. Or if this is just not do-able (for someone with my skills), that would be helpful to know as well. Many thanks if advance! -- Johanna |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hiding and unhiding rows
You need to change that to
Private Sub Workbook_Open() Me.Sheets("Inputs").ComboBox1.Add 1 Me.Sheets("Inputs").ComboBox1.Add 2 Me.Sheets("Inputs").ComboBox1.Add 3 End Sub -- Jacob "Johanna Gronlund" wrote: Thanks for your continuing assistance. I have followed your instructions step by step and the same message persists. I re-named the combo box to ComboBox1 as it was easier than changing the code so I don't think this is problem. In the code that goes into ThisWorkbook, there are references to 'Sheet3'. Do I need to change that? The sheet where I have my combobox (and where I have pasted the two other subs) is called Sheet3 (Inputs) in the VBE view. If you have any further suggestions of what I might want to try, they would be gratefully received! -- Johanna "Jacob Skaria" wrote: ---Check out the combo box name ...I assumed that as ComboBox1 ---From workbook press Alt+F11 to launch VBE (Visual Basic Editor). From the left treeview search for the workbook name and click on + to expand it. Within that you should see the following VBAProject(Your_Filename) Microsoft Excel Objects Sheet1(Sheet1) Sheet2(Sheet2) Sheet3(Sheet3) This Workbook Double click 'This WorkBook' and paste the below code to the right code pane. Private Sub Workbook_Open() Me.Sheets("Sheet3").ComboBox1.Add 1 Me.Sheets("Sheet3").ComboBox1.Add 2 Me.Sheets("Sheet3").ComboBox1.Add 3 End Sub Double click on the Sheet where you have the combox control and place the below code (2 Subs) Dim varData As Variant Private Sub ComboBox1_Change() If varData < ComboBox1.Value Then UsedRange.EntireRow.Hidden = False Select Case ComboBox1.Value Case 2 Range("A51:A61").EntireRow.Hidden = True Case 3 Range("A32:A52").EntireRow.Hidden = True End Select End If End Sub Private Sub Worksheet_Activate() varData = ComboBox1.Value End Sub -- Jacob "Johanna Gronlund" wrote: Ops! Something went wrong. I got an error message: Run time error '424' object not found. I thought that I followed the instructions but obviously something is missing. Thanks again. -- Johanna "Jacob Skaria" wrote: Instead add a combobox from VewToolBarsControlToolBox... and add the below code to sheet module and Workbook as below 'Add the below in your Sheet module Dim varData As Variant Private Sub ComboBox1_Change() If varData < ComboBox1.Value Then UsedRange.EntireRow.Hidden = False Select Case ComboBox1.Value Case 2 Range("A51:A61").EntireRow.Hidden = True Case 3 Range("A32:A52").EntireRow.Hidden = True End Select End If End Sub Private Sub Worksheet_Activate() varData = ComboBox1.Value End Sub 'Add the below in Workbook ('ThisWorkbook') Open Private Sub Workbook_Open() Me.Sheets("Sheet3").ComboBox1.Add 1 Me.Sheets("Sheet3").ComboBox1.Add 2 Me.Sheets("Sheet3").ComboBox1.Add 3 End Sub -- Jacob "Johanna Gronlund" wrote: Thanks, that worked really well! I am very pleased! However, now that I have shared it with other people they would like to have it looking better visually. They would like to have a combobox where they can select the values from. Is this possible? I have managed to create a combobox which excel has named 'dropdown91' but was unable to modify the macro to take the values from that box. Many thanks again! -- Johanna G "Jacob Skaria" wrote: Select the sheet tab which you want to work with. Right click the sheet tab and click on 'View Code'. This will launch VBE. Paste the below code to the right blank portion. Get back to to workbook and try out. Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$D$30" Then UsedRange.EntireRow.Hidden = False If Target.Value = 2 Then Range("A51:A61").EntireRow.Hidden = True If Target.Value = 3 Then Range("A32:A52").EntireRow.Hidden = True End If End Sub -- Jacob "Johanna Gronlund" wrote: Hello, I have been trying to work out how to do this by reading previous posts but this has defeated me. I have a sheet called 'inputs'. Based on a value on a cell D30, I want to hide/unhide rows. The possible values for D30 are 1, 2 or 3. If cell value is 1, I want to show all rows If cell value is 2, I want to hide rows 51-61 and show all others, including 32-52 if value 3 has previously been selected. If cell value is 3, I want to hide rows 32-52 and show all others, including 51-61 if value 2 has previously been selected. If there is anyone who knows how to do this, please help. Or if this is just not do-able (for someone with my skills), that would be helpful to know as well. Many thanks if advance! -- Johanna |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hiding/unhiding rows | Excel Worksheet Functions | |||
hidden rows but not from hiding/unhiding? | Excel Discussion (Misc queries) | |||
unhiding and hiding rows | Excel Discussion (Misc queries) | |||
Hiding/unhiding rows | Excel Discussion (Misc queries) | |||
Hiding/Unhiding rows | Excel Programming |