VIEWABLE, not VISIBLE!

[sts023]

Hi Guys....

Does anyone know a way to discover which is the rightmost column IN VIEW in a Window showing an Excel Worksheet with column A as its leftmost column?

I'm visually impaired, so I use a low resolution, and normally I can see col L with Excel in full screen mode.

I'm trying to use vba to create an OLE button in the top right of the rightmost VIEWABLE (not VISIBLE) column (i.e. always at the top right of the screen)

People with better sight than mine (i.e. most people) will be able to see more columns; also if Excel is in a window, the leftmost column will vary as the window is resized.

I'm struggling with this 'cos if I Google "Excel visible area", I get reams of answers about the "Visible" Property.

I realise it may be either impossible (or beyond my limited ability to understand a solution), but I'm hoping its just possible that there's a (fairly) simple way to achieve this.

Any ideas?

 

[Rick Rothstein]

Can you describe in more detail what you mean by "top right of the rightmost VIEWABLE (not VISIBLE) column"... what is it about VIEWABLE that makes it not the same as VISIBLE?

 

[shg]

Perhaps

Sub sts()
  With ActiveWindow.VisibleRange
    MsgBox .Cells(1, .Columns.Count - 1).Address
  End With
End Sub

The last column might be totally visible in the window, but likely not.

 

[sts023]

Rick - it's exactly what it says in the post. I was trying to avoid using VISIBLE, 'cos that's a Property. I mean it in the English sense - that which can be seen.

With regard to shg's suggested code - it works!
It gives the column letter of the last fully visible column.
If the only visible column is "A", and it is wider than the screen, "Run time error 1004" is returned.
Hidden columns are "ignored", and other than when col "A" is wider than the viewable window, its WYSIWYG - the last completely visible column in the window.

Thanks shg!

 

[sts023]

In case anyone else wants this as a solution (???) to their own problem, here's a slightly modified error trapped version of shg's code which works for my application, but may need modification for yours...

Public Function funLastVisibleCol() As String
Dim asAddr()                        As String
  On Error GoTo Err_Handler
  With ActiveWindow.VisibleRange
    asAddr = Split(.Cells(1, .Columns.Count - 1).Address, "$")
    funLastVisibleCol = asAddr(1)
  End With
ExitHere:
  Exit Function
Err_Handler:
  Select Case Err.Number
    Case 1004   'Col A greater than screen width.
      funLastVisibleCol = "A"
    Case Else
      Call MsgBox("Unexpected error!" & vbCrLf & _
                  "Number: " & Err.Number & vbCrLf & _
                  Err.Description & vbCrLf & _
                  vbCrLf & _
                  "Process will terminate", _
                  vbCritical, _
                  "Error in function funLastVisibleCol")
      End
  End Select
  On Error GoTo 0
  GoTo ExitHere
End Function 'funLastVisibleCol