re-asking for help

[Guest]

I had left a question on 6/14 about having Excel 2003 automatically do a
library checkout by having the user enter a book # in the first column and
then the library card # in the second column....then upon "enter" the cursor
would move to the next line at the first column (A2).

The answer I received suggested unlocking columns A and B and then protect
the sheet. I think I did these steps properly, but perhaps not because the
cursor just keeps going right. (I preset the cursor to move right not down)

One thing I did notice is that in highlighting columns A and B the cursor
did just what I wanted it to do....but one key stroke wrong and the
highlighting disappears.

Any suggestions??

Thanks,
Karen

 

[Guest]

Make sure the sheet is not protected.

Right click on the sheet tab and select View Code. Paste in this procedure

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Column > 2 Then
Cells(ActiveCell.Row + 1, 1).Select
End If
End Sub

The disadvantage is that you probably won't be able to paste on this sheet
or use undo. It doesn't sound like that should be a problem.

 

[Guest]

Hi Karen

When protecting the sheet there is a number of check boxes that you can
check/uncheck.

Ensure that Select locked cell is uncheck - then enter your password, and
confirm in the next dialogue prompt

HTH

 

[Guest]

Thanks....I'll try that. I did notice a large number of boxes checked and
wasn't sure what to do!

 

[Guest]

Thanks....I will try it!
Karen

Make sure the sheet is not protected.

Right click on the sheet tab and select View Code. Paste in this procedure

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Selection.Column > 2 Then
Cells(ActiveCell.Row + 1, 1).Select
End If
End Sub

The disadvantage is that you probably won't be able to paste on this sheet
or use undo. It doesn't sound like that should be a problem.

 

[Guest]

Success!!! I knew there had to be a simple answer. Thanks so much!

Karen

 

[Guest]

Just a heads up -
While that has worked in earlier versions of excel, Interestingly, that
didn't work for me in xl2003 and was the reason I didn't suggest it -
hopefully it will work for your users.

 

[Guest]

Thanks for the message. It worked on my computer at home which is running
Excel 2003. I will try it on the school's computer later which I'm pretty
sure runs the same version of Excel. But just in case it isn't, I've saved
your information to try.

Thanks,
Karen

 

[Guest]

Thats odd Tom
worked for me in xl2003 on both home and work computer.
I did initially have an issue with it working on blanks cell - but that
seems to have cleared up

Regards
Steve

 

[Tom Ogilvy]

Hello Steve,
On several attempts with two separate workbooks, it just went between A1:B1
(same machine and instance of Excel). I could select farther down using the
mouse, but when I hit enter, it returned to A1 and again moved only between
A1 and B1, I could not travel down the column without using the mouse. I
was on a new worksheet with all blank cells in each case.

So yes, I agree it is odd since that (locked/unlocked cells/sheet protection
with restrictions) has been a standard suggestion for the 10 years I have
been posting here.

 

[Guest]

I initially experienced the same thing Tom
So long as you enter data in the cell - it seems to work ok
Problem only occors on blank cells for me

Regards

 

[Tom Ogilvy]

Ah, I see what it is. The movement is restricted to the usedrange or one
row beyond it. When the sheet is new, and the used range is A1, it appears
to be problematic.

 

[NickHK]

You can utilise the fact that the .UsedRange is not updated to overcome
that. But seems the OP does not require this ability.

With Range("A1")
.Value = "Some Text"
.Value = ""
.Offset(1000, 0).Value = "Some Text"
.Offset(1000, 0).Value = ""
End With

NickHK