API to find position of active cell no longer working in 2007.

G

GollyJer

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
-------------------------------------------------------------------------------------------
 
B

Bob Phillips

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)
 
G

GollyJer

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 said:
The Excel class name in 2007 seems to be NetUIHWND instead of XLMAIN
now.

GollyJer said:
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
-------------------------------------------------------------------------------------------
Click to expand...
Click to expand...
 
P

Peter T

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
 
G

GollyJer

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
 
P

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++.
Click to expand...

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
 
N

NickHK

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
 
P

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
 
N

NickHK

Peter,
I must admit I hadn't paid much attention to the OP's aim, but it seems to
be to cover the area of where a chart was with a userform.
Not sure why though...

NickHK
 
P

Peter T

Not sure I follow what you're not sure about <g>

The OP's aim, as I understand looking at his original code, is to show his
form positioned to top-left of ActiveCell.Offset(0, 1), ie just to right of
the activecell (in passing he ought check the cell is well within the
visible range).

FWIW I posted something similar recently where the OP wanted the screen
pixel coordinates of a shape on a chart sheet.

http://tinyurl.com/2mw4ou

Regards,
Peter T
 
J

Jon Peltier

If you don't mind a pretty good alternative to this, you could see if minor
modifications to Chip Pearson's FormPositioner will work for you.

http://cpearson.com/excel/FormPosition.htm

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


GollyJer said:
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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel 2007 Rect type problem 1
Hide the Name Box 3
Closing programs on remote PC - help please 1
Show full Combo List at once 4
Progress Bar Stuck after Crashing 2
Show/Hide Minimize/Maximize Button in Excel 2007/2010 2
error trying to use GetFromClipboard 4
Defined Name Box Shortcut 4

Top