Macros with protected sheets

priyanka18desai

New Member
Joined
May 5, 2020
Messages
6
Office Version
  1. 365
Platform
  1. Windows
Not known to VBA coding.
1. I want Passward Protection on the Refresh Button.
(code applied for Refresh Button was created by recoding macro and assigned refresh macro to it.)
2. In the Workbook there are total 7sheets.
The first sheet is the Master-Data sheet which is compiled data from other sheets in same workbook.
3. Want the refresh button working on Master-Data sheet which should be password protected and only few will be able to refresh the data.
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
I'm using below code:-
Gets error saying:- The cells your trying to change is on a protected sheet. To make change, unprotecte the sheet. You might be requested to enter a password"
kindly help me with this..

Sub Refresh ()
Const strPass As String = "ops"
Dim strPassCheck As String
strPassCheck = "ops"
If ActiveSheet.Protection = True Then
MsgBox "The Worksheet is protected"
strPassCheck= InputBox("Enter Password to refresh", "Enter Password")

If strPassCheck Like "ops" Then
MsgBox "Data is getting Refresh. press ok"
Else
MsgBox "Password is Incorrect"
End if
Else
MsgBox "The Worksheet is not protected"
End if
ActiveSheet.Unprotect "ops"
Activeworkbook.RefreshAll
Active sheet.Protect "ops"

End Sub
 
Upvote 0
Maybe something like this?? Run it from a command button or ??

VBA Code:
Private Sub Refresh()
Dim pswd As Variant

If ActiveSheet.ProtectContents = True Then

pswd = InputBox("Enter Password to Refresh")

ActiveSheet.Unprotect pswd

Else

MsgBox "Sheet is not currently locked", vbInformation

Exit Sub
End If

ActiveWorkbook.RefreshAll
ActiveSheet.Protect "ops"

End Sub

Just an idea... Modify as you see fit...
 
Upvote 0
Hi thank you for replying ?
I pasted the above code in Command Button into my excel data sheet which is connected using Power query editor.
i saved the code and than clicked the button. Gets the message saying "Sheet is not currently locked".

I want the code in such a way that:-
Whenever Workbook is opened the Master Data sheet should be Locked & can be opened by individuals whoever knows the password.
2. When password is entered, Data sheet should be refreshed, updates the data & saves it.
3. Now data is refreshed-updated&saved into sheet, should again fall back to protection.
This should be functioning I'm expecting.
(Lock- Enter password- data Refresh & updated- Again Lock)
 
Upvote 0
So...as I understand it, you want the following:
1. Click button
2. Enter password
3. Refresh sheet
4. save sheet
5. Lock sheet

I am using a simple input box to enter the password, and save it to a variable that is included in the unlock. It then runs your refresh and saves...

If you want it to lock the sheet even if it is not currently, you can remove the "Exit Sub" line and put the save and protect lines under the msgbox line.

I am not an expert, but this seems like it would work for what you need...

VBA Code:
Private Sub Refresh()
Dim pswd As Variant



If ActiveSheet.ProtectContents = True Then

pswd = InputBox("Enter Password to Refresh")

ActiveSheet.Unprotect pswd
ActiveWorkbook.RefreshAll
ThisWorkbook.Save
ActiveSheet.Protect "ops"
Else

MsgBox "Sheet is not currently locked, but will be after pressing OK", vbInformation

ActiveWorkbook.RefreshAll
ThisWorkbook.Save
ActiveSheet.Protect "ops"

Exit Sub

End If



End Sub
 
Upvote 0
Hi,
Tried doing both options above.
Result 1:- gives error saying"This will cancel a pending data Refresh. Continue?"
Than pressing as ok, it doesn't update anything or runs Power query
 
Upvote 0
OK...looks like you need a pro. I don't know where to go from there... No knowledge with Power Query, and how it interacts with Excel...

Sorry...
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

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