SendKeys: Enable Trusted Access to VBE

[JTBorton]

I am working on an excel sheet for some not-so-excel-savvy users. They are using varying versions of excel and I need to make sure they all have the correct reference libraries to run the macros. I plan to use the VBE object and set references to the library GUIDs. That way it would set the correct library no matter the version if it is missing. I was tinkering with the idea of using the SendKeys method to gain programming access to the VBE - with user consent of course! The code could then check for the required libraries at startup, then turn back off programming access to the VBE. I have figured out how to enable the access with SendKeys, but it will not acknowledge the granted access until the sub is finished and re-run. Is there a way to get VB to recognize the granted access while still in the sub? The main problem is the Calendar Control library. VB will not even allow the workbook to use macros at all if there is an error here. So i can't end the sub and have the user click something to re-initiate it. I've got to knock it out in round 1. Here is my code, any suggestions?

Sub CheckVBE_Libraries()
Dim AltKey As String
Dim CtrlKey As String
Dim ShiftKey As String
Dim TabKey As String
Dim EnterKey As String

CheckVBE:
    On Error Resume Next
    With Application.VBE
    [highlight]If Err.Number = 1004 Then:[/highlight] GoTo TurnOnVBETrust 
    [b][COLOR=red]Here it continually throws an error even after enabling VBE programming access [/color][/b]
        On Error GoTo 0
        '(DO STUFF HERE)
    End With
    Exit Sub
    
TurnOnVBETrust:
    '--------------------------
    AltKey = "%"
    CtrlKey = "^"
    ShiftKey = "+"
    TabKey = "{TAB}"
    EnterKey = "~"
    '--------------------------
    SendKeys AltKey & "T", False
    '- delay 1 second
    Application.Wait Now + TimeValue("00:00:01")
    SendKeys "M", False
    SendKeys "S", False
    '- delay 1 second
    Application.Wait Now + TimeValue("00:00:01")
    SendKeys "T", False
    SendKeys AltKey & "V", False
    SendKeys "S", False
    SendKeys EnterKey
    GoTo CheckVBE
End Sub

It's hard to test it on my end because, well, the excel sheet works for me. If I unlink the libraries it just comes off with a Object with Block Variable Not Set error. For them it wont even compile.

-JTBorton
Another Day, Another Disaster

 

[JTBorton]

Oh yes, forgot to specify: Excel 2003 Prof. - in case it helps

-JTBorton
Another Day, Another Disaster

 

[JTBorton]

I just had the thought that maybe it was because I had not cleared the error with Err.Clear, but I tried it and the problem still exists.

-JTBorton
Another Day, Another Disaster

 

[JTBorton]

Here's a thought, what if before the sub ends I could designate another macro that will run after a timed delay. Say 3 seconds after. Maybe that will give the application time to updated the new security setting. Any thoughts?

-JTBorton
Another Day, Another Disaster

 

[HughLerwill]

If your code is Excel Version sensitive it suggests you are using early binding somewhere; if so have you considered late binding to avoid the issue.
Maybe some examples of your version sensitive code would help someone help you more.

 

[JTBorton]

HughLerwill

I can't post any examples at the moment, but I'll get them up asap.

As for late binding, I've thought about it, but I didn't think you could late bind a control. I noticed on one computer that I was trying to debug that the calendar controls did not even exist on the forms anymore. When I tried to find the library myself, Microsoft Calendar Control ##.0, it did not even exist on the list. The Microsoft Common Dialog Control 6.0 did not exist either. That's why I considered the GUID route. I got the GUIDs from my computer, and they are bound to be in their system somewhere! Is there another work-around for that? I've been considering creating an active x control in Vb and then importing it into VBA, but I'm afraid that might be more trouble than it's worth.

I've also considered making my own calendar form that pops up when a user clicks on a text box. Text boxes have the MouseDown event, but what if a user tabs over to the box. I have heard that you can create your own events with class subs, but can you create a new event, say a TabOver event, for controls that already exist?

After all, If it isn't broken, it doesn't have enough parts yet.

-JTBorton
Another Day, Another Disaster

 

[HughLerwill]

As you say late binding is not normally applicable to Controls.

You appear to be looking at issues caused by missing or incompatible versions of the ocx files for the Controls, not the Excel version being used.

>and they are bound to be in their system somewhere!

No they are not. Your app has dependencies (the Controls)and you must ensure that their .ocx files are present and registered on all computers on which it is to be run. You can/ must do that manually, or programmatically. Popping a magic 'GUID' number into your code at run time is unlikely to satisfy the requirement for a missing ocx file.

 

[JTBorton]

I'm starting to realize that all my fancy work has gone to waste.

But, for the sake of learning, how would I go about installing the ocx files by code? Installing manually is just a matter of dropping them in the Win32 folder, right?

And, back to the original question, who could I get VBE to recognize the change in trusted access while in code?

-JTBorton
Another Day, Another Disaster

 

[HughLerwill]

>installing the ocx files by code?

You may consider using third party installation software or develop your own for the specific task; noteworthy steps for the latter;

Get admin rights
Is ocx file in host computer's Win32 folder
If Yes
If it is older version than that on setup disk
copy file from setup disk, register it
end if
else
copy file from setup disk, register it
end if
end if

>..dropping them ...

That may work but you may have to register them too; typically from a command line using RegSvr32.

>change in trusted access while in code

You may find a way to do that but I guess the main purpose of the 'Trusted' checkbox is to prevent what you are trying to do.