1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Vba code to enter password

Discussion in 'Business Applications' started by Teacherless, Jul 30, 2012.

Thread Status:
Not open for further replies.
Advertisement
  1. Teacherless

    Teacherless Thread Starter

    Joined:
    Jun 28, 2012
    Messages:
    94
    Hi guys, this one is directed at the professionals. I am trying to find some vba code that will do the following. I would like, if it's possible, to have a macro button on my Excel toolbar, which, when selected, will trigger a personal macro that will insert a pre-determined password into the vba password dialogue box that appears when I try to open my workbook's vba editor. All my workbooks are protected with the same vba protection password, and as I am constantly needing access to the editor, this would save me having to type in, or copy and paste the password every time. I don't know if this is possible or not, so I am seeking the advise of the masters.
    Hoping for some help here, regards, John
     
  2. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    456
    Hi John,

    i found this and tested it and it worked.

    Basically i had a workbook names "ABook.xlsm" and the password was "Blah123".

    i added a button to the tob of the ribbon using the customise ribbon option in 'Options'

    Click on the 'File' at the top left of Excel
    Click Options
    Click 'Customise Ribbon'
    Then on the right Click 'New Tab'

    now when you add you macro into the vba editor module you will be able to assign the macro to your new tab.

    so, when i opened my excel document i tried to open the VBA project but it was paswword protected, so i hit the button and it unprotected it. give it a go, if you need help please let me know.

    http://www.ozgrid.com/forum/showthread.php?t=13006
     
  3. Teacherless

    Teacherless Thread Starter

    Joined:
    Jun 28, 2012
    Messages:
    94
    Thanks for your interest DataBase, glad to see someone has given this some thought. Can you post the Personal macro code that you used as it is this that I can't seem to get right. I will try out your suggestion. If it works ok it will save me a lot of repetitious work. I should be able to let you know the results in a couple of hours.
    Thanks again, John
     
  4. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    456
    Hi,

    no problem the code is in the link i provided in my first reply. however here it is: hope it helps

    'need reference To VBA Extensibility
    'need To make sure that the target project Is the active project
    Sub test()
    UnprotectVBProject Workbooks("ABook.xls"), "password"
    End Sub

    Sub UnprotectVBProject(WB As Workbook, ByVal Password As String)
    '
    ' Bill Manville, 29-Jan-2000
    '
    Dim VBP As VBProject, oWin As VBIDE.Window
    Dim wbActive As Workbook
    Dim i As Integer

    Set VBP = WB.VBProject
    Set wbActive = ActiveWorkbook

    If VBP.Protection <> vbext_pp_locked Then Exit Sub

    Application.ScreenUpdating = False

    ' Close any code windows To ensure we hit the right project
    For Each oWin In VBP.VBE.Windows
    If InStr(oWin.Caption, "(") > 0 Then oWin.Close
    Next oWin

    WB.Activate
    ' now use lovely SendKeys To unprotect
    Application.OnKey "%{F11}"
    SendKeys "%{F11}%TE" & Password & "~~%{F11}", True

    If VBP.Protection = vbext_pp_locked Then
    ' failed - maybe wrong password
    SendKeys "%{F11}%TE", True
    End If

    ' leave no evidence of the password
    Password = ""
    ' go back To the previously active workbook
    wbActive.Activate

    End Sub

    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Sub ProtectVBProject(WB As Workbook, ByVal Password As String)

    Dim VBP As VBProject, oWin As VBIDE.Window
    Dim wbActive As Workbook
    Dim i As Integer

    Set VBP = WB.VBProject
    Set wbActive = ActiveWorkbook

    ' Close any code windows To ensure we hit the right project
    For Each oWin In VBP.VBE.Windows
    If InStr(oWin.Caption, "(") > 0 Then oWin.Close
    Next oWin

    WB.Activate
    ' now use lovely SendKeys To unprotect
    Application.OnKey "%{F11}"
    SendKeys "+{TAB}{RIGHT}%V{+}{TAB}" & Password & "{TAB}" & Password & "~"
    Application.VBE.CommandBars(1).FindControl(Id:=2578, recursive:=True).Execute
    WB.Save
    End Sub
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
     
  5. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    456
    If you are still struggling maybe attached you workbook and i will add the code and button on it for you?

    you may want to remove and sensistive data from it before you attached it though.
     
  6. Teacherless

    Teacherless Thread Starter

    Joined:
    Jun 28, 2012
    Messages:
    94
    Hi there DataBase, I've been trying to get this code to work, and so far I have murdered the garden boy, eaten all the chocolate I had saved up, been to the toilet 4 times to think, and I still am not so clever as I thought I was. Maybe it's the cold weather here, I don't know but the fact is I can't get it to work!
    I have attached a simple wb with buttons,(at least I know how to do that), in the hope that you can rescue me from my murderous moods.
    Cheers, John
     

    Attached Files:

  7. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    456
    View attachment 212531 Hi John,

    ok i have edited your workbook, placed two buttons in the ribbon itself rather than the worksheet.

    i have also added some information for you please read that.

    Any questions please reply.

    Thanks.
     

    Attached Files:

  8. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    456
    Please note i have just uploaded another wokbook, the previous one had an error.
     

    Attached Files:

  9. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    456
    I couldnt help myself, had to figure out the password issue, here it is all complete with your original password.
     

    Attached Files:

  10. Teacherless

    Teacherless Thread Starter

    Joined:
    Jun 28, 2012
    Messages:
    94
    Hi there DataBase, I must congratulate you on your dedication to this problem, shows true spirit.
    I downloaded and tried your wb but I seem to have two problems, both of which are probably due to my lack of knowledge. The first one is that I can't find the "Custom Macro" option in my ribbon. the second one is that when I try to run the macros from the vba editor, I get error messages. I have attached a .zip file containing two screen shots for you to look at. When I run the "View Macros, I can't see the ones that you placed in sheet1. I would appreciate it if you could walk me through the steps that I need to take in order to get your method working. Another thought is that I will need to place these macros in my "Personal Macros" in order to be able to use them on any workbook containing my password. Will I just have to copy and paste them there?
    Again, thanks for your persistence, and putting up with the less educated, like me.
    Cheers, John
     

    Attached Files:

  11. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    456
    Hi,

    ok here are the instructions for adding the 'Custom Macro' Button/Tab in the ribbon:

    1) Click on File
    2) Click on Options
    3) Click on Customize Ribbon
    4) You will now see two list box's, click anywhere in the right hand size box and at the botton click 'New Tab'
    5) Now click on 'New Tab (Custom)" and from the bottom click 'Rename' and rename it 'Custom Macro'
    6) Now click on 'New Group' underneath your custom macro so that it is selected.

    Note for the next part the code must already be in the project. so use the spread sheet i attached earlier instead of using a new workbook.

    6) On the left list box, you will see a drop down menu at the top called 'choose commands from' drop that down and choose 'Macros' this will now show you two macros.

    7) click on the first Macro and click the 'Add' button. you shold see that the macro has now moved to your custom macro new group?
    8) click the second macro and press Add again.
    9) Now click on each of the macro and select rename you can assign a picture to each one too. see the attached.

    probably wise to print this out and see if you can follow it.


    Now to address the error. this is occuring because the code needs a reference to a library. Here is how you need to add that reference.

    1) open the VBA editor (Alt + F11)
    2) click on Tools
    3) click on References
    4) look for 'Microsoft Visual Basic for Applications Extensibility 5.3'
    5) tick the box next to it and press ok.

    you may have a different version other than 5.3 - this should not matter.

    Thats it....should work. :)

    any problems let me know.
     

    Attached Files:

  12. Teacherless

    Teacherless Thread Starter

    Joined:
    Jun 28, 2012
    Messages:
    94
    Hi DataBase, I did what you suggested regarding the 'Microsoft Visual Basic for Applications Extensibility 5.3', but I couldn't find it in Office 2007. I did however find and tick the box in Office 2003. Does this mean that I will have to download it, or do I need an update for 2007? Meanwhile I am going to experiment with 2003 and see if I can get somewhere.
    Thanks for you patience, John
     
  13. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    456
    Hi,

    I think you may be looking for 'Microsoft Visual Basic for Applications Extensibility 5.3' in the wrong place. You need to be in the VB editor, where you write the code, then look under Tools > Reference

    Is that where you were looking?

    Can you attach a print screen of that window?

    The file should be located at

    C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\Vbe6ext.olb

    If you can find this file we should be able to get around the problem
     
  14. Teacherless

    Teacherless Thread Starter

    Joined:
    Jun 28, 2012
    Messages:
    94
    Hi DataBase, I opened the workbook with your latest password adjustments(screen shot as proof), and went into the references and the Microsoft Visual Basic for Applications Extensibility 5.3 was ticked. As I have both 2003 and 2007 running on my machine, and I found and ticked it in 2003 yesterday, it looks as though the references apply, and now appear in both versions, so that's solved that problem. However, I have just tried to run the macros again from the Visual Basic Editor and still get the same error message.(See attached screen shots).
    Any ideas?:confused:
     

    Attached Files:

  15. DataBase

    DataBase

    Joined:
    Jan 15, 2004
    Messages:
    456
    an thats a pitty its not working, though we are getting closer. its a long shot but try this, under the developer tab, macro security, make sure the 'Trust access check box is ticked" see the screen shot. if it unticked, tick it, save and close then try again.
     

    Attached Files:

    • p1.jpg
      p1.jpg
      File size:
      65 KB
      Views:
      115
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1063198