Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > API to find position of active cell no longer working in 2007.
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default API to find position of active cell no longer working in 2007.
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
-------------------------------------------------------------------------------------------



Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,593
Default API to find position of active cell no longer working in 2007.
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" wrote in message
...
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
-------------------------------------------------------------------------------------------





Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default API to find position of active cell no longer working in 2007.
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" wrote in message
...
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
-------------------------------------------------------------------------------------------

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,600
Default API to find position of active cell no longer working in 2007.
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.Inse rt

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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10
Default API to find position of active cell no longer working in 2007.
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.Inse rt

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


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 6,582
Default API to find position of active cell no longer working in 2007.
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" wrote in message
...
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
-------------------------------------------------------------------------------------------





Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Active cell position for macro MechEng Excel Worksheet Functions 3 September 24th 07 01:44 PM
move within ss by active cell position, NOT by cell names BEEJAY Excel Programming 2 August 23rd 06 04:34 PM
In macros, an instruction that will return the position of the Active Cell aca[_4_] Excel Programming 2 January 20th 06 10:50 PM
Relative Cell position NOT working with or without macro Scratching my Head Excel Discussion (Misc queries) 6 May 30th 05 06:12 PM
Active Cell Position Using Go To Or Hyperlink Keith Chipman Excel Programming 1 November 19th 03 03:05 PM


All times are GMT +1. The time now is 04:33 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement