Re: Move Cursor Up by YoureNotAtHomeNow
YoureNotAtHomeNow
Sat Jul 26 06:54:14 CDT 2008
On Jul 26, 11:50=A0am, YoureNotAtHome...@googlemail.com wrote:
> On Jul 23, 5:55=A0am, "Rick Rothstein \(MVP - VB\)"
>
>
>
>
>
> <rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> > Use this code instead; it keeps the "cursor" on the same row if that ro=
w is
> > 68 (your last hidden row)...
>
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > =A0 On Error GoTo Whoops
> > =A0 If Target.Column =3D 12 Then
> > =A0 =A0 If Len(Target.Value) > 0 And Target.Row < 68 Then
> > =A0 =A0 =A0 Target.Offset(1).EntireRow.Hidden =3D False
> > =A0 =A0 =A0 If Target.Row < 68 Then Target.Offset(1).Select
> > =A0 =A0 Else
> > =A0 =A0 =A0 Target.Select
> > =A0 =A0 End If
> > =A0 End If
> > Whoops:
> > End Sub
>
> > Rick
>
> > "Rick Rothstein (MVP - VB)" <rick.newsNO.S...@NO.SPAMverizon.net> wrote=
in
> > messagenews:%23KTnm8H7IHA.1196@TK2MSFTNGP05.phx.gbl...
>
> > >I think the following Change event code does what you want (and can re=
place
> > >all 50 of your If-Then blocks at the same time). Note... you used the
> > >SelectChange event in your first post, but since in your last post you=
said
> > >you wanted to react off the Enter key, I used a Change event procedure
> > >instead.
>
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > =A0On Error GoTo Whoops
> > > =A0If Target.Column =3D 12 Then
> > > =A0 =A0If Len(Target.Value) > 0 Then
> > > =A0 =A0 =A0Target.Offset(1).EntireRow.Hidden =3D False
> > > =A0 =A0 =A0If Target.Row < 68 Then Target.Offset(1).Select
> > > =A0 =A0Else
> > > =A0 =A0 =A0Target.Select
> > > =A0 =A0End If
> > > =A0End If
> > > Whoops:
> > > End Sub
>
> > > I wasn't sure what to do if you erased an earlier entry, so I do noth=
ing;
> > > that means you can create a blank L:N merged cell in between filled i=
n
> > > cells.
>
> > > Rick
>
> > > <YoureNotAtHome...@googlemail.com> wrote in message
> > >news:81ba0275-904d-4379-8a2e-d267483e3b58@d77g2000hsb.googlegroups.com=
...
> > > On Jul 23, 1:49 am, "Rick Rothstein \(MVP - VB\)"
> > > <rick.newsNO.S...@NO.SPAMverizon.net> wrote:
> > >> Your full layout is a little unclear to me. For example, do any of t=
he
> > >> cells
> > >> in Column L of the hidden rows have values in them? Where did you wa=
nt
> > >> the
> > >> "cursor" to be after the row is unhidden... on the row you just unhi=
d? If
> > >> so, in which column? Based on what you posted so far, I can tell you=
that
> > >> your 50-some If-Then blocks can be replaced with a single If-Then; b=
ut
> > >> how
> > >> to handle it (or if doing so is even right) depends on what it is yo=
u are
> > >> ultimately trying to do. Can you give us more details on what you wa=
nt to
> > >> do
> > >> (interaction-wise) and what you want to happen as a result?
>
> > >> Rick
>
> > >> <YoureNotAtHome...@googlemail.com> wrote in message
>
> > >>news:56eceb6a-dcde-43fb-a109-3ed53b0a46df@m45g2000hsb.googlegroups.co=
m...
>
> > >> > Excel 2000
>
> > >> > Hello
>
> > >> > I've set up some code (crudely - I'm a VBA beginner) to make a row
> > >> > unhide itself when a cell above that row has something typed into =
it
> > >> > thus:
>
> > >> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>
> > >> > 'Stops showing every step on screen
> > >> > Application.ScreenUpdating =3D False
>
> > >> > If [L19].Value > 0 Then
> > >> > Range("A20:N20").EntireRow.Hidden =3D False
> > >> > End If
>
> > >> > If [L20].Value > 0 Then
> > >> > Range("A21:N21").EntireRow.Hidden =3D False
> > >> > End If
>
> > >> > If [L21].Value > 0 Then
> > >> > Range("A22:N22").EntireRow.Hidden =3D False
> > >> > End If
>
> > >> > etc down to row 69.
>
> > >> > Trouble is, on activation, the row unhides itself ok but thecursor
> > >> > ends up below the row that's just been unhidden, amongst the hidde=
n
> > >> > rows, and I want it IN the row that's just been unhidden, ie if I'=
ve
> > >> > just typed something into L21 and Entered, I want thecursorto be i=
n
> > >> > L22 - ready for the next entry.- Hide quoted text -
>
> > >> - Show quoted text -
>
> > > Hello Stumped. =A0Thanks for that, it's working so far - but I think =
I'm
> > > going to see what Rick says - I sort of knew that there'd be a quicke=
r
> > > way than all those If-Thens.
>
> > > Hello Rick. =A0Col A is the only column of A:N which has anything in =
it.
> > > So for row 19, A19 has text already in it, B19:K19 (one merged cell)
> > > is empty, L19:N19 (one merged cell) is empty, and so on down.
>
> > > The idea behind this is only to show as many rows as there is data to
> > > be entered (actually it'll turn out as number of rows needed + 1, but
> > > that's ok), so I've got row 19 showing to start with as I know
> > > there'll be at least one row needed, rows 20 to 68 (last row for
> > > entering data) are hidden. =A0The assumption is that if L19:N19 is
> > > filled then another row will be wanted, so I want the user to be able
> > > to type data in L19:N19, Enter, and end up with row 20 Unhidden and
> > > the 'cursor'/selected cell/sorry not sure of correct term, on L20:N20
> > > ready for the next data entry, and so on. =A0At the moment, the 'curs=
or'
> > > seems to be 'jumping down' one or more cells and disappearing into th=
e
> > > 'clump' of hidden cells below row 20, I have to key 'up-arrow' to get
> > > it back into L20:N20.
>
> > > I hope that's clear.- Hide quoted text -
>
> > - Show quoted text -
>
> Hello Rick
>
> Neither of your programs works, I'm afraid. =A0Perhaps I'm doing
> something wrong, I did this:
>
> Select (your code - I tried it with both sets of code)
> Copy
>
> Then in VBA window
> Insert
> Module
> Paste- Hide quoted text -
>
> - Show quoted text -
Forget that last, Rick. Was just browsing some other stuff where you
were advising and have now entered the code the proper way, ie right
clicking worksheet tab, etc. I don't understand why this is different
to putting it in a Module.
Will now have to figure out how to modify your code to work for some
other blocks of cells. Might need more help! Don't suppose you can
recommend a VBA book? Have got VBA for Dummies (John Walkenbach) but
haven't really found that much good - beyond the very basic stuff.
Thanks.