Scroll worksheet right using VBA

Curry

Registered User.
Local time
Today, 23:58
Joined
Jul 21, 2003
Messages
73
I have a worksheet that every month when running a month roll over VBA code, creates a new month column on the right side Therefore increasing the number of columns. I have frozen the left hand column with the row labels. I am using code in a form to go to the particular worksheet however I would always like to show only the last 12 columns. I have acheived this using commands like "ActiveWindow.SmallScroll ToRight:=-9" to reposition the spreadsheet where I want. THis works fine until the spreadsheet is viewed on another PC with a different screen resolution and the Worksheet positions differently.

Is there a command that will look for the last colums and scroll the worksheet to it showing only the last columns only.

THanks
IC
 
Hi, IC,

I´m afraid it will take a little bit more than just a code like

Code:
Dim lngLastColum As Long
lngLastColum = Cells(1, Columns.Count).End(xlToLeft).Column - 11
If lngLastColum <= 0 Then lngLastColum = 2
Application.Goto Cells(1, lngLastColum), True
I would suggest the use of API for finding out about the resolution of the screen, code goes into a standard module

Code:
Declare Function GetSystemMetrics Lib "user32" _
    (ByVal nIndex As Long) As Long

Const clngWIDTH As Long = 0
Const clngHEIGHT As Long = 1

Sub API_ScreenResolution()

Dim lngWidth As Long
Dim lngHeight As Long

lngWidth = GetSystemMetrics(clngWIDTH)
lngHeight = GetSystemMetrics(clngHEIGHT)

MsgBox "Screen width:" & vbTab & lngWidth & _
    vbLf & "screen height:" & vbTab & lngHeight, _
    vbInformation, "Screen Resolution"

End Sub
Zoom for the active window

Code:
MsgBox "Zoom is: " & ActiveWindow.Zoom & "%"
Ciao,
Holger
 
Thanks for that....I will give this a go.

IC
 

Users who are viewing this thread

Back
Top Bottom