Jamie,

I just tried it, and it does indeed work in Excel 97 and 2003.
Good work!


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Jamie Collins" wrote in message
om...
"Chip Pearson" wrote ...

Jamie,

I can't get the code to work. It will activate the Immediate
window, but won't clear it. I tested in 97, 2002, and 2003.

Chip,
This is very frustrating! I suspect sabotage <g.

Seriously though, I've now tested in Excel97 (Win2K), Excel2000
(Win98, WinXP), Excel2002 (Win2K) and Excel2003 (Win2K, WinXP)
and it
worked every time. It also worked for a colleague on Excel2000
(NT4),
just to prove it was a trick of my real and virtual machines.

In Excel2002 and above, the code as posted ('Excel version'
only) will
cause a run-time error if you have not granted trust access to
Visual
basic project. I wouldn't expect the behavior you describe i.e.
activating but not clearing the Immediate Window so I can't
guess why
it's not working for you.

I'd be grateful if anyone else out there could test my code.
Here is
the Excel version again, with the fix to handle the situation
where
trust access to Visual basic project has not been granted.
Paste the
following code into a standard .bas module in an open workbook,
type
some text into the Immediate Window, optionally close the VBE,
run the
ClearImmediateWindow macro. Does the Immediate Window clear for
you?

Private Declare Function FindWindow _
Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function FindWindowEx _
Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
ByVal lpsz1 As String, _
ByVal lpsz2 As String) As Long
Private Declare Function GetKeyboardState _
Lib "user32" (pbKeyState As Byte) As Long
Private Declare Function SetKeyboardState _
Lib "user32" (lppbKeyState As Byte) As Long
Private Declare Function PostMessage _
Lib "user32" Alias "PostMessageA" ( _
ByVal hwnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, ByVal lParam As Long _
) As Long

Private Const WM_ACTIVATE As Long = &H6
Private Const WM_KEYDOWN As Long = &H100
Private Const VK_CONTROL As Long = &H11
Private Const KEYSTATE_KEYDOWN As Long = &H80

Private m_KeyboardState(0 To 255) As Byte
Private m_hSaveKeystate As Long

Sub ClearImmediateWindow()

Dim hChild As Long
Dim hParent As Long
Dim strCaptionVbe As String

Const CLASS_VBE As String = "wndclass_desked_gsk"
Const CLASS_IMMEDIATE As String = "VbaWindow"
Const CAPTION_IMMEDIATE As String = "Immediate"

' Get handle to Immediate Window
'On Error Resume Next
strCaptionVbe = Excel.Application.VBE.MainWindow.Caption
On Error GoTo 0

hParent = FindWindow(CLASS_VBE, strCaptionVbe)
hChild = FindWindowEx(hParent, ByVal 0&, _
CLASS_IMMEDIATE, CAPTION_IMMEDIATE)

If hChild = 0 Then
MsgBox "Immediate Window not found."
Exit Sub
End If

' Activate Immediate Window
PostMessage hChild, WM_ACTIVATE, 1, 0&

' Simulate depressing of CTRL key
GetKeyboardState m_KeyboardState(0)
m_hSaveKeystate = m_KeyboardState(VK_CONTROL)
m_KeyboardState(VK_CONTROL) = KEYSTATE_KEYDOWN
SetKeyboardState m_KeyboardState(0)
DoEvents

' Send CTRL+A (select all) and Delete keystokes
PostMessage hChild, WM_KEYDOWN, vbKeyA, 0&
PostMessage hChild, WM_KEYDOWN, vbKeyDelete, 0&

' Schedule cleanup code to run
Application.OnTime Now + TimeSerial(0, 0, 0), "DoCleanUp"

End Sub

Sub DoCleanUp()

' Restore keyboard state
GetKeyboardState m_KeyboardState(0)
m_KeyboardState(VK_CONTROL) = m_hSaveKeystate
SetKeyboardState m_KeyboardState(0)

End Sub
'---------------------

Thanks,
Jamie.

--