Johanna Gronlund
2/2/2010 1:23:00 PM
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 Vew>ToolBars>ControlToolBox... 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