SendKeys "%{F11}", True

[Stefi]

Hi All,

I tried to use (in XL2003)

SendKeys "%{F11}", True

to open VBE window, but it had no effect. In fact my real aim was

SendKeys "%{F11}%TE+{TAB}{RIGHT}%V{+}{TAB}" & Password & "{TAB}" & Password
& "~%{F11}", True

to set VBA project password. I found out that "%{F11}" part doesn't work. If
I previously open VBE window manually with Alt+F11, the remaining part sets
the password. Any advice?

Thanks,
Stefi

 

[Chip Pearson]

Stefi,

Try code like:

Sub OpenVBProject(WB As Workbook)
Dim VBProj As Object
Dim N As Long
N = 0
With Application.VBE
.MainWindow.Visible = True
For Each VBProj In .VBProjects
N = N + 1
If VBProj.Filename = WB.FullName Then
Set .ActiveVBProject = .VBProjects(N)
SendKeys "{ENTER}"
SendKeys "aa" '<<< YOUR PASSWORD
SendKeys "{ENTER}"
Exit For
End If
End With
Next VBProj
End Sub


You call this from a procedure as:

OpenVBProject Workbooks("Book123.xls")


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Stefi]

Thanks Chip, I immediately start testing, I'll post the result'
Stefi


„Chip Pearson†ezt írta:

 

[Stefi]

Hi Chip,

I found that this form works:

Sub OpenVBProject(WB As Workbook)
Dim VBProj As Object
Dim N As Long
N = 0
Password = "test"
With Application.VBE
.MainWindow.Visible = True
For Each VBProj In .VBProjects
N = N + 1
If VBProj.Filename = WB.FullName Then
Set .ActiveVBProject = .VBProjects(N)
SendKeys "%TE+{TAB}{RIGHT}%V{TAB}" & Password & "{TAB}" &
Password & "~", True
Exit For
End If
Next VBProj
.MainWindow.Visible = False
End With
End Sub

There is a minor question remained: I couldn't make it work when started
OpenVBProject via a hot key (say Ctrl+t). Do you have any explanation?

Many thanks anyway!

Regards,
Stefi


„Chip Pearson†ezt írta:

 

[Chip Pearson]

Do you have any explanation?

Nothing beyond that SendKeys is very unreliable. I would never use it in
commercial code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Stefi]

Well, I made it work via a Commandbutton. I would negligate using Sendkeys
with pleasure, but how else could I achieve my aim (setting VBA project
password)?

Thanks for your assistance!

Best regards,
Stefi

„Chip Pearson†ezt írta:

 

[Chip Pearson]

Unfortunately, there is no programmatic way to modify the security settings
of a VBProject. You can test the setting but not unlock the project.
SendKeys, as poor as it is, is the only way to do it.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Stefi]

I realized that it's a great weakness of VBA (XL2003). Have you heard of any
development in this question in later releases?

Regards,
Stefi


„Chip Pearson†ezt írta:

 

[Chip Pearson]

They haven't touched VBA since 2000 and even then it was a relatively minor
upgrade from 97. I doubt there will be any upgrades to VBA. My guess it that
in a release or two we'll be working in NET.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Stefi]

See you later in the brave new NET world!
Best regards,
Stefi


„Chip Pearson†ezt írta: