Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Centralizing a command button on a userform
I have a userform which sizes to the screen using the following code
Private Sub UserForm_Initialize() With Me .Height = Application.Height .Width = Application.Width .Left = Application.Left .Top = Application.Top End With End Sub I have a command button on the userform, but I would like it to be always centered on the page. Is there a way to do this? Many thanks, Roger |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Centralizing a command button on a userform
Roger,
If you know the height and width of your command button, then after you set the size of your UserForm (with the code you posted), do a little math to get the center coordinates, do some more math to get the left and top coordiates for the command button, and set the .Left and .Top properties of the command button. (You know half the height and half the width of the UserForm. You also know half the command button height and half the command button width. The reset is addition/subtraction). Best, Matthew Herbert "Roger on Excel" wrote: I have a userform which sizes to the screen using the following code Private Sub UserForm_Initialize() With Me .Height = Application.Height .Width = Application.Width .Left = Application.Left .Top = Application.Top End With End Sub I have a command button on the userform, but I would like it to be always centered on the page. Is there a way to do this? Many thanks, Roger |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Centralizing a command button on a userform
Hi Mathew,
Thats fine for a particular screen resolution. Is there a way to make the code adjust for screen resolution - the auto sizing of the userform with the code i use works great, but i need the button to adjust its position accordingly too. Any ideas? Roger "Matthew Herbert" wrote: Roger, If you know the height and width of your command button, then after you set the size of your UserForm (with the code you posted), do a little math to get the center coordinates, do some more math to get the left and top coordiates for the command button, and set the .Left and .Top properties of the command button. (You know half the height and half the width of the UserForm. You also know half the command button height and half the command button width. The reset is addition/subtraction). Best, Matthew Herbert "Roger on Excel" wrote: I have a userform which sizes to the screen using the following code Private Sub UserForm_Initialize() With Me .Height = Application.Height .Width = Application.Width .Left = Application.Left .Top = Application.Top End With End Sub I have a command button on the userform, but I would like it to be always centered on the page. Is there a way to do this? Many thanks, Roger |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Centralizing a command button on a userform
To calculate for a specified resolution you use the numbers that you know
(i.e.800x600). To calculate for the auto-sized screen, wouldn't you just use the variables Application.Height, Application.Width, etc.? "Roger on Excel" wrote: Hi Mathew, Thats fine for a particular screen resolution. Is there a way to make the code adjust for screen resolution - the auto sizing of the userform with the code i use works great, but i need the button to adjust its position accordingly too. Any ideas? Roger "Matthew Herbert" wrote: Roger, If you know the height and width of your command button, then after you set the size of your UserForm (with the code you posted), do a little math to get the center coordinates, do some more math to get the left and top coordiates for the command button, and set the .Left and .Top properties of the command button. (You know half the height and half the width of the UserForm. You also know half the command button height and half the command button width. The reset is addition/subtraction). Best, Matthew Herbert "Roger on Excel" wrote: I have a userform which sizes to the screen using the following code Private Sub UserForm_Initialize() With Me .Height = Application.Height .Width = Application.Width .Left = Application.Left .Top = Application.Top End With End Sub I have a command button on the userform, but I would like it to be always centered on the page. Is there a way to do this? Many thanks, Roger |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Centralizing a command button on a userform
Roger,
As fisch4bill mentioned, Application.Height will give you the height of the application. If the application is maximized then you essentially know the screen resolution via Application.Height and Application.Width. Maybe I'm just not understanding your situation. Is there more detail that you can provide? (Or are you maybe referring to ths UserForm startup position, which can be changed via the "StartUpPosition" property of a UserForm?) (In general, if you are looking fo the screen resolution then you'll need to use the GetSystemMetrics API. The argument 0, more so seen as "Private Const SM_CXSCREEN As Long = 0" or some variation of a Const statement, gives you the resolution of X; and the argument 1, more so seen as "Private Const SM_CYSCREEN As Long = 1" or some variation of a Const statement, gives you the resolution of Y. The API function declaration is "Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long". So, GetSystemMetrics(SM_CYSCREEN), or GetSystemMetrics(1), will return the resolution of Y). Best, Matt "Roger on Excel" wrote: Hi Mathew, Thats fine for a particular screen resolution. Is there a way to make the code adjust for screen resolution - the auto sizing of the userform with the code i use works great, but i need the button to adjust its position accordingly too. Any ideas? Roger "Matthew Herbert" wrote: Roger, If you know the height and width of your command button, then after you set the size of your UserForm (with the code you posted), do a little math to get the center coordinates, do some more math to get the left and top coordiates for the command button, and set the .Left and .Top properties of the command button. (You know half the height and half the width of the UserForm. You also know half the command button height and half the command button width. The reset is addition/subtraction). Best, Matthew Herbert "Roger on Excel" wrote: I have a userform which sizes to the screen using the following code Private Sub UserForm_Initialize() With Me .Height = Application.Height .Width = Application.Width .Left = Application.Left .Top = Application.Top End With End Sub I have a command button on the userform, but I would like it to be always centered on the page. Is there a way to do this? Many thanks, Roger |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Centralizing a command button on a userform
I am sorry guys,
I read my post and I think I wasnt clear enough in describing my problem. I use userforms to drive my spreadsheet. The main userform sits in the center of the screen and is sized to fit a 1024/768 screen resolution. I set it this way as most of the PC's I use have this resolution and if the resolution is higher the userform sits neatly in the middle of the screen using the correctly selected userform position stting. Now, here is my problem. Behind the main userform is a background userform which effectively hides excel in the background. I set this to activate at the workbook startup. This autosizes to fit the screen whatever the screen resolution is using the code i posted at the start of this thread. This way the background is always hiddem from view On the background userform is a button to recall the main userform if the user inadvertedly closes it. I would like this to have its position dictated by code rather than manually entering the position attributes. This way I could have the button always be in the center of the autosizing background userform. At present I can manually position it so it hides behind the main userform, but as a perfectionist, i would like to explore better options to make it go in the center. What do you think? Roger "Matthew Herbert" wrote: Roger, As fisch4bill mentioned, Application.Height will give you the height of the application. If the application is maximized then you essentially know the screen resolution via Application.Height and Application.Width. Maybe I'm just not understanding your situation. Is there more detail that you can provide? (Or are you maybe referring to ths UserForm startup position, which can be changed via the "StartUpPosition" property of a UserForm?) (In general, if you are looking fo the screen resolution then you'll need to use the GetSystemMetrics API. The argument 0, more so seen as "Private Const SM_CXSCREEN As Long = 0" or some variation of a Const statement, gives you the resolution of X; and the argument 1, more so seen as "Private Const SM_CYSCREEN As Long = 1" or some variation of a Const statement, gives you the resolution of Y. The API function declaration is "Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long". So, GetSystemMetrics(SM_CYSCREEN), or GetSystemMetrics(1), will return the resolution of Y). Best, Matt "Roger on Excel" wrote: Hi Mathew, Thats fine for a particular screen resolution. Is there a way to make the code adjust for screen resolution - the auto sizing of the userform with the code i use works great, but i need the button to adjust its position accordingly too. Any ideas? Roger "Matthew Herbert" wrote: Roger, If you know the height and width of your command button, then after you set the size of your UserForm (with the code you posted), do a little math to get the center coordinates, do some more math to get the left and top coordiates for the command button, and set the .Left and .Top properties of the command button. (You know half the height and half the width of the UserForm. You also know half the command button height and half the command button width. The reset is addition/subtraction). Best, Matthew Herbert "Roger on Excel" wrote: I have a userform which sizes to the screen using the following code Private Sub UserForm_Initialize() With Me .Height = Application.Height .Width = Application.Width .Left = Application.Left .Top = Application.Top End With End Sub I have a command button on the userform, but I would like it to be always centered on the page. Is there a way to do this? Many thanks, Roger |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Centralizing a command button on a userform
Roger,
UserForm1: The "hide Excel" UserForm (i.e. your "background") - Also has a "CommandButton1" button UserForm2: The "main" UserForm (i.e. your "main") Instructions: 1. Open a blank workbook 2. Open VBE Do the following to the blank workbooks VBAProject: 3. Add two user forms 4. On the first user form (i.e. UserForm1), add a command button (i.e. CommandButton1) anywhere you like 5. Copy the "UserForm1 Code Module:" code portion (the code is below) and past it into the UserForm1 code window 6. Add a module (i.e. Module1) 7. Copy the "General/Standard Module Level Code:" code portion (the code is below) into the Module1 code window 8. Run Macro "ABC" Let me know if I addressed your issue. Best, Matthew Herbert '------------------------------------------------------------------------ UserForm1 Code Module: Private Sub CommandButton1_Click() UserForm2.Show End Sub Private Sub UserForm_Initialize() Dim sngPxl As Single Dim sngHgt As Single Dim sngWdt As Single Dim sngHgtMid As Single Dim sngWdtMid As Single 'get the screen size in order to size the user form sngPxl = PointsPerPixel sngHgt = SystemMetrics(smScreenHeight) * sngPxl sngWdt = SystemMetrics(smScreenWidth) * sngPxl 'size the user form With Me .Height = sngHgt .Width = sngWdt .Left = 0 .Top = 0 End With 'get the center coordinates of the user form sngHgtMid = sngHgt / 2 sngWdtMid = sngWdt / 2 'set the command button in the center With Me.CommandButton1 .Left = sngWdtMid - (.Width / 2) .Top = sngHgtMid - (.Height / 2) End With End Sub '------------------------------------------------------------------------ General/Standard Module Level Code: Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long Private Declare Function GetDC Lib "user32" (ByVal hWnd As Long) As Long Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hDc As Long, ByVal nIndex As Long) As Long Private Declare Function ReleaseDC Lib "user32" (ByVal hWnd As Long, ByVal hDc As Long) As Long Private Const SM_CXSCREEN As Long = 0 Private Const SM_CYSCREEN As Long = 1 'Notes: Point = 1/72 Inches; Twip = 1/20 Point Private Const LOGPIXELSX As Long = 88 'Pixels/Inch in X Private Const LOGPIXELSY As Long = 90 'Pixels/Inch in Y Private Const POINTS_PER_INCH As Long = 72 Public Enum SystemMetricsConstants smScreenWidth = SM_CXSCREEN smScreenHeight = SM_CYSCREEN End Enum Function SystemMetrics(SMC As SystemMetricsConstants) As Long SystemMetrics = GetSystemMetrics(SMC) End Function Public Function PointsPerPixel() As Double Dim lngHWndDC As Long Dim lngDotsPerInch As Long 'if DC is NULL then GetDC retrieves the device context for the entire screen lngHWndDC = GetDC(0) lngDotsPerInch = GetDeviceCaps(lngHWndDC, LOGPIXELSX) PointsPerPixel = POINTS_PER_INCH / lngDotsPerInch ReleaseDC 0, lngHWndDC End Function Sub ABC() UserForm1.Show End Sub "Roger on Excel" wrote: I am sorry guys, I read my post and I think I wasnt clear enough in describing my problem. I use userforms to drive my spreadsheet. The main userform sits in the center of the screen and is sized to fit a 1024/768 screen resolution. I set it this way as most of the PC's I use have this resolution and if the resolution is higher the userform sits neatly in the middle of the screen using the correctly selected userform position stting. Now, here is my problem. Behind the main userform is a background userform which effectively hides excel in the background. I set this to activate at the workbook startup. This autosizes to fit the screen whatever the screen resolution is using the code i posted at the start of this thread. This way the background is always hiddem from view On the background userform is a button to recall the main userform if the user inadvertedly closes it. I would like this to have its position dictated by code rather than manually entering the position attributes. This way I could have the button always be in the center of the autosizing background userform. At present I can manually position it so it hides behind the main userform, but as a perfectionist, i would like to explore better options to make it go in the center. What do you think? Roger "Matthew Herbert" wrote: Roger, As fisch4bill mentioned, Application.Height will give you the height of the application. If the application is maximized then you essentially know the screen resolution via Application.Height and Application.Width. Maybe I'm just not understanding your situation. Is there more detail that you can provide? (Or are you maybe referring to ths UserForm startup position, which can be changed via the "StartUpPosition" property of a UserForm?) (In general, if you are looking fo the screen resolution then you'll need to use the GetSystemMetrics API. The argument 0, more so seen as "Private Const SM_CXSCREEN As Long = 0" or some variation of a Const statement, gives you the resolution of X; and the argument 1, more so seen as "Private Const SM_CYSCREEN As Long = 1" or some variation of a Const statement, gives you the resolution of Y. The API function declaration is "Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long". So, GetSystemMetrics(SM_CYSCREEN), or GetSystemMetrics(1), will return the resolution of Y). Best, Matt "Roger on Excel" wrote: Hi Mathew, Thats fine for a particular screen resolution. Is there a way to make the code adjust for screen resolution - the auto sizing of the userform with the code i use works great, but i need the button to adjust its position accordingly too. Any ideas? Roger "Matthew Herbert" wrote: Roger, If you know the height and width of your command button, then after you set the size of your UserForm (with the code you posted), do a little math to get the center coordinates, do some more math to get the left and top coordiates for the command button, and set the .Left and .Top properties of the command button. (You know half the height and half the width of the UserForm. You also know half the command button height and half the command button width. The reset is addition/subtraction). Best, Matthew Herbert "Roger on Excel" wrote: I have a userform which sizes to the screen using the following code Private Sub UserForm_Initialize() With Me .Height = Application.Height .Width = Application.Width .Left = Application.Left .Top = Application.Top End With End Sub I have a command button on the userform, but I would like it to be always centered on the page. Is there a way to do this? Many thanks, Roger |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Centralizing a command button on a userform
Many Thanks Matthew,
I took a look at your code and modified it using the code I used at the top of the page : Thus: Private Sub UserForm_Initialize() With Me .Height = Application.Height .Width = Application.Width .Left = Application.Left .Top = Application.Top End With 'set the command button in the center With Me.cmdbutton1 .Left = ((Application.Width - 140) / 2) .Top = ((Application.Height - 24) / 2) End With End Sub 140 is the width of the command button and 24 is the height. Have a great weekend, and thanks again for your patience Roger "Matthew Herbert" wrote: Roger, UserForm1: The "hide Excel" UserForm (i.e. your "background") - Also has a "CommandButton1" button UserForm2: The "main" UserForm (i.e. your "main") Instructions: 1. Open a blank workbook 2. Open VBE Do the following to the blank workbooks VBAProject: 3. Add two user forms 4. On the first user form (i.e. UserForm1), add a command button (i.e. CommandButton1) anywhere you like 5. Copy the "UserForm1 Code Module:" code portion (the code is below) and past it into the UserForm1 code window 6. Add a module (i.e. Module1) 7. Copy the "General/Standard Module Level Code:" code portion (the code is below) into the Module1 code window 8. Run Macro "ABC" Let me know if I addressed your issue. Best, Matthew Herbert '------------------------------------------------------------------------ UserForm1 Code Module: Private Sub CommandButton1_Click() UserForm2.Show End Sub Private Sub UserForm_Initialize() Dim sngPxl As Single Dim sngHgt As Single Dim sngWdt As Single Dim sngHgtMid As Single Dim sngWdtMid As Single 'get the screen size in order to size the user form sngPxl = PointsPerPixel sngHgt = SystemMetrics(smScreenHeight) * sngPxl sngWdt = SystemMetrics(smScreenWidth) * sngPxl 'size the user form With Me .Height = sngHgt .Width = sngWdt .Left = 0 .Top = 0 End With 'get the center coordinates of the user form sngHgtMid = sngHgt / 2 sngWdtMid = sngWdt / 2 'set the command button in the center With Me.CommandButton1 .Left = sngWdtMid - (.Width / 2) .Top = sngHgtMid - (.Height / 2) End With End Sub '------------------------------------------------------------------------ General/Standard Module Level Code: Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long Private Declare Function GetDC Lib "user32" (ByVal hWnd As Long) As Long Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hDc As Long, ByVal nIndex As Long) As Long Private Declare Function ReleaseDC Lib "user32" (ByVal hWnd As Long, ByVal hDc As Long) As Long Private Const SM_CXSCREEN As Long = 0 Private Const SM_CYSCREEN As Long = 1 'Notes: Point = 1/72 Inches; Twip = 1/20 Point Private Const LOGPIXELSX As Long = 88 'Pixels/Inch in X Private Const LOGPIXELSY As Long = 90 'Pixels/Inch in Y Private Const POINTS_PER_INCH As Long = 72 Public Enum SystemMetricsConstants smScreenWidth = SM_CXSCREEN smScreenHeight = SM_CYSCREEN End Enum Function SystemMetrics(SMC As SystemMetricsConstants) As Long SystemMetrics = GetSystemMetrics(SMC) End Function Public Function PointsPerPixel() As Double Dim lngHWndDC As Long Dim lngDotsPerInch As Long 'if DC is NULL then GetDC retrieves the device context for the entire screen lngHWndDC = GetDC(0) lngDotsPerInch = GetDeviceCaps(lngHWndDC, LOGPIXELSX) PointsPerPixel = POINTS_PER_INCH / lngDotsPerInch ReleaseDC 0, lngHWndDC End Function Sub ABC() UserForm1.Show End Sub "Roger on Excel" wrote: I am sorry guys, I read my post and I think I wasnt clear enough in describing my problem. I use userforms to drive my spreadsheet. The main userform sits in the center of the screen and is sized to fit a 1024/768 screen resolution. I set it this way as most of the PC's I use have this resolution and if the resolution is higher the userform sits neatly in the middle of the screen using the correctly selected userform position stting. Now, here is my problem. Behind the main userform is a background userform which effectively hides excel in the background. I set this to activate at the workbook startup. This autosizes to fit the screen whatever the screen resolution is using the code i posted at the start of this thread. This way the background is always hiddem from view On the background userform is a button to recall the main userform if the user inadvertedly closes it. I would like this to have its position dictated by code rather than manually entering the position attributes. This way I could have the button always be in the center of the autosizing background userform. At present I can manually position it so it hides behind the main userform, but as a perfectionist, i would like to explore better options to make it go in the center. What do you think? Roger "Matthew Herbert" wrote: Roger, As fisch4bill mentioned, Application.Height will give you the height of the application. If the application is maximized then you essentially know the screen resolution via Application.Height and Application.Width. Maybe I'm just not understanding your situation. Is there more detail that you can provide? (Or are you maybe referring to ths UserForm startup position, which can be changed via the "StartUpPosition" property of a UserForm?) (In general, if you are looking fo the screen resolution then you'll need to use the GetSystemMetrics API. The argument 0, more so seen as "Private Const SM_CXSCREEN As Long = 0" or some variation of a Const statement, gives you the resolution of X; and the argument 1, more so seen as "Private Const SM_CYSCREEN As Long = 1" or some variation of a Const statement, gives you the resolution of Y. The API function declaration is "Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long". So, GetSystemMetrics(SM_CYSCREEN), or GetSystemMetrics(1), will return the resolution of Y). Best, Matt "Roger on Excel" wrote: Hi Mathew, Thats fine for a particular screen resolution. Is there a way to make the code adjust for screen resolution - the auto sizing of the userform with the code i use works great, but i need the button to adjust its position accordingly too. Any ideas? Roger "Matthew Herbert" wrote: Roger, If you know the height and width of your command button, then after you set the size of your UserForm (with the code you posted), do a little math to get the center coordinates, do some more math to get the left and top coordiates for the command button, and set the .Left and .Top properties of the command button. (You know half the height and half the width of the UserForm. You also know half the command button height and half the command button width. The reset is addition/subtraction). Best, Matthew Herbert "Roger on Excel" wrote: I have a userform which sizes to the screen using the following code Private Sub UserForm_Initialize() With Me .Height = Application.Height .Width = Application.Width .Left = Application.Left .Top = Application.Top End With End Sub I have a command button on the userform, but I would like it to be always centered on the page. Is there a way to do this? Many thanks, Roger |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Centralizing a command button on a userform
Roger,
Glad to be of help. One more thought. There is no need to hard code your "140" and "24". If you were to ever change the width or height of the command button, then your button would no longer be "centered." Instead, replace "140" with ".Width" and replace "24" with ".Height". This is more robust than the hard coded values. Best, Matt "Roger on Excel" wrote: Many Thanks Matthew, I took a look at your code and modified it using the code I used at the top of the page : Thus: Private Sub UserForm_Initialize() With Me .Height = Application.Height .Width = Application.Width .Left = Application.Left .Top = Application.Top End With 'set the command button in the center With Me.cmdbutton1 .Left = ((Application.Width - 140) / 2) .Top = ((Application.Height - 24) / 2) End With End Sub 140 is the width of the command button and 24 is the height. Have a great weekend, and thanks again for your patience Roger "Matthew Herbert" wrote: Roger, UserForm1: The "hide Excel" UserForm (i.e. your "background") - Also has a "CommandButton1" button UserForm2: The "main" UserForm (i.e. your "main") Instructions: 1. Open a blank workbook 2. Open VBE Do the following to the blank workbooks VBAProject: 3. Add two user forms 4. On the first user form (i.e. UserForm1), add a command button (i.e. CommandButton1) anywhere you like 5. Copy the "UserForm1 Code Module:" code portion (the code is below) and past it into the UserForm1 code window 6. Add a module (i.e. Module1) 7. Copy the "General/Standard Module Level Code:" code portion (the code is below) into the Module1 code window 8. Run Macro "ABC" Let me know if I addressed your issue. Best, Matthew Herbert '------------------------------------------------------------------------ UserForm1 Code Module: Private Sub CommandButton1_Click() UserForm2.Show End Sub Private Sub UserForm_Initialize() Dim sngPxl As Single Dim sngHgt As Single Dim sngWdt As Single Dim sngHgtMid As Single Dim sngWdtMid As Single 'get the screen size in order to size the user form sngPxl = PointsPerPixel sngHgt = SystemMetrics(smScreenHeight) * sngPxl sngWdt = SystemMetrics(smScreenWidth) * sngPxl 'size the user form With Me .Height = sngHgt .Width = sngWdt .Left = 0 .Top = 0 End With 'get the center coordinates of the user form sngHgtMid = sngHgt / 2 sngWdtMid = sngWdt / 2 'set the command button in the center With Me.CommandButton1 .Left = sngWdtMid - (.Width / 2) .Top = sngHgtMid - (.Height / 2) End With End Sub '------------------------------------------------------------------------ General/Standard Module Level Code: Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long Private Declare Function GetDC Lib "user32" (ByVal hWnd As Long) As Long Private Declare Function GetDeviceCaps Lib "gdi32" (ByVal hDc As Long, ByVal nIndex As Long) As Long Private Declare Function ReleaseDC Lib "user32" (ByVal hWnd As Long, ByVal hDc As Long) As Long Private Const SM_CXSCREEN As Long = 0 Private Const SM_CYSCREEN As Long = 1 'Notes: Point = 1/72 Inches; Twip = 1/20 Point Private Const LOGPIXELSX As Long = 88 'Pixels/Inch in X Private Const LOGPIXELSY As Long = 90 'Pixels/Inch in Y Private Const POINTS_PER_INCH As Long = 72 Public Enum SystemMetricsConstants smScreenWidth = SM_CXSCREEN smScreenHeight = SM_CYSCREEN End Enum Function SystemMetrics(SMC As SystemMetricsConstants) As Long SystemMetrics = GetSystemMetrics(SMC) End Function Public Function PointsPerPixel() As Double Dim lngHWndDC As Long Dim lngDotsPerInch As Long 'if DC is NULL then GetDC retrieves the device context for the entire screen lngHWndDC = GetDC(0) lngDotsPerInch = GetDeviceCaps(lngHWndDC, LOGPIXELSX) PointsPerPixel = POINTS_PER_INCH / lngDotsPerInch ReleaseDC 0, lngHWndDC End Function Sub ABC() UserForm1.Show End Sub "Roger on Excel" wrote: I am sorry guys, I read my post and I think I wasnt clear enough in describing my problem. I use userforms to drive my spreadsheet. The main userform sits in the center of the screen and is sized to fit a 1024/768 screen resolution. I set it this way as most of the PC's I use have this resolution and if the resolution is higher the userform sits neatly in the middle of the screen using the correctly selected userform position stting. Now, here is my problem. Behind the main userform is a background userform which effectively hides excel in the background. I set this to activate at the workbook startup. This autosizes to fit the screen whatever the screen resolution is using the code i posted at the start of this thread. This way the background is always hiddem from view On the background userform is a button to recall the main userform if the user inadvertedly closes it. I would like this to have its position dictated by code rather than manually entering the position attributes. This way I could have the button always be in the center of the autosizing background userform. At present I can manually position it so it hides behind the main userform, but as a perfectionist, i would like to explore better options to make it go in the center. What do you think? Roger "Matthew Herbert" wrote: Roger, As fisch4bill mentioned, Application.Height will give you the height of the application. If the application is maximized then you essentially know the screen resolution via Application.Height and Application.Width. Maybe I'm just not understanding your situation. Is there more detail that you can provide? (Or are you maybe referring to ths UserForm startup position, which can be changed via the "StartUpPosition" property of a UserForm?) (In general, if you are looking fo the screen resolution then you'll need to use the GetSystemMetrics API. The argument 0, more so seen as "Private Const SM_CXSCREEN As Long = 0" or some variation of a Const statement, gives you the resolution of X; and the argument 1, more so seen as "Private Const SM_CYSCREEN As Long = 1" or some variation of a Const statement, gives you the resolution of Y. The API function declaration is "Private Declare Function GetSystemMetrics Lib "user32" (ByVal nIndex As Long) As Long". So, GetSystemMetrics(SM_CYSCREEN), or GetSystemMetrics(1), will return the resolution of Y). Best, Matt "Roger on Excel" wrote: Hi Mathew, Thats fine for a particular screen resolution. Is there a way to make the code adjust for screen resolution - the auto sizing of the userform with the code i use works great, but i need the button to adjust its position accordingly too. Any ideas? Roger "Matthew Herbert" wrote: Roger, If you know the height and width of your command button, then after you set the size of your UserForm (with the code you posted), do a little math to get the center coordinates, do some more math to get the left and top coordiates for the command button, and set the .Left and .Top properties of the command button. (You know half the height and half the width of the UserForm. You also know half the command button height and half the command button width. The reset is addition/subtraction). Best, Matthew Herbert "Roger on Excel" wrote: I have a userform which sizes to the screen using the following code Private Sub UserForm_Initialize() With Me .Height = Application.Height .Width = Application.Width .Left = Application.Left .Top = Application.Top End With End Sub I have a command button on the userform, but I would like it to be always centered on the page. Is there a way to do this? Many thanks, Roger |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
print command from command button in userform causes double chart | Excel Programming | |||
BUG: print command from command button in userform causes double c | Excel Programming | |||
Userform Command Button not available until another command buttonhas been used | Excel Programming | |||
Use Command Button from each Row to fill UserForm | Excel Programming | |||
need to minimize userform via command button on itself | Excel Programming |