This works fine but....

clumein2

New Member
Joined
Sep 1, 2006
Messages
32
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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Hi Cliff,

Or you could put the password into your code. Also, you already have your variable via the Target object passed ByVal in the change event. You can just use it's column value in your code...

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 14 Then
        Application.EnableEvents = False
        ActiveCell.Offset(1, -7).Select
        Application.EnableEvents = True
    End If
End Sub

Note the Application.EnableEvents lines. That is because this is a SelectionChange event and you're selecting another cell in your action. This means that another cell will be selected, hence the procedure will fire again. Turning the events off will disable any change events (ie SelectionChange, Change, etc), perform what you want to perform, then turn them back on (they don't reset in recent versions).

HTH
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,236
Hi Cliff,
in addition to what say firefytr, you should also check if when protecting the sheet the option "allow the selection of locked cells" is set to on.

Bye,
 

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
Good point Anthony47! Also note that this is only available in Excel 2002 and on.
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,236

ADVERTISEMENT

Thank you firefytr; before asserting if it is a "good point" or only "a possible point" I shall wait and see if it helps Cliff !

Bye,
 

clumein2

New Member
Joined
Sep 1, 2006
Messages
32
Hi Cliff,
Or you could put the password into your code. Also, you already have your variable via the Target object passed ByVal in the change event. You can just use it's column value in your code...

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 14 Then
        Application.EnableEvents = False
        ActiveCell.Offset(1, -7).Select
        Application.EnableEvents = True
    End If
End Sub

Note the Application.EnableEvents lines. That is because this is a SelectionChange event and you're selecting another cell in your action. This means that another cell will be selected, hence the procedure will fire again. Turning the events off will disable any change events (ie SelectionChange, Change, etc), perform what you want to perform, then turn them back on (they don't reset in recent versions).
HTH

Well... that's fascinating LOL... I'm a little embarassed because............ I don't know WHAT the heck you're talking about.... I'm sorry if I gave the impression that I did that code.....

If you really wanna help this poor clueless soul, you're gonna have to put it in simpler terms then that.

regards
Cliff
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,236

ADVERTISEMENT

I wrote: "you should also check if when protecting the sheet the option "allow the selection of locked cells" is set to on"

Do it this way:
1)unprotect the sheet
2)now protect it back:
-Menu ->Tools ->Protection ->Protect Sheet
-at this point the "sheet protection" window will pop-up; you can set a password on the protection (set it only if you really need it), and below you have a list of boxes with possible actions when the sheet is protected; check at least "Allow selection of blocked cells" and "Allow selection of unblocked cells".
-complete the protection with OK

Now test your macro.

Let us know.
 

clumein2

New Member
Joined
Sep 1, 2006
Messages
32
Thanks Anthony,

Yes, I should have said that I 'allowed the selection or locked cells', but the cursor still goes back to the wrong cell.

Cliff
 

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,236
Try using the code suggested by firefytr:

Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 14 Then
Application.EnableEvents = False
ActiveCell.Offset(1, -7).Range("A1").Select
End If
Application.EnableEvents = True
End Sub

1) writeclick on the sheet name tab
2) the vba edito will open and should show the current macro
3) delete everything and paste the new code.
4) move to excel and test the macro.

Let us know.
 

Forum statistics

Threads
1,141,286
Messages
5,705,517
Members
421,399
Latest member
hjweiss00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top