Solved

resize a form i VBA (excel97)

Posted on 2000-04-25
6
366 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
  • 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 4

Accepted Solution

by:
Okki earned 100 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

This article will show you how to use shortcut menus in the Access run-time environment.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This video shows and describes the main difference between both orientations in Microsoft Word. Viewers will understand when to use each orientation and how to get the most out of them.
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now