On Jul 23, 5:55*am, "Rick Rothstein \(MVP - VB\)"
wrote:
Use this code instead; it keeps the "cursor" on the same row if that row is
68 (your last hidden row)...

Private Sub Worksheet_Change(ByVal Target As Range)
* On Error GoTo Whoops
* If Target.Column = 12 Then
* * If Len(Target.Value) 0 And Target.Row < 68 Then
* * * Target.Offset(1).EntireRow.Hidden = False
* * * If Target.Row < 68 Then Target.Offset(1).Select
* * Else
* * * Target.Select
* * End If
* End If
Whoops:
End Sub

Rick

"Rick Rothstein (MVP - VB)" wrote in
l...



I think the following Change event code does what you want (and can replace
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)
*On Error GoTo Whoops
*If Target.Column = 12 Then
* *If Len(Target.Value) 0 Then
* * *Target.Offset(1).EntireRow.Hidden = False
* * *If Target.Row < 68 Then Target.Offset(1).Select
* *Else
* * *Target.Select
* *End If
*End If
Whoops:
End Sub

I wasn't sure what to do if you erased an earlier entry, so I do nothing;
that means you can create a blank L:N merged cell in between filled in
cells.

Rick

wrote in message
....
On Jul 23, 1:49 am, "Rick Rothstein \(MVP - VB\)"
wrote:
Your full layout is a little unclear to me. For example, do any of the
cells
in Column L of the hidden rows have values in them? Where did you want
the
"cursor" to be after the row is unhidden... on the row you just unhid? 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; but
how
to handle it (or if doing so is even right) depends on what it is you are
ultimately trying to do. Can you give us more details on what you want to
do
(interaction-wise) and what you want to happen as a result?

Rick

wrote in message

....

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 = False

If [L19].Value 0 Then
Range("A20:N20").EntireRow.Hidden = False
End If

If [L20].Value 0 Then
Range("A21:N21").EntireRow.Hidden = False
End If

If [L21].Value 0 Then
Range("A22:N22").EntireRow.Hidden = 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 hidden
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 in
L22 - ready for the next entry.- Hide quoted text -

- Show quoted text -

Hello Stumped. *Thanks 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 quicker
way than all those If-Thens.

Hello Rick. *Col 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. *The 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. *At the moment, the 'cursor'
seems to be 'jumping down' one or more cells and disappearing into the
'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. Perhaps 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

On Jul 26, 11:50*am, wrote:
On Jul 23, 5:55*am, "Rick Rothstein \(MVP - VB\)"





wrote:
Use this code instead; it keeps the "cursor" on the same row if that row is
68 (your last hidden row)...

Private Sub Worksheet_Change(ByVal Target As Range)
* On Error GoTo Whoops
* If Target.Column = 12 Then
* * If Len(Target.Value) 0 And Target.Row < 68 Then
* * * Target.Offset(1).EntireRow.Hidden = False
* * * If Target.Row < 68 Then Target.Offset(1).Select
* * Else
* * * Target.Select
* * End If
* End If
Whoops:
End Sub

Rick

"Rick Rothstein (MVP - VB)" wrote in
l...

I think the following Change event code does what you want (and can replace
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)
*On Error GoTo Whoops
*If Target.Column = 12 Then
* *If Len(Target.Value) 0 Then
* * *Target.Offset(1).EntireRow.Hidden = False
* * *If Target.Row < 68 Then Target.Offset(1).Select
* *Else
* * *Target.Select
* *End If
*End If
Whoops:
End Sub

I wasn't sure what to do if you erased an earlier entry, so I do nothing;
that means you can create a blank L:N merged cell in between filled in
cells.

Rick

wrote in message
....
On Jul 23, 1:49 am, "Rick Rothstein \(MVP - VB\)"
wrote:
Your full layout is a little unclear to me. For example, do any of the
cells
in Column L of the hidden rows have values in them? Where did you want
the
"cursor" to be after the row is unhidden... on the row you just unhid? 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; but
how
to handle it (or if doing so is even right) depends on what it is you are
ultimately trying to do. Can you give us more details on what you want to
do
(interaction-wise) and what you want to happen as a result?

Rick

wrote in message

...

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 = False

If [L19].Value 0 Then
Range("A20:N20").EntireRow.Hidden = False
End If

If [L20].Value 0 Then
Range("A21:N21").EntireRow.Hidden = False
End If

If [L21].Value 0 Then
Range("A22:N22").EntireRow.Hidden = 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 hidden
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 in
L22 - ready for the next entry.- Hide quoted text -

- Show quoted text -

Hello Stumped. *Thanks 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 quicker
way than all those If-Thens.

Hello Rick. *Col 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. *The 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. *At the moment, the 'cursor'
seems to be 'jumping down' one or more cells and disappearing into the
'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. *Perhaps 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.

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.

The reason is that it is worksheet event code and not simply a macro, sub or
function. The range reference associated with Target that are used inside
the procedure are provided by the event itself (via the argument in the
Worksheet_Change header declaration). I guess it would be possible to move
the code to a module and to use Selection to replace Target within the moved
code, but you would lose the automatic execution of the code that takes
place in response to a cell's value being changed.

Rick