Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

resize a form i VBA (excel97)

Posted on 2000-04-25
6
Medium Priority
?
376 Views
Last Modified: 2008-03-17
I have a form i an excel workbook. The form is very large, and the workbook will be used both with 800x600 and 1024x800 resolution. The problem is that the form is to big for 800x600.

I would like the form to resize when 800x600 is being used, and then a scroll bar shoud be pressent.

I know I could brake the form in two pages, but I don't like that ide.


Edvard Rognlid
Hammerfest, Norway
0
Comment
Question by:edvardr
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 4

Expert Comment

by:Okki
ID: 2752140
Doing it your way would mean that they would have to scroll in two directions. I personally would have chosen the "Multipage" option because it is simple to work with and you can group things nicely. I would hate to have a scrollbar on a form.

A more fancy solution might be to make a function that check the users screen resolution in during startup (if they use Windows they can find this information here: HKEY_LOCAL_MACHINE\Config\0001\Display\Settings) and open different forms depending on the answer but I think U might do better if U concentrate on the functions of your application rather than the apperance for different users.

Acctually I would suggest that U make a spreadsheet appear just as a form (one of my favorites) and then have the start-macro autozoom that (and other) sheet/sheets.

Johan
Sweden
0
 

Author Comment

by:edvardr
ID: 2761770
Thank you Okki. I'm going to make two different forms depending on the scrren resolution.

But I can't figure out how to get the information from the registry.

Pleace help.


Edvard
0
 
LVL 4

Expert Comment

by:Okki
ID: 2761835
I guess that I could post this as an answer since it works 4 me but I don't know about the norwegian version of Excel.

Open a new workbook and paste the following in a modul:

'32-bit API declaration
Declare Function GetSystemMetrics Lib "user32" _
  (ByVal nIndex As Long) As Long

Public Const SM_CXSCREEN = 0
Public Const SM_CYSCREEN = 1

Sub Video()
    vidWidth = GetSystemMetrics(SM_CXSCREEN)
    vidHeight = GetSystemMetrics(SM_CYSCREEN)
   
    If vidWidth = 800 Then GoTo 800
    If vidWidth = 1024 Then GoTo 1024
    MsgBox "It's not 800 x 600 nor 1024 x 768"
    Exit Sub
800:
    Call Video800
1024:
    Call Video1024
End Sub
Sub Video800()
    Range("D7").Select
    ActiveCell.FormulaR1C1 = "800 x 600"
    Columns("D:D").EntireColumn.AutoFit
End Sub
Sub Video1024()
    Range("D5").Select
    ActiveCell.FormulaR1C1 = "1024 x 768"
    Columns("D:D").EntireColumn.AutoFit
End Sub

If you run the sub "Video" from an empty sheet you should get some action depending if you use 800x600 or 1024x768 of course.
You may add extra resolution alternatives to the code and make sub's for them aswell but I thought 2 alternative's would be enough (as an example) for that brilliant norwegian brain of yours.  =)

Hope U like the solution (I really need some points to be able to make my own questions).

Johan
Sweden
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Accepted Solution

by:
Okki earned 300 total points
ID: 2762270
Sorry, but there should of course be a "Exit Sub" after the two Call functions.

800:
       Call Video800
       Exit Sub
1024:
       Call Video1024
       Exit Sub
End Sub

Johan
(Still in Sweden)
0
 

Author Comment

by:edvardr
ID: 2763823
Thank you.

I did not use the hole code, but you defently pointed me in the right direction and helped a lot.

I only had to use:

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

                      Public Const SM_CXSCREEN = 0
                      Public Const SM_CYSCREEN = 1

and

Sub start()

If GetSystemMetrics(SM_CYSCREEN) < 600 Then
   UserForm4.Show
else
   UserForm1.Show
end if

End Sub

Now I have a following up question - posted as a new question. Maybe you can help me with that.

Edvard
0
 
LVL 4

Expert Comment

by:Okki
ID: 2764944
I'm glad it worked out for you and understand that you didn't need all the code (unless you wanted the resolution printed on a sheet).  I just added the extra code to see if it worked as it was supposed to, but since I use 1024x768 I didn't realize it didn't on my first answer right away.

When I logged in tonight I didn't get my mail before going here and when I saw that I still only had 140 points I thought you had missed my answer. Now I noticed they were given to me as expert points.

Wish I knew the difference and I wonder if I somehow can use them for my own questions.  

I tried to help you with your other question but it didn't quite work out well. Maybee it will guide you in the right direction though.  I'm new to VBA but was very good with Excel4 macros.  I'm doing my best to convert my brain.

Regards,
Johan
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question