PDA

View Full Version : Where Am I Looking On A Sheet??



Cyberdude
11-23-2008, 05:29 PM
Suppose a user?s last selection on a sheet is somewhere on column ?FF? . Then the user moves the screen left using the scrool bar so that now he is looking at column ?G?. He has done nothing to cause a selection to occur on the screen he is now looking at. However my macro needs to know where on the sheet the user is now looking. My macro thinks the user is positioned at column ?FF? since that?s where the last selection occurred. My macro will perform an action that depends on where the user is looking on the sheet, so the action will be incorrect.

The macro execution is initiated when the user clicks on a button (not a cell), which is one of many scattered around the sheet. The macro thinks the user has clicked on the buitton located near column ?FF?, when in fact the user has clicked on the button near column ?G?.

How can my macro determine where the screen is positioned on the sheet? I seem to recall that there is a property that will tell you the coordinates of the upper left corner being displayed at any given moment, but I can?t for the life of me remember what that property is called. Since the user has not done anything to the sheet after he moved it to a different viewing area, what is available to tell the macro where the user is now looking? :bug:

GTO
11-23-2008, 06:15 PM
Greetings Cyberdude,

Though it doesn't give upper/left coordinates, might you be recalling VisibleRange? Probably of no help, but thought to mention 'just in case'.

MsgBox ActiveWindow.VisibleRange.Address

Mark

GTO
11-23-2008, 06:54 PM
How can my macro determine where the screen is positioned on the sheet? I seem to recall that there is a property that will tell you the coordinates of the upper left corner being displayed at any given moment, but I can?t for the life of me remember what that property is called.

Uhmmm... Regarding my last post, yeesh and my bad.

MsgBox ActiveWindow.VisibleRange.Cells(1, 1).Address

I would humbly mention that using what cell(s) is/are in view seems, well, rather challenging at the minimum. Various users screen sizes, the users chosen display settings, and finally, the users' choice as to zoom - well, you see where I'm going.

Mark

david000
11-24-2008, 12:31 AM
Nice code on the topic via Chip Pearson. Deals with the issues GTO mentions.

http://www.cpearson.com/excel/zoom.htm

Cyberdude
11-24-2008, 03:30 PM
Thanx, guys. I haven't solved the problem yet, but what you guys gave me will probably contain the tools I need. If I can just determine one of the columns on the visible screen and roughly where that column is on the screen (like upper left corner) then I think I'll have enough info. This has been an interesting challenge.


Thanx again to both of you.

Sid

GTO
11-24-2008, 03:36 PM
Sid,

If you are still there, I am curious as to this: You mention several ranges and command buttons on placed in various areas of the sheet(s). By chance (as in a bit of a guess on my part) are you wanting to have all the command buttons run the same procedure(s), but want to set arguments (such as what range the procedure is working on) by what section of the sheet the user is viewing?

Mark

Cyberdude
11-27-2008, 10:51 AM
Hi, GTO. Just saw your request. Sorry for the reply delay.

This is a little hard to describe, but here goes. I have 16 charts side-by-side on a sheet. Each chart fills up most of the visible window. On these charts I have some descriptive information contained in a label that?s pretty large. With time these labels get shoved around to different locations, some of which cover parts of the chart. I find myself manually moving these labels to more convenient positions on the chart every so often. So I wrote a macro that will move the label a small fixed distance each time I click on a button (AutoShape arrow) that is located below the chart somewhere. The problem is that the macro needs to know which chart I?m operating on when it executes.
I can correct this problem by clicking on any location below that chart to cause a selection. That?s a bloody nuisance which I often forget to do, so nothing happens when I click the ?nudge? button, I needed a programable method for simulating that manual click which causes the selection to occur.
Here?s the code I am using in my nudge macro to select the chart. I stick this comment and VBA statement in the nudge macro to cause the selection to occur:
'Make sure the viewed portion of the sheet has been selected

VisWinAddr = GetVisibleWindowCoordinates(UprLeftCornerAddr) <-Calls the window coordinates function

This is the specialized function I wrote to get the range covered by the visible window:
Function GetVisibleWindowCoordinates(UprLeftCornerAddr$, Optional LeftCol$ = "A", Optional TopRow& = 1) As String
'This function has as its returned value the address range (window corner coordinates
'ex: $BK$23:$CD$123)' of the currently active visible window.
'In addition, the 3 arguments return the address (ex: $BK$23) of the upper left corner, and
'the column (ex: BK) (as a string), and row (ex: 23) (as an integer) of the upper left corner of
'the active window.
Dim Dolr2Loc&
GetVisibleWindowCoordinates = ActiveWindow.VisibleRange.Address 'Returned format: $BK$23:$CD$123
UprLeftCornerAddr = Left(GetVisibleWindowCoordinates, Application.Search(":", GetVisibleWindowCoordinates, 1) - 1) 'Returned format: $BK$23
Dolr2Loc = Application.Search("$", UprLeftCornerAddr, 2)
LeftCol = Mid(UprLeftCornerAddr, 2, Dolr2Loc - 2)
TopRow = Mid(UprLeftCornerAddr, Dolr2Loc + 1, Len(UprLeftCornerAddr) - Dolr2Loc)
End Function
I know . . . it?s clumsy, BUT IT WORKS!