protect sheet while being able to update the sheet via macro

[hon]

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?

 

[BrianB]

Use Protect with the UserInterfaceOnly:= True switch as the first line of your macro (protection reverts to normal when the macro ends).

 

[hon]

I need to password protect the sheet but by hardcoding the password in the macro would mean smart users can seek out the password.

 

[MichaelRo]

Put protection on the VBA project itself?

 

[hon]

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

 

[hon]

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.

 

[GlennUK]

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.

 

[GlennUK]

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.

 

[hon]

will give it a try. Get back to you tomorrow. Thanks

 

[hon]

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?