Working with db queries/worksheet protection/tabs 2

[bas]

We have several excel worksheets that are not lists - they are invoices/forms with "blank cells” on them for the user to fill out. (I'd rather be using Crystal Reports for these forms, but that's another story)

When the a worksheet is protected we can “tab” from one unlocked cell to the next.
This is good. The users want to tab from among certain cells.

Recently, I have introduced database queries to some of the cells. The queries work fine when the worksheet is unprotected. When I protect the sheet(so the users can tab), the queries do not work (whether cells are locked or not)

We want to be able to tab to certain cells.
I hope this is clear.

Any ideas?

If I solve this today alot of people will be happy.
Thank you.

 

[DaleWatson123321]

bas,

What about simply unprotecting before your query and protecting after your query, with code such as...

Sub Unprotect_Sheet()
ActiveSheet.Unprotect "123"
End Sub

Sub Protect_Sheet()
ActiveSheet.Protect "123"
End Sub

Hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[bas]

Dale,
That worked great

I added these as macros and assigned them to cusomized toolbar buttons called "Auto" and "Edit" - I did not want to introduce all of our users to the protect and unprotect features.

Do you know if it is "by design that:
A) db queries do not refresh if a worksheet is protected
B) db queries cannot be associated with a merged cell

Thanks, again.

 

[DaleWatson123321]

bas,

I'm pleased that my suggestion was useful.

As for the "by design" questions... probably "yes" on both accounts.

It probably makes sense to prevent ALL modifications (except to unprotected cells) when the sheet's protection is enabled. And the MS developers likely expect users to utilize VBA code to unprotect/protect if and when required, to deal with a protected environment.

As for "merged cells", again it makes sense that there will be a conflict. If cells are merged, internally Excel needs to tread the cells as "ONE reference point" - and therefore cannot be expected to place data within the individual cells of a "merged range of cells".

Not being aware of your exact application, I'm not sure whether the following suggestion will help, BUT...

If you are merging cells because you need to alter the way the data is displayed, then perhaps you might be able to perform your db query on one sheet. Then, have a "separate" sheet that "pulls out" the "select data" you need for reporting purposes. Then have "separate" formulas that reference the extracted data. These "separate" formulas can then be "merged" - because there is an "indirect" reference back to the original data.

The above might not make a whole lot of sense if you are not yet familiar with Excel's "database functionality" - which includes the ability to extract data (selectively) from one sheet to another - based on specific "criteria".

Wow! Did I get "carried away" or what, with my "rambling". Hope it helps a bit.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[bas]

Excel's "database functionality" based on specific criteria was unfamiliar to me before your kind response! Now I don't even have a need for the db query - much quicker for my application.

Thank you again. And I may add it works like a charm!
All is displayed neatly.

How do I vote for you as a Forum Expert?

Dare I pick your brain further?...
Say the user is "in the worksheet" which he already auto filled as I designed. Now he tabs through and edits some of the cells (which use the nice DBGET function). Can I prevent those nice formulas from being deleted and still allow him to edit the cell? He could print the form, close and not save changes and reopen. But, it seems like there should an easy way to "hang on to the original formulas".

Now i'm rambling.
Does this make sense?

bas

 

[DaleWatson123321]

bas,

Can I first get you to confirm that the "DBGET" function you referred to, is actually the "DGET" function ??? Otherwise, you might be referring to a newer version of Excel and/or something I'm not aware of.

If it is "DGET", then the following suggestion might be a reasonable approach to resolving your task - that of allowing the user to edit any of the cells that "pull in" data via your DGET function.

My suggestion would be to have a separate (protected) sheet where you have a resident DGET formula and criteria. Then have a VBA routine that "feeds" the one criteria each of the different values - one at a time - and then have your VBA place these values into the "destination cells" that you want to allow the end-user to edit.

Hope this makes sense.

As for your other question, the method of showing appreciation and recognition to contributors, is to click on the "Click here to mark this post as a helpful or expert post!" - located in the lower-left-corner of the contributor's posting.

Please advise as to how you make out with this latest suggestion.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

bas,

A further thought...

If you've already set up multiple DGET formulas and separate criteria, then another option would be to (simply) copy/paste the entire set of values to the sheet where the end-user will be allowed to make changes. You will of course need to ensure via your VBA code that these "end-user cells" are left as "unprotected cells" after your copy/paste procedure.

Again, I hope this has some value.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[bas]

Dale,
I'm finally responding to your May 2 suggestion.
Yes, I meant the DGET function not "DBGET".
I did just what you advised in having a seperate (protected) sheet with the resident DGET formula and criteria. Then I wrote a VBA routine to feed to the destination cells as you also advised.

I can't thank you enough.
Your help has been indispensable.
The users are very happy and I learned so much about Excel.

Thanks again.
bas