This works fine but....

[clumein2]

This works correctly on 'unprotected' sheets. When the cursor hits column 14, it jumps back to col 6. But... as soon as I protect the sheet, the cursor jumps back to col 3. So I figured it was in the 'Allow all users of this worksheet to' checkboxs.Just to quickly see if it was, I allowed all... no good. I then applied protect workbook, but the same.
What do you think?

Public sColumn As String
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
sColumn = ActiveCell.Column
If sColumn = 14 Then
ActiveCell.Offset(1, -7).Range("A1").Select
End If
End Sub

Thanks

Cliff

 

[clumein2]

Anthony, I rightclicked the sheet name tab - clicked 'view code' - pasted in the above code - saved the workbook - no go... that darn cursor keeps popping back to row 3. It's funny but when I click in row 14 it pops back to row 6. Could it have something to do with the fact that I have rows 6 & 7 merged?

thanks again
Cliff

 

[Anthony47]

It is almost time to go to bed, for me...
I assume you meant Col 3 (not row 3) and Col 14 (not row 14).

Let's try "debugging":
1) Open the vba editor (alt Tab, or the Rightclick on sheet name)
2) set the cursor at the beginning of ActiveCell.Offset(1, -7).Range("A1").Select
3) press F9; the line will be marked brown (breakpoint on the line)
4) go to excel
5) select cell A1, then with the arrows move 1 position right, slowly, until the VBA windows come in foreground. Confirm that the instruction ActiveCell.Offset etc etc is highlighted.
6) Alt-Tab to excel and take notice of the current selected cell
7) alt-tab to Vba, press F8: End If should highlight
8) Alt-tab to excel and take notice about the new selected cell
9) press F5 to complete the macro and F9 to remove the break.
Do this procedure with the sheet unprotected and then with the sheet protected.

Tell me the results.

 

[clumein2]

Yes, I mean't Col not row..

ok, everything up to 9) followed perfectly, but when I hit F5 a 'Go To' box popped up with the cursor blinking in a 'reference' field in the box.... so I couldn't get to F9.

 

[clumein2]

opps.... I just read the last line of your instruction... "Do this procedure with the sheet unprotected and then with the sheet protected."

sorry, I try again.

thanks for your time.... hit the sack

 

[Anthony47]

Where this GoTo pop up is coming from? Is this the first time it appears?
Is there any other macro on that sheet, on any other sheet, or on ThisWorkbook?
1) Open or goto the Vba editor
1) on the Project frame (at the left), double click on each of the sheet names and on ThisWorkbook, and check if any macro is stored.

Bye,

 

[clumein2]

yes, I have a macro on the workbook - but don't worry bout in now. I'll try the debugging procedure.

again, thanks for your time..... if it's a no go.... I'll post back tomorrow. If it works I'll post back tonight.

thanks

 

[Zack Barresse]

Merged cells will wreak havoc with your code, that could very well be the problem. The other issue is if you choose any cell cell prior to column H (A:G), as the 7 is hardcoded, there are no columns seven columns left of those columns.

If you can get rid of the cell merges, do it. That's by far the worst (IMHO) feature MSFT has ever put into Excel.

 

[Anthony47]

I have proposed to Cliff, via pm, a new macro that use absolute addressing rather than relative:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
If Target.Column > 13 Then
Cells(Target.Row + 1, 6).Select
End If
Application.EnableEvents = True

End Sub

Waiting for his feedback.
Bye,

 

[mark hansen]

I find the code to automatically wrap the cursor when reaching a certian column itneresting and can see its use in some of the stuff I do...

But, I can't get it to work.
I've tried to put it in the Sheet, and the workbook VBE pages and it doesn't seem to kick in.

What can I be overlooking.
Thanks.

 

[Anthony47]

Hi mark,
this code has to be put into the "sheet" module.

Can you explain which code are you using, how are you testing it, what happen that you don't agree and what does not happen?

Bye,