Locking/Protecting worksheet limits user activities.

Livin404

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Greetings, I secured/protected my worksheets/workbook, but when I I do I cannot use my macro buttons I created with shapes, and I get an error which links to the code below. The code works good as long as I do not protect the sheet. The VBA Code that won't work is:



VBA Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("O3:O500").Value = Range("C3:C500").Value
End Sub


I'm sure coming up with a code to use the above macro is necessary .

The second part of my question is coming up with a macro so I can use my buttons while the sheet is protect. Am I to use something like?
VBA Code:
Sub SheetProtection()
'Checks if the Training sheet:
'  -  If it is PROTECTED ... then UNPROTECT it
'  -  If it is UNPROTECTED ... then PROTECT it

    If ThisWorkbook.Sheets("Xman").ProtectContents = True Then
      ThisWorkbook.Sheets("Xman").Unprotect "mozzer"
    Else
      ThisWorkbook.Sheets("Xman").protect "mozzer"
    End If
End Sub

Then for each macro that uses a command button do I insert
VBA Code:
Call SheetProtection
at the beginning and end of the macros.
I'm asking these two now, since I cannot do anything until the first macro is sorted.
Thank you,
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
Yes you would add that at the start & end of the macros, but you only need to do that if the code is working on the Xman sheet.
 
Solution

Livin404

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Yes you would add that at the start & end of the macros, but you only need to do that if the code is working on the Xman sheet.
Thank you, I did that I think it's nearly working; however, the autofit does not want to work despite using the macro described above. I've attached an image. I put that same macro on Sheet one thought i don't think it's necessary. It is also in the Module "Clean up". Thank you,


Autofit.JPG
 

Livin404

Active Member
Joined
Jan 7, 2019
Messages
392
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I think it is working now. It didn't like me have the macro on this sheet as well as in a Module. I just removed it from this sheet. It seems fine now. Thanks.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,412
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,328
Messages
5,624,034
Members
416,007
Latest member
csf

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