protect sheet while being able to update the sheet via macro

hon

Board Regular
Joined
Oct 10, 2005
Messages
73
I have a workbook with multiple sheets in it. One of the worksheet is extracting data from an ODBC datasource. I am trying to protect the datasource sheet from being updated by users. I have tried to hide or protect the sheet but it won't work because it would cause a runtime error in my macro which attempts to refresh the data in the datasource sheet. Even if I can get the protection to work, I would need to password protect the sheet so that none can turn the protection off. In another words, I need to include the password in the macro to unprotect it which still poses a security issue. Any better idea?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Use Protect with the UserInterfaceOnly:= True switch as the first line of your macro (protection reverts to normal when the macro ends).
 
Upvote 0
I need to password protect the sheet but by hardcoding the password in the macro would mean smart users can seek out the password.
 
Upvote 0
I have tried to stick 'UserInterfaceOnly:= True' in the first of the macro. But it spat the dummy reporting syntax error.

Sub Refresh_Data_Source()
'
' Refresh_Data_Source Macro

'
UserInterfaceOnly:=True
ActiveWorkbook.RefreshAll

End Sub
 
Upvote 0
Put protection on the VBA project itself?

No. I need protection on the sheet so that users of the file can't touch the sheet but be able to refresh the data via a button running a macro.
 
Upvote 0
UserInterfaceOnly is an argument of the Protect method. See the help:
Protects a chart or worksheet (Syntax 1) or a workbook (Syntax 2) so that it cannot be modified.

Syntax 1

expression.Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly)

Syntax 2

expression.Protect(Password, Structure, Windows)

expression Required. An expression that returns a Chart or Worksheet object (Syntax 1) or a Workbook object (Syntax 2).

Password Optional Variant. A string that specifies a case-sensitive password for the sheet or workbook. If this argument is omitted, you can unprotect the sheet or workbook without using a password. Otherwise, you must specify the password to unprotect the sheet or workbook. If you forget the password, you cannot unprotect the sheet or workbook. It's a good idea to keep a list of your passwords and their corresponding document names in a safe place.

DrawingObjects Optional Variant. True to protect shapes. The default value is False.

Contents Optional Variant. True to protect contents. For a chart, this protects the entire chart. For a worksheet, this protects the individual cells. The default value is True.

Scenarios Optional Variant. True to protect scenarios. This argument is valid only for worksheets. The default value is True.

Structure Optional Variant. True to protect the structure of the workbook (the relative position of the sheets). The default value is False.

UserInterfaceOnly Optional Variant. True to protect the user interface, but not macros. If this argument is omitted, protection applies both to macros and to the user interface.

Windows Optional Variant. True to protect the workbook windows. If this argument is omitted, the windows aren't protected.

Remarks

If you apply the Protect method with the UserInterfaceOnly argument set to True to a worksheet and then save the workbook, the entire worksheet (not just the interface) will be fully protected when you reopen the workbook. To unprotect the worksheet but re-enable user interface protection after the workbook is opened, you must again apply the Protect method with UserInterfaceOnly set to True.
 
Upvote 0
You reasoning behind not protecting the VBA does not make sense.
I need protection on the sheet so that users of the file can't touch the sheet but be able to refresh the data via a button running a macro.
You can still do the above with the VBA under protection, and the user cannot then go and look in the code for the password.
 
Upvote 0
I tried the following code in my workbook:

Sheets("DataSource").Unprotect ("abc")
ActiveWorkbook.RefreshAll
Sheets("DataSource").Protect Password:="abc", DrawingObjects:=True, Contents:=True, Scenarios:=True

But when it ran the last line of code to protect the worksheet again. It reported the following message a number of times and strangely the protection was applied correctly:

The cell or chart you are trying to change is protected and therefore read-only ...

any idea why?
 
Upvote 0

Forum statistics

Threads
1,216,380
Messages
6,130,286
Members
449,570
Latest member
TomMacca52

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