How to determine monitor size to centre text?

[TotallyConfused]

I am creating a Menu program, with the opening sheet titled, 'MAIN MENU'. I would like to have this centred on the monitor and I can do this on mine simply by visually positioning the words. My problem is that I have no idea of the horizontal length of the user's monitor. Is there any way my program could use VBA to determine the user's monitor size when the program is run, and then adjust the location of the title to fit that monitor's horizontal length? I assume this system information is stored by Windows someplace but I have no idea where or how to access it. I know the user is using MS Office 365 with Windows, but that is all I know.

To test an idea, I merged several cells together reaching across my secondary monitor, then instructed Excel to centre those two words. Worked great. Then I slid everything over to my laptop's much smaller screen hoping that somehow everything would be 'squeezed' together so it would fit on that screen. That didn't work and I ended up with only a couple of letters showing with the rest way off the right hand edge.

If there is no way to have VBA adjust the location, if I knew of some way to determine the size of a monitor, perhaps there is a way ( I hope ) that I could calculate the location so it would 'look good' on that monitor, regardless how it looked on mine. I won't be running the program. Possibly if I knew the physical size in inches of their screen, I could compare that to mine and figure out a relative starting point.

If anyone has any ideas, suggestions or better yet, some code, I sure would appreciate hearing from you. THANK YOU in advance for any help.

Sincerely,
TotallyConfused

 

[lrobbo314]

This function will return the height and width values for the screen resolution.

Declare Function GetSystemMetrics32 Lib "User32" _
    Alias "GetSystemMetrics" (ByVal nIndex As Long) As Long

Sub ScreenRes()
Dim w As Long, h As Long
    w = GetSystemMetrics32(0) ' width in points
    h = GetSystemMetrics32(1) ' height in points
    Debug.Print (Join(Array(h, w), ","))
End Sub

 

[TotallyConfused]

Hello Irobbo

I want to THANK YOU for your quick response to my question about determining screen size. When I tried running the code you posted, I get an error message, which means I've probably done something wrong. What I did, was open a new Excel spreadsheet, and then copy/pasted all of your code (Function and Sub) into the module area where any other macro's would be put. It's my brief understanding of Functions, that they are run the same way we use to run any other Excel command. With this in mind, after saving this workbook as an '.XLSM' formated file, I returned to Excel, and in an empty cell I typed the following: =GetSystemMetrics32 The only response was the error message of #NAME?. Thinking maybe I had placed your code in the wrong place, I right clicked on the sheet tab name and in the editor box, I again pasted your code. Same error message. I did the same thing by pasting your code in the ThisWorkBook but the result was no different.

Would you have any idea what I've done wrong?

Thanks again for your effort.
TotallyConfused

 

[Yongle]

@TotallyConfused
Run macro ScreenRes which write the screen dimensions to the Immediate Window

 

[TotallyConfused]

Hello Yongle.

I'm sorry I wasn't able to return here earlier but things have been a bit hectic around here lately. THANK YOU for the sample code and even more so, the instructions on how to run it. After typing in your macro, I was given the numbers of 1080,1920 points in my Immediate window. so I know I typed in your code okay.

Referring back to my original post, and I think I wasn't precise enough in explaining exactly what I needed. For that, I apologize. The program I'm working on will NOT be run on my computer, so the numbers of 1080,1920 may or may not apply, depending on the other person's monitor. They live a long ways away, so there is no way I can get physical access to their computer, though I suppose I could have them run your code and send me the resulting numbers.

The program I'm working on will have an opening screen titled: MAIN MENU My question is:

1) What I've been hoping for was to find some way that my program could run your macro, then through some kind of calculations it would use those numbers to determine the approximate horizontal centre of their screen and then by some variation of 'Range, Cells, Rows, Columns' etc., commands, display the title. I'm open to any combination of VBA or Excel. Whatever works. How can I use VBA to horizontally centre those 9 keystrokes by using whatever screen resolution your code will display for THEIR monitor?

As far as my programming knowledge goes, I know I am way in over my head here, so any help such as code etc., will be greatly appreciated. THANK YOU in advance for any assistance you may be able to offer.

TotallyConfused

 

[Jaafar Tribak]

The program I'm working on will have an opening screen titled: MAIN MENU My question is:

What exactly do you mean by "opening screen" ? Is that like a welcome message that you want to be displayed upon opening the workbook and be dismissed after a few seconds?
If it is a permanent message, where is it going to be displayed ? In a cell,a shape etc .. ?
And when you say "centre of the screen", do you mean the center of the actual physical monitor or the center of the excel application window ?

 

[TotallyConfused]

Hello Jaafar

Thank you for your interest in my problem. I'll try to answer each of your questions.

1) You asked, "What exactly do you mean by 'Opening screen'? This part of the program is nothing but a menu program whereby users will be offered choices they can make by clicking on different buttons. Each button will then take them to a different segment of the overall program, eg. Data Entry, Reports, etc. I can code all those buttons, etc., as well as the rest of it, so no problem there. What I'm trying to figure out is how to place the title of that screen, which is MAIN MENU in the horizontal centre of the users screen. When they click on a button, all this will disappear and they will be taken to whatever other program they selected. Just for an example, lets say their viewing screen is 24" from the left side to the right side. Lets assume the title of MAIN MENU takes up 2". This would leave a margin to the left of 11" with the same on the right. How do I calculate that 11" margin without knowing the size of their viewing screen?

2) You asked, "If this is a permanent message, where is it going to be displayed?" Yes, it will be permanent until the user clicks on a button, (see description for question #1). The position of those two words 'MAIN MENU', will be near the top of the viewing screen and centred from left to right. Since this is Excel, yes, those two words will be in a cell because I don't know how it could be anything but a cell. Everything here is simple text with no pictures, graphics etc., involved. You seem to have misunderstood my #1 & #5 posting and are thinking I need help in how to code all this. This is NOT the case. All I need is some way to calculate the position of those two words, going from left to right.

3) You asked, "And when you say "centre of the screen", do you mean the center of the actual physical monitor or the center of the excel application window? I'm only talking about the centre of the Excel application window since this is what the user will be looking at. I want those two words, MAIN MENU to be at the top and equal distance from the left side to the right side.

Thanks again and I hope I've fully answered your questions.

TotallyConfused

 

[Jaafar Tribak]

Ca you post an image of the "opening screen" ?

 

[TotallyConfused]

Ca you post an image of the "opening screen" ?

There is nothing to post about the opening screen. Imagine a completely blank screen with ONLY the two words, MAIN MENU, in the top row of the Excel screen. I need it to be centred from the left edge to the right edge of the screen. That's it. Nothing more! You're trying to make this sound as if it is a very complicated screen, and it's NOT! What could be simpler than only two words?

Thanks anyway.
TotallyConfused

 

[Yongle]

You're trying to make this sound as if it is a very complicated screen
@TotallyConfused - the questions are valid.