Defining location of sheet relative to the screen.

G

Guest

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
 

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

Top