Custom MsgBox 8
By Richard Rost
3 years ago
Custom MsgBox in Access. Part 8: Format Properties
In this Microsoft Access tutorial series, we focus on creating a custom message box using VBA, enhancing the standard MsgBox function.
In Part 8, we will learn how specify the height and width of the form. This way whether you want a form that's tall and narrow, or short and stubby, you can have it your way. We'll also see how to use Anchoring to have the prompt label stretch according to the new size.
Members
Members will learn how to center the three buttons in the middle of the form at its new size. This will involve some math and some VBA.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Coming Up
Keywords
TechHelp Access 2016, Access 2019, Access 2021, Access 365, Microsoft Access, MS Access, MS Access Tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Custom Dynamic MsgBox, Custom MsgBox in Access, Dynamically resize form, specify form size, acheader, acfooter, acsection enumeration, me.windowheight, me.windowwidth, twips, pixels, size of current form, me.move,
Intro In this video, we will build on the custom message box project in Microsoft Access by adding features to let you control the size of the message box. We will talk about adjusting height and width, using twips for sizing, and making the prompt box resize automatically with the form using the anchoring property. You'll also learn how to keep background colors consistent with form sections and see some tips for handling button placement as the box size changes. This is part 8.Transcript Today is part eight of my custom message box series. Today we are going to add features to allow us to control the size of the message box. We can control pretty much everything else so far - the number of buttons, the font, the font size, the color, all that stuff. Today you want it tall and wide, you want it tall and narrow, you want it thin and short, you want it short and fat. You're tall and fat, you're short and ugly kid. Give me back my pen. We'll be quote who knows what it was. Anyways, we're going to cover all that today.
And of course this is part eight. So you know what to do by now. Go watch parts one through seven before you try to do this stuff. Get out of here. Go on.
Alrighty, so she's coming along pretty good. The next thing to do is to allow us to change the size of the box. You want a big one that takes up most of the screen, you want a short and narrow one, you want it tall, skinny one, whatever.
Alright, so first, let's go into design view. And I'm going to move these buttons into the footer and you'll see why in a few minutes.
Alright, so first we have to turn on the form header and footer, not the page header footer. We don't use the page header and footer in forms because the page header footer is for the printed page. And we don't print forms. What do we print? We print reports so we will never use a page header footer in a form. We'll use them in reports, but not forms. Alright, we don't need the form header so just go like that. And we're going to take the buttons and we're going to slide them down here. Just like so. And shrink that up. And you can do whatever you want with this prompt. We're going to resize that too. So save that.
Now we're going to have to apply our color that we did in code last time to the form footer because look, if you save this and open it, the color did not apply. You could leave it a different color if you want to. You can set a parameter up to do the whole separate color if you want. You can have two different colors here if you want. That's completely up to you. Those are your old AGOs, play with them however you want. I'm going to keep these both the same so that it doesn't look like a footer.
So let's go into the code for this form and let's find where we set the background color which is right here - the form background color. We did it to the AC detail. Now we need to do it to the form footer which is AC footer. Same exact command right there. And this is going to be AC footer. I believe the number is two. Don't quote me.
Oh yeah, I'm good. Here it is. If you search for AC section, enumeration, access, whatever, detail is zero like I said last time. Header is one, footer is two. And then you can have different group level headers and footers too but that's for reports.
Alright. So now that we got that done, let's make a really, really long prompt. Let's go to where we call it - which is our main menu. And this thing here. We're going to say just a whole lot of stuff. I'm just going to copy and paste what I got somewhere else, hang on. Alright. Really long, long prompt.
Alright. Now save it. Give it a quick debug compile. Make sure. Let's come over here. Click the button. And you can see that does not fit there. So this is one of those instances where you might want to specify a larger box.
Yeah, you could shrink the font, font size, all that stuff. But that's no fun. Let's make it bigger.
Now before we can specify the size, let's find out what the current size is. Now if you go into design view, alright, and if you look at the properties, we got a height and width. Where's the height? There's a width. It's 4.4167. That doesn't help us much. That's inches. That's what you get is inches here. Access internally uses something called twips. T-W-I-P-S. Twips. It stands for a 20th of an inch point, which is really weird. Because this twips is not even the same as what you'd use for pixels.
If you do a screen measurement, those are pixels. Pixels are different. Twips to pixels is about 15 to 1. That's weird. I know. You don't have to worry about that. All you really want to do is figure out how many twips you're dealing with right now.
So I'm just going to copy one of these buttons and drop it up here. Right click, build event. All I want to do in here is I'm going to say message box, me.window height, and maybe a next in the middle, and me.window width. And that will tell you the size of the current form.
Alright, so save that. Come back over here. Save it. Save it. Close it. Close it. Whatever. Open it up. Hit the button. Okay. So this form right here is 3690 by 6600. And those are twips. And if you do have a screen measurement tool, if you look at the top of it up here, down at the bottom right there, that's 238 pixels high. And 238 times 15 is 3570. Yeah, it's close enough. Alright. Close enough to that. Those are twips to pixels. So these are the numbers that you want right here. So save those. Write them down. Put them in your notepad, whatever.
Alright. Now we can get rid of that button that we made. Design view. You served your purpose. Goodbye. Save it. Clear that code out of there.
Okay. Now we can add that as a parameter to our message box. So the user, you, the developer, can specify the height and width of the box.
So let's go over to our global module. We've done this a million times, right? Same thing here. We're going to come into here. We're going to add two more parameters.
Optional window height as long equals (put your default height in here that you liked before, right) 3690. Or if you want to keep it around 3700, whatever. I don't care.
Optional window width as long equals the other one, 6600.
Okay. Add those down here. Alright. Just like we've done a million times.
Window height.
Window width.
Alright. Save it.
Now we've got to make the form read those. So go into the forms code. Open this up. Come down to here.
Case window height. And that's going to be - now we have to move the window using the me.move. Me.move is also, it should be me.move size. It's actually changing the size. So we have to say me.move.
Now again, the first property, the first parameter is required. So we're not moving it. So we're going to specify me.window left. I know it's a quirk. But then we can go comma, comma, comma, and put the height in here. Now where's the height? The height's in name value one. Do that.
Case window width. Same trick. Me.move. Me.window left. And now we're going to go comma, comma for the width property name value one. Do that. So we're basically changing the size to these if they're specified.
Alright. Save it. Debug compile, you know the drill.
Let's go back to our main menu and change the size of the box. And I've got two more parameters I can add in here. Comma. We're at window height. I know it's really long. If I scroll, I got to go way over here and watch. I got to keep going. It's further. Yeah.
Note to access team: Make this not be one gigantic long line. Yeah. I know if I move my window way over the edge of the screen here, yeah, it starts to do that. Here, what? It does start to double up like that. But it goes all the way to my screen, and my screen's huge. So I don't want to have to do that. It should stay inside this window.
Alright. Note to access team. Someone who's keeping a list out there for me, and all my videos. I got tons of lists.
Alright. So let's go. Let's go height. What do we have before? We had 36. Let's go 5000 by 10,000.
Alright. Save it. Come back out here. Close it up. Open it. And you can see it's much, much bigger now.
Still is a finger thing. Now, interesting to note: the size of the prompt box in here hasn't changed. If you take a look - not layout view - if you take a look in design view, right, the size of this box has not changed at all. All we're doing is specifying a new dimension for the form.
If I come in here and say, you know, if I make this 15,000 - oh, I got to stop the code. If I come in here and make this 15,000, right, and then I open it up, look at that. It's really super tall. Really, really tall. But again, the prompt box hasn't changed.
Also, keep in mind, if I change the dimensions to something like this, right, if I go really, really short, but really, really wide, now watch the box. It opens up and it's off center.
Remember I said before, the auto center property is good for the basic window. But in this particular case, we might want that code now to move it back to the left a little bit. So you might want to hang on to that code.
In fact, for the extended cut for the members, I'm going to come back in the form load event and come right down to the bottom of this now. So after it's done all the resizing, members, now we can say move form me, which should recenter the form now with our recentering code. Save that. And now let's try it. And look at that. It's better. It's closer to the center now. In fact, if you close this and open it up, yep, now it's centered.
So remember, I told you that code wasn't a waste. If you're going to resize this thing, you're going to need to move it too, because auto center won't put it in the right spot. So see, that's the reason why I kept that code around.
Alright, but let's go back and change this to something reasonable. Let's make this, let's go, let's go square - 5,000, 5,000. Let's see what this looks like. Okay, that's not too bad.
Alright, now let's go a little wider, let's go a little wider. Let's go 5,000 by 10,000. Okay, that looks good.
Now, I want the prompt box to resize as the form resizes. What can I use to do that automatically without any code? I've covered it before in a video. Anybody know? Take a minute, pause the video, think about it. Here's the video: it's something called anchoring.
Anchoring is where you have a small form like this, and then you resize it, and all the text boxes will stretch with the size of the form. Go watch this video if you're not familiar with this concept.
So all you got to do is come in here, design view. Take this guy, the prompt, open up form, it's on arrange, I think, I don't use it that often. Alright, go to anchoring, make sure this prompt box is selected, go to anchoring, and we're going to pick stretch down and across. That means that control is going to stay anchored right where it is, but it can stretch down and across to the right. So if the form is resized, that guy will resize. Go watch my other video for more details on this.
Alright, and then when you've done that, save it, close it, open it, and oh, look at that, isn't that nice. Look at that. Now even if you take this form - and oh, we can't resize it because it's a dialog box - but if you do resize, if you have a normal form and you resize it, then that guy will get bigger and smaller. And I can even make this guy even bigger. Let's go 12,000, let's say. Alright, click the button, whoop, that's the only guy - even bigger. And this still continued to stretch.
So there you go, and that's pretty cool, huh? Now it's probably at about this point where you're saying to yourself, I'd like these buttons to be centered in here too. What do you think?
Well, you can play around with the anchoring a little bit, for example. You can go into here and say, for this guy, I want to anchor it bottom left. And for this guy, I want to anchor it bottom right. And those will move just fine. But for the center button, you run into a little problem because it's got stretch across bottom. That's the only - there is no anchor center, I wish there was.
Again, note to access team. You got left and you got right, but you got no anchor center. So what you end up with is that. And that's not terribly bad. You get one big button in the middle, it stretches. But again, you might want all three buttons to be nice and centered in the middle of the screen, like a traditional dialog box.
And that is something we're going to cover in the extended cut for the members. Silver members and up get access to all of my extended cut videos. And gold members can download these databases and get access to my code vault.
In this extended cut video, I'm going to show you - it's going to take a little math and a little manually positioning these with some VBA code, but it's not super hard to do. So we're going to cover that in the extended cut.
But that is going to be your TechHelp video for today. Hope you learned something. Live long and prosper, my friends. I'll see you next time.
Part nine is going to cover some more stuff, including adding icons to the message box. That's going to be pretty cool.
Okay, bye.Quiz Q1. What new feature was added to the custom message box in this part of the series?
A. Ability to add sound notifications
B. Ability to control the size (height and width) of the message box
C. Ability to display animated gifs
D. Ability to print the message box
Q2. Why is the page header and footer not used in Access forms?
A. They make the form load more slowly
B. Page header and footer are for printed pages, not forms
C. They are not supported in newer versions of Access
D. They do not allow adding controls
Q3. Which Access section number represents the form footer in code?
A. 0
B. 1
C. 2
D. 3
Q4. What unit of measurement does Access use internally for form dimensions?
A. Pixels
B. Centimeters
C. Twips
D. Points
Q5. What is the approximate conversion between twips and pixels?
A. 1 twip is about 100 pixels
B. 15 twips is about 1 pixel
C. 1 twip is about 1 pixel
D. 1 pixel is about 50 twips
Q6. Why might you want to specify a custom size for the message box, rather than just changing the font size?
A. Larger message boxes make the code run faster
B. Changing font size is less flexible and impacts readability more than box size
C. Access does not allow font size changes
D. Specifying size prevents form closing
Q7. How does the developer allow customization of the message box size?
A. By creating a new form for every size
B. By adding height and width parameters to the message box function
C. By changing Windows screen resolution
D. By using different templates
Q8. What method is used to change the size of an Access form in code?
A. Me.size
B. Me.resize()
C. Me.move
D. Me.expand
Q9. When resizing the message box form, why does the prompt text box sometimes not change size?
A. It is anchored to not stretch by default
B. The form's minimum size is too small
C. The text box is locked
D. There is a maximum width limit in Access
Q10. What Access feature automatically resizes controls when the form is resized?
A. AutoFormat
B. Grouping
C. Anchoring
D. Sorting and Filtering
Q11. Why does centering of buttons become a challenge when resizing the custom message box?
A. Buttons always move to the top
B. Auto centering is not available as an anchor option
C. Buttons are fixed in place by default
D. Font size changes move buttons automatically
Q12. What property should you use to recenter a form after resizing when Auto Center does not produce the desired result?
A. Form Order
B. Form Caption
C. Manual move code using positioning
D. Tab Order
Q13. What anchoring options are available to keep buttons aligned at the bottom corners of the message box?
A. Top right and bottom left
B. Bottom left and bottom right
C. Center and bottom center
D. Top left and top right
Q14. What is stated as being covered in the next part of the series (part nine)?
A. Saving message box logs
B. Adding icons to the message box
C. Connecting to online APIs
D. Printing the message box
Answers: 1-B; 2-B; 3-C; 4-C; 5-B; 6-B; 7-B; 8-C; 9-A; 10-C; 11-B; 12-C; 13-B; 14-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone will focus on expanding the capabilities of a custom message box in Microsoft Access by allowing control over the size of the box itself. Up until now, we've added a wealth of customization options including the number of buttons, font, font size, colors, and more. Today, we're going to take things further and give you the tools to make the message box as tall, wide, narrow, or short as you need for your application.
As this is part eight of my custom message box series, I strongly suggest you watch parts one through seven before proceeding with anything in this lesson. Each part builds on the previous one, so make sure you're caught up before moving forward.
My custom message box form has come a long way, and the next step is giving us the ability to change its size. Whether you need a message box that fills most of the screen, something tall and thin, or a short and wide box, you can now achieve exactly what you want.
To begin, you'll want to turn on the Form Header and Footer in design view. Note that we do not use Page Header and Footer in forms; those are for printed pages and reports, not forms viewed on the screen. Once you have the form footer in place, move your message box buttons into it. This not only makes the layout more flexible, it also sets us up for properly resizing the form.
At this point, it's important to ensure that your footer matches the look of the rest of your form. The background color we previously set in code applied only to the form's detail section, so you'll need to add a line of code to also set the color for the form footer. Internally, Access refers to these sections using constants: 0 for detail, 1 for header, and 2 for footer. Make sure you set the appropriate section number when updating your code.
Now, before we give users the power to set custom dimensions, it's useful to understand the current size of your form. In design view, Access shows size in inches, but internally, it measures everything in units called 'twips.' A twip is 1/20th of a point, which turns out to be a rather odd unit when compared to pixels, as there are about 15 twips to one pixel. What you want here is to get the height and width in twips so you can use them in your code.
To check your form's current dimensions, you can temporarily add a button to your form, attach a simple event that calls up the form's current window height and width, then run it to see the current numbers in twips. Once you have these numbers, you can remove the temporary button and note down the values for use as defaults.
Once you know the default height and width in twips, it's time to add two new optional parameters to your message box code: one for height and one for width. Set them both to default to the values you just determined. Then, modify your form's code to listen for these parameters and use the Move method to set the form's size accordingly. It's important to know that Move expects the first parameter (the form's left position), so you'll want to pass the current value for left and only update the size.
With those changes saved, you can now specify the desired form size each time you invoke your message box, right alongside your existing parameters. This gives you precise control over how large or small the box appears, providing ample room for longer prompts or giving your dialog box a custom look.
However, keep in mind that changing the form size with this new parameter does not automatically resize the prompt box inside the form. If you stretch the form taller or wider, the prompt stays the same size unless you take additional action. This is where Access's anchoring feature comes in handy. Anchoring allows controls, such as your prompt text box, to automatically stretch as the form resizes. To enable this, select your prompt box in design view, go to the arrangement settings, and set the anchoring option to "stretch down and across." This ensures that your prompt expands to fill the available space as your form gets bigger.
Once anchoring is set, your prompt box will expand and contract along with the form. If your message box is resizable (not set as a dialog), users can even increase or decrease the size manually while the controls adjust themselves.
Next, you might want the buttons in your form's footer to stay nicely aligned as the form resizes. While anchoring works well for buttons set to the left and right edges, the center button presents more of a challenge since there's no native way to anchor to the exact center. The result is that the center button might stretch, which is not always ideal for a traditional dialog look.
If you want perfect centering of all buttons regardless of form size, that's a more advanced topic that involves a bit of math and direct manipulation through VBA, which I will be covering in detail in the Extended Cut for members. Silver members and higher can watch all of these Extended Cuts, and gold members can also download sample databases and get access to the Code Vault.
This wraps up the main lesson for today, allowing you to set the size of your custom message boxes and make your prompt field adjust elegantly as the form grows or shrinks. In part nine, I'll cover even more features, such as adding icons to the message box.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Moving buttons into the form footer
Enabling form header and footer in design view
Applying background color to the form footer with VBA
Using Access section enumeration for header and footer
Retrieving form dimensions in twips programmatically
Understanding the difference between twips and pixels
Adding height and width parameters to a custom message box
Setting default dimensions for form size parameters
Using the Me.Move method to resize forms in VBA
Allowing developer-specified custom form sizing
Passing custom height and width values to a form
Observing how resizing only affects the form and not inner controls
Limitations of the Auto Center property with resizable forms
Using anchoring to automatically resize controls with the form
Applying stretch down and across anchoring to a text box
Behavior of anchored buttons when resizing forms
Exploring bottom left and bottom right anchoring for controls |
