Protect Microsoft Query

D

Don

I am inporting data using a query.
Is there a way of protecting that query so a user cannot
modify it?
It appears that anyone can go to Data>edit query and
change it.
Thanks - Don
 
D

Dick Kusleika

Don

Protecting the sheet will protect the query. Tools - Protection - Protect
Sheet.
 
D

Don

Thanks - However, I stil need them to be able to refresh
the data. Protecting the sheet disables that command.

Thanks - Don
 
D

Don

Not yet.
I don't care if the user manipulates the data, I just
don't want them to change the query.
 
D

Dick Kusleika

Don

Two options that I see. You can disable all the ways that the user can get
to edit query. Consider putting this in the sheet activate event and then
re-enable them in the sheet deactivate event

Dim cbcs As CommandBarControls
Dim cbc As CommandBarControl

Set cbcs = Application.CommandBars.FindControls(, 1950)

For Each cbc In cbcs
cbc.Enabled = False
Next cbc

Another option is to code your own Refresh button and protect the sheet.
The regular Refresh button, along with the Edit Query button, will be
disabled and the user will have to use your Refresh button.
 
D

Don

Thanks - I will try those
-----Original Message-----
Don

Two options that I see. You can disable all the ways that the user can get
to edit query. Consider putting this in the sheet activate event and then
re-enable them in the sheet deactivate event

Dim cbcs As CommandBarControls
Dim cbc As CommandBarControl

Set cbcs = Application.CommandBars.FindControls(, 1950)

For Each cbc In cbcs
cbc.Enabled = False
Next cbc

Another option is to code your own Refresh button and protect the sheet.
The regular Refresh button, along with the Edit Query button, will be
disabled and the user will have to use your Refresh button.

--
Dick Kusleika
MVP - Excel
Excel Blog - Daily Dose of Excel
www.dicks-blog.com




.
Click to expand...
 
Top