[Teacherless]

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]

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]

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

[DataBase]

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]

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]

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

[DataBase]

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.

[DataBase]

Please note i have just uploaded another wokbook, the previous one had an error.

[DataBase]

I couldnt help myself, had to figure out the password issue, here it is all complete with your original password.

[Teacherless]

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

[DataBase]

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.

[Teacherless]

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]

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

[Teacherless]

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?

[DataBase]

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.