Greetings,

I have a workbook with a UserForm that is accessed by five different
computers. On some the UserForm fills the screen and on others it
fills about a third of the screen.

Is there anyway to get vba to check what machine is opening the
UserForm and adjust the form accordingly?

Any help is greatly appreciated.

TIA

-Minitman

Could you check the Username?

Environ("UserName")

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"Minitman" wrote in message
...
Greetings,

I have a workbook with a UserForm that is accessed by five different
computers. On some the UserForm fills the screen and on others it
fills about a third of the screen.

Is there anyway to get vba to check what machine is opening the
UserForm and adjust the form accordingly?

Any help is greatly appreciated.

TIA

-Minitman

Hey Bob,

Thanks for the reply.

I'm not sure. How would that keep the UserForm at full screen on
different computers with different resolutions? It is set for full
screen at 640 x 480 on my machine. But some of the machines will not
go down to that resolution and I'm stuck with 800 x 600 (smaller sized
UserForm). At that smaller size, I have difficulty making out what is
in the TextBoxes (I work on all of the machines from time to time).

Is there anyway to have the UserForm automatically fill the screen, no
matter what the native resolution is?

-Minitman


On Mon, 4 Dec 2006 23:25:42 -0000, "Bob Phillips"
wrote:

Could you check the Username?

Environ("UserName")

This will automatically adjust the form size depending on the
screen resolution. You should adjust the multiplication
factor and the resolution increments to suit.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long
Const SM_CXSCREEN = 0
Const SM_CYSCREEN = 1
'----------------------------------------------

Public Function GetSR() As String
GetSR = CStr(GetSystemMetrics(SM_CXSCREEN)) & " x " & _
CStr(GetSystemMetrics(SM_CYSCREEN))
End Function
'----------------------------------------------

Sub ResizeForm()
' Jim Cone - San Francisco, USA
Dim lngSize As Long
Dim strSR As String
Dim lngMax As Long

strSR = GetSR
lngMax = Val(strSR)

If lngMax 1200 Then 'resolution
lngSize = 100 '<< larger num bigger form
ElseIf lngMax 1000 Then
lngSize = 80
ElseIf lngMax 799 Then
lngSize = 70
Else
lngSize = 50
End If

UserForm1.Zoom = lngSize
UserForm1.Width = UserForm1.Width * (lngSize / 100)
UserForm1.Height = UserForm1.Height * (lngSize / 100)
UserForm1.Show
Unload UserForm1
Set UserForm1 = Nothing
End Sub
'------------------------------------

Hey Jim,

Thanks for the reply.

This is beyond my current level of understanding, but I think I can
find out the references to tell me what is going on. It will take me
a couple of days to muddle though it. It looks like what I need at
first glance.

I will give this a shot. Thanks.

-Minitman

On Mon, 4 Dec 2006 21:18:07 -0800, "Jim Cone"
wrote:

This will automatically adjust the form size depending on the
screen resolution. You should adjust the multiplication
factor and the resolution increments to suit.

Sorry, I thought that you had that code, you just wanted to know some way of
tseting before invoking it.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"Bob Phillips" wrote in message
...
Could you check the Username?

Environ("UserName")

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)
"Minitman" wrote in message
...
Greetings,

I have a workbook with a UserForm that is accessed by five different
computers. On some the UserForm fills the screen and on others it
fills about a third of the screen.

Is there anyway to get vba to check what machine is opening the
UserForm and adjust the form accordingly?

Any help is greatly appreciated.

TIA

-Minitman

This is a modified code version that should require less tinkering.
It also should be easier to use as you only have to adjust the
BaseX and BaseY values before running the code.
(also change the userform name to the actual name)
There are some explanatory notes included in the code.

Note: The Declare Function GetSystemMetrics code line goes at the
very top of the module just below "Option Explicit"
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


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

'---------------------------------
Public Function GetSR() As Variant
' x and y
GetSR = Array(GetSystemMetrics(0), GetSystemMetrics(1))
End Function
'---------------------------------

Sub ResizeForm_R1()
' Adjusts userform size to compensate for screen resolution changes.
' Jim Cone - San Francisco, USA - Dec 2006
Dim varSize As Variant
Dim RatioX As Single
Dim RatioY As Single
Dim ActualX As Long
Dim ActualY As Long

'Screen resolution in development environment.
'Adjust as necessary.
Const BaseX As Long = 800
Const BaseY As Long = 600

'Call function to get actual screen resolution
varSize = GetSR
ActualX = varSize(0)
ActualY = varSize(1)

'Determine ratio of actual screen resolution to
'the original or base resolution.
RatioX = ActualX / BaseX
RatioY = ActualY / BaseY

'Adjust userform magnification and size.
UserForm1.Zoom = (100 * ((RatioX + RatioY) / 2))
UserForm1.Width = UserForm1.Width * RatioX
UserForm1.Height = UserForm1.Height * RatioY
UserForm1.Show
Unload UserForm1
Set UserForm1 = Nothing
End Sub
--------------


"Minitman"
wrote in message
Hey Jim,
Thanks for the reply.
This is beyond my current level of understanding, but I think I can
find out the references to tell me what is going on. It will take me
a couple of days to muddle though it. It looks like what I need at
first glance.
I will give this a shot. Thanks.
-Minitman


On Mon, 4 Dec 2006 21:18:07 -0800, "Jim Cone"

wrote:
This will automatically adjust the form size depending on the
screen resolution. You should adjust the multiplication
factor and the resolution increments to suit.

Hey Jim,

Thanks For the code.

I seem to have a problem using resolution as the trigger.

I have two machines both with 800 x 600 resolution. One opens the
workbook and UserForm full screen and the other opens them much
smaller! A different machine is set to 640 x 480 opens full screen.
The forms were built at 640 x 480 resolution.

It seems that using the name of the machine that Bob Phillips
suggested, might be the only way to accomplish the goal of full screen
viewing on all machines.

-Minitman



On Tue, 5 Dec 2006 11:59:46 -0800, "Jim Cone"
wrote:


This is a modified code version that should require less tinkering.
It also should be easier to use as you only have to adjust the
BaseX and BaseY values before running the code.
(also change the userform name to the actual name)
There are some explanatory notes included in the code.

Note: The Declare Function GetSystemMetrics code line goes at the
very top of the module just below "Option Explicit"

Hey Bob,

It turns out that the computer name is the best way to trigger the
size change. I only need to change one machine of my 5.

Your suggestion of using Environ("UserName") modified with
Environ("ComputerName") works very well. Thank you.

-Minitman



On Tue, 5 Dec 2006 12:00:08 -0000, "Bob Phillips"
wrote:

Sorry, I thought that you had that code, you just wanted to know some way of
testing before invoking it.

Are all the monitors the same size?
Jim Cone


"Minitman"
wrote in message
Hey Jim,

Thanks For the code.
I seem to have a problem using resolution as the trigger.

I have two machines both with 800 x 600 resolution. One opens the
workbook and UserForm full screen and the other opens them much
smaller! A different machine is set to 640 x 480 opens full screen.
The forms were built at 640 x 480 resolution.

It seems that using the name of the machine that Bob Phillips
suggested, might be the only way to accomplish the goal of full screen
viewing on all machines.
-Minitman



On Tue, 5 Dec 2006 11:59:46 -0800, "Jim Cone"

wrote:

This is a modified code version that should require less tinkering.
It also should be easier to use as you only have to adjust the
BaseX and BaseY values before running the code.
(also change the userform name to the actual name)
There are some explanatory notes included in the code.

Note: The Declare Function GetSystemMetrics code line goes at the
very top of the module just below "Option Explicit"

Serendipity strikes again <bg

Bob

"Minitman" wrote in message
...
Hey Bob,

It turns out that the computer name is the best way to trigger the
size change. I only need to change one machine of my 5.

Your suggestion of using Environ("UserName") modified with
Environ("ComputerName") works very well. Thank you.

-Minitman



On Tue, 5 Dec 2006 12:00:08 -0000, "Bob Phillips"
wrote:

Sorry, I thought that you had that code, you just wanted to know some way
of
testing before invoking it.

Don't you have to hard code a list of computer and user names with screen
resolution? What if one of the users gets a new monitor, or changes the
current monitor's settings? What if the program is used by another user or
on another computer? I'd dig into Jim's approach a little deeper to figure
out how to make it work.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"Minitman" wrote in message
...
Hey Bob,

It turns out that the computer name is the best way to trigger the
size change. I only need to change one machine of my 5.

Your suggestion of using Environ("UserName") modified with
Environ("ComputerName") works very well. Thank you.

-Minitman



On Tue, 5 Dec 2006 12:00:08 -0000, "Bob Phillips"
wrote:

Sorry, I thought that you had that code, you just wanted to know some way
of
testing before invoking it.