Results 1 to 7 of 7

vba screen size =

This is a discussion on vba screen size = within the Excel Questions forums, part of the Question Forums category; Heyup all, First of all thanks to everyone who helped me out with the =sumproduct queries, everything with my MI ...

  1. #1
    New Member
    Join Date
    Oct 2007
    Location
    leeds, west yorkshire
    Posts
    41

    Default vba screen size =

    Heyup all,

    First of all thanks to everyone who helped me out with the =sumproduct queries, everything with my MI pack is working great!


    Im a little stumped with a bit of VBA coding and wondering if anyone can help,

    Ive created a submission form that people will open in readonly......I'm trying to set it up so that when they open this form, "mainly for visial purposes" I want to set the window width and height to fit around the form and if possible, eliminate all the options from the top of the window leaving only the file - edit etc options. Essentially giving the impression that the form is a standalone "program" for better choice of words.

    I've seen it done, any help???

    cheers

    Paul

  2. #2
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    61,512

    Default Re: vba screen size =

    In design mode right click the userform, select View Code and paste in

    Code:
    Private Sub UserForm_Activate()
    With Application
        Me.Top = .Top
        Me.Left = .Left
        Me.Height = .Height
        Me.Width = .Width
    End With
    End Sub
    When you show the form it should fill the window.
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    10,982

    Default Re: vba screen size =

    This will not only fill the window but spacially distribute the controls.

    Code:
     
     
    Private Sub UserForm_Initialize()
    With Application
    .WindowState = xlMaximized
    Zoom = Int(.Width / Me.Width * 100)
    Width = .Width
    Height = .Height
    End With
    End Sub

  4. #4
    Board Regular
    Join Date
    Oct 2008
    Location
    Derby(home), Derby/London(work)
    Posts
    296

    Default Re: vba screen size =

    Ive just stumbled upon this code and its amazing!! I didnt know this could be done!

    One issue though is using the following code
    Code:
    Private Sub UserForm_Initialize()
    With Application
    .WindowState = xlMaximized
    Zoom = Int(.Width / Me.Width * 100)
    Width = .Width
    Height = .Height
    End With
    End Sub
    the bottom of the code goes below my screen and behind the taskbar so some parts are hidden, is there a way a can stop this? or fix the width and height?
    i type lol but really im like :|

    Professional Website Solutions

  5. #5
    MrExcel MVP Tom Urtis's Avatar
    Join Date
    Feb 2002
    Location
    San Francisco, California USA
    Posts
    10,982

    Default Re: vba screen size =

    Here's my take on this, just my approach, others may well have a different approach that works for them, and that's how it goes with Excel sometimes.

    I understand what you are referring to, and the problem with this issue is, some userforms are much wider than they are tall, or much taller than they are wide. To exactly fit a userform on a screen and have its controls not look misshapen should, in my opinion, take into consideration the shape and intent of the userform. Generally, only by happenstance is a userform sized in advance of a perfect full-screen fit, as its primary purpose is to be a user-friendly functional interface.

    To me then, a full screen fit can compromise the look of controls by making them look wide and fat or tall and skinny, so it'd be wise to seek a common sense balance of visual normalcy and "as full screen as possible".

    This is an example of how to do that. I created a userform with around four dozen controls (optionbuttons, labels, checkboxes, commandbuttons, listboxes, comboboxes, the whole mishmash), and sized the form at 560 height and 684 width. This code sizes that userform on the screen as good as I would expect, with the controls still looking proportional.

    Code:
    Private Sub UserForm_Initialize()
    With Application
    .WindowState = xlMaximized
    Zoom = Int((.Width * 0.9) / (Width * 0.9) * 75)
    Width = .Width * 0.75
    Height = .Height
    End With
    End Sub
    The idea is to modify the basic code by shaving off a proportional percentage of height, width, and zoom, depending on your form.

  6. #6
    New Member
    Join Date
    Oct 2007
    Location
    leeds, west yorkshire
    Posts
    41

    Default Re: vba screen size =

    wow, all of that just baffled the hell out of me :O)

    my query was much simpler as Im simply not that good yet, when I said user form, what I meant was a simple spreadsheet with a macro button that copies and pastes the information from the spreadsheet into a rawdata workbook and then clears the spreadsheet ready for a new user to put details in.......thats about as advanced a user form as I can do right now lol

    however I have still seen it done where a macro is set that when a workbook is open, the size of the window is defined on open to fit around the "cells" that are to be used, giving the impression of a standalone uderform, scroll bars are removed etc so that the user can do nothing other than use the spreadsheet and nothing else.

    any ideas how?

    cheers all

  7. #7
    VoG
    VoG is offline
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    61,512

    Default Re: vba screen size =

    Try this: right click the Excel logo just to the left of File on the menu bar, select View Code and paste in

    Code:
    Private Sub Workbook_Open()
    Application.ScreenUpdating = False
    With Sheets("Sheet1")
        .Select
        .UsedRange.Select
        ActiveWindow.Zoom = True
        .Range("A1").Select
    End With
    Application.ScreenUpdating = True
    End Sub
    Change the sheet name to suit then save, close and re-open the workbook to see the effect.
    HTH, Peter
    Please test any code on a copy of your workbook.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com