Paste disable button - in sheet

[vitorio]

Hi all

I have this VBA macro, workbook link enclosed, name MacrosTestingPasteBlock.xlsm.

The macros works fine for me, but when try to paste once enter the password "test" to enter data the paste button do not work, its disable.

Need to use the Clipboard to manually paste.

Is my belief that somehow the macros disable the paste option.

Been unable to load xlsm file (see image attached), enclosed link to download from mega.nz


https://mega.nz/file/3IBgFCrD#UAE_m8...RCm7ABd1YQS8AY



Any suggestions how this issue can be solved.

Thanks for the help!!!!!!!

[Aussiebear]

I believe that your file is over the maximum size. Is there any way to minimise the file size?

[vitorio]

Size is 4,191kb.

Been unable to load xlsm file (see image attached), enclosed link to download from mega.nz


https://mega.nz/file/3IBgFCrD#UAE_m8...RCm7ABd1YQS8AY

This size still can not load.

[Aussiebear]

You have 25 modules within your workbook. Is your workbook or worksheet protected when you try to carry out the operation?

[vitorio]

Yes. The worksheet is protected but the cells where enter data are unlocked.

If used the Excel Clipboard manually I can paste data after copy.

[Aussiebear]

So I would suggest you have a good look at any macro that has as its intention pasteing data into the sheet.

[vitorio]

Any suggestions, more specifically, where to look, please.
I have been looking various days for information about this issue without any luck.

Any information, specially, those experience VBA coders is welcome

Thanks Aussiebear for your interest and ideas to try to solve this issue

[Aussiebear]

If you run this macro, does it allow you to paste data?

Sub AllowPasteToProtectedSheet()
    Dim TargetRange As Range
    ' Specify the target range where you want to allow pasting
    Set TargetRange = Range("A1:D10") 
    ' Adjust the range as needed
    ' Unprotect the sheet (you may need to set a password)
    ActiveSheet.Unprotect Password:="your_password"
    ' Paste the data into the target range
    TargetRange.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    ' Protect the sheet again
    ActiveSheet.Protect Password:="your_password", UserInterfaceOnly:=True, AllowFormattingCells:=True, AllowFormattingRows:=True, _
    AllowFormattingColumns:=True, AllowInsertingRows:=True, AllowInsertingColumns:=True, AllowDeletingRows:=True, _
    AllowDeletingColumns:=True, AllowSorting:=True, AllowFiltering:=True, AllowPivotTables:=True
End Sub

I've not tested this, however it I believe that it should. Note after UserInterfaceOnly:= True, the rest are optional ones which may be deleted.

[vitorio]

Just return from Xmas vacation.

I removed the sheet protection and still can not copy and paste only if use the Clipboard to manually paste.

If the cells are protected Excel provide this message:

"The cell you are trying to change is on a protect sheet. To make a change unprotect the sheet"

Please provide any other possible situations the prevent the copy and paste so I can look deeper in them.

Thanks all for their input specially to Aussiebear for their time in help me.


PS The Excel macro link with this issue is above. Can be downloaded from MEGA link. Password "test"

[Aussiebear]

Did you change "Your password" twice in the code provided?

[vitorio]

No I did not.

There is an initial password, "test".

In the real file depending of the password enter it opens a page for the sheet that is under he/she responsibility.

Why you ask? Maybe this way I can understand the purpose of the question and answer it more detail.

Thanks.

[vitorio]

Found this a few minutes ago from a Microsoft note site post:

" Using the Worksheet_SelectionChange event will clear the clipboard, which disables the Paste Special option. To enable the option, you need to set the value of the Application.EnableEvents property to False."


If this is possible?, this way I lost the Worksheet SelectionChange event (turn off (false)) which I needed.

Any way to bypass this possibility?

[Aussiebear]

In the code I provided have a look at lines 7 & 11. When you change both instances of "your password" then you give the code a chance to work.

[vitorio]

I still do not get it. If I unprotect the sheet, no password assigned. I still can not copy and paste. Only paste manually using the Excel clipboard.

My thinking still is that there is not a relation with passwords. That is why I not see the purpose of the code provided.

More where there is a comment from Microsoft as mention above and below

" Using the Worksheet_SelectionChange event will clear the clipboard, which disables the Paste Special option. To enable the option, you need to set the value of the Application.EnableEvents property to False."

Something is disable the paste option, in my opinion.

Thanks.