Message Prompt to unprotect sheet for Macro to function

yendordf

New Member
Joined
Mar 28, 2021
Messages
5
Office Version
  1. 2016
Platform
  1. Windows
How to add Macro command below to be able to Unprotect then Protect the sheet ? Thanks


Sub Group1()
'
' Group1 Macro
' 1. Copy column A value=1 to the table
'

'
ActiveWindow.SmallScroll Down:=0
Range("QA7").Select
End Sub
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Try :


VBA Code:
If ActiveSheet.ProtectContents = true then
I tried but still prompting "You cannot use this command on a protected sheet...")

Sub Group1()
'

If ActiveSheet.ProtectContents = True Then


' Group1 Macro
' 1. Copy column A value=1 to the table
'

'
ActiveWindow.SmallScroll Down:=0
Range("QA7").Select
End Sub
 
Upvote 0
Go a different route. In your macro ... unprotect the sheet first, perform the various actions, then protect the sheet again.

Do not think in terms of a warning message at all.
 
Upvote 0
H
Go a different route. In your macro ... unprotect the sheet first, perform the various actions, then protect the sheet again.

Do not think in terms of a warning message at all.
Hi Logit,
I tried this code below, but still the same.
Did i write it correctly?


Sub Group1()
'
ActiveSheet.Unprotect password:="123456"

' Group1 Macro
' 1. Copy column A value=1 to the table
'

'
ActiveWindow.SmallScroll Down:=0
Range("QA7").Select
ActiveSheet.Protect password:="123456"
End Sub
 
Upvote 0
VBA Code:
Option Explicit

Sub ProtectSheet()
    Sheet1.Protect Password:="abc"
    
End Sub

Sub UnprotectSheet()
    Sheet1.Unprotect Password:="abc"
End Sub

Change sheet number and password as required.
 
Upvote 0
VBA Code:
Option Explicit

Sub ProtectSheet()
    Sheet1.Protect Password:="abc"
   
End Sub

Sub UnprotectSheet()
    Sheet1.Unprotect Password:="abc"
End Sub

Change sheet number and password as required.
Still not working...
Thanks anyway.

Too early to give up
 
Upvote 0
Paste this line as the first line of code in your macro :

VBA Code:
Sheet1.Unprotect Password:="123456"

Paste this line as the last line of code in your macro :

Code:
Sheet1.Protect Password:="123456"

Make certain the SHEET NUMBER corresponds to the sheet you are referencing.
 
Upvote 0
Paste this line as the first line of code in your macro :

VBA Code:
Sheet1.Unprotect Password:="123456"

Paste this line as the last line of code in your macro :

Code:
Sheet1.Protect Password:="123456"

Make certain the SHEET NUMBER corresponds to the sheet you are referencing.
Still it's not working.
I wrote it this way:

Sub Group1()
'Stocks.Unprotect Password:="123456"
' 1. Copy column A value=1 to the table
'

'
ActiveWindow.SmallScroll Down:=0
Range("QA7").Select


Stocks.Protect Password:="123456"


End Sub
 

Attachments

  • macro.JPG
    macro.JPG
    58.4 KB · Views: 4
Upvote 0
Do not use the Sheet Name "STOCKS". Use the Sheet Number for the Sheet STOCKS.

Also, you have a HYPHEN in front of the line of code. That tells Excel to view that line as a comment and not code.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

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
Back
Top