Not able to copy and paste on specific sheets

[bearcub]

I have a sheet which doesn't allow me to copy and paste anything. This doesn't happen when I add new sheets.

I don't remember protecting the workbook or worksheet but if it is protected where would I look?

When I copy the information to a new workbook I am able to copy and paste data on the sheet. I can't quite figure out what is going on.

Thank you for your help,

Michael

 

[MARK858]

or worksheet but if it is protected where would I look?

First place is under the Review tab and see if it says Unprotect sheet or Protect sheet.

 

[bearcub]

No, the sheet or file are not password protected. If I had protected the sheet I would be asked for password to unprotect it.

 

[MARK858]

No, the sheet or file are not password protected. If I had protected the sheet I would be asked for password to unprotect it.

No, you don't have to use a password to protect a sheet and so if you haven't used a password then you won't get asked for one to unprotect it.

What was showing under the Review tab when you looked.

 

[bearcub]

If the worksheet was protected in some way, what would I be looking for. As it is now, I just see a default dialog box with the box being checked for a password to protect. This dialog box appears on all the worksheets.

How would I know if the worksheet was protected in some way? Would there be an icon to notify me? As it is now, I only see a yellow icon with a lock. If this were protected, would that change color?

 

[MARK858]

No under the Review tab it would have said Unprotect sheet, the fact that it produces the dialog box suggests it isn't protected but to be safe run the macro below.

Sub Test1()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        With ws
            If .ProtectContents Or .ProtectDrawingObjects Or .ProtectScenarios Then _
               MsgBox ws.Name & " is password protected"
        End With
    Next
End Sub

 

[bearcub]

I ran the code and nothing appeared. I get a little bell sound whenever I go to paste. I can copy and paste to another sheet that doesn't have this issue but I can't paste on this particular sheet. Very strange behavior. I'm wondering if I tinkered with something the backstage environment.

I do have the following code on the page but I don't think this would case it, do you?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Sheets("Custom Dialog Formats").Calculate
End Sub

I actually just removed the format and I can copy and paste now. This is strange behavior, isn't it?

 

[MARK858]

I actually just removed the format

What format did you remove?

 

[bearcub]

Sorry, I actually removed the code, not the format. It seems that using the Calculate function with the Worksheet.SelectionChange event prevents me from pasting anything. Why do you think that could be?

Is the SelectionChange event preventing the paste action to fire properly?

 

[MARK858]

I would think it is still trying to calculate when you are doing the paste but but do you really need to do a calculation every time you change selection on every cell in the worksheet?

Do you get the same result if you turn events off at the start of the code (remembering to turn them on again at the end of the code)?