VBA Allow to copy only visible cells in a protect sheet

1
votes

I have a protected sheet with the options below and this sheet contains some hidden rows / columns.

        ThisWorkbook.Sheets(ws.Name).EnableOutlining = True
        ThisWorkbook.Sheets(ws.Name).Protect password, _
            AllowFormattingColumns:=True, _
            AllowFormattingRows:=True, _
            userInterfaceOnly:=True, _
            contents:=True, _
            AllowFormattingCells:=True

And I use this code to block sheets but I want to allow users to also copy only visible cells (using alt +; for example).

I saw the Microsoft website with the documentation (already posted by @Mech), but I tested the options and none worked except the option Contents:= False, but this option set to False allows the user to copy only visible cells edit the contents of the cells (which I don't want to happen).

Does anyone know any other way?

Thanks in advance!

1
excelvba
docs.microsoft.com/en-us/office/vba/api/excel.worksheet.protect – Mech
Thanks for the answer. I edited the post – Danilo Perl

1 Answers

2
votes

This code should do what you are looking for based on unprotecting the cells for copying purposes.

ActiveSheet.Unprotect Password:="password"
Selection.SpecialCells(xlCellTypeVisible).Select
ActiveSheet.Protect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True

src: https://www.mrexcel.com/board/threads/copy-only-visible-cells-on-protected-sheet-book.412461/