Open VB Editor

[WoodyHays]

I am using the following code to turn off the VB protection on a project:

Sub UnprotectVBProject()
Dim VBProj As Object
On Error Resume Next
Set VBProj = ActiveWorkbook.VBProject

Application.SendKeys "Range", True
Application.SendKeys "~", True
VBProj.VBE.SelectedVBComponent.Activate
On Error GoTo 0
End Sub

Which works great if the editor is already open, but if the editor is not open it does not work.

How can I open the VB editor with code? It must be simple and I feel silly asking, but it not very apparant to me. Is it something like Application.VBE.open?

Thanks,
Woody Hays

 

[NateO]

Hello Woody, let's stick with the send keys and send an alt+f11 to Excel:

Application.SendKeys "%{f11}"

This will toggle between the VBE and XL, so it really ought to be closed.

To close,

Application.SendKeys "%{f4}"

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-05-20 08:29

 

[WoodyHays]

I am a bit tangled up in the code here. Where should I put the statement to open VB?
If I put it first then it seems to have trouble finding the project to send the password. Do I need to be more specific that ActiveWorkbook.VBProject? All of the pieces work individualy, but when I put them together it bombs.


Dim VBProj As Object
Set VBProj = ActiveWorkbook.VBProject

'open VB
Application.SendKeys "%{f11}"

'send password
Application.SendKeys "Range", True

Application.SendKeys "~", True
VBProj.VBE.SelectedVBComponent.Activate
Application.SendKeys "%{f4}"

Thanks,
Woody Hays

 

[NateO]

Hi Woody, try going to the Macro itself, as below:<pre>
Sub tryThis()
Application.SendKeys "Range", True
Application.SendKeys "~", True
Application.Goto Reference:="tryThis"
Application.SendKeys "%{f4}"
End Sub</pre>
_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-05-20 12:45

 

[WoodyHays]

Nate,
That was some good thinking! It works great!

One final brain teaser for you. After the VBE is closed using SendKeys a user can still access the code without having to enter a pasword. I haven't experimented yet with running it from a seperate routine, but I doubt that will make a difference.

Any ideas how to secure it? I will experiment with it some later tonight, got to go for now.

Thanks,
Woody Hays

 

[WoodyHays]

I never noticed before that once a protected project is opened via the password, that closing the VBE will not turn the protection back on. While the file is still open subsequent access to the project does not require the pssword. The workbook must be closed and reopened to reset the protection.

Is there a way to force the protection to reactivate once the editor is closed, but the file is still open?

I guess Microsoft decided that if the user knows the password there is no need to reset the protection during that session, but if you are controlling access via a procedure that is not the case.

Anybody have any ideas??

Thnaks,
Woody Hays

 

[Ivan F Moala]

On 2002-05-21 03:24, WoodyHays wrote:
I never noticed before that once a protected project is opened via the password, that closing the VBE will not turn the protection back on. While the file is still open subsequent access to the project does not require the pssword. The workbook must be closed and reopened to reset the protection.

Is there a way to force the protection to reactivate once the editor is closed, but the file is still open?

I guess Microsoft decided that if the user knows the password there is no need to reset the protection during that session, but if you are controlling access via a procedure that is not the case.

Anybody have any ideas??

Thnaks,
Woody Hays

No you will have to shut it down and reopen.

 

[WoodyHays]

Thanks Ivan,

I will put my routine in Auto_close.

wgh

 

[Ivan F Moala]

Hi Woody

Just a couple of other things to watch
out for when doing this.

If you have any other projects open and
unprotected, you may have to close down
the projects windows incase the send keys
Hits the wrong project.

I think the original code had this routine in it ?

 

[WoodyHays]

Thankd for the tip Ivan,
I doubt any of my users for the curent application will have VBE open, but future applications will go to differerent groups so this may become an issue. I will fix it.

Thanks,
wgh