Re: Macro to show/hide rows by New2Macros
New2Macros
Tue May 13 16:48:02 CDT 2008
I got this macro to work on typical rows, but the odd thing is that if a row
contains a combo box (which references a different sheet), it will give a
run-time error. It says "unable to set the hidden property of the range
class." I think this is what has been causing the problem from the start.
Any ideas?
"Rick Rothstein (MVP - VB)" wrote:
> I'm not sure what to tell you as I can't duplicate the problem here. Just in
> case something accidentally changed that you are not aware of, delete the
> existing Worksheet_Change event code and then copy/paste (don't type) the
> code I posted earlier into that code window. Did doing that fix the problem?
> If not, send your worksheet to me (remove the obvious stuff from my email
> address) and I'll see if it does the same thing you are seeing here on my
> system. I'll answer back to this thread for continuity sake.
>
> Rick
>
>
> "New2Macros" <New2Macros@discussions.microsoft.com> wrote in message
> news:52271BF6-F557-45A4-8D39-F102AE692AE8@microsoft.com...
> >I don't get it - no matter where I put a breakpoint, nothing changes. I
> >get
> > nothing highlighted.
> >
> > "Rick Rothstein (MVP - VB)" wrote:
> >
> >> I'm not sure why you are getting that message. Did you modify the code in
> >> any way? Is it highlighting a line of code when it displays the message?
> >> If
> >> so, which line. If not, put a breakpoint on the first line of my code
> >> (click
> >> the left hand border and a red dot will appear along with the line being
> >> highlighted in red indicating the breakpoint is set); then change the
> >> value
> >> in B1. You will be taken back to the code window and the line with the
> >> breakpoint will be highlighted in yellow. Pressing F8 will execute the
> >> line
> >> of code and advance the yellow highlight to the next executable line.
> >> Keep
> >> pressing F8 until the message appears. Which line was the last one
> >> highlighted in yellow before the error appeared?
> >>
> >> Rick
> >>
> >>
> >> "New2Macros" <New2Macros@discussions.microsoft.com> wrote in message
> >> news:0178A450-5613-49B6-9ADA-E7F205D24FA6@microsoft.com...
> >> > This seemed to work right away, but now I'm getting an "argument not
> >> > optional" message when I put a number. What could this mean?
> >> >
> >> >
> >> > "Rick Rothstein (MVP - VB)" wrote:
> >> >
> >> >> Okay, the 'start at Row 3' was a mistype then. By the way, you
> >> >> originally
> >> >> said B1 would have the number in it, but now are saying A1. No
> >> >> problem,
> >> >> I'll
> >> >> use A1 in my example. Give this a try and see if it does what you
> >> >> want.
> >> >> Right-click the tab on the worksheet where you want to have this
> >> >> functionality and select View Code from the popup menu that appears.
> >> >> This
> >> >> will take you into the VBA editor and present an already opened code
> >> >> window
> >> >> to you. Copy/Paste the following into that code window...
> >> >>
> >> >> Private Sub Worksheet_Change(ByVal Target As Range)
> >> >> If Target.Address = "$B$1" Then
> >> >> Range("2:" & Rows.Count).EntireRow.Hidden = False
> >> >> Range(CStr(2 + 2 * Range("B1").Value) & ":" & _
> >> >> Rows.Count).EntireRow.Hidden = True
> >> >> End If
> >> >> End Sub
> >> >>
> >> >> Now, go back to the worksheet and type different numbers into A1 to
> >> >> see
> >> >> if
> >> >> the code is doing what you want. You can put a drop-down list into A1
> >> >> if
> >> >> you
> >> >> want... it will not matter to the code how the number gets into the
> >> >> cell.
> >> >>
> >> >> Rick
> >> >>
> >> >>
> >> >> "New2Macros" <New2Macros@discussions.microsoft.com> wrote in message
> >> >> news:E31AE09A-69DC-4A11-A08C-C26C325F3761@microsoft.com...
> >> >> > New2Macros:
> >> >> >
> >> >> > Sorry I'm not being clear enough. Say in cell A1 you want to enter
> >> >> > the
> >> >> > number of rows being shown in the spreadsheet. A1 should always be
> >> >> > shown,
> >> >> > of
> >> >> > course, and each number entered in A1 will show double the amount of
> >> >> > rows
> >> >> > below. Each row starting with A2 will have information entered
> >> >> > previously,
> >> >> > so we are just displaying, or hiding those rows below. For
> >> >> > instance:
> >> >> > if
> >> >> > I
> >> >> > want only 2 rows displaying below, I would enter a "1" in cell A1.
> >> >> > If
> >> >> > I
> >> >> > want
> >> >> > 10 rows to show, I would enter "5" in A1, and so on. A1 probably
> >> >> > will
> >> >> > have a
> >> >> > pull down.
> >> >> >
> >> >> >
> >> >> >
> >> >> > "Rick Rothstein (MVP - VB)" wrote:
> >> >> >
> >> >> >> I don't completely understand your criteria. From your example, if
> >> >> >> the
> >> >> >> user
> >> >> >> picks 2, how do you know to start showing rows at Row 3? And if you
> >> >> >> hide
> >> >> >> everything except for Rows 3 through 6, then B1 will be hidden and
> >> >> >> you
> >> >> >> won't
> >> >> >> be able to change the number in it again without manually unhiding
> >> >> >> it.
> >> >> >> Can
> >> >> >> you give us more details on how you want this functionality to work
> >> >> >> (keep
> >> >> >> in
> >> >> >> mind that no one here has any idea what you want to do, so you have
> >> >> >> to
> >> >> >> tell
> >> >> >> us).
> >> >> >>
> >> >> >> Rick
> >> >> >>
> >> >> >>
> >> >> >> "New2Macros" <New2Macros@discussions.microsoft.com> wrote in
> >> >> >> message
> >> >> >> news:652FC3FB-B54E-4536-A443-B4D98C0A9BC6@microsoft.com...
> >> >> >> > That is close. What I need is that you can choose from 1 to 35
> >> >> >> > (maybe
> >> >> >> > in
> >> >> >> > a
> >> >> >> > pull down), and have it show the twice that many rows. So if you
> >> >> >> > pick
> >> >> >> > 2
> >> >> >> > it
> >> >> >> > would show rows 3 through 6 and hide all the other pre-populated
> >> >> >> > rows.
> >> >> >> >
> >> >> >> > Also when I plugged in that macro I'm getting an error '1004'.
> >> >> >> > "Unable
> >> >> >> > to
> >> >> >> > set the hidden property of the range class."
> >> >> >> >
> >> >> >> >
> >> >> >> >
> >> >> >> > "New2Macros" wrote:
> >> >> >> >
> >> >> >> >> I need to use a single cell to show/hide other rows. For
> >> >> >> >> instance
> >> >> >> >> if
> >> >> >> >> you
> >> >> >> >> put
> >> >> >> >> a "6" in cell B1 it would show the next 12 rows and hide the
> >> >> >> >> rest.
> >> >> >> >> If
> >> >> >> >> you
> >> >> >> >> put in a "10" it would show the next 20 rows and hide the rest,
> >> >> >> >> etc.
> >> >> >> >>
> >> >> >> >> I'm new to writing macros, sorry!
> >> >> >>
> >> >> >>
> >> >>
> >> >>
> >>
> >>
>
>