Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740

    Unanswered: annoying prompt for VBA Project Password

    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
    currently using SS 2008R2

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    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
    currently using SS 2008R2

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

  4. #4
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    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
    Code:
    Me.boxThis.ControlSource = Worksheets("dABC").Cells(Me.boxAbcRow, cstThis).Address(, , xlA1, True)
    the first two forms populate their list with:
    Code:
                    strAdd = Worksheets("dAbc").Range("F" & intI).Value & ", " _
                           & Worksheets("dAbc").Range("G" & intI).Value
                    Me.listThat.AddItem strAdd
                    Me.listThat.List(Me.listThat.ListCount - 1, 1) = intI
    the third form sets 51 checkbox .Caption from one of the 1-row sheets with
    Code:
        For intX = 1 To 17
            Me("I" & intX).Caption = Worksheets("dXyz").Cells(3, firstI + intX).Value
        Next
        For intX = 1 To 17
            Me("P" & intX).Caption = Worksheets("dXyz").Cells(3, firstP + intX).Value
        Next
        For intX = 1 To 17
            Me("U" & intX).Caption = Worksheets("dXyz").Cells(3, firstU + intX).Value
        Next
    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
    currently using SS 2008R2

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    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.
    Code:
    Option Explicit
    
    Private Sub butDone_Click()
        ActiveWorkbook.Save
        Application.Quit
    End Sub
    
    Private Sub UserForm_Activate()
    
        Me.boxFirst.ControlSource = Worksheets("data").Cells(2, 1).Address(, , xlA1, True)
        Me.boxLast.ControlSource = Worksheets("data").Cells(2, 2).Address(, , xlA1, True)
        Me.boxMail.ControlSource = Worksheets("data").Cells(2, 3).Address(, , xlA1, True)
    
    End Sub
    aaaaarrrgggghhhhhhhhhh!

    izy
    Attached Files Attached Files
    currently using SS 2008R2

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

  7. #7
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    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
    currently using SS 2008R2

  8. #8
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    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
    currently using SS 2008R2

  10. #10
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    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
    currently using SS 2008R2

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    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
    currently using SS 2008R2

  12. #12
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    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:

    Is Excel asking you for a password on exit ?
    Date: 11 december 2004
    reference id: QA0032

    It is possible that you have the following problems if you have Google Desktop installed on your machine.
    If you have VBA password protected files/Add-ins (such as ASAP Utilities) the password prompt appears when you close Excel.
    VBA project remains open if you close a password protected file.
    Duplication of VBA project names in the VBA Editor.
    Solution: Uninstall Google Desktop

    (author: Ron de Bruin)
    Have a nice one
    BUD

  13. #13
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Also try this out from Microsoft itself:

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

  14. #14
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    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
    currently using SS 2008R2

  15. #15
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •