PDA

View Full Version : How to Determine Where I Am on a Worksheet



Cyberdude
04-20-2006, 01:19 PM
I have a worksheet that contains 16 charts. When I open the workbook, the part of the sheet that is displayed could be just about anywhere, so I typically scroll right or left to gete to the chart I want to view. At this point either no cell is selected or, if selected, the selected cell is off screen somewhere to the left or right.

My problem is that I want to determine in a macro what columns are currently being viewed. I?ll settle for a range of columns or any one column on the screen being displayed. Is there a statement I can execute that will return the columns (or column) currently being displayed?

Remember that I have no idea where the currently selected cell is, or even IF there is a currently seleced cell. The last thing selected could be a chart or even an autoshape.

Norie
04-20-2006, 01:22 PM
Take a look at the VisibleRange property?


MsgBox ActiveWindow.VisibleRange.Address

Ken Puls
04-20-2006, 01:50 PM
Hi Sid,

Don't know if this is exactly what you're after, but how about:

Sub WhereAmI()
If Not ActiveCell Is Nothing Then
MsgBox "Active Cell is " & ActiveCell.Address & vbNewLine & _
"Visible Range is " & ActiveWindow.VisibleRange.Address
Else
MsgBox "Active Cell is *no active cells*" & vbNewLine & _
"Visible Range is " & _
ActiveChart.Parent.Parent.Parent.Parent.Windows(2).VisibleRange.Address
End If
End Sub

You may have to change Windows(2) to another number. My (1) was the chart that I inserted, so it may need to change if you have more than one. I suppose a loop could be constructed to get the first real worksheet, but I wanted to make sure this is what you were after.

HTH,

Cyberdude
04-20-2006, 08:40 PM
I knew that I'd seen the "VisibleRange" property somewhere along the way, but my aging personal Google just couldn't find it. That's EXACTLY what I was hoping for. Thanx, guys, for coming to my resqueue. :bow: