Check locked Cells

[Partjob]

Hi All
I have a bit of code to help me Paste Values

Sub PastSpec()
If Selection.Locked = True Then Exit Sub
On Error GoTo ErrHan
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
ErrHan:
Exit Sub
End Sub

I recorded part of it and adapted it afterwards. You will notice I have a line to check for locked cells, this does its job if the cell selected is locked. However if the cell selected is unlocked and the cell below is locked the code still excutes and pastes.

I am not sure how to make the code exit if any of the possible target cells are locked.

Is there a way to achieve what I need

Thanks
Partjob

 

[Jonmo1]

I think you would have to loop through each cell in selection...

something like

For Each C in Selection
    If C.Locked = True Then Exit Sub
Next C

Hope that helps.

 

[MrKowz]

Aaaand Jonmo beat me to the punch.

 

[Partjob]

Ok I have not quite explained my self properly.

When I copy I could have a range 5 rows by 6 columns in the copy.
I then go to the target.
I generally pick just one cell, and then fire the macro.
My bit of code dealt with one cell, the cell I picked. Your new lines still only checks the cell I pick, one cell.
I would rarely if ever pick the range that the same size as the copy, that I think is the root of the problem.
I need to check the possible paste range.
I have been here before with a similar problem.
after the pase operaration the selection become the new paste range but it is to late at this stage to check.
I do hope this makes sense.

Regards Partjob

 

[Jonmo1]

You would have to capture the size/shape of the range you copied...

so right after you copy the data, use the same range to set variables for the hight/width of the range copied...

Say you copied range A1:E100

RCount = Range("A1:E100).Rows.Count
CCount = Range("A1:E100).Columns.Count

Then you can use this

For Each C in Selection.Resize(RCount,CCount)
If C.Locked = True Then Exit Sub
Next C

 

[Jonmo1]

Also, it is almost NEVER necessary to select things to manipulate them.

If you give a bigger description of what you're doing, it can probably be done much easier/faster.

 

[Partjob]

Jonmo1
Thanks for taking the time to help me and not for the first time.
I am a little confused now though.
I will step through the process.
I copy a range, I will use your example("A1:E100"). this could be any size though.
This is now in the clip board.
I go to the target and pick a Cell. I execute the code.
You then suggest that I count the Rows and Columns in the copied range, and transfer those variables as area to check for locked cells.
This would be a variable range and is collected before the code even "knows" about it, I am not sure how to achieve this?
I understand that I can check a range of cells before for locked cells but not how I get the range variables in the first place.
thanks
Partjob

 

[Jonmo1]

OK, so the code isn't copying anything.
You're just trying to make a shortcut for Paste Special - Values?

If the sheet is protected, that alone should stop you from pasting over locked cells.

the only way I can think to check for locked cells is to go ahead and paste, then get the range size/shape of what was pasted, then UNDO, Then check if that range had locked cells, then repaste or not...

Try

Sub PastSpec()
On Error GoTo ErrHan
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Rcount = Selection.Rows.Count
Ccount = Selection.Columns.Count
Application.Undo
For Each C in Selection.Resize(Rcount,Ccount)
    If C.Locked = True Then Exit Sub
End If
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
ErrHan:
Exit Sub
End Sub

 

[Partjob]

Jonmo1
When I first read your code I thought that will do it, I had not seen "Undo" before but it made sense.
It did not work though. I had to take the Error checker off to work out what was happening I get a run time error 1004 on the Application.Undo.
Method 'Undo' of object' _Application' failed.

I have got a kind of solution now, though.

Sub PastSpec()
On Error GoTo ErrHan
Selection.PasteSpecial Paste:=xlPasteColumnWidths
Rcount = Selection.Rows.Count
Ccount = Selection.Columns.Count
For Each c In Selection.Resize(Rcount, Ccount)
    If c.Locked = True Then Exit Sub
    Next c
Selection.PasteSpecial Paste:=xlPasteValues
ErrHan:
Exit Sub
End Sub

The column widths are the same on the copied data so this works.
I would not had thought of this with out your help so thanks

I will add a msgbox to let user know what has happened and I am done.

Regards
Partjob

 

[Jonmo1]

Yep, I forgat that undo can only be used to undo actions done by hand in the User Interface. It can't undo actions done by code...bummer.

Glad that works though..

P.S. I also can't believe how many syntax errors I had in there. End If not needed, For without Next, sheesh. That's what I get for giving code without testing it first...