Bill,
Maybe see how Chip calculates position in
http://www.cpearson.com/excel/FormPosition.htm
NickHK
"Bill Nevin" <(E-Mail Removed)> wrote in message
news:C07798EE-B955-4BA8-98C6-(E-Mail Removed)...
> 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
>
Similar Threads
