i thought i had completed a small XLS with UserForms. everything worked perfectly until i protected the VBA project. now, each time i quit the application i get prompted several times for the VBA Project Password.

why? how to prevent?

thanks for ideas, izy

hello ???
anyone there ????

pleeeeeeeeze!
how can i get rid of this stupid STUPID password prompt.

i have now split the data and the forms into separate XLS.
the forms redefine their .controlsource which is probably why i get the password prompt (to save the VBAproject (which has a readonly .Saved property (grrrrrrr)))

my exit routine looks like:

Private Sub butQuit_Click()

Worksheets("dAbc").[C3].Value = Me.boxThis
Worksheets("dAbc").[D3].Value = Me.boxThat
ActiveWorkbook.Save
ActiveWorkbook.Close
Workbooks("MyUserFormsAreHere.XLS").Activate
ActiveWorkbook.Saved = True
ActiveWorkbook.Close False

End Sub

anyone, please: some ideas how i can get rid of this password prompt when i quit

Hi Isz

Having written more vba Projects in excel that open/read/manipulate and close multiple spreadsheets and/or read/write data to/from Access than I can remember, all with password protected code, I have never, ever, been prompted for a VBA Project password while running code. The only time I need a VBA Project password is to view/edit code.

I have run your posted code in a password protected project without any hitch.

I am therefore at a total loss as to what is causing this prompt to appear.

You say your "forms redefine their .controlsource", what do you mean/what is the code.

Sorry so negative but .... !!??


MTB

thanks MTB, you give me hope that there is a way out of this mess i have created.

i have fifty XLS each with four worksheets containing data extracted from a database. the amount of data in each XLS is quite small. 1-row, 1-row, N-rows, N*M rows (N is in the range 1...4, M is in the range 5...25), largest N*M is 60. there are quite a few columns (102 in the N*M sheet)

a fiftyfirst XLS contains edit forms
each entity gets the forms XLS and their data XLS

first form shows one of the 1-row sheets and a list of the N entries.
user selects one of the N and is taken to second form which shows detals of the selected N and a list of the M entries.
user selects one of the M and is taken to the third form which shows details of the selected M

each form sets .ControlSource using the first two forms populate their list with: the third form sets 51 checkbox .Caption from one of the 1-row sheets with
in a feeble effort to stop people messing up the data (after edits, it is sucked back into the database), i have worksheet/workbook 'protection' and view code 'protection'. i am fully aware that this is not secure, but it discourages the idiots.

the application runs fine, allows updates to everything that should be updateable, moves smoothly between the forms, saves the edited data. everything is perfect EXCEPT at exit i am left with the VBA password prompt. i can either cancel * 5 or enter the password/OK to get rid of the prompt.
i have not yet discovered which part of the application is throwing up the prompt... it is just sitting there on screen after the app closes down. i suspect, but cannot yet prove, that the .Caption stuff is the cause. later today i will comment out the .Caption set and see if the problem remains.

izy

it wasn't (only) the .Caption

attached ZIP contains two XLS which are identical EXCEPT Test2.XLS has view code protection and Test1.XLS hasn't (all passwords are 'pw')

Test2 throws the password prompt, Test1 does not.

interestingly, this very simple app drops the password prompt after one cancel vs the 5*Cancel required by the 'real' app

the form code is reduced to the minimum.
aaaaarrrgggghhhhhhhhhh!

izy

Hi Isy

Unfortunately our IT bods do not allow downloads of zip files so cannot look at the actual files (mybe I will log on at home - dial up modem, very painful!).

However I have played with the code (I assume boxThis etc are text boxes) and cannot fine anything wrong and, more importantly, still cannot provoke the password request.

Only one question, why change the control source (something I have never used in excel, only in VB6 with a direct DB connection/recordsets DAO/ADO Data Controls).

If the value is changed why not use the afterupdate to just write it to the cell in question ?

Sorry I can not be of more help (if I get time I will down load the zip to-night).

BTW from your earlier post, if you have not sust out (True, True, xlR1C1, True) yet

first 2 agument specify row/column absolut/revlative addressing, the next is either xlA1 for A1 style to be returned or xlR1C1 for (you guessed it) R1C1 style to be returned and the last is to include the sheet name etc or just the cell address. Only just found out my self (more ways to skin a cat...). Only xlR1C1 produces an error in .recordsource = .. , in fact using the default Cell(2,2).Address seem to work on my machine (Excel 2k3).

Any one else any ideas ??

MTB



MTB

hi MTB,

thanks for your perseverence.
boxFirst is a textbox on the userform frmTest for first name
boxLast is a textbox on the userform frmTest for last name
boxMail is a textbox on the userform frmTest for e-mail address

my 'real' third form is a mammoth edit screen with 90 checkboxes and 12 textfields ...editing in normal XLS display is ugly - the userform allows an elegant display.

to answer your question: 102 * _afterupdate subs is very ugly!
.controlsource is a convenient way to feed/harvest edits (i could box = cell on activate and then cell = box on terminate as an alternate solution, but that still leaves me with .caption)

.caption handles field redefinition in the source db. imagine this as some sort of marketing database - when the company stops making widgets and starts making gadgets, i don't need the (obsolete) widget-mail preference but i do need the punters' choice to receive/not gadget-mail.

thanks for the (,,xlA1,True) info - ties in with my guesswork tho i was a bit hazy on the meaning of the External argument.

it would be great if you could try the XLS and see if you can spot anything wrong (or confirm that my approach is impossible).

my current thinking is that my approach is doomed and i will have to revert back to feed/harvest. that still leaves my .caption issue - maybe kill the captions and use textboxes to stop XL thinking that the form has been 'redesigned'?? (but then i have not discovered how to hide the textbox 'frame' (yet))
...or maybe i just abandon codeview-protect (since it is a long way from secure anyway) and let the punters browse my code. there is nothing magic there anyway.

this is really a great pain! soooooo easy in Access and such an uphill struggle in XL.

izy

Hi izy

The good news is I down load the file last night.

The bad news is it work perfectly on this machine (Excel2k3) !!??

Absolutely no messages or prompts of any kind.

The only problem encountered was when using Excel97. Instead of getting the project password prompt when trying to view the code it comes up as Project Unviewable !! This happens both on my home machine and an old at work PC. I have also experienced this on one of my workbooks. However, that, and this, still worked without problem.

So, no further forward.

As far as I can see it should (and does!) work without problem.

I really do not know what to suggest as you seem to have started with a new blank file.

The only other comment I can think of that might have the slightest relevance is that the ControlSource property assigned in code is not saved (on my machine) when the file is closed (as is normal!).

Anybody else any ideas.


MTB

i'm also Excel2k3 (under Win2K)
Test2.XLS throws the password prompt every time!!

now i'm completely baffled. why does my code work for you and not for me?

i guess i will have to dig deep into XL to see if i have some strange setting or property that is getting in the way.

thanks MTB for trying.

izy

i went back to new XLS with simple SIMPLE single box=cell (not even a _terminate cell=box) and i still get this stupid prompt for the vb viewcode pw.

baaaah - 99.9% on the give-up scale

izy

i hit 100% on the give-up scale.
chosen solution is to embed some crypto-crap in the XLS

let the heroic users see/mess with my code. zap them if they mess around.

many thanks to MTB for the contributions.
thanks anyway to the rest.

izy

Hiya Izy.....take a look at what I found and see if this is possibly your situation. Otherwise you might have to re-install Excel maybe:

Have a nice one
BUD

Also try this out from Microsoft itself:

http://support.microsoft.com/kb/280454

thanks Bud,
sorry for the delay in noticing your reply - i had given up on this mystery.

your first one: Google Desktop might be the cause!

i have GDTop on my machine (it is occasionally useful - i'm reluctant to remove it just for a test).

hi MTB ...my code worked on your machine but not mine. did you have Google Desktop installed ???

??? anyone else with or without GDTop willing to play with test2.XLS in tests.zip (post #5) and see if the password prompt shows on quit (don't forget to mention if you yes/no have Google Desktop installed).

the whole point of this XLS is to find a lowest common denominator way to talk to people totally outside of myCo IT environment - i have zero control over their use/not of GDTop (or anything else). if GDTop is the cause of the errant behaviour i obviously can't protect the code without this password prompt cropping up... but at least i can document it and warn folk running GDTop to expect a strange exit if it is really is the cause.

supplementary question whilst i'm here - who is screwing up in this case: GDTop or XL ????

izy

Hi izy

I have so fare managed to resist Googles offer of a Desktop/toolbar.

Not sure if that helps or not, but will obvously be very interested if a cause/solution for this problem found.

MTB