Advertisement

There's no such thing as a stupid question, but they're the easiest to answer.
Login
Search

Advertisement

Business Applications Business Applications
Search Search
Search for:
Tech Support Guy > > >

Solved: Vba code to enter password


(!)

Teacherless's Avatar
Teacherless   (John) Teacherless is offline
Computer Specs
Member with 94 posts.
THREAD STARTER
 
Join Date: Jun 2012
Location: South Coast, South Africa.
Experience: Beginner
30-Jul-2012, 05:41 PM #1
Solved: Vba code to enter password
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
DataBase's Avatar
DataBase DataBase is offline
Computer Specs
Member with 456 posts.
 
Join Date: Jan 2004
Experience: Intermediate
02-Aug-2012, 05:29 AM #2
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
Teacherless's Avatar
Teacherless   (John) Teacherless is offline
Computer Specs
Member with 94 posts.
THREAD STARTER
 
Join Date: Jun 2012
Location: South Coast, South Africa.
Experience: Beginner
02-Aug-2012, 05:42 AM #3
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

Last edited by Teacherless; 02-Aug-2012 at 05:48 AM..
DataBase's Avatar
DataBase DataBase is offline
Computer Specs
Member with 456 posts.
 
Join Date: Jan 2004
Experience: Intermediate
02-Aug-2012, 06:13 AM #4
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
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
DataBase's Avatar
DataBase DataBase is offline
Computer Specs
Member with 456 posts.
 
Join Date: Jan 2004
Experience: Intermediate
02-Aug-2012, 06:29 AM #5
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.
Teacherless's Avatar
Teacherless   (John) Teacherless is offline
Computer Specs
Member with 94 posts.
THREAD STARTER
 
Join Date: Jun 2012
Location: South Coast, South Africa.
Experience: Beginner
02-Aug-2012, 10:07 AM #6
Vba code to enter password
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
File Type: xls Book3.xls (19.5 KB, 71 views)
DataBase's Avatar
DataBase DataBase is offline
Computer Specs
Member with 456 posts.
 
Join Date: Jan 2004
Experience: Intermediate
02-Aug-2012, 12:16 PM #7
Attachment 212531Hi 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
File Type: xlsm Book3.xlsm (20.9 KB, 73 views)

Last edited by DataBase; 02-Aug-2012 at 12:21 PM..
DataBase's Avatar
DataBase DataBase is offline
Computer Specs
Member with 456 posts.
 
Join Date: Jan 2004
Experience: Intermediate
02-Aug-2012, 12:22 PM #8
Please note i have just uploaded another wokbook, the previous one had an error.
Attached Files
File Type: xlsm Book3.xlsm (20.9 KB, 55 views)
DataBase's Avatar
DataBase DataBase is offline
Computer Specs
Member with 456 posts.
 
Join Date: Jan 2004
Experience: Intermediate
02-Aug-2012, 01:02 PM #9
I couldnt help myself, had to figure out the password issue, here it is all complete with your original password.
Attached Files
File Type: xlsm Book3.xlsm (21.0 KB, 90 views)
Teacherless's Avatar
Teacherless   (John) Teacherless is offline
Computer Specs
Member with 94 posts.
THREAD STARTER
 
Join Date: Jun 2012
Location: South Coast, South Africa.
Experience: Beginner
03-Aug-2012, 03:48 AM #10
Vba code to enter password
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
File Type: zip Documents.zip (65.3 KB, 31 views)
DataBase's Avatar
DataBase DataBase is offline
Computer Specs
Member with 456 posts.
 
Join Date: Jan 2004
Experience: Intermediate
03-Aug-2012, 04:53 AM #11
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 Thumbnails
Solved: Vba code to enter password-pic-1.jpg   Solved: Vba code to enter password-pic-2.jpg  
Teacherless's Avatar
Teacherless   (John) Teacherless is offline
Computer Specs
Member with 94 posts.
THREAD STARTER
 
Join Date: Jun 2012
Location: South Coast, South Africa.
Experience: Beginner
04-Aug-2012, 02:45 AM #12
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
DataBase's Avatar
DataBase DataBase is offline
Computer Specs
Member with 456 posts.
 
Join Date: Jan 2004
Experience: Intermediate
04-Aug-2012, 05:49 AM #13
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

Last edited by DataBase; 04-Aug-2012 at 06:03 AM..
Teacherless's Avatar
Teacherless   (John) Teacherless is offline
Computer Specs
Member with 94 posts.
THREAD STARTER
 
Join Date: Jun 2012
Location: South Coast, South Africa.
Experience: Beginner
04-Aug-2012, 06:28 AM #14
Vba code to enter password
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?
Attached Files
File Type: zip Documents.zip (109.5 KB, 29 views)
DataBase's Avatar
DataBase DataBase is offline
Computer Specs
Member with 456 posts.
 
Join Date: Jan 2004
Experience: Intermediate
04-Aug-2012, 06:53 AM #15
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 Thumbnails
Solved: Vba code to enter password-p1.jpg  
As Seen On

BBC, Reader's Digest, PC Magazine, Today Show, Money Magazine
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.


(clock)
THIS THREAD HAS EXPIRED.
Are you having the same problem? We have volunteers ready to answer your question, but first you'll have to join for free. Need help getting started? Check out our Welcome Guide.

Search Tech Support Guy

Find the solution to your
computer problem!




Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools


WELCOME
You Are Using: Server ID
Trusted Website Back to the Top ↑

Content Relevant URLs by vBSEO 3.3.2