sheet protection - Select Locked Cells

Chas17

Well-known Member
Joined
Oct 16, 2002
Messages
657
.
Tools>>Options Edit Tab has a checkbox "Move Selection after Enter"
I have this disabled (Unchecked)

I have a sheet that I Sheet Protect, Select UnLocked Cells is Enabled, Select Locked Cells and All others are disabled.

A named range of Cells (E3:E122) named "PowerInput" are the only cells with Format Cell protection "not" locked.

With the above conditions, when I change a cell in the range "PowerInput" it moves the selection to the next cell even though the Tools>>Options Edit Tab checkbox "Move Selection after Enter" is disabled (Unchecked).

If, when protecting the sheet, Select Locked Cells is enabled, then this does not happen.

Is this by design or poor design? Is there a way to not move the selection after a cell in the "PowerInput" range is changed? I have code which runs based on the contents of a cell offset from the actice cell; if the selection moves then the wrong cell is input to code.
I could just leave Select Locked Cells disabled, but I like that paticular feature for this particular sheet.

my code for worksheet change:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)

Dim vrange As Range, cell As Range
Dim chas As Range

Set vrange = Range("PowerInput")

For Each cell In Target
    If Union(cell, vrange).Address = vrange.Address Then
        Set chas = ActiveCell.Offset(0, 4)
        If chas = Range("AlertViolation") Then
            frmViolation.Show
        ElseIf chas = Range("AlertHold") Then
            frmHold.Show
        ElseIf chas = Range("AlertCaution") Then
            frmCaution.Show
        End If
        
    End If
Next cell

End Sub

Thanks, Chas
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Yep, it's a feature.

Why are you using ActiveCell when you already have Target, or more precisely Target.Cells(1, 1)?
 

Chas17

Well-known Member
Joined
Oct 16, 2002
Messages
657
Andrew,
Not sure what you mean.

A user selects cell E20 and enters a number, I want to return the results of Cell I20 after the change. Thus the Set chas = ActiveCell.Offset(0, 4)

How would you adjust code to use target?

Edit:

Got it:
Code:
        'Set chas = ActiveCell.Offset(0, 4)
        Set chas = Target.Cells(1, 5)

Thanks,
Chas
 

Chas17

Well-known Member
Joined
Oct 16, 2002
Messages
657

ADVERTISEMENT

Thank You

Is
Set chas = Target.Offset(0, 4)

better than
Set chas = Target.Cells(1, 5)

or same, seems to work.

thank you again, had the blinders on...
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
In your case they are equivalent, because Target is a single cell. If Target was multiple cells they would not be equivalent. The range returned by Offset would be the same size as Target, but the range returned by Cells would still be a single cell.
 

Forum statistics

Threads
1,136,991
Messages
5,679,014
Members
419,799
Latest member
APInfa

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