Open VB Editor

W

WoodyHays

Board Regular
Joined
May 14, 2002
Messages
185
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
Click to expand...

No you will have to shut it down and reopen.
 
Upvote 0
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 ?
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
File to open hidden by macro only, but visible when opened through conventional means?
Replies
5
Views
156
kymmeeh
K
G
  • Question
Select an Open workbook and apply a macro1 contained in the new book to the new book
Replies
1
Views
222
LazyBug
LazyBug
R
  • Question
Three-lane Stopwatch?
Replies
7
Views
322
RaymondW
R
G
  • Question
Email from Excel query
Replies
0
Views
235
gmazza76
G
J
  • Question
VBA Excel / Word - Want to Fix "Run Coding and received Security Notice"
Replies
1
Views
289
NdNoviceHlp
NdNoviceHlp

Forum statistics

Threads
1,216,774
Messages
6,132,649
Members
449,740
Latest member
Stevejhonsy

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top