Controlling the VBA window

  • Thread starter Thread starter james4177
  • Start date Start date
J

james4177

I have written code that creates some modules and assigns them to
various form events. The problem I'm having is that the VBA window (by
which I mean the VBA IDE) opens while the code is being created. I
don't want my users to see this window.

Any ideas how I can control the VBA window opening during code
execution.

Thanks...
James--
 
I've done some experimenting with this and implemented the
LockWindowUpdate API in the VBA code but it doesn't seem to solve the
problem. The VBE window continues to maximize once I start writing the
code module.

On a positive note I discovered the Spy++ software that comes with
Visual Studio .Net which is used for discovering the class names of
various windows. There is also a version over at:

http://www.codeproject.com/dialog/windowfinder.asp

I'm still tinkering with this and if I find a solution I'll post it.

J--
 
I found an alternative.

If instead of relying on CreateEventProc() you write the code to add
the new code to the approriate form control yourself the VBE window
never shows its ugly head.

Sample code:

Dim testForm As Form
Set testForm = Forms("test")
Dim newMod As Module
Dim LineNum As Long: LineNum = 1

Set newMod = testForm.Module
newMod.InsertLines LineNum, "sub test01_" & "Click()" & Chr(13) &
"Msgbox " & Chr(34) & "It Worked" & Chr(34) & Chr(13) & "end sub"

Forms("test").Controls("test01").OnClick = "[Event Procedure]"

This code assigns a message box that prints "It Worked" to the 'On
Click' event for the test01 command button on the test form and more
importantly it DOES NOT cause the VBE to appear on the screen the way
the following code does:

Dim testForm As Form
Set testForm = Forms("test")
Dim newMod As Module
Dim LineNum As Long

LineNum = newMod.CreateEventProc("Click", testForm.Name)
newMod.InsertLines LineNum, "sub test01_" & "Click()" & Chr(13) &
"Msgbox " & Chr(34) & "It Worked" & Chr(34) & Chr(13) & "end sub"
 
Back
Top