Help!

hide or unhide rows with button

 
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Excel General (archive) RSS
Next:  Number formating  
Author Message
Bea
External


Since: Nov 29, 2005
Posts: 9



PostPosted: Tue Nov 29, 2005 4:08 pm    Post subject: hide or unhide rows with button
Archived from groups: microsoft>public>excel>misc (more info?)

I want to group rows together and have a quick expand/collapse button on the
sheet itself. I see the group/outline function but do not like the display of
the expand/collapse in the left side of the rows.
Back to top
Dave Peterson
External


Since: Jul 08, 2005
Posts: 16264



PostPosted: Tue Nov 29, 2005 8:01 pm    Post subject: Re: hide or unhide rows with button [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

You could put a button from the control toolbox toolbar on that worksheet.
Double click on that button and you'll see where the code goes.

Paste this in that window and end up with something that looks like this.

Option Explicit
Private Sub CommandButton1_Click()
Dim myRng As Range

Set myRng = Me.Range("a3:a5,a7:a19,a22:a33")

myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)

End Sub

Adjust the range you want to hide/show

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Bea wrote:
>
> I want to group rows together and have a quick expand/collapse button on the
> sheet itself. I see the group/outline function but do not like the display of
> the expand/collapse in the left side of the rows.

--

Dave Peterson
Back to top
Bea
External


Since: Nov 29, 2005
Posts: 9



PostPosted: Tue Nov 29, 2005 8:01 pm    Post subject: Re: hide or unhide rows with button [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

This was perfect Dave! Thank you so much!

"Dave Peterson" wrote:

> You could put a button from the control toolbox toolbar on that worksheet.
> Double click on that button and you'll see where the code goes.
>
> Paste this in that window and end up with something that looks like this.
>
> Option Explicit
> Private Sub CommandButton1_Click()
> Dim myRng As Range
>
> Set myRng = Me.Range("a3:a5,a7:a19,a22:a33")
>
> myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)
>
> End Sub
>
> Adjust the range you want to hide/show
>
> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
>
> Bea wrote:
> >
> > I want to group rows together and have a quick expand/collapse button on the
> > sheet itself. I see the group/outline function but do not like the display of
> > the expand/collapse in the left side of the rows.
>
> --
>
> Dave Peterson
>
Back to top
Gord Dibben
External


Since: Feb 23, 2004
Posts: 9431



PostPosted: Tue Nov 29, 2005 8:01 pm    Post subject: Re: hide or unhide rows with button [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Bea

There is also Views>Custom Views if you don't want to go the macro route.


Gord Dibben Excel MVP

On Tue, 29 Nov 2005 13:18:06 -0800, Bea wrote:

>This was perfect Dave! Thank you so much!
>
>"Dave Peterson" wrote:
>
>> You could put a button from the control toolbox toolbar on that worksheet.
>> Double click on that button and you'll see where the code goes.
>>
>> Paste this in that window and end up with something that looks like this.
>>
>> Option Explicit
>> Private Sub CommandButton1_Click()
>> Dim myRng As Range
>>
>> Set myRng = Me.Range("a3:a5,a7:a19,a22:a33")
>>
>> myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)
>>
>> End Sub
>>
>> Adjust the range you want to hide/show
>>
>> If you're new to macros, you may want to read David McRitchie's intro at:
>> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>>
>>
>> Bea wrote:
>> >
>> > I want to group rows together and have a quick expand/collapse button on the
>> > sheet itself. I see the group/outline function but do not like the display of
>> > the expand/collapse in the left side of the rows.
>>
>> --
>>
>> Dave Peterson
>>
Back to top
Bea
External


Since: Nov 29, 2005
Posts: 9



PostPosted: Tue Nov 29, 2005 8:01 pm    Post subject: Re: hide or unhide rows with button [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Gord - Thanks for your help. Can you use a button with the Custom Views? I
could not figure that one out.

"Gord Dibben" wrote:

> Bea
>
> There is also Views>Custom Views if you don't want to go the macro route.
>
>
> Gord Dibben Excel MVP
>
> On Tue, 29 Nov 2005 13:18:06 -0800, Bea wrote:
>
> >This was perfect Dave! Thank you so much!
> >
> >"Dave Peterson" wrote:
> >
> >> You could put a button from the control toolbox toolbar on that worksheet.
> >> Double click on that button and you'll see where the code goes.
> >>
> >> Paste this in that window and end up with something that looks like this.
> >>
> >> Option Explicit
> >> Private Sub CommandButton1_Click()
> >> Dim myRng As Range
> >>
> >> Set myRng = Me.Range("a3:a5,a7:a19,a22:a33")
> >>
> >> myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)
> >>
> >> End Sub
> >>
> >> Adjust the range you want to hide/show
> >>
> >> If you're new to macros, you may want to read David McRitchie's intro at:
> >> http://www.mvps.org/dmcritchie/excel/getstarted.htm
> >>
> >>
> >> Bea wrote:
> >> >
> >> > I want to group rows together and have a quick expand/collapse button on the
> >> > sheet itself. I see the group/outline function but do not like the display of
> >> > the expand/collapse in the left side of the rows.
> >>
> >> --
> >>
> >> Dave Peterson
> >>
>
>
Back to top
Gord Dibben
External


Since: Feb 23, 2004
Posts: 9431



PostPosted: Tue Nov 29, 2005 8:01 pm    Post subject: Re: hide or unhide rows with button [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Bea

Not really.

You can go to Tools>Customize>Commands>View.

Drag the Custom Views drop-down onto your Toolbar.

You can choose which view from the drop-down.

Possible to have many custom views, each with a distinct name like "hidden"
"unhidden" "group1" etc.


Gord

On Tue, 29 Nov 2005 14:01:02 -0800, Bea wrote:

>Gord - Thanks for your help. Can you use a button with the Custom Views? I
>could not figure that one out.
>
>"Gord Dibben" wrote:
>
>> Bea
>>
>> There is also Views>Custom Views if you don't want to go the macro route.
>>
>>
>> Gord Dibben Excel MVP
>>
>> On Tue, 29 Nov 2005 13:18:06 -0800, Bea wrote:
>>
>> >This was perfect Dave! Thank you so much!
>> >
>> >"Dave Peterson" wrote:
>> >
>> >> You could put a button from the control toolbox toolbar on that worksheet.
>> >> Double click on that button and you'll see where the code goes.
>> >>
>> >> Paste this in that window and end up with something that looks like this.
>> >>
>> >> Option Explicit
>> >> Private Sub CommandButton1_Click()
>> >> Dim myRng As Range
>> >>
>> >> Set myRng = Me.Range("a3:a5,a7:a19,a22:a33")
>> >>
>> >> myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)
>> >>
>> >> End Sub
>> >>
>> >> Adjust the range you want to hide/show
>> >>
>> >> If you're new to macros, you may want to read David McRitchie's intro at:
>> >> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>> >>
>> >>
>> >> Bea wrote:
>> >> >
>> >> > I want to group rows together and have a quick expand/collapse button on the
>> >> > sheet itself. I see the group/outline function but do not like the display of
>> >> > the expand/collapse in the left side of the rows.
>> >>
>> >> --
>> >>
>> >> Dave Peterson
>> >>
>>
>>
Back to top
Dave Peterson
External


Since: Jul 08, 2005
Posts: 16264



PostPosted: Tue Nov 29, 2005 9:55 pm    Post subject: Re: hide or unhide rows with button [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Sometimes you can just record a macro while you do it manually.

I got this when I showed the view named Test1.

Option Explicit
Sub Macro1()
ActiveWorkbook.CustomViews("test1").Show
End Sub


Behind a commandbutton from the control toolbox toolbar:

Option Explicit
Private Sub CommandButton1_Click()
Me.Parent.CustomViews("test1").Show
End Sub

Me is the worksheet that owns the code (and button). Me.Parent is the workbook.

Bea wrote:
>
> Gord - Thanks for your help. Can you use a button with the Custom Views? I
> could not figure that one out.
>
> "Gord Dibben" wrote:
>
> > Bea
> >
> > There is also Views>Custom Views if you don't want to go the macro route.
> >
> >
> > Gord Dibben Excel MVP
> >
> > On Tue, 29 Nov 2005 13:18:06 -0800, Bea wrote:
> >
> > >This was perfect Dave! Thank you so much!
> > >
> > >"Dave Peterson" wrote:
> > >
> > >> You could put a button from the control toolbox toolbar on that worksheet.
> > >> Double click on that button and you'll see where the code goes.
> > >>
> > >> Paste this in that window and end up with something that looks like this.
> > >>
> > >> Option Explicit
> > >> Private Sub CommandButton1_Click()
> > >> Dim myRng As Range
> > >>
> > >> Set myRng = Me.Range("a3:a5,a7:a19,a22:a33")
> > >>
> > >> myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)
> > >>
> > >> End Sub
> > >>
> > >> Adjust the range you want to hide/show
> > >>
> > >> If you're new to macros, you may want to read David McRitchie's intro at:
> > >> http://www.mvps.org/dmcritchie/excel/getstarted.htm
> > >>
> > >>
> > >> Bea wrote:
> > >> >
> > >> > I want to group rows together and have a quick expand/collapse button on the
> > >> > sheet itself. I see the group/outline function but do not like the display of
> > >> > the expand/collapse in the left side of the rows.
> > >>
> > >> --
> > >>
> > >> Dave Peterson
> > >>
> >
> >

--

Dave Peterson
Back to top
Gord Dibben
External


Since: Feb 23, 2004
Posts: 9431



PostPosted: Tue Nov 29, 2005 9:55 pm    Post subject: Re: hide or unhide rows with button [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Dave

My custom views suggestion was only if Bea didn't want to use macros.

If using macros, better off to go with the toggle macro you first posted.


Gord Dibben Excel MVP

On Tue, 29 Nov 2005 16:55:36 -0600, Dave Peterson
wrote:

>Sometimes you can just record a macro while you do it manually.
>
>I got this when I showed the view named Test1.
>
>Option Explicit
>Sub Macro1()
> ActiveWorkbook.CustomViews("test1").Show
>End Sub
>
>
>Behind a commandbutton from the control toolbox toolbar:
>
>Option Explicit
>Private Sub CommandButton1_Click()
> Me.Parent.CustomViews("test1").Show
>End Sub
>
>Me is the worksheet that owns the code (and button). Me.Parent is the workbook.
>
>Bea wrote:
>>
>> Gord - Thanks for your help. Can you use a button with the Custom Views? I
>> could not figure that one out.
>>
>> "Gord Dibben" wrote:
>>
>> > Bea
>> >
>> > There is also Views>Custom Views if you don't want to go the macro route.
>> >
>> >
>> > Gord Dibben Excel MVP
>> >
>> > On Tue, 29 Nov 2005 13:18:06 -0800, Bea wrote:
>> >
>> > >This was perfect Dave! Thank you so much!
>> > >
>> > >"Dave Peterson" wrote:
>> > >
>> > >> You could put a button from the control toolbox toolbar on that worksheet.
>> > >> Double click on that button and you'll see where the code goes.
>> > >>
>> > >> Paste this in that window and end up with something that looks like this.
>> > >>
>> > >> Option Explicit
>> > >> Private Sub CommandButton1_Click()
>> > >> Dim myRng As Range
>> > >>
>> > >> Set myRng = Me.Range("a3:a5,a7:a19,a22:a33")
>> > >>
>> > >> myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)
>> > >>
>> > >> End Sub
>> > >>
>> > >> Adjust the range you want to hide/show
>> > >>
>> > >> If you're new to macros, you may want to read David McRitchie's intro at:
>> > >> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>> > >>
>> > >>
>> > >> Bea wrote:
>> > >> >
>> > >> > I want to group rows together and have a quick expand/collapse button on the
>> > >> > sheet itself. I see the group/outline function but do not like the display of
>> > >> > the expand/collapse in the left side of the rows.
>> > >>
>> > >> --
>> > >>
>> > >> Dave Peterson
>> > >>
>> >
>> >
Back to top
Dave Peterson
External


Since: Jul 08, 2005
Posts: 16264



PostPosted: Tue Nov 29, 2005 10:24 pm    Post subject: Re: hide or unhide rows with button [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Ah, but your suggestion also works if Bea wants to set up the workbook the way
Bea wants and then use a button to show that view.

It might be a combination solution that works best for Bea.

Gord Dibben wrote:
>
> Dave
>
> My custom views suggestion was only if Bea didn't want to use macros.
>
> If using macros, better off to go with the toggle macro you first posted.
>
> Gord Dibben Excel MVP
>
> On Tue, 29 Nov 2005 16:55:36 -0600, Dave Peterson
> wrote:
>
> >Sometimes you can just record a macro while you do it manually.
> >
> >I got this when I showed the view named Test1.
> >
> >Option Explicit
> >Sub Macro1()
> > ActiveWorkbook.CustomViews("test1").Show
> >End Sub
> >
> >
> >Behind a commandbutton from the control toolbox toolbar:
> >
> >Option Explicit
> >Private Sub CommandButton1_Click()
> > Me.Parent.CustomViews("test1").Show
> >End Sub
> >
> >Me is the worksheet that owns the code (and button). Me.Parent is the workbook.
> >
> >Bea wrote:
> >>
> >> Gord - Thanks for your help. Can you use a button with the Custom Views? I
> >> could not figure that one out.
> >>
> >> "Gord Dibben" wrote:
> >>
> >> > Bea
> >> >
> >> > There is also Views>Custom Views if you don't want to go the macro route.
> >> >
> >> >
> >> > Gord Dibben Excel MVP
> >> >
> >> > On Tue, 29 Nov 2005 13:18:06 -0800, Bea wrote:
> >> >
> >> > >This was perfect Dave! Thank you so much!
> >> > >
> >> > >"Dave Peterson" wrote:
> >> > >
> >> > >> You could put a button from the control toolbox toolbar on that worksheet.
> >> > >> Double click on that button and you'll see where the code goes.
> >> > >>
> >> > >> Paste this in that window and end up with something that looks like this.
> >> > >>
> >> > >> Option Explicit
> >> > >> Private Sub CommandButton1_Click()
> >> > >> Dim myRng As Range
> >> > >>
> >> > >> Set myRng = Me.Range("a3:a5,a7:a19,a22:a33")
> >> > >>
> >> > >> myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)
> >> > >>
> >> > >> End Sub
> >> > >>
> >> > >> Adjust the range you want to hide/show
> >> > >>
> >> > >> If you're new to macros, you may want to read David McRitchie's intro at:
> >> > >> http://www.mvps.org/dmcritchie/excel/getstarted.htm
> >> > >>
> >> > >>
> >> > >> Bea wrote:
> >> > >> >
> >> > >> > I want to group rows together and have a quick expand/collapse button on the
> >> > >> > sheet itself. I see the group/outline function but do not like the display of
> >> > >> > the expand/collapse in the left side of the rows.
> >> > >>
> >> > >> --
> >> > >>
> >> > >> Dave Peterson
> >> > >>
> >> >
> >> >

--

Dave Peterson
Back to top
Gord Dibben
External


Since: Feb 23, 2004
Posts: 9431



PostPosted: Tue Nov 29, 2005 10:24 pm    Post subject: Re: hide or unhide rows with button [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

I find it just as easy to drag the Custom Views drop-down to a Toolbar and
select a view from there.

But, that's me, not Bea.


Gord


On Tue, 29 Nov 2005 17:24:03 -0600, Dave Peterson
wrote:

>Ah, but your suggestion also works if Bea wants to set up the workbook the way
>Bea wants and then use a button to show that view.
>
>It might be a combination solution that works best for Bea.
>
>Gord Dibben wrote:
>>
>> Dave
>>
>> My custom views suggestion was only if Bea didn't want to use macros.
>>
>> If using macros, better off to go with the toggle macro you first posted.
>>
>> Gord Dibben Excel MVP
>>
>> On Tue, 29 Nov 2005 16:55:36 -0600, Dave Peterson
>> wrote:
>>
>> >Sometimes you can just record a macro while you do it manually.
>> >
>> >I got this when I showed the view named Test1.
>> >
>> >Option Explicit
>> >Sub Macro1()
>> > ActiveWorkbook.CustomViews("test1").Show
>> >End Sub
>> >
>> >
>> >Behind a commandbutton from the control toolbox toolbar:
>> >
>> >Option Explicit
>> >Private Sub CommandButton1_Click()
>> > Me.Parent.CustomViews("test1").Show
>> >End Sub
>> >
>> >Me is the worksheet that owns the code (and button). Me.Parent is the workbook.
>> >
>> >Bea wrote:
>> >>
>> >> Gord - Thanks for your help. Can you use a button with the Custom Views? I
>> >> could not figure that one out.
>> >>
>> >> "Gord Dibben" wrote:
>> >>
>> >> > Bea
>> >> >
>> >> > There is also Views>Custom Views if you don't want to go the macro route.
>> >> >
>> >> >
>> >> > Gord Dibben Excel MVP
>> >> >
>> >> > On Tue, 29 Nov 2005 13:18:06 -0800, Bea wrote:
>> >> >
>> >> > >This was perfect Dave! Thank you so much!
>> >> > >
>> >> > >"Dave Peterson" wrote:
>> >> > >
>> >> > >> You could put a button from the control toolbox toolbar on that worksheet.
>> >> > >> Double click on that button and you'll see where the code goes.
>> >> > >>
>> >> > >> Paste this in that window and end up with something that looks like this.
>> >> > >>
>> >> > >> Option Explicit
>> >> > >> Private Sub CommandButton1_Click()
>> >> > >> Dim myRng As Range
>> >> > >>
>> >> > >> Set myRng = Me.Range("a3:a5,a7:a19,a22:a33")
>> >> > >>
>> >> > >> myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)
>> >> > >>
>> >> > >> End Sub
>> >> > >>
>> >> > >> Adjust the range you want to hide/show
>> >> > >>
>> >> > >> If you're new to macros, you may want to read David McRitchie's intro at:
>> >> > >> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>> >> > >>
>> >> > >>
>> >> > >> Bea wrote:
>> >> > >> >
>> >> > >> > I want to group rows together and have a quick expand/collapse button on the
>> >> > >> > sheet itself. I see the group/outline function but do not like the display of
>> >> > >> > the expand/collapse in the left side of the rows.
>> >> > >>
>> >> > >> --
>> >> > >>
>> >> > >> Dave Peterson
>> >> > >>
>> >> >
>> >> >
Back to top
Dave Peterson
External


Since: Jul 08, 2005
Posts: 16264



PostPosted: Tue Nov 29, 2005 10:45 pm    Post subject: Re: hide or unhide rows with button [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

I agree with you--but if Bea wanted a button...

But you are a poet!

> But, that's me, not Bea.

Gord Dibben wrote:
>
> I find it just as easy to drag the Custom Views drop-down to a Toolbar and
> select a view from there.
>
> But, that's me, not Bea.
>
> Gord
>
> On Tue, 29 Nov 2005 17:24:03 -0600, Dave Peterson
> wrote:
>
> >Ah, but your suggestion also works if Bea wants to set up the workbook the way
> >Bea wants and then use a button to show that view.
> >
> >It might be a combination solution that works best for Bea.
> >
> >Gord Dibben wrote:
> >>
> >> Dave
> >>
> >> My custom views suggestion was only if Bea didn't want to use macros.
> >>
> >> If using macros, better off to go with the toggle macro you first posted.
> >>
> >> Gord Dibben Excel MVP
> >>
> >> On Tue, 29 Nov 2005 16:55:36 -0600, Dave Peterson
> >> wrote:
> >>
> >> >Sometimes you can just record a macro while you do it manually.
> >> >
> >> >I got this when I showed the view named Test1.
> >> >
> >> >Option Explicit
> >> >Sub Macro1()
> >> > ActiveWorkbook.CustomViews("test1").Show
> >> >End Sub
> >> >
> >> >
> >> >Behind a commandbutton from the control toolbox toolbar:
> >> >
> >> >Option Explicit
> >> >Private Sub CommandButton1_Click()
> >> > Me.Parent.CustomViews("test1").Show
> >> >End Sub
> >> >
> >> >Me is the worksheet that owns the code (and button). Me.Parent is the workbook.
> >> >
> >> >Bea wrote:
> >> >>
> >> >> Gord - Thanks for your help. Can you use a button with the Custom Views? I
> >> >> could not figure that one out.
> >> >>
> >> >> "Gord Dibben" wrote:
> >> >>
> >> >> > Bea
> >> >> >
> >> >> > There is also Views>Custom Views if you don't want to go the macro route.
> >> >> >
> >> >> >
> >> >> > Gord Dibben Excel MVP
> >> >> >
> >> >> > On Tue, 29 Nov 2005 13:18:06 -0800, Bea wrote:
> >> >> >
> >> >> > >This was perfect Dave! Thank you so much!
> >> >> > >
> >> >> > >"Dave Peterson" wrote:
> >> >> > >
> >> >> > >> You could put a button from the control toolbox toolbar on that worksheet.
> >> >> > >> Double click on that button and you'll see where the code goes.
> >> >> > >>
> >> >> > >> Paste this in that window and end up with something that looks like this.
> >> >> > >>
> >> >> > >> Option Explicit
> >> >> > >> Private Sub CommandButton1_Click()
> >> >> > >> Dim myRng As Range
> >> >> > >>
> >> >> > >> Set myRng = Me.Range("a3:a5,a7:a19,a22:a33")
> >> >> > >>
> >> >> > >> myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)
> >> >> > >>
> >> >> > >> End Sub
> >> >> > >>
> >> >> > >> Adjust the range you want to hide/show
> >> >> > >>
> >> >> > >> If you're new to macros, you may want to read David McRitchie's intro at:
> >> >> > >> http://www.mvps.org/dmcritchie/excel/getstarted.htm
> >> >> > >>
> >> >> > >>
> >> >> > >> Bea wrote:
> >> >> > >> >
> >> >> > >> > I want to group rows together and have a quick expand/collapse button on the
> >> >> > >> > sheet itself. I see the group/outline function but do not like the display of
> >> >> > >> > the expand/collapse in the left side of the rows.
> >> >> > >>
> >> >> > >> --
> >> >> > >>
> >> >> > >> Dave Peterson
> >> >> > >>
> >> >> >
> >> >> >

--

Dave Peterson
Back to top
Bea
External


Since: Nov 29, 2005
Posts: 9



PostPosted: Wed Nov 30, 2005 1:04 pm    Post subject: Re: hide or unhide rows with button [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Me - Bea - appreciates your help! I have learned a couple of new things! Smile

"Dave Peterson" wrote:

> I agree with you--but if Bea wanted a button...
>
> But you are a poet!
>
> > But, that's me, not Bea.
>
> Gord Dibben wrote:
> >
> > I find it just as easy to drag the Custom Views drop-down to a Toolbar and
> > select a view from there.
> >
> > But, that's me, not Bea.
> >
> > Gord
> >
> > On Tue, 29 Nov 2005 17:24:03 -0600, Dave Peterson
> > wrote:
> >
> > >Ah, but your suggestion also works if Bea wants to set up the workbook the way
> > >Bea wants and then use a button to show that view.
> > >
> > >It might be a combination solution that works best for Bea.
> > >
> > >Gord Dibben wrote:
> > >>
> > >> Dave
> > >>
> > >> My custom views suggestion was only if Bea didn't want to use macros.
> > >>
> > >> If using macros, better off to go with the toggle macro you first posted.
> > >>
> > >> Gord Dibben Excel MVP
> > >>
> > >> On Tue, 29 Nov 2005 16:55:36 -0600, Dave Peterson
> > >> wrote:
> > >>
> > >> >Sometimes you can just record a macro while you do it manually.
> > >> >
> > >> >I got this when I showed the view named Test1.
> > >> >
> > >> >Option Explicit
> > >> >Sub Macro1()
> > >> > ActiveWorkbook.CustomViews("test1").Show
> > >> >End Sub
> > >> >
> > >> >
> > >> >Behind a commandbutton from the control toolbox toolbar:
> > >> >
> > >> >Option Explicit
> > >> >Private Sub CommandButton1_Click()
> > >> > Me.Parent.CustomViews("test1").Show
> > >> >End Sub
> > >> >
> > >> >Me is the worksheet that owns the code (and button). Me.Parent is the workbook.
> > >> >
> > >> >Bea wrote:
> > >> >>
> > >> >> Gord - Thanks for your help. Can you use a button with the Custom Views? I
> > >> >> could not figure that one out.
> > >> >>
> > >> >> "Gord Dibben" wrote:
> > >> >>
> > >> >> > Bea
> > >> >> >
> > >> >> > There is also Views>Custom Views if you don't want to go the macro route.
> > >> >> >
> > >> >> >
> > >> >> > Gord Dibben Excel MVP
> > >> >> >
> > >> >> > On Tue, 29 Nov 2005 13:18:06 -0800, Bea wrote:
> > >> >> >
> > >> >> > >This was perfect Dave! Thank you so much!
> > >> >> > >
> > >> >> > >"Dave Peterson" wrote:
> > >> >> > >
> > >> >> > >> You could put a button from the control toolbox toolbar on that worksheet.
> > >> >> > >> Double click on that button and you'll see where the code goes.
> > >> >> > >>
> > >> >> > >> Paste this in that window and end up with something that looks like this.
> > >> >> > >>
> > >> >> > >> Option Explicit
> > >> >> > >> Private Sub CommandButton1_Click()
> > >> >> > >> Dim myRng As Range
> > >> >> > >>
> > >> >> > >> Set myRng = Me.Range("a3:a5,a7:a19,a22:a33")
> > >> >> > >>
> > >> >> > >> myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)
> > >> >> > >>
> > >> >> > >> End Sub
> > >> >> > >>
> > >> >> > >> Adjust the range you want to hide/show
> > >> >> > >>
> > >> >> > >> If you're new to macros, you may want to read David McRitchie's intro at:
> > >> >> > >> http://www.mvps.org/dmcritchie/excel/getstarted.htm
> > >> >> > >>
> > >> >> > >>
> > >> >> > >> Bea wrote:
> > >> >> > >> >
> > >> >> > >> > I want to group rows together and have a quick expand/collapse button on the
> > >> >> > >> > sheet itself. I see the group/outline function but do not like the display of
> > >> >> > >> > the expand/collapse in the left side of the rows.
> > >> >> > >>
> > >> >> > >> --
> > >> >> > >>
> > >> >> > >> Dave Peterson
> > >> >> > >>
> > >> >> >
> > >> >> >
>
> --
>
> Dave Peterson
>
Back to top
Johan
External


Since: Jan 05, 2007
Posts: 1



PostPosted: Fri Jan 05, 2007 10:40 am    Post subject: Re: hide or unhide rows with button [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Hi

I searched and found also this post helpful. I have an additional question
in this matter.

Is it possible to hide and unhide the rows smoothly as in a Powerpoint?

/Johan




"Bea" skrev:

> This was perfect Dave! Thank you so much!
>
> "Dave Peterson" wrote:
>
> > You could put a button from the control toolbox toolbar on that worksheet.
> > Double click on that button and you'll see where the code goes.
> >
> > Paste this in that window and end up with something that looks like this.
> >
> > Option Explicit
> > Private Sub CommandButton1_Click()
> > Dim myRng As Range
> >
> > Set myRng = Me.Range("a3:a5,a7:a19,a22:a33")
> >
> > myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)
> >
> > End Sub
> >
> > Adjust the range you want to hide/show
> >
> > If you're new to macros, you may want to read David McRitchie's intro at:
> > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> >
> >
> > Bea wrote:
> > >
> > > I want to group rows together and have a quick expand/collapse button on the
> > > sheet itself. I see the group/outline function but do not like the display of
> > > the expand/collapse in the left side of the rows.
> >
> > --
> >
> > Dave Peterson
> >
Back to top
Dave Peterson
External


Since: Jul 08, 2005
Posts: 16264



PostPosted: Fri Jan 05, 2007 2:30 pm    Post subject: Re: hide or unhide rows with button [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Maybe...

You can hide rows if you use Data|Subtotals, or Data|Outlining or even
Data|Filter|autofilter.

But it really depends...

Johan wrote:
>
> Hi
>
> I searched and found also this post helpful. I have an additional question
> in this matter.
>
> Is it possible to hide and unhide the rows smoothly as in a Powerpoint?
>
> /Johan
>
> "Bea" skrev:
>
> > This was perfect Dave! Thank you so much!
> >
> > "Dave Peterson" wrote:
> >
> > > You could put a button from the control toolbox toolbar on that worksheet.
> > > Double click on that button and you'll see where the code goes.
> > >
> > > Paste this in that window and end up with something that looks like this.
> > >
> > > Option Explicit
> > > Private Sub CommandButton1_Click()
> > > Dim myRng As Range
> > >
> > > Set myRng = Me.Range("a3:a5,a7:a19,a22:a33")
> > >
> > > myRng.EntireRow.Hidden = Not (myRng(1).EntireRow.Hidden)
> > >
> > > End Sub
> > >
> > > Adjust the range you want to hide/show
> > >
> > > If you're new to macros, you may want to read David McRitchie's intro at:
> > > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> > >
> > >
> > > Bea wrote:
> > > >
> > > > I want to group rows together and have a quick expand/collapse button on the
> > > > sheet itself. I see the group/outline function but do not like the display of
> > > > the expand/collapse in the left side of the rows.
> > >
> > > --
> > >
> > > Dave Peterson
> > >

--

Dave Peterson
Back to top
Display posts from previous:   
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Excel General (archive) All times are: Eastern Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum