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

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.

Hello pkeegs,

Running the code manually removes the protection before the query is
run. If the query is running automatically, the protection will have to
removed before the refresh and applied after the refresh. The Query
object generates 2 events *BeforeRefresh* and *AfterRefresh*. To use the
events in VBA requires creating a custom Class with events. This is
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 Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48227

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

"Heera" wrote:


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.

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.

"Leith Ross" wrote:


Hello pkeegs,

Running the code manually removes the protection before the query is
run. If the query is running automatically, the protection will have to
removed before the refresh and applied after the refresh. The Query
object generates 2 events *BeforeRefresh* and *AfterRefresh*. To use the
events in VBA requires creating a custom Class with events. This is
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 Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.thecodecage.com/forumz/member.php?userid=75
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=48227