I have a similar problem to Dennis. I am trying to get VB to set a scal for
cells & shapes to suit the current users screen size. I run VB code to change
the sizing of the shapes for the size shed the customer wants. I'm not sure
of the code I need to do this.

Please help
--
SS

Shapes have a Height and Width property.

for each shp in ActiceSheet.Shapes
with shp
.Height = 1.25 * .Height
.Width = 1.37 * .Width
End With
Next

these are also scalewidth and scaleheight properties

Also look at the LockAspectRatio property.

See help for details.

--
Regards,
Tom Ogilvy


"StevenS" wrote in message
...
I have a similar problem to Dennis. I am trying to get VB to set a scal
for
cells & shapes to suit the current users screen size. I run VB code to
change
the sizing of the shapes for the size shed the customer wants. I'm not
sure
of the code I need to do this.

Please help
--
SS

Hi Tom. I have hard coded the top properties for my shapes, this is the
problem. I have to get my programme to determine the new users monitor size
and set new top properties for my shapes. I am not sure on how to get the new
users monitor size and how to give new top values to the shapes.
--
SS


"Tom Ogilvy" wrote:

Shapes have a Height and Width property.

for each shp in ActiceSheet.Shapes
with shp
.Height = 1.25 * .Height
.Width = 1.37 * .Width
End With
Next

these are also scalewidth and scaleheight properties

Also look at the LockAspectRatio property.

See help for details.

--
Regards,
Tom Ogilvy


"StevenS" wrote in message
...
I have a similar problem to Dennis. I am trying to get VB to set a scal
for
cells & shapes to suit the current users screen size. I run VB code to
change
the sizing of the shapes for the size shed the customer wants. I'm not
sure
of the code I need to do this.

Please help
--
SS

Everything you wanted to know about monitors but were affraid to ask...

Declare Function GetDeviceCaps Lib "Gdi32" _
(ByVal hdc As Long, ByVal nIndex As Long) As Long
Declare Function GetDC Lib "User32" _
(ByVal hWnd As Long) As Long
Declare Function ReleaseDC Lib "User32" _
(ByVal hWnd As Long, ByVal hdc As Long) As Long

Public Function HRes() As Integer
'Returns the horizontal resolution in pixels
Dim lDC As Long
lDC = GetDC(0)
HRes = GetDeviceCaps(lDC, 8)
ReleaseDC 0, lDC
End Function

Public Function VRes() As Integer
'Returns the vertical resolution in pixels
Dim lDC As Long
lDC = GetDC(0)
VRes = GetDeviceCaps(lDC, 10)
ReleaseDC 0, lDC
End Function

Public Function ColorDepth() As Integer
'Returns the color depth in bits per pixel
Dim lDC As Long
lDC = GetDC(0)
ColorDepth = GetDeviceCaps(lDC, 12) _
* GetDeviceCaps(lDC, 14)
ReleaseDC 0, lDC
End Function

Public Function Colors() As Single
'Returns the number of available colors
Dim lDC As Long
lDC = GetDC(0)
Colors = 2 ^ (GetDeviceCaps(lDC, 12) _
* GetDeviceCaps(lDC, 14))
ReleaseDC 0, lDC
End Function

Public Function VRefresh() As Integer
'Returns the vertical refresh rate in Hz
Dim lDC As Long
lDC = GetDC(0)
VRefresh = GetDeviceCaps(lDC, 116)
ReleaseDC 0, lDC
End Function

--
HTH...

Jim Thomlinson


"StevenS" wrote:

Hi Tom. I have hard coded the top properties for my shapes, this is the
problem. I have to get my programme to determine the new users monitor size
and set new top properties for my shapes. I am not sure on how to get the new
users monitor size and how to give new top values to the shapes.
--
SS


"Tom Ogilvy" wrote:

Shapes have a Height and Width property.

for each shp in ActiceSheet.Shapes
with shp
.Height = 1.25 * .Height
.Width = 1.37 * .Width
End With
Next

these are also scalewidth and scaleheight properties

Also look at the LockAspectRatio property.

See help for details.

--
Regards,
Tom Ogilvy


"StevenS" wrote in message
...
I have a similar problem to Dennis. I am trying to get VB to set a scal
for
cells & shapes to suit the current users screen size. I run VB code to
change
the sizing of the shapes for the size shed the customer wants. I'm not
sure
of the code I need to do this.

Please help
--
SS

Excellent. Excuse my ignorace. Where should this information be placed. When
my user opens the programme I want it to fit everything to the screen.
--
SS


"Jim Thomlinson" wrote:

Everything you wanted to know about monitors but were affraid to ask...

Declare Function GetDeviceCaps Lib "Gdi32" _
(ByVal hdc As Long, ByVal nIndex As Long) As Long
Declare Function GetDC Lib "User32" _
(ByVal hWnd As Long) As Long
Declare Function ReleaseDC Lib "User32" _
(ByVal hWnd As Long, ByVal hdc As Long) As Long

Public Function HRes() As Integer
'Returns the horizontal resolution in pixels
Dim lDC As Long
lDC = GetDC(0)
HRes = GetDeviceCaps(lDC, 8)
ReleaseDC 0, lDC
End Function

Public Function VRes() As Integer
'Returns the vertical resolution in pixels
Dim lDC As Long
lDC = GetDC(0)
VRes = GetDeviceCaps(lDC, 10)
ReleaseDC 0, lDC
End Function

Public Function ColorDepth() As Integer
'Returns the color depth in bits per pixel
Dim lDC As Long
lDC = GetDC(0)
ColorDepth = GetDeviceCaps(lDC, 12) _
* GetDeviceCaps(lDC, 14)
ReleaseDC 0, lDC
End Function

Public Function Colors() As Single
'Returns the number of available colors
Dim lDC As Long
lDC = GetDC(0)
Colors = 2 ^ (GetDeviceCaps(lDC, 12) _
* GetDeviceCaps(lDC, 14))
ReleaseDC 0, lDC
End Function

Public Function VRefresh() As Integer
'Returns the vertical refresh rate in Hz
Dim lDC As Long
lDC = GetDC(0)
VRefresh = GetDeviceCaps(lDC, 116)
ReleaseDC 0, lDC
End Function

--
HTH...

Jim Thomlinson


"StevenS" wrote:

Hi Tom. I have hard coded the top properties for my shapes, this is the
problem. I have to get my programme to determine the new users monitor size
and set new top properties for my shapes. I am not sure on how to get the new
users monitor size and how to give new top values to the shapes.
--
SS


"Tom Ogilvy" wrote:

Shapes have a Height and Width property.

for each shp in ActiceSheet.Shapes
with shp
.Height = 1.25 * .Height
.Width = 1.37 * .Width
End With
Next

these are also scalewidth and scaleheight properties

Also look at the LockAspectRatio property.

See help for details.

--
Regards,
Tom Ogilvy


"StevenS" wrote in message
...
I have a similar problem to Dennis. I am trying to get VB to set a scal
for
cells & shapes to suit the current users screen size. I run VB code to
change
the sizing of the shapes for the size shed the customer wants. I'm not
sure
of the code I need to do this.

Please help
--
SS

This code can just be placed into a regular code module. You can get the
screen resolution by adding the function VRes and HRes.
--
HTH...

Jim Thomlinson


"StevenS" wrote:

Excellent. Excuse my ignorace. Where should this information be placed. When
my user opens the programme I want it to fit everything to the screen.
--
SS


"Jim Thomlinson" wrote:

Everything you wanted to know about monitors but were affraid to ask...

Declare Function GetDeviceCaps Lib "Gdi32" _
(ByVal hdc As Long, ByVal nIndex As Long) As Long
Declare Function GetDC Lib "User32" _
(ByVal hWnd As Long) As Long
Declare Function ReleaseDC Lib "User32" _
(ByVal hWnd As Long, ByVal hdc As Long) As Long

Public Function HRes() As Integer
'Returns the horizontal resolution in pixels
Dim lDC As Long
lDC = GetDC(0)
HRes = GetDeviceCaps(lDC, 8)
ReleaseDC 0, lDC
End Function

Public Function VRes() As Integer
'Returns the vertical resolution in pixels
Dim lDC As Long
lDC = GetDC(0)
VRes = GetDeviceCaps(lDC, 10)
ReleaseDC 0, lDC
End Function

Public Function ColorDepth() As Integer
'Returns the color depth in bits per pixel
Dim lDC As Long
lDC = GetDC(0)
ColorDepth = GetDeviceCaps(lDC, 12) _
* GetDeviceCaps(lDC, 14)
ReleaseDC 0, lDC
End Function

Public Function Colors() As Single
'Returns the number of available colors
Dim lDC As Long
lDC = GetDC(0)
Colors = 2 ^ (GetDeviceCaps(lDC, 12) _
* GetDeviceCaps(lDC, 14))
ReleaseDC 0, lDC
End Function

Public Function VRefresh() As Integer
'Returns the vertical refresh rate in Hz
Dim lDC As Long
lDC = GetDC(0)
VRefresh = GetDeviceCaps(lDC, 116)
ReleaseDC 0, lDC
End Function

--
HTH...

Jim Thomlinson


"StevenS" wrote:

Hi Tom. I have hard coded the top properties for my shapes, this is the
problem. I have to get my programme to determine the new users monitor size
and set new top properties for my shapes. I am not sure on how to get the new
users monitor size and how to give new top values to the shapes.
--
SS


"Tom Ogilvy" wrote:

Shapes have a Height and Width property.

for each shp in ActiceSheet.Shapes
with shp
.Height = 1.25 * .Height
.Width = 1.37 * .Width
End With
Next

these are also scalewidth and scaleheight properties

Also look at the LockAspectRatio property.

See help for details.

--
Regards,
Tom Ogilvy


"StevenS" wrote in message
...
I have a similar problem to Dennis. I am trying to get VB to set a scal
for
cells & shapes to suit the current users screen size. I run VB code to
change
the sizing of the shapes for the size shed the customer wants. I'm not
sure
of the code I need to do this.

Please help
--
SS

Unless you are going to predetermine the locations of your shapes for each
resolution setting, I think you can skip all the API stuff and just work
with

set rng = ActiveWindow.VisibleRange

If Excel is not full screen, then knowing the resolution doesn't buy you
much.

Assume you want one shape to start at the top if the visible range and
centered horizontally

Sub EFG()
Set shp = ActiveSheet.Shapes(1)
With shp
.Top = ActiveWindow.VisibleRange.Top
.Left = ActiveWindow.VisibleRange.Width / 2 - .Width / 2
End With

End Sub

This is just an example - you would have to adapt it to accomplish what you
want to do.

--
Regards,



"StevenS" wrote in message
...
Excellent. Excuse my ignorace. Where should this information be placed.
When
my user opens the programme I want it to fit everything to the screen.
--
SS


"Jim Thomlinson" wrote:

Everything you wanted to know about monitors but were affraid to ask...

Declare Function GetDeviceCaps Lib "Gdi32" _
(ByVal hdc As Long, ByVal nIndex As Long) As Long
Declare Function GetDC Lib "User32" _
(ByVal hWnd As Long) As Long
Declare Function ReleaseDC Lib "User32" _
(ByVal hWnd As Long, ByVal hdc As Long) As Long

Public Function HRes() As Integer
'Returns the horizontal resolution in pixels
Dim lDC As Long
lDC = GetDC(0)
HRes = GetDeviceCaps(lDC, 8)
ReleaseDC 0, lDC
End Function

Public Function VRes() As Integer
'Returns the vertical resolution in pixels
Dim lDC As Long
lDC = GetDC(0)
VRes = GetDeviceCaps(lDC, 10)
ReleaseDC 0, lDC
End Function

Public Function ColorDepth() As Integer
'Returns the color depth in bits per pixel
Dim lDC As Long
lDC = GetDC(0)
ColorDepth = GetDeviceCaps(lDC, 12) _
* GetDeviceCaps(lDC, 14)
ReleaseDC 0, lDC
End Function

Public Function Colors() As Single
'Returns the number of available colors
Dim lDC As Long
lDC = GetDC(0)
Colors = 2 ^ (GetDeviceCaps(lDC, 12) _
* GetDeviceCaps(lDC, 14))
ReleaseDC 0, lDC
End Function

Public Function VRefresh() As Integer
'Returns the vertical refresh rate in Hz
Dim lDC As Long
lDC = GetDC(0)
VRefresh = GetDeviceCaps(lDC, 116)
ReleaseDC 0, lDC
End Function

--
HTH...

Jim Thomlinson


"StevenS" wrote:

Hi Tom. I have hard coded the top properties for my shapes, this is
the
problem. I have to get my programme to determine the new users monitor
size
and set new top properties for my shapes. I am not sure on how to get
the new
users monitor size and how to give new top values to the shapes.
--
SS


"Tom Ogilvy" wrote:

Shapes have a Height and Width property.

for each shp in ActiceSheet.Shapes
with shp
.Height = 1.25 * .Height
.Width = 1.37 * .Width
End With
Next

these are also scalewidth and scaleheight properties

Also look at the LockAspectRatio property.

See help for details.

--
Regards,
Tom Ogilvy


"StevenS" wrote in message
...
I have a similar problem to Dennis. I am trying to get VB to set a
scal
for
cells & shapes to suit the current users screen size. I run VB
code to
change
the sizing of the shapes for the size shed the customer wants. I'm
not
sure
of the code I need to do this.

Please help
--
SS