Hi, I've been attempting to create a macro for a shape that covers a region.
The macro is designed so that where ever you click on the shape, it will find
which cell your mouse is over on and highlight that cell accordingly. I've
managed to factor in different variables like application position, worksheet
position, scrollbar, etc... however there is one variable I cannot figure out
how to aquire and that is the position of the sheet itself relative to the
window. That is to say, how do I define how much space the end user is using
for toolbars. Right now this is just a constant which is obviously a problem
if the end-users don't have the same toolbar setup that I use. I know that
there are tons of objects that define a "top" method in them, but I've tried
several and none of them are useful to me. I've also tried cycling through
the CommandBar objects to get their heights but that's not working right. So
does anyone know which property I could use to get the height I need so that
I can factor it into the algorithm?

------------------------------------------------------------------------

Sub GridArea_Click()
Dim CursPos As POINTAPI
Dim locRow As String
Dim locCol As String
Dim ScrollXPos As Integer
Dim ScrollYPos As Integer
Dim AppXPos As Integer
Dim AppYPos As Integer

Call GetCursorPos(CursPos)

ScrollXPos = (Windows(1).ScrollColumn - 1) * Range("C1").Width
ScrollYPos = (Windows(1).ScrollRow - 1) * Range("C1").Height

AppXPos = IIf(Application.WindowState = xlMaximized, 2, Application.Left)
AppYPos = IIf(Application.WindowState = xlMaximized, 3, Application.Top)

locRow = Chr(((CursPos.x - 323 + ScrollXPos - AppXPos) \ 29) + 67)
locCol = CStr(((CursPos.y - 172 + ScrollYPos - AppYPos) \ 17) + 3)
' By the way, this converts the numbers to a range (ie "A1"), however, I
' do believe there must be a way to reference range by number index. No?

If Range(locRow & locCol).Interior.ColorIndex = 3 Then
Range(locRow & locCol).Interior.ColorIndex = 41
Range(locRow & locCol).Value = Range(locRow & "2").Value
ElseIf Range(locRow & locCol).Interior.ColorIndex = 41 Then
Range(locRow & locCol).Interior.ColorIndex = 15
Range(locRow & locCol).Interior.Pattern = xlGray25
Range(locRow & locCol).ClearContents
If (Range(Chr(Asc(locRow) - 1) & locCol).Interior.ColorIndex = 15) Then
Range(locRow & locCol).Borders(xlEdgeLeft).LineStyle = xlNone
End If
If (Range(Chr(Asc(locRow) + 1) & locCol).Interior.ColorIndex = 15) Then
Range(locRow & locCol).Borders(xlEdgeRight).LineStyle = xlNone
End If
ElseIf Range(locRow & locCol).Interior.ColorIndex = 15 Then
Range(locRow & locCol).Interior.ColorIndex = 0
Range(locRow & locCol).Interior.Pattern = xlNone
Range(locRow & locCol).Value = Range(locRow & "2").Value
Range(locRow & locCol).Borders(xlEdgeLeft).LineStyle = xlThin
Range(locRow & locCol).Borders(xlEdgeLeft).ColorIndex = 2
Range(locRow & locCol).Borders(xlEdgeRight).LineStyle = xlThin
Range(locRow & locCol).Borders(xlEdgeRight).ColorIndex = 2
Else
Range(locRow & locCol).Interior.ColorIndex = 3
Range(locRow & locCol).Value = Range(locRow & "2").Value
End If

' MsgBox CursPos.x & " " & CursPos.y & " " & locRow & " " & locCol
End Sub

Bill,
Maybe see how Chip calculates position in
http://www.cpearson.com/excel/FormPosition.htm

NickHK

"Bill Nevin" wrote in message
...
Hi, I've been attempting to create a macro for a shape that covers a
region.
The macro is designed so that where ever you click on the shape, it will
find
which cell your mouse is over on and highlight that cell accordingly. I've
managed to factor in different variables like application position,
worksheet
position, scrollbar, etc... however there is one variable I cannot figure
out
how to aquire and that is the position of the sheet itself relative to the
window. That is to say, how do I define how much space the end user is
using
for toolbars. Right now this is just a constant which is obviously a
problem
if the end-users don't have the same toolbar setup that I use. I know that
there are tons of objects that define a "top" method in them, but I've
tried
several and none of them are useful to me. I've also tried cycling through
the CommandBar objects to get their heights but that's not working right.
So
does anyone know which property I could use to get the height I need so
that
I can factor it into the algorithm?

------------------------------------------------------------------------

Sub GridArea_Click()
Dim CursPos As POINTAPI
Dim locRow As String
Dim locCol As String
Dim ScrollXPos As Integer
Dim ScrollYPos As Integer
Dim AppXPos As Integer
Dim AppYPos As Integer

Call GetCursorPos(CursPos)

ScrollXPos = (Windows(1).ScrollColumn - 1) * Range("C1").Width
ScrollYPos = (Windows(1).ScrollRow - 1) * Range("C1").Height

AppXPos = IIf(Application.WindowState = xlMaximized, 2,
Application.Left)
AppYPos = IIf(Application.WindowState = xlMaximized, 3, Application.Top)

locRow = Chr(((CursPos.x - 323 + ScrollXPos - AppXPos) \ 29) + 67)
locCol = CStr(((CursPos.y - 172 + ScrollYPos - AppYPos) \ 17) + 3)
' By the way, this converts the numbers to a range (ie "A1"), however, I
' do believe there must be a way to reference range by number index. No?

If Range(locRow & locCol).Interior.ColorIndex = 3 Then
Range(locRow & locCol).Interior.ColorIndex = 41
Range(locRow & locCol).Value = Range(locRow & "2").Value
ElseIf Range(locRow & locCol).Interior.ColorIndex = 41 Then
Range(locRow & locCol).Interior.ColorIndex = 15
Range(locRow & locCol).Interior.Pattern = xlGray25
Range(locRow & locCol).ClearContents
If (Range(Chr(Asc(locRow) - 1) & locCol).Interior.ColorIndex = 15)
Then
Range(locRow & locCol).Borders(xlEdgeLeft).LineStyle = xlNone
End If
If (Range(Chr(Asc(locRow) + 1) & locCol).Interior.ColorIndex = 15)
Then
Range(locRow & locCol).Borders(xlEdgeRight).LineStyle = xlNone
End If
ElseIf Range(locRow & locCol).Interior.ColorIndex = 15 Then
Range(locRow & locCol).Interior.ColorIndex = 0
Range(locRow & locCol).Interior.Pattern = xlNone
Range(locRow & locCol).Value = Range(locRow & "2").Value
Range(locRow & locCol).Borders(xlEdgeLeft).LineStyle = xlThin
Range(locRow & locCol).Borders(xlEdgeLeft).ColorIndex = 2
Range(locRow & locCol).Borders(xlEdgeRight).LineStyle = xlThin
Range(locRow & locCol).Borders(xlEdgeRight).ColorIndex = 2
Else
Range(locRow & locCol).Interior.ColorIndex = 3
Range(locRow & locCol).Value = Range(locRow & "2").Value
End If

' MsgBox CursPos.x & " " & CursPos.y & " " & locRow & " " &
locCol
End Sub