vba screen size =

gin2334

New Member
Joined
Oct 16, 2007
Messages
41
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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Try this: right click the Excel logo just to the left of File on the menu bar, select View Code and paste in

Rich (BB 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.
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top