protect in macro

P

pkeegs

I have the following code in a Macro to refresh an external database query.

Sheets("Client & Staff Data").Select
ActiveSheet.Unprotect Password:="pancake"
ActiveWorkbook.RefreshAll

Range("M3:N3").Select
Selection.AutoFill Destination:=Range("M3:N98"), Type:=xlFillDefault
ActiveSheet.Protect Password:="pancake"

When I test it by stepping through the code using F8 it works perfectly. But
when I activate it with a button, I get two messages that I need to first
unprotect the sheet. Why is it working when tested but not on the button?
Regards
 
H

Heera

1. Did you manually unprotected the sheet checked the password?
2. Are you working on more then 1 workbook. If two workbooks has same
worksheet name then it may create issue.
3. Have you linked the correct macro to the button.

Check these things.
 
L

Leith Ross

Hello pkeegs,

Running the code manually removes the protection before the query i
run. If the query is running automatically, the protection will have t
removed before the refresh and applied after the refresh. The Quer
object generates 2 events *BeforeRefresh* and *AfterRefresh*. To use th
events in VBA requires creating a custom Class with events. This i
quite involved technique and not practical to attempt to explain here
Chip Pearson has a good write up on this 'Application Events
(http://www.cpearson.com/excel/AppEvent.aspx)

Sincerely,
Leith Ros
 
P

pkeegs

Thanks for the reply:
1. The sheet was protected before I ran the macro
2. Only working on one sheet
3. I have checked the macro allocated to the button - it is correct
 
P

pkeegs

Thanks Leth,

I'll have to study what you are saying & check with Pearson - but it seems
to me that I am unprotecting before the refresh and protecting after the
refresh, but there must be more to this than I am seeing.
 
Top