Link to home
Start Free TrialLog in
Avatar of edvardr
edvardr

asked on

resize a form i VBA (excel97)

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
Avatar of Okki
Okki

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
Avatar of edvardr

ASKER

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
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
ASKER CERTIFIED SOLUTION
Avatar of Okki
Okki

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of edvardr

ASKER

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
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