In Excel 2003 and below the following code worked great to position a form
next to the active cell.
With 2007 the line:
hWndXLChart = FindWindowEx(hWndXLDesk, 0&, "EXCELE", vbNullString)
returns 0.

I assume it's because of the new charting functionality in 2007. Does
anyone know a way to get that line working?

Thanks,
Jeremy



-------------------------------------------------------------------------------------------
Public ActiveForm As String

Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long

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

Declare Function GetWindowRect Lib "user32" _
(ByVal HWND As Long, _
lpRect As RECT) As Long

Declare Function GetDC Lib "user32" _
(ByVal HWND As Long) As Long

Declare Function ReleaseDC Lib "user32" _
(ByVal HWND As Long, _
ByVal hdc As Long) As Long

Declare Function GetDeviceCaps Lib "gdi32" _
(ByVal hdc As Long, _
ByVal nIndex As Long) As Long

Const HWNDDESKTOP As Long = 0
Const LOGPIXELSX As Long = 88
Const LOGPIXELSY As Long = 90

Private Type RECT
Left As Long
Top As Long
Right As Long
Bottom As Long
End Type

Sub ShowAtCell()
Dim DC As Long
Dim WinFont As Integer
Dim ZoomFactor As Single
Dim winRect As RECT
Dim hWndXL As Long
Dim hWndXLDesk As Long
Dim hWndXLChart As Long
Dim ChtObj As ChartObject
Dim TargetRange As Range


OutlierForm.Hide
Set TargetRange = ActiveCell.Offset(0, 1)

Set ChtObj = ActiveSheet.ChartObjects.Add(0, 0, 20, 20)
With ChtObj
.Top = TargetRange.Top
.Left = TargetRange.Left
.Activate
End With

hWndXL = FindWindow("XLMAIN", Application.Caption)
hWndXLDesk = FindWindowEx(hWndXL, 0&, "XLDESK", vbNullString)
hWndXLChart = FindWindowEx(hWndXLDesk, 0&, "EXCELE", vbNullString)
GetWindowRect hWndXLChart, winRect
ChtObj.Delete

DC = GetDC(HWNDDESKTOP)
WinFont = GetDeviceCaps(DC, LOGPIXELSX)
ReleaseDC HWNDDESKTOP, DC

ZoomFactor = (ActiveWindow.Zoom - 100) * 0.005

With OutlierForm
.StartUpPosition = 0
.Top = (winRect.Top * 72 / WinFont) + ZoomFactor
.Left = (winRect.Left * 72 / WinFont) + ZoomFactor
If Workbooks(Mgr_File).ReadOnly Then .Caption = "Outlier Explanation
[Read Only]"
.Show
End With

End Sub
-------------------------------------------------------------------------------------------

The Excel class name in 2007 seems to be NetUIHWND instead of XLMAIN now.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"GollyJer" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> In Excel 2003 and below the following code worked great to position a form
> next to the active cell.
> With 2007 the line:
> hWndXLChart = FindWindowEx(hWndXLDesk, 0&, "EXCELE", vbNullString)
> returns 0.
>
> I assume it's because of the new charting functionality in 2007. Does
> anyone know a way to get that line working?
>
> Thanks,
> Jeremy
>
>
>
> -------------------------------------------------------------------------------------------
> Public ActiveForm As String
>
> Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
> (ByVal lpClassName As String, _
> ByVal lpWindowName As String) As Long
>
> 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
>
> Declare Function GetWindowRect Lib "user32" _
> (ByVal HWND As Long, _
> lpRect As RECT) As Long
>
> Declare Function GetDC Lib "user32" _
> (ByVal HWND As Long) As Long
>
> Declare Function ReleaseDC Lib "user32" _
> (ByVal HWND As Long, _
> ByVal hdc As Long) As Long
>
> Declare Function GetDeviceCaps Lib "gdi32" _
> (ByVal hdc As Long, _
> ByVal nIndex As Long) As Long
>
> Const HWNDDESKTOP As Long = 0
> Const LOGPIXELSX As Long = 88
> Const LOGPIXELSY As Long = 90
>
> Private Type RECT
> Left As Long
> Top As Long
> Right As Long
> Bottom As Long
> End Type
>
> Sub ShowAtCell()
> Dim DC As Long
> Dim WinFont As Integer
> Dim ZoomFactor As Single
> Dim winRect As RECT
> Dim hWndXL As Long
> Dim hWndXLDesk As Long
> Dim hWndXLChart As Long
> Dim ChtObj As ChartObject
> Dim TargetRange As Range
>
>
> OutlierForm.Hide
> Set TargetRange = ActiveCell.Offset(0, 1)
>
> Set ChtObj = ActiveSheet.ChartObjects.Add(0, 0, 20, 20)
> With ChtObj
> .Top = TargetRange.Top
> .Left = TargetRange.Left
> .Activate
> End With
>
> hWndXL = FindWindow("XLMAIN", Application.Caption)
> hWndXLDesk = FindWindowEx(hWndXL, 0&, "XLDESK", vbNullString)
> hWndXLChart = FindWindowEx(hWndXLDesk, 0&, "EXCELE", vbNullString)
> GetWindowRect hWndXLChart, winRect
> ChtObj.Delete
>
> DC = GetDC(HWNDDESKTOP)
> WinFont = GetDeviceCaps(DC, LOGPIXELSX)
> ReleaseDC HWNDDESKTOP, DC
>
> ZoomFactor = (ActiveWindow.Zoom - 100) * 0.005
>
> With OutlierForm
> .StartUpPosition = 0
> .Top = (winRect.Top * 72 / WinFont) + ZoomFactor
> .Left = (winRect.Left * 72 / WinFont) + ZoomFactor
> If Workbooks(Mgr_File).ReadOnly Then .Caption = "Outlier Explanation
> [Read Only]"
> .Show
> End With
>
> End Sub
> -------------------------------------------------------------------------------------------
>
>
>

Bob,
Thanks for the help.

The call to XLMAIN:
hWndXL = FindWindow("XLMAIN", Application.Caption)
seems to still work.

It's the call to the chart ("EXCELE") that doesn't return anything:
hWndXLChart = FindWindowEx(hWndXLDesk, 0&, "EXCELE", vbNullString)

Thanks,
Jeremy


Bob Phillips wrote:
> The Excel class name in 2007 seems to be NetUIHWND instead of XLMAIN
> now.
>
> "GollyJer" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> In Excel 2003 and below the following code worked great to position
>> a form next to the active cell.
>> With 2007 the line:
>> hWndXLChart = FindWindowEx(hWndXLDesk, 0&, "EXCELE", vbNullString)
>> returns 0.
>>
>> I assume it's because of the new charting functionality in 2007. Does
>> anyone know a way to get that line working?
>>
>> Thanks,
>> Jeremy
>>
>>
>>
>> -------------------------------------------------------------------------------------------
>> Public ActiveForm As String
>>
>> Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
>> (ByVal lpClassName As String, _
>> ByVal lpWindowName As String) As Long
>>
>> 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
>>
>> Declare Function GetWindowRect Lib "user32" _
>> (ByVal HWND As Long, _
>> lpRect As RECT) As Long
>>
>> Declare Function GetDC Lib "user32" _
>> (ByVal HWND As Long) As Long
>>
>> Declare Function ReleaseDC Lib "user32" _
>> (ByVal HWND As Long, _
>> ByVal hdc As Long) As Long
>>
>> Declare Function GetDeviceCaps Lib "gdi32" _
>> (ByVal hdc As Long, _
>> ByVal nIndex As Long) As Long
>>
>> Const HWNDDESKTOP As Long = 0
>> Const LOGPIXELSX As Long = 88
>> Const LOGPIXELSY As Long = 90
>>
>> Private Type RECT
>> Left As Long
>> Top As Long
>> Right As Long
>> Bottom As Long
>> End Type
>>
>> Sub ShowAtCell()
>> Dim DC As Long
>> Dim WinFont As Integer
>> Dim ZoomFactor As Single
>> Dim winRect As RECT
>> Dim hWndXL As Long
>> Dim hWndXLDesk As Long
>> Dim hWndXLChart As Long
>> Dim ChtObj As ChartObject
>> Dim TargetRange As Range
>>
>>
>> OutlierForm.Hide
>> Set TargetRange = ActiveCell.Offset(0, 1)
>>
>> Set ChtObj = ActiveSheet.ChartObjects.Add(0, 0, 20, 20)
>> With ChtObj
>> .Top = TargetRange.Top
>> .Left = TargetRange.Left
>> .Activate
>> End With
>>
>> hWndXL = FindWindow("XLMAIN", Application.Caption)
>> hWndXLDesk = FindWindowEx(hWndXL, 0&, "XLDESK", vbNullString)
>> hWndXLChart = FindWindowEx(hWndXLDesk, 0&, "EXCELE", vbNullString)
>> GetWindowRect hWndXLChart, winRect
>> ChtObj.Delete
>>
>> DC = GetDC(HWNDDESKTOP)
>> WinFont = GetDeviceCaps(DC, LOGPIXELSX)
>> ReleaseDC HWNDDESKTOP, DC
>>
>> ZoomFactor = (ActiveWindow.Zoom - 100) * 0.005
>>
>> With OutlierForm
>> .StartUpPosition = 0
>> .Top = (winRect.Top * 72 / WinFont) + ZoomFactor
>> .Left = (winRect.Left * 72 / WinFont) + ZoomFactor
>> If Workbooks(Mgr_File).ReadOnly Then .Caption = "Outlier
>> Explanation [Read Only]"
>> .Show
>> End With
>>
>> End Sub
>> -------------------------------------------------------------------------------------------

Hi Jeremy,

If you don't have Spy++ or equivalent, see if you can find your chartobject
window with the code below.

Run Test2 (at the bottom) and look for differences between the two calls to
'MySpy', without then with a chartobject window. In particular look for
"EXCELE" or if not found "SheetName SpyChart".

Assuming you find the right window you'll need to work back up the tree to
Excel's main window and start from there to find your activated
chartobject's chart window. Then for your purposes 'GetWindowRect' to return
its coordinates.

If the two calls to MySpy don't show any differences, change -
MySpy ActiveSheet, False > True ( in both calls) to get all windows

Even in pre XL2007 some of Excel's windows are at the same 'level' as XLMAIN
and so would need to search from the desktop, or perhaps even from some
other window.

There's no error handling in the code. The array ArrWins is dimensioned to
accommodate 10000 windows. That's way more than enough for me but if your
system has more you'll need to increase it (unlikely necessary if passing
bDesktop as False to only get Excel's windows).

Regards,
Peter T


'''''''''''''''''''''''''''''''''''''''''''
' MySpy - Obtain Window details
' Based on Stephen Bullen's EnumDlg.xls (.zip)
' http://www.oaltd.co.uk/Excel/Default.htm
' although heavily adapted intrinsically it's the same
' see Stephen's original for comments
' whilst there see the link for 'Professional Excel Development'
'
' pmbthornton gmail com

Option Explicit

Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Declare Function GetWindow Lib "user32" ( _
ByVal hwnd As Long, ByVal wCmd As Long) As Long
Declare Function GetClassName Lib "user32" Alias "GetClassNameA" ( _
ByVal hwnd As Long, ByVal lpClassName As String, _
ByVal nMaxCount As Long) As Long
Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" ( _
ByVal hwnd As Long, ByVal lpString As String, _
ByVal cch As Long) As Long

Public Const GW_HWNDFIRST = 0
'Public Const GW_HWNDLAST = 1
Public Const GW_HWNDNEXT = 2
'Public Const GW_HWNDPREV = 3
'Public Const GW_OWNER = 4
Public Const GW_CHILD = 5
'Public Const GW_MAX = 5

Sub Test()

MySpy ActiveSheet, True

ActiveSheet.Cells.Find("XLMAIN").Select

End Sub

Sub MySpy(mSht As Worksheet, bDesktop As Boolean)
Dim hWindFirst As Long
Dim nRow As Long, nCol As Long
Dim nMaxCols As Long

ReDim ArrWins(1 To 10000, 1 To 3) ' increase if +10000 windows

If Application.Version > 9 Then
hWindFirst = Application.hwnd
Else
hWindFirst = FindWindow("XLMAIN", Application.Caption)
End If

If bDesktop = True Then
hWindFirst = GetWindow(hWindFirst, GW_HWNDFIRST)
End If

nRow = 1
nCol = 1

GetChildWindows 0, hWindFirst, ArrWins, nRow, nCol, nMaxCols, bDesktop

With mSht
.Range("A1").CurrentRegion.Clear
.Range(.Cells(1, 1), .Cells(nRow, nMaxCols)) = ArrWins
nRow = .UsedRange.Rows.Count '
End With

End Sub

Sub GetChildWindows(hParent As Long, hChild As Long, _
ArrWins(), nRow As Long, nCol As Long, _
nMaxCols, bDesktop As Boolean)
Dim sBuff As String * 128
Dim hwnNext As Long

If nCol + 2 > nMaxCols Then nMaxCols = nCol + 2
If nMaxCols > UBound(ArrWins, 2) Then
ReDim Preserve ArrWins(1 To UBound(ArrWins), 1 To nMaxCols)
End If

ArrWins(nRow, nCol) = hChild

Call GetClassName(hChild, sBuff, 128)
ArrWins(nRow, nCol + 1) = TrimBuffer(sBuff)

Call GetWindowText(hChild, sBuff, 128)
ArrWins(nRow, nCol + 2) = TrimBuffer(sBuff)

nRow = nRow + 1

hwnNext = GetWindow(hChild, GW_CHILD)

If hwnNext <> 0 Then
GetChildWindows hChild, hwnNext, ArrWins, nRow, nCol + 1, _
nMaxCols, bDesktop
End If

If hParent <> 0 Or bDesktop = True Then
hwnNext = GetWindow(hChild, GW_HWNDNEXT)
If hwnNext = 0 Then
Exit Sub
Else
GetChildWindows hParent, hwnNext, ArrWins, nRow, nCol, _
nMaxCols, bDesktop
End If
End If

End Sub

Public Function TrimBuffer(ByVal strIn As String) As String
Dim nPos As Long
nPos = InStr(1, strIn, vbNullChar, vbTextCompare)
If nPos > 0 Then
TrimBuffer = Left(strIn, nPos - 1)
Else
TrimBuffer = strIn
End If
End Function

Sub Test2()
Dim chtObj As ChartObject

MySpy ActiveSheet, False

ActiveSheet.Range("A1").CurrentRegion.Columns.Insert

On Error Resume Next
Set chtObj = ActiveSheet.ChartObjects("SpyChart")
On Error GoTo 0
With ActiveSheet.Range("D2:E4")
If chtObj Is Nothing Then
Set chtObj = .Parent.ChartObjects.Add( _
.Left, .Top, .Width, .Height)
chtObj.Name = "SpyChart"
Else
chtObj.Left = .Left
chtObj.Top = .Top
chtObj.Width = .Width
chtObj.Height = .Height
'.Visible = True
End If
End With
chtObj.Activate

MySpy ActiveSheet, False

' delete or keep the chart for future use
'chtObj.Delete
Range("A1").Select
chtObj.Visible = False

End Sub

Peter T

Peter,
Thanks for the help. I tried your code as well as a program called
WinSpector (http://www.windows-spy.com). It seems the chart object is
completely hidden from the windows shell. I certainly am not seeing it.

-Jeremy

Peter T wrote:
> Hi Jeremy,
>
> If you don't have Spy++ or equivalent, see if you can find your
> chartobject window with the code below.
>
> Run Test2 (at the bottom) and look for differences between the two
> calls to 'MySpy', without then with a chartobject window. In
> particular look for "EXCELE" or if not found "SheetName SpyChart".
>
> Assuming you find the right window you'll need to work back up the
> tree to Excel's main window and start from there to find your
> activated chartobject's chart window. Then for your purposes
> 'GetWindowRect' to return its coordinates.
>
> If the two calls to MySpy don't show any differences, change -
> MySpy ActiveSheet, False > True ( in both calls) to get all windows
>
> Even in pre XL2007 some of Excel's windows are at the same 'level' as
> XLMAIN and so would need to search from the desktop, or perhaps even
> from some other window.
>
> There's no error handling in the code. The array ArrWins is
> dimensioned to accommodate 10000 windows. That's way more than enough
> for me but if your system has more you'll need to increase it
> (unlikely necessary if passing bDesktop as False to only get Excel's
> windows).
>
> Regards,
> Peter T
>
>
> '''''''''''''''''''''''''''''''''''''''''''
> ' MySpy - Obtain Window details
> ' Based on Stephen Bullen's EnumDlg.xls (.zip)
> ' http://www.oaltd.co.uk/Excel/Default.htm
> ' although heavily adapted intrinsically it's the same
> ' see Stephen's original for comments
> ' whilst there see the link for 'Professional Excel Development'
> '
> ' pmbthornton gmail com
>
> Option Explicit
>
> Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
> ByVal lpClassName As String, ByVal lpWindowName As String) As Long
> Declare Function GetWindow Lib "user32" ( _
> ByVal hwnd As Long, ByVal wCmd As Long) As Long
> Declare Function GetClassName Lib "user32" Alias "GetClassNameA" ( _
> ByVal hwnd As Long, ByVal lpClassName As String, _
> ByVal nMaxCount As Long) As Long
> Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" ( _
> ByVal hwnd As Long, ByVal lpString As String, _
> ByVal cch As Long) As Long
>
> Public Const GW_HWNDFIRST = 0
> 'Public Const GW_HWNDLAST = 1
> Public Const GW_HWNDNEXT = 2
> 'Public Const GW_HWNDPREV = 3
> 'Public Const GW_OWNER = 4
> Public Const GW_CHILD = 5
> 'Public Const GW_MAX = 5
>
> Sub Test()
>
> MySpy ActiveSheet, True
>
> ActiveSheet.Cells.Find("XLMAIN").Select
>
> End Sub
>
> Sub MySpy(mSht As Worksheet, bDesktop As Boolean)
> Dim hWindFirst As Long
> Dim nRow As Long, nCol As Long
> Dim nMaxCols As Long
>
> ReDim ArrWins(1 To 10000, 1 To 3) ' increase if +10000 windows
>
> If Application.Version > 9 Then
> hWindFirst = Application.hwnd
> Else
> hWindFirst = FindWindow("XLMAIN", Application.Caption)
> End If
>
> If bDesktop = True Then
> hWindFirst = GetWindow(hWindFirst, GW_HWNDFIRST)
> End If
>
> nRow = 1
> nCol = 1
>
> GetChildWindows 0, hWindFirst, ArrWins, nRow, nCol, nMaxCols,
> bDesktop
>
> With mSht
> .Range("A1").CurrentRegion.Clear
> .Range(.Cells(1, 1), .Cells(nRow, nMaxCols)) = ArrWins
> nRow = .UsedRange.Rows.Count '
> End With
>
> End Sub
>
> Sub GetChildWindows(hParent As Long, hChild As Long, _
> ArrWins(), nRow As Long, nCol As Long, _
> nMaxCols, bDesktop As Boolean)
> Dim sBuff As String * 128
> Dim hwnNext As Long
>
> If nCol + 2 > nMaxCols Then nMaxCols = nCol + 2
> If nMaxCols > UBound(ArrWins, 2) Then
> ReDim Preserve ArrWins(1 To UBound(ArrWins), 1 To nMaxCols)
> End If
>
> ArrWins(nRow, nCol) = hChild
>
> Call GetClassName(hChild, sBuff, 128)
> ArrWins(nRow, nCol + 1) = TrimBuffer(sBuff)
>
> Call GetWindowText(hChild, sBuff, 128)
> ArrWins(nRow, nCol + 2) = TrimBuffer(sBuff)
>
> nRow = nRow + 1
>
> hwnNext = GetWindow(hChild, GW_CHILD)
>
> If hwnNext <> 0 Then
> GetChildWindows hChild, hwnNext, ArrWins, nRow, nCol + 1, _
> nMaxCols, bDesktop
> End If
>
> If hParent <> 0 Or bDesktop = True Then
> hwnNext = GetWindow(hChild, GW_HWNDNEXT)
> If hwnNext = 0 Then
> Exit Sub
> Else
> GetChildWindows hParent, hwnNext, ArrWins, nRow, nCol, _
> nMaxCols, bDesktop
> End If
> End If
>
> End Sub
>
> Public Function TrimBuffer(ByVal strIn As String) As String
> Dim nPos As Long
> nPos = InStr(1, strIn, vbNullChar, vbTextCompare)
> If nPos > 0 Then
> TrimBuffer = Left(strIn, nPos - 1)
> Else
> TrimBuffer = strIn
> End If
> End Function
>
> Sub Test2()
> Dim chtObj As ChartObject
>
> MySpy ActiveSheet, False
>
> ActiveSheet.Range("A1").CurrentRegion.Columns.Insert
>
> On Error Resume Next
> Set chtObj = ActiveSheet.ChartObjects("SpyChart")
> On Error GoTo 0
> With ActiveSheet.Range("D2:E4")
> If chtObj Is Nothing Then
> Set chtObj = .Parent.ChartObjects.Add( _
> .Left, .Top, .Width, .Height)
> chtObj.Name = "SpyChart"
> Else
> chtObj.Left = .Left
> chtObj.Top = .Top
> chtObj.Width = .Width
> chtObj.Height = .Height
> '.Visible = True
> End If
> End With
> chtObj.Activate
>
> MySpy ActiveSheet, False
>
> ' delete or keep the chart for future use
> 'chtObj.Delete
> Range("A1").Select
> chtObj.Visible = False
>
> End Sub
>
> Peter T

Jeremy,
According to:
http://www.awprofessional.com/articl...&seqNum=3&rl=1
"EXCELE
A window used to provide in-sheet editing of embedded charts."

Not sure of the significance of "editing" in the above, but I see no hwnd
exposed for embedded charts from Spy++.

NickHK

"GollyJer" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Peter,
> Thanks for the help. I tried your code as well as a program called
> WinSpector (http://www.windows-spy.com). It seems the chart object is
> completely hidden from the windows shell. I certainly am not seeing it.
>
> -Jeremy
>
> Peter T wrote:
> > Hi Jeremy,
> >
> > If you don't have Spy++ or equivalent, see if you can find your
> > chartobject window with the code below.
> >
> > Run Test2 (at the bottom) and look for differences between the two
> > calls to 'MySpy', without then with a chartobject window. In
> > particular look for "EXCELE" or if not found "SheetName SpyChart".
> >
> > Assuming you find the right window you'll need to work back up the
> > tree to Excel's main window and start from there to find your
> > activated chartobject's chart window. Then for your purposes
> > 'GetWindowRect' to return its coordinates.
> >
> > If the two calls to MySpy don't show any differences, change -
> > MySpy ActiveSheet, False > True ( in both calls) to get all windows
> >
> > Even in pre XL2007 some of Excel's windows are at the same 'level' as
> > XLMAIN and so would need to search from the desktop, or perhaps even
> > from some other window.
> >
> > There's no error handling in the code. The array ArrWins is
> > dimensioned to accommodate 10000 windows. That's way more than enough
> > for me but if your system has more you'll need to increase it
> > (unlikely necessary if passing bDesktop as False to only get Excel's
> > windows).
> >
> > Regards,
> > Peter T
> >
> >
> > '''''''''''''''''''''''''''''''''''''''''''
> > ' MySpy - Obtain Window details
> > ' Based on Stephen Bullen's EnumDlg.xls (.zip)
> > ' http://www.oaltd.co.uk/Excel/Default.htm
> > ' although heavily adapted intrinsically it's the same
> > ' see Stephen's original for comments
> > ' whilst there see the link for 'Professional Excel Development'
> > '
> > ' pmbthornton gmail com
> >
> > Option Explicit
> >
> > Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
> > ByVal lpClassName As String, ByVal lpWindowName As String) As Long
> > Declare Function GetWindow Lib "user32" ( _
> > ByVal hwnd As Long, ByVal wCmd As Long) As Long
> > Declare Function GetClassName Lib "user32" Alias "GetClassNameA" ( _
> > ByVal hwnd As Long, ByVal lpClassName As String, _
> > ByVal nMaxCount As Long) As Long
> > Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" ( _
> > ByVal hwnd As Long, ByVal lpString As String, _
> > ByVal cch As Long) As Long
> >
> > Public Const GW_HWNDFIRST = 0
> > 'Public Const GW_HWNDLAST = 1
> > Public Const GW_HWNDNEXT = 2
> > 'Public Const GW_HWNDPREV = 3
> > 'Public Const GW_OWNER = 4
> > Public Const GW_CHILD = 5
> > 'Public Const GW_MAX = 5
> >
> > Sub Test()
> >
> > MySpy ActiveSheet, True
> >
> > ActiveSheet.Cells.Find("XLMAIN").Select
> >
> > End Sub
> >
> > Sub MySpy(mSht As Worksheet, bDesktop As Boolean)
> > Dim hWindFirst As Long
> > Dim nRow As Long, nCol As Long
> > Dim nMaxCols As Long
> >
> > ReDim ArrWins(1 To 10000, 1 To 3) ' increase if +10000 windows
> >
> > If Application.Version > 9 Then
> > hWindFirst = Application.hwnd
> > Else
> > hWindFirst = FindWindow("XLMAIN", Application.Caption)
> > End If
> >
> > If bDesktop = True Then
> > hWindFirst = GetWindow(hWindFirst, GW_HWNDFIRST)
> > End If
> >
> > nRow = 1
> > nCol = 1
> >
> > GetChildWindows 0, hWindFirst, ArrWins, nRow, nCol, nMaxCols,
> > bDesktop
> >
> > With mSht
> > .Range("A1").CurrentRegion.Clear
> > .Range(.Cells(1, 1), .Cells(nRow, nMaxCols)) = ArrWins
> > nRow = .UsedRange.Rows.Count '
> > End With
> >
> > End Sub
> >
> > Sub GetChildWindows(hParent As Long, hChild As Long, _
> > ArrWins(), nRow As Long, nCol As Long, _
> > nMaxCols, bDesktop As Boolean)
> > Dim sBuff As String * 128
> > Dim hwnNext As Long
> >
> > If nCol + 2 > nMaxCols Then nMaxCols = nCol + 2
> > If nMaxCols > UBound(ArrWins, 2) Then
> > ReDim Preserve ArrWins(1 To UBound(ArrWins), 1 To nMaxCols)
> > End If
> >
> > ArrWins(nRow, nCol) = hChild
> >
> > Call GetClassName(hChild, sBuff, 128)
> > ArrWins(nRow, nCol + 1) = TrimBuffer(sBuff)
> >
> > Call GetWindowText(hChild, sBuff, 128)
> > ArrWins(nRow, nCol + 2) = TrimBuffer(sBuff)
> >
> > nRow = nRow + 1
> >
> > hwnNext = GetWindow(hChild, GW_CHILD)
> >
> > If hwnNext <> 0 Then
> > GetChildWindows hChild, hwnNext, ArrWins, nRow, nCol + 1, _
> > nMaxCols, bDesktop
> > End If
> >
> > If hParent <> 0 Or bDesktop = True Then
> > hwnNext = GetWindow(hChild, GW_HWNDNEXT)
> > If hwnNext = 0 Then
> > Exit Sub
> > Else
> > GetChildWindows hParent, hwnNext, ArrWins, nRow, nCol, _
> > nMaxCols, bDesktop
> > End If
> > End If
> >
> > End Sub
> >
> > Public Function TrimBuffer(ByVal strIn As String) As String
> > Dim nPos As Long
> > nPos = InStr(1, strIn, vbNullChar, vbTextCompare)
> > If nPos > 0 Then
> > TrimBuffer = Left(strIn, nPos - 1)
> > Else
> > TrimBuffer = strIn
> > End If
> > End Function
> >
> > Sub Test2()
> > Dim chtObj As ChartObject
> >
> > MySpy ActiveSheet, False
> >
> > ActiveSheet.Range("A1").CurrentRegion.Columns.Insert
> >
> > On Error Resume Next
> > Set chtObj = ActiveSheet.ChartObjects("SpyChart")
> > On Error GoTo 0
> > With ActiveSheet.Range("D2:E4")
> > If chtObj Is Nothing Then
> > Set chtObj = .Parent.ChartObjects.Add( _
> > .Left, .Top, .Width, .Height)
> > chtObj.Name = "SpyChart"
> > Else
> > chtObj.Left = .Left
> > chtObj.Top = .Top
> > chtObj.Width = .Width
> > chtObj.Height = .Height
> > '.Visible = True
> > End If
> > End With
> > chtObj.Activate
> >
> > MySpy ActiveSheet, False
> >
> > ' delete or keep the chart for future use
> > 'chtObj.Delete
> > Range("A1").Select
> > chtObj.Visible = False
> >
> > End Sub
> >
> > Peter T
>
>

Hi Nick,

> Not sure of the significance of "editing" in the above, but I see no hwnd
> exposed for embedded charts from Spy++.

The significance of "editing" is the embedded chart must be activated and
selected. Then it's own window (class-name "EXCELE") is created. In an
instance of Excel there can only ever be one activated embedded chart. This
is same for all versions XL8-11. I don't have 2007 but would have assumed
some new window is created, though perhaps not EXCELE, and would have
expected the sample code I posted to show different sets of windows, firstly
without an activated chart then with.

I always see an activated embedded chart's window in Spy++, normally the
first window under "XLDESK", did you actually select the chart, say the
chart-area.

Jeremy - running Test2() was there really no difference in the dumps to
cells between the two calls to MySpy. If not, on a new run Test(), add an
embedded chart and select it, insert enough columns so as not to write over
cells and run Test() again. Compare the two sets of windows' data.

Regards,
Peter T



"NickHK" <(E-Mail Removed)> wrote in message
news:#c16$(E-Mail Removed)...
> Jeremy,
> According to:
> http://www.awprofessional.com/articl...&seqNum=3&rl=1
> "EXCELE
> A window used to provide in-sheet editing of embedded charts."
>
> Not sure of the significance of "editing" in the above, but I see no hwnd
> exposed for embedded charts from Spy++.
>
> NickHK
>
> "GollyJer" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > Peter,
> > Thanks for the help. I tried your code as well as a program called
> > WinSpector (http://www.windows-spy.com). It seems the chart object is
> > completely hidden from the windows shell. I certainly am not seeing it.
> >
> > -Jeremy
> >
> > Peter T wrote:
> > > Hi Jeremy,
> > >
> > > If you don't have Spy++ or equivalent, see if you can find your
> > > chartobject window with the code below.
> > >
> > > Run Test2 (at the bottom) and look for differences between the two
> > > calls to 'MySpy', without then with a chartobject window. In
> > > particular look for "EXCELE" or if not found "SheetName SpyChart".
> > >
> > > Assuming you find the right window you'll need to work back up the
> > > tree to Excel's main window and start from there to find your
> > > activated chartobject's chart window. Then for your purposes
> > > 'GetWindowRect' to return its coordinates.
> > >
> > > If the two calls to MySpy don't show any differences, change -
> > > MySpy ActiveSheet, False > True ( in both calls) to get all windows
> > >
> > > Even in pre XL2007 some of Excel's windows are at the same 'level' as
> > > XLMAIN and so would need to search from the desktop, or perhaps even
> > > from some other window.
> > >
> > > There's no error handling in the code. The array ArrWins is
> > > dimensioned to accommodate 10000 windows. That's way more than enough
> > > for me but if your system has more you'll need to increase it
> > > (unlikely necessary if passing bDesktop as False to only get Excel's
> > > windows).
> > >
> > > Regards,
> > > Peter T
> > >
> > >
> > > '''''''''''''''''''''''''''''''''''''''''''
> > > ' MySpy - Obtain Window details
> > > ' Based on Stephen Bullen's EnumDlg.xls (.zip)
> > > ' http://www.oaltd.co.uk/Excel/Default.htm
> > > ' although heavily adapted intrinsically it's the same
> > > ' see Stephen's original for comments
> > > ' whilst there see the link for 'Professional Excel Development'
> > > '
> > > ' pmbthornton gmail com
> > >
> > > Option Explicit
> > >
> > > Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
> > > ByVal lpClassName As String, ByVal lpWindowName As String) As Long
> > > Declare Function GetWindow Lib "user32" ( _
> > > ByVal hwnd As Long, ByVal wCmd As Long) As Long
> > > Declare Function GetClassName Lib "user32" Alias "GetClassNameA" ( _
> > > ByVal hwnd As Long, ByVal lpClassName As String, _
> > > ByVal nMaxCount As Long) As Long
> > > Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA" ( _
> > > ByVal hwnd As Long, ByVal lpString As String, _
> > > ByVal cch As Long) As Long
> > >
> > > Public Const GW_HWNDFIRST = 0
> > > 'Public Const GW_HWNDLAST = 1
> > > Public Const GW_HWNDNEXT = 2
> > > 'Public Const GW_HWNDPREV = 3
> > > 'Public Const GW_OWNER = 4
> > > Public Const GW_CHILD = 5
> > > 'Public Const GW_MAX = 5
> > >
> > > Sub Test()
> > >
> > > MySpy ActiveSheet, True
> > >
> > > ActiveSheet.Cells.Find("XLMAIN").Select
> > >
> > > End Sub
> > >
> > > Sub MySpy(mSht As Worksheet, bDesktop As Boolean)
> > > Dim hWindFirst As Long
> > > Dim nRow As Long, nCol As Long
> > > Dim nMaxCols As Long
> > >
> > > ReDim ArrWins(1 To 10000, 1 To 3) ' increase if +10000 windows
> > >
> > > If Application.Version > 9 Then
> > > hWindFirst = Application.hwnd
> > > Else
> > > hWindFirst = FindWindow("XLMAIN", Application.Caption)
> > > End If
> > >
> > > If bDesktop = True Then
> > > hWindFirst = GetWindow(hWindFirst, GW_HWNDFIRST)
> > > End If
> > >
> > > nRow = 1
> > > nCol = 1
> > >
> > > GetChildWindows 0, hWindFirst, ArrWins, nRow, nCol, nMaxCols,
> > > bDesktop
> > >
> > > With mSht
> > > .Range("A1").CurrentRegion.Clear
> > > .Range(.Cells(1, 1), .Cells(nRow, nMaxCols)) = ArrWins
> > > nRow = .UsedRange.Rows.Count '
> > > End With
> > >
> > > End Sub
> > >
> > > Sub GetChildWindows(hParent As Long, hChild As Long, _
> > > ArrWins(), nRow As Long, nCol As Long, _
> > > nMaxCols, bDesktop As Boolean)
> > > Dim sBuff As String * 128
> > > Dim hwnNext As Long
> > >
> > > If nCol + 2 > nMaxCols Then nMaxCols = nCol + 2
> > > If nMaxCols > UBound(ArrWins, 2) Then
> > > ReDim Preserve ArrWins(1 To UBound(ArrWins), 1 To nMaxCols)
> > > End If
> > >
> > > ArrWins(nRow, nCol) = hChild
> > >
> > > Call GetClassName(hChild, sBuff, 128)
> > > ArrWins(nRow, nCol + 1) = TrimBuffer(sBuff)
> > >
> > > Call GetWindowText(hChild, sBuff, 128)
> > > ArrWins(nRow, nCol + 2) = TrimBuffer(sBuff)
> > >
> > > nRow = nRow + 1
> > >
> > > hwnNext = GetWindow(hChild, GW_CHILD)
> > >
> > > If hwnNext <> 0 Then
> > > GetChildWindows hChild, hwnNext, ArrWins, nRow, nCol + 1, _
> > > nMaxCols, bDesktop
> > > End If
> > >
> > > If hParent <> 0 Or bDesktop = True Then
> > > hwnNext = GetWindow(hChild, GW_HWNDNEXT)
> > > If hwnNext = 0 Then
> > > Exit Sub
> > > Else
> > > GetChildWindows hParent, hwnNext, ArrWins, nRow, nCol, _
> > > nMaxCols, bDesktop
> > > End If
> > > End If
> > >
> > > End Sub
> > >
> > > Public Function TrimBuffer(ByVal strIn As String) As String
> > > Dim nPos As Long
> > > nPos = InStr(1, strIn, vbNullChar, vbTextCompare)
> > > If nPos > 0 Then
> > > TrimBuffer = Left(strIn, nPos - 1)
> > > Else
> > > TrimBuffer = strIn
> > > End If
> > > End Function
> > >
> > > Sub Test2()
> > > Dim chtObj As ChartObject
> > >
> > > MySpy ActiveSheet, False
> > >
> > > ActiveSheet.Range("A1").CurrentRegion.Columns.Insert
> > >
> > > On Error Resume Next
> > > Set chtObj = ActiveSheet.ChartObjects("SpyChart")
> > > On Error GoTo 0
> > > With ActiveSheet.Range("D2:E4")
> > > If chtObj Is Nothing Then
> > > Set chtObj = .Parent.ChartObjects.Add( _
> > > .Left, .Top, .Width, .Height)
> > > chtObj.Name = "SpyChart"
> > > Else
> > > chtObj.Left = .Left
> > > chtObj.Top = .Top
> > > chtObj.Width = .Width
> > > chtObj.Height = .Height
> > > '.Visible = True
> > > End If
> > > End With
> > > chtObj.Activate
> > >
> > > MySpy ActiveSheet, False
> > >
> > > ' delete or keep the chart for future use
> > > 'chtObj.Delete
> > > Range("A1").Select
> > > chtObj.Visible = False
> > >
> > > End Sub
> > >
> > > Peter T
> >
> >
>
>

Typo -

> If not, on a new run Test(), add an embedded chart ...

should read -

If not, on a new Sheet run Test(), then add an embedded chart ...

Peter T


<snip>
>
> Jeremy - running Test2() was there really no difference in the dumps to
> cells between the two calls to MySpy. If not, on a new run Test(), add an
> embedded chart and select it, insert enough columns so as not to write
over
> cells and run Test() again. Compare the two sets of windows' data.
>
> Regards,
> Peter T

Peter,
OK, I see it under XLMAIN. If the chart is activated that window is first
and has a caption. Otherwise it is last and has no caption.

And this returns the correct HWnd, even with the .Activate code commented
out:

Private Sub CommandButton1_Click()
Dim hWndXL As Long
Dim hWndXLDesk As Long
Dim hWndXLChart As Long

'ActiveSheet.ChartObjects(1).Activate

hWndXL = FindWindow("XLMAIN", Application.Caption)
hWndXLDesk = FindWindowEx(hWndXL, 0&, "XLDESK", vbNullString)
hWndXLChart = FindWindowEx(hWndXLDesk, 0&, "EXCELE", vbNullString)
Debug.Print hWndXL, hWndXLDesk, hWndXLChart

End Sub

As the value of hWndXLChart is always the same, the Window is not really
destroyed/created.

NickHK

"Peter T" <peter_t@discussions> wrote in message
news:(E-Mail Removed)...
> Hi Nick,
>
> > Not sure of the significance of "editing" in the above, but I see no
hwnd
> > exposed for embedded charts from Spy++.
>
> The significance of "editing" is the embedded chart must be activated and
> selected. Then it's own window (class-name "EXCELE") is created. In an
> instance of Excel there can only ever be one activated embedded chart.
This
> is same for all versions XL8-11. I don't have 2007 but would have assumed
> some new window is created, though perhaps not EXCELE, and would have
> expected the sample code I posted to show different sets of windows,
firstly
> without an activated chart then with.
>
> I always see an activated embedded chart's window in Spy++, normally the
> first window under "XLDESK", did you actually select the chart, say the
> chart-area.
>
> Jeremy - running Test2() was there really no difference in the dumps to
> cells between the two calls to MySpy. If not, on a new run Test(), add an
> embedded chart and select it, insert enough columns so as not to write
over
> cells and run Test() again. Compare the two sets of windows' data.
>
> Regards,
> Peter T
>
>
>
> "NickHK" <(E-Mail Removed)> wrote in message
> news:#c16$(E-Mail Removed)...
> > Jeremy,
> > According to:
> >
http://www.awprofessional.com/articl...&seqNum=3&rl=1
> > "EXCELE
> > A window used to provide in-sheet editing of embedded charts."
> >
> > Not sure of the significance of "editing" in the above, but I see no
hwnd
> > exposed for embedded charts from Spy++.
> >
> > NickHK
> >
> > "GollyJer" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > Peter,
> > > Thanks for the help. I tried your code as well as a program called
> > > WinSpector (http://www.windows-spy.com). It seems the chart object is
> > > completely hidden from the windows shell. I certainly am not seeing
it.
> > >
> > > -Jeremy
> > >
> > > Peter T wrote:
> > > > Hi Jeremy,
> > > >
> > > > If you don't have Spy++ or equivalent, see if you can find your
> > > > chartobject window with the code below.
> > > >
> > > > Run Test2 (at the bottom) and look for differences between the two
> > > > calls to 'MySpy', without then with a chartobject window. In
> > > > particular look for "EXCELE" or if not found "SheetName SpyChart".
> > > >
> > > > Assuming you find the right window you'll need to work back up the
> > > > tree to Excel's main window and start from there to find your
> > > > activated chartobject's chart window. Then for your purposes
> > > > 'GetWindowRect' to return its coordinates.
> > > >
> > > > If the two calls to MySpy don't show any differences, change -
> > > > MySpy ActiveSheet, False > True ( in both calls) to get all windows
> > > >
> > > > Even in pre XL2007 some of Excel's windows are at the same 'level'
as
> > > > XLMAIN and so would need to search from the desktop, or perhaps even
> > > > from some other window.
> > > >
> > > > There's no error handling in the code. The array ArrWins is
> > > > dimensioned to accommodate 10000 windows. That's way more than
enough
> > > > for me but if your system has more you'll need to increase it
> > > > (unlikely necessary if passing bDesktop as False to only get Excel's
> > > > windows).
> > > >
> > > > Regards,
> > > > Peter T
> > > >
> > > >
> > > > '''''''''''''''''''''''''''''''''''''''''''
> > > > ' MySpy - Obtain Window details
> > > > ' Based on Stephen Bullen's EnumDlg.xls (.zip)
> > > > ' http://www.oaltd.co.uk/Excel/Default.htm
> > > > ' although heavily adapted intrinsically it's the same
> > > > ' see Stephen's original for comments
> > > > ' whilst there see the link for 'Professional Excel Development'
> > > > '
> > > > ' pmbthornton gmail com
> > > >
> > > > Option Explicit
> > > >
> > > > Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
> > > > ByVal lpClassName As String, ByVal lpWindowName As String) As
Long
> > > > Declare Function GetWindow Lib "user32" ( _
> > > > ByVal hwnd As Long, ByVal wCmd As Long) As Long
> > > > Declare Function GetClassName Lib "user32" Alias "GetClassNameA" ( _
> > > > ByVal hwnd As Long, ByVal lpClassName As String, _
> > > > ByVal nMaxCount As Long) As Long
> > > > Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA"
( _
> > > > ByVal hwnd As Long, ByVal lpString As String, _
> > > > ByVal cch As Long) As Long
> > > >
> > > > Public Const GW_HWNDFIRST = 0
> > > > 'Public Const GW_HWNDLAST = 1
> > > > Public Const GW_HWNDNEXT = 2
> > > > 'Public Const GW_HWNDPREV = 3
> > > > 'Public Const GW_OWNER = 4
> > > > Public Const GW_CHILD = 5
> > > > 'Public Const GW_MAX = 5
> > > >
> > > > Sub Test()
> > > >
> > > > MySpy ActiveSheet, True
> > > >
> > > > ActiveSheet.Cells.Find("XLMAIN").Select
> > > >
> > > > End Sub
> > > >
> > > > Sub MySpy(mSht As Worksheet, bDesktop As Boolean)
> > > > Dim hWindFirst As Long
> > > > Dim nRow As Long, nCol As Long
> > > > Dim nMaxCols As Long
> > > >
> > > > ReDim ArrWins(1 To 10000, 1 To 3) ' increase if +10000 windows
> > > >
> > > > If Application.Version > 9 Then
> > > > hWindFirst = Application.hwnd
> > > > Else
> > > > hWindFirst = FindWindow("XLMAIN", Application.Caption)
> > > > End If
> > > >
> > > > If bDesktop = True Then
> > > > hWindFirst = GetWindow(hWindFirst, GW_HWNDFIRST)
> > > > End If
> > > >
> > > > nRow = 1
> > > > nCol = 1
> > > >
> > > > GetChildWindows 0, hWindFirst, ArrWins, nRow, nCol, nMaxCols,
> > > > bDesktop
> > > >
> > > > With mSht
> > > > .Range("A1").CurrentRegion.Clear
> > > > .Range(.Cells(1, 1), .Cells(nRow, nMaxCols)) = ArrWins
> > > > nRow = .UsedRange.Rows.Count '
> > > > End With
> > > >
> > > > End Sub
> > > >
> > > > Sub GetChildWindows(hParent As Long, hChild As Long, _
> > > > ArrWins(), nRow As Long, nCol As Long, _
> > > > nMaxCols, bDesktop As Boolean)
> > > > Dim sBuff As String * 128
> > > > Dim hwnNext As Long
> > > >
> > > > If nCol + 2 > nMaxCols Then nMaxCols = nCol + 2
> > > > If nMaxCols > UBound(ArrWins, 2) Then
> > > > ReDim Preserve ArrWins(1 To UBound(ArrWins), 1 To nMaxCols)
> > > > End If
> > > >
> > > > ArrWins(nRow, nCol) = hChild
> > > >
> > > > Call GetClassName(hChild, sBuff, 128)
> > > > ArrWins(nRow, nCol + 1) = TrimBuffer(sBuff)
> > > >
> > > > Call GetWindowText(hChild, sBuff, 128)
> > > > ArrWins(nRow, nCol + 2) = TrimBuffer(sBuff)
> > > >
> > > > nRow = nRow + 1
> > > >
> > > > hwnNext = GetWindow(hChild, GW_CHILD)
> > > >
> > > > If hwnNext <> 0 Then
> > > > GetChildWindows hChild, hwnNext, ArrWins, nRow, nCol + 1, _
> > > > nMaxCols, bDesktop
> > > > End If
> > > >
> > > > If hParent <> 0 Or bDesktop = True Then
> > > > hwnNext = GetWindow(hChild, GW_HWNDNEXT)
> > > > If hwnNext = 0 Then
> > > > Exit Sub
> > > > Else
> > > > GetChildWindows hParent, hwnNext, ArrWins, nRow, nCol, _
> > > > nMaxCols, bDesktop
> > > > End If
> > > > End If
> > > >
> > > > End Sub
> > > >
> > > > Public Function TrimBuffer(ByVal strIn As String) As String
> > > > Dim nPos As Long
> > > > nPos = InStr(1, strIn, vbNullChar, vbTextCompare)
> > > > If nPos > 0 Then
> > > > TrimBuffer = Left(strIn, nPos - 1)
> > > > Else
> > > > TrimBuffer = strIn
> > > > End If
> > > > End Function
> > > >
> > > > Sub Test2()
> > > > Dim chtObj As ChartObject
> > > >
> > > > MySpy ActiveSheet, False
> > > >
> > > > ActiveSheet.Range("A1").CurrentRegion.Columns.Insert
> > > >
> > > > On Error Resume Next
> > > > Set chtObj = ActiveSheet.ChartObjects("SpyChart")
> > > > On Error GoTo 0
> > > > With ActiveSheet.Range("D2:E4")
> > > > If chtObj Is Nothing Then
> > > > Set chtObj = .Parent.ChartObjects.Add( _
> > > > .Left, .Top, .Width, .Height)
> > > > chtObj.Name = "SpyChart"
> > > > Else
> > > > chtObj.Left = .Left
> > > > chtObj.Top = .Top
> > > > chtObj.Width = .Width
> > > > chtObj.Height = .Height
> > > > '.Visible = True
> > > > End If
> > > > End With
> > > > chtObj.Activate
> > > >
> > > > MySpy ActiveSheet, False
> > > >
> > > > ' delete or keep the chart for future use
> > > > 'chtObj.Delete
> > > > Range("A1").Select
> > > > chtObj.Visible = False
> > > >
> > > > End Sub
> > > >
> > > > Peter T
> > >
> > >
> >
> >
>
>

If the chart is not activated, but has been (say when it was manually
added), the EXCELE window remains as does its window coordinates
(GetWindowRect), which surprisingly update to the new position if the chart
toolbar is no longer active or screen location moved in some other way. This
window remains even after the chart is deleted or even if all charts are
deleted, irrespective of the activesheet and/or workbook.

If a new chartobject is programmatically added, but not activated, the
EXCELE window remains that of the previously activated embedded chart (even
if deleted). However its window no longer exists in the application's or
workbook's windows collection as .Windows(1).

So for the objective, to get pixel coordinates of an area on the worksheet
to which the chart is sized (to be converted to points), it means the known
sized chart must first be activated.

Of course all this applies to XL2003 and earlier. I don't know what window
is created in XL2007, if any, though I assume one is.

Regards,
Peter T


"NickHK" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Peter,
> OK, I see it under XLMAIN. If the chart is activated that window is first
> and has a caption. Otherwise it is last and has no caption.
>
> And this returns the correct HWnd, even with the .Activate code commented
> out:
>
> Private Sub CommandButton1_Click()
> Dim hWndXL As Long
> Dim hWndXLDesk As Long
> Dim hWndXLChart As Long
>
> 'ActiveSheet.ChartObjects(1).Activate
>
> hWndXL = FindWindow("XLMAIN", Application.Caption)
> hWndXLDesk = FindWindowEx(hWndXL, 0&, "XLDESK", vbNullString)
> hWndXLChart = FindWindowEx(hWndXLDesk, 0&, "EXCELE", vbNullString)
> Debug.Print hWndXL, hWndXLDesk, hWndXLChart
>
> End Sub
>
> As the value of hWndXLChart is always the same, the Window is not really
> destroyed/created.
>
> NickHK
>
> "Peter T" <peter_t@discussions> wrote in message
> news:(E-Mail Removed)...
> > Hi Nick,
> >
> > > Not sure of the significance of "editing" in the above, but I see no
> hwnd
> > > exposed for embedded charts from Spy++.
> >
> > The significance of "editing" is the embedded chart must be activated
and
> > selected. Then it's own window (class-name "EXCELE") is created. In an
> > instance of Excel there can only ever be one activated embedded chart.
> This
> > is same for all versions XL8-11. I don't have 2007 but would have
assumed
> > some new window is created, though perhaps not EXCELE, and would have
> > expected the sample code I posted to show different sets of windows,
> firstly
> > without an activated chart then with.
> >
> > I always see an activated embedded chart's window in Spy++, normally the
> > first window under "XLDESK", did you actually select the chart, say the
> > chart-area.
> >
> > Jeremy - running Test2() was there really no difference in the dumps to
> > cells between the two calls to MySpy. If not, on a new run Test(), add
an
> > embedded chart and select it, insert enough columns so as not to write
> over
> > cells and run Test() again. Compare the two sets of windows' data.
> >
> > Regards,
> > Peter T
> >
> >
> >
> > "NickHK" <(E-Mail Removed)> wrote in message
> > news:#c16$(E-Mail Removed)...
> > > Jeremy,
> > > According to:
> > >
> http://www.awprofessional.com/articl...&seqNum=3&rl=1
> > > "EXCELE
> > > A window used to provide in-sheet editing of embedded charts."
> > >
> > > Not sure of the significance of "editing" in the above, but I see no
> hwnd
> > > exposed for embedded charts from Spy++.
> > >
> > > NickHK
> > >
> > > "GollyJer" <(E-Mail Removed)> wrote in message
> > > news:(E-Mail Removed)...
> > > > Peter,
> > > > Thanks for the help. I tried your code as well as a program called
> > > > WinSpector (http://www.windows-spy.com). It seems the chart object
is
> > > > completely hidden from the windows shell. I certainly am not seeing
> it.
> > > >
> > > > -Jeremy
> > > >
> > > > Peter T wrote:
> > > > > Hi Jeremy,
> > > > >
> > > > > If you don't have Spy++ or equivalent, see if you can find your
> > > > > chartobject window with the code below.
> > > > >
> > > > > Run Test2 (at the bottom) and look for differences between the two
> > > > > calls to 'MySpy', without then with a chartobject window. In
> > > > > particular look for "EXCELE" or if not found "SheetName SpyChart".
> > > > >
> > > > > Assuming you find the right window you'll need to work back up the
> > > > > tree to Excel's main window and start from there to find your
> > > > > activated chartobject's chart window. Then for your purposes
> > > > > 'GetWindowRect' to return its coordinates.
> > > > >
> > > > > If the two calls to MySpy don't show any differences, change -
> > > > > MySpy ActiveSheet, False > True ( in both calls) to get all
windows
> > > > >
> > > > > Even in pre XL2007 some of Excel's windows are at the same 'level'
> as
> > > > > XLMAIN and so would need to search from the desktop, or perhaps
even
> > > > > from some other window.
> > > > >
> > > > > There's no error handling in the code. The array ArrWins is
> > > > > dimensioned to accommodate 10000 windows. That's way more than
> enough
> > > > > for me but if your system has more you'll need to increase it
> > > > > (unlikely necessary if passing bDesktop as False to only get
Excel's
> > > > > windows).
> > > > >
> > > > > Regards,
> > > > > Peter T
> > > > >
> > > > >
> > > > > '''''''''''''''''''''''''''''''''''''''''''
> > > > > ' MySpy - Obtain Window details
> > > > > ' Based on Stephen Bullen's EnumDlg.xls (.zip)
> > > > > ' http://www.oaltd.co.uk/Excel/Default.htm
> > > > > ' although heavily adapted intrinsically it's the same
> > > > > ' see Stephen's original for comments
> > > > > ' whilst there see the link for 'Professional Excel Development'
> > > > > '
> > > > > ' pmbthornton gmail com
> > > > >
> > > > > Option Explicit
> > > > >
> > > > > Declare Function FindWindow Lib "user32" Alias "FindWindowA" ( _
> > > > > ByVal lpClassName As String, ByVal lpWindowName As String) As
> Long
> > > > > Declare Function GetWindow Lib "user32" ( _
> > > > > ByVal hwnd As Long, ByVal wCmd As Long) As Long
> > > > > Declare Function GetClassName Lib "user32" Alias "GetClassNameA"
( _
> > > > > ByVal hwnd As Long, ByVal lpClassName As String, _
> > > > > ByVal nMaxCount As Long) As Long
> > > > > Declare Function GetWindowText Lib "user32" Alias "GetWindowTextA"
> ( _
> > > > > ByVal hwnd As Long, ByVal lpString As String, _
> > > > > ByVal cch As Long) As Long
> > > > >
> > > > > Public Const GW_HWNDFIRST = 0
> > > > > 'Public Const GW_HWNDLAST = 1
> > > > > Public Const GW_HWNDNEXT = 2
> > > > > 'Public Const GW_HWNDPREV = 3
> > > > > 'Public Const GW_OWNER = 4
> > > > > Public Const GW_CHILD = 5
> > > > > 'Public Const GW_MAX = 5
> > > > >
> > > > > Sub Test()
> > > > >
> > > > > MySpy ActiveSheet, True
> > > > >
> > > > > ActiveSheet.Cells.Find("XLMAIN").Select
> > > > >
> > > > > End Sub
> > > > >
> > > > > Sub MySpy(mSht As Worksheet, bDesktop As Boolean)
> > > > > Dim hWindFirst As Long
> > > > > Dim nRow As Long, nCol As Long
> > > > > Dim nMaxCols As Long
> > > > >
> > > > > ReDim ArrWins(1 To 10000, 1 To 3) ' increase if +10000
windows
> > > > >
> > > > > If Application.Version > 9 Then
> > > > > hWindFirst = Application.hwnd
> > > > > Else
> > > > > hWindFirst = FindWindow("XLMAIN", Application.Caption)
> > > > > End If
> > > > >
> > > > > If bDesktop = True Then
> > > > > hWindFirst = GetWindow(hWindFirst, GW_HWNDFIRST)
> > > > > End If
> > > > >
> > > > > nRow = 1
> > > > > nCol = 1
> > > > >
> > > > > GetChildWindows 0, hWindFirst, ArrWins, nRow, nCol, nMaxCols,
> > > > > bDesktop
> > > > >
> > > > > With mSht
> > > > > .Range("A1").CurrentRegion.Clear
> > > > > .Range(.Cells(1, 1), .Cells(nRow, nMaxCols)) = ArrWins
> > > > > nRow = .UsedRange.Rows.Count '
> > > > > End With
> > > > >
> > > > > End Sub
> > > > >
> > > > > Sub GetChildWindows(hParent As Long, hChild As Long, _
> > > > > ArrWins(), nRow As Long, nCol As Long, _
> > > > > nMaxCols, bDesktop As Boolean)
> > > > > Dim sBuff As String * 128
> > > > > Dim hwnNext As Long
> > > > >
> > > > > If nCol + 2 > nMaxCols Then nMaxCols = nCol + 2
> > > > > If nMaxCols > UBound(ArrWins, 2) Then
> > > > > ReDim Preserve ArrWins(1 To UBound(ArrWins), 1 To nMaxCols)
> > > > > End If
> > > > >
> > > > > ArrWins(nRow, nCol) = hChild
> > > > >
> > > > > Call GetClassName(hChild, sBuff, 128)
> > > > > ArrWins(nRow, nCol + 1) = TrimBuffer(sBuff)
> > > > >
> > > > > Call GetWindowText(hChild, sBuff, 128)
> > > > > ArrWins(nRow, nCol + 2) = TrimBuffer(sBuff)
> > > > >
> > > > > nRow = nRow + 1
> > > > >
> > > > > hwnNext = GetWindow(hChild, GW_CHILD)
> > > > >
> > > > > If hwnNext <> 0 Then
> > > > > GetChildWindows hChild, hwnNext, ArrWins, nRow, nCol + 1, _
> > > > > nMaxCols, bDesktop
> > > > > End If
> > > > >
> > > > > If hParent <> 0 Or bDesktop = True Then
> > > > > hwnNext = GetWindow(hChild, GW_HWNDNEXT)
> > > > > If hwnNext = 0 Then
> > > > > Exit Sub
> > > > > Else
> > > > > GetChildWindows hParent, hwnNext, ArrWins, nRow, nCol,
_
> > > > > nMaxCols, bDesktop
> > > > > End If
> > > > > End If
> > > > >
> > > > > End Sub
> > > > >
> > > > > Public Function TrimBuffer(ByVal strIn As String) As String
> > > > > Dim nPos As Long
> > > > > nPos = InStr(1, strIn, vbNullChar, vbTextCompare)
> > > > > If nPos > 0 Then
> > > > > TrimBuffer = Left(strIn, nPos - 1)
> > > > > Else
> > > > > TrimBuffer = strIn
> > > > > End If
> > > > > End Function
> > > > >
> > > > > Sub Test2()
> > > > > Dim chtObj As ChartObject
> > > > >
> > > > > MySpy ActiveSheet, False
> > > > >
> > > > > ActiveSheet.Range("A1").CurrentRegion.Columns.Insert
> > > > >
> > > > > On Error Resume Next
> > > > > Set chtObj = ActiveSheet.ChartObjects("SpyChart")
> > > > > On Error GoTo 0
> > > > > With ActiveSheet.Range("D2:E4")
> > > > > If chtObj Is Nothing Then
> > > > > Set chtObj = .Parent.ChartObjects.Add( _
> > > > > .Left, .Top, .Width, .Height)
> > > > > chtObj.Name = "SpyChart"
> > > > > Else
> > > > > chtObj.Left = .Left
> > > > > chtObj.Top = .Top
> > > > > chtObj.Width = .Width
> > > > > chtObj.Height = .Height
> > > > > '.Visible = True
> > > > > End If
> > > > > End With
> > > > > chtObj.Activate
> > > > >
> > > > > MySpy ActiveSheet, False
> > > > >
> > > > > ' delete or keep the chart for future use
> > > > > 'chtObj.Delete
> > > > > Range("A1").Select
> > > > > chtObj.Visible = False
> > > > >
> > > > > End Sub
> > > > >
> > > > > Peter T
> > > >
> > > >
> > >
> > >
> >
> >
>
>