Results 1 to 8 of 8

Thread: Set zoom (2003)

  1. #1
    Star Lounger
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Set zoom (2003)

    I'm wondering whether there's a way to cause a userform to zoom itself so that the screen display shows, say, columns A to P. This would include the working part of the screen plus one column for more. With my resolution and monitor size, this comes to a zoom of about 65, but it would vary from workstation to workstation. Any suggestions as to approach?

    Many thanks,

    Ann

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Set zoom (2003)

    Sorry, I don't understand - you can move around a userform the way you want, so what exactly do you mean by "so that the screen display shows, say, columns A to P"?

  3. #3
    Star Lounger
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Set zoom (2003)

    Oops, oops, oops. I should have proofread better. I meant to ask whether it's possible to set the screen view zoom to some variable number that displays a predetermined number of columns.

    I have a spreadsheet that's e-mailed back and forth and somehow ends up opening to show a screen either much enlarged so only a small part of the working section is visible, or magically appearing at 25% (and my, did THAT confuse the user!) so that all the range names show but nothing much else does.

    I'm wondering whether there is some way to use the Zoom/Selection option, for instance. Has anyone ever done this?

    Thanks,

    Ann

    I should specify that ideally the degree of zoom would be set whenever the document is opened.

  4. #4
    Star Lounger
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Set zoom (2003)

    Well, there's nothing like having to frame a question to clarify the thinking! I went back and tried this, and it seems to do what I want it to do when the macro's run. Now, how do I get it to run on opening the worksheet? It would have to be an instruction tucked in the worksheets, which are passed back and forth, rather than in a personal.xls file.

    Columns("A:Q").Select
    ActiveWindow.Zoom = True

    seems to set the view as I want it.

    Thanks,

    Ann

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Set zoom (2003)

    Open the workbook.
    Activate the Visual Basic Editor.
    Double click ThisWorkbook in the Project Explorer.
    Select Workbook in the dropdown list in the upper left corner of the module window that appears. This creates a Workbook_Open event procedure.
    Make the code look like this:

    Private Sub Workbook_Open()
    Worksheets("MySheet").Activate
    Columns("A:Q").Select
    ActiveWindow.Zoom = True
    End Sub

    where MySheet is the name of the worksheet you want to be active when the workbook opens.

  6. #6
    Star Lounger
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Set zoom (2003)

    Works perfectly. Thank you again!

    Ann

  7. #7
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Set zoom (2003)

    Thanks, axsc, for formulating a question which interests me, and you, Hans, for providing a detailed solution which works.

  8. #8
    Star Lounger
    Join Date
    Jul 2006
    Posts
    71
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Set zoom (2003)

    I discovered one hiccup with it which you may find of interest:

    I have three similar spreadsheets and two of them have a named range to the right of the main body. The named range has a merged-cell caption. I started out trying to tweak the version without the named range, and that went well, thanks to all the expert input from the Loungers, but when I applied the same code to the second sheet, I wanted one or two columns of the named range to be visible -- just to remind the user that it's there and needs to be dealt with.

    Ho! I could select the columns I wanted manually and have the view as I wanted it, but the moment I let the *macro* do it, I got the entire named range as part of the view. After a few trials, I guessed that the reason VBA insisted on selecting the whole named range was because of the merged-cell caption, so I unmerged it, left the left and right columns ("Sunday" and "Saturday") unmerged, and re-merged the heading over the middle columns ("Monday" to "Friday"). Worked perfectly after that -- "Sunday" shows in the basic view -- and a little tweaking of borders makes it *look* as if the caption is centered over the whole week.

    Just one more little thing to suspect if your code isn't doing what you told it to.

    Ann

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •