Select Text On Click
By Richard Rost
3 years ago
Select All Text in a Text Box w SelStart, SelLength
In this Microsoft Access tutorial, I'm going to teach you how to select all of the text in a text box on a form when you click on it. This way, you can just click and type, much like how Excel works when you click on a cell.
Gabriel from Plano, Texas (a Platinum Member) asks: When I click on a cell in Microsoft Excel, the entire cell is selected, and if I want to overwrite that, I just begin typing. In Microsoft Access, when I click on a field, the cursor goes wherever I click, which could be in the middle of the data or at the end or wherever. Is there a way that I can make it so that when I click on a field on a form, all of the data in that field is selected so I can just type over it instead of having to manually select it with the mouse? It works fine if I tab to it, but I use the mouse more than I tab around the form. I just want to click and type.
Members
There is no extended cut, but here is the database download, and a link to the Code Vault:
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
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, select all text, SelStart, SelLength, Screen.ActiveControl, Len, Text Property, Excel Cell Click, Access form text select, Select all text Access form, VBA text selection, Access OnClick text select
Intro In this video, I will show you how to select all of the text in a text box when you click on it in Microsoft Access, creating a behavior similar to Microsoft Excel where you can easily overwrite field data with a single click. We will talk about why this feature is helpful, how to set up the On Click event with a bit of VBA programming, and how to create a reusable function to apply this functionality to multiple fields in your forms efficiently. I will also explain the difference between using the .Value and .Text properties to ensure your code works correctly with both text and formatted values.Transcript In today's video, I am going to show you how to select all of the text in a text box when you click on it. Why would you want to do that? I am going to explain it right now.
Today's question comes from Gabriel in Plano, Texas, one of my Platinum members. Gabriel says, When I click on a cell in Microsoft Excel, the entire cell is selected. If I want to overwrite that, I just begin typing. In Microsoft Access, when I click on a field, the cursor goes wherever I click, which could be in the middle of the data or at the end or wherever. Is there a way that I can make it so that when I click on a field on a form, all of the data in that field is selected so I can just type over it instead of having to manually select it with the mouse? It works fine if I tab to it, but I use the mouse more than I tab around the form. I just want to click and type.
I understand completely, Gabriel. In fact, this is a feature that I built into my account balances template. Normally in Access, when you click on a cell, your cursor goes wherever you click. If you click here, that's where the cursor goes.
If you just want to be able to click and type, it's a bit of a pain because you have to come over here, you have to click and select all of that text or maybe tab off it and tab back to it. What I added was a feature where, when you click on the cell, it just selects all the text in that cell. Click here, it selects all the text in the cell. That's how Excel behaves. If I click here and type in Smith, it just overrides the cell.
How can we do that in Access? It is going to require a little bit of programming, but a whole lot of understanding. There are only a couple of lines of code, but you have to understand a lot about what I am going to show you first.
Prerequisites: If you have never done any VBA programming before, now would be a good time to go watch my Intro to VBA video. It is about 20 minutes long. It teaches you everything you need to know to get started, so go watch that first.
I also strongly recommend you watch my Create a Function video. We're going to make our own little custom function so that we do not have to repeat the same code for all the different controls. For example, first name, last name, whatever. If you want the same behavior in a bunch of them, we are going to make our own function to do that. We can call that function from multiple different controls.
Go watch this video and the video on event handler functions. We're going to put a function right in the property of the event. So we are going to have to make multiple calls to it. Of course, go watch the video on string functions. We're going to use the length function in this video. Go watch all of these and then come on back. They're free. They are on my YouTube channel and on my website. Go watch them. I'll hold up class just for you.
Here I am in my TechHelp free template. This is a free database. You can download a copy from my website if you want to.
The issue is if I click somewhere like here, and I want to just type in that value, I have to click on it, and then grab my mouse and then select it. It is just a pain. I want to just be able to click and have it select the whole thing.
What we have to do is tell Access, in the On Click event for this field, I want you to move the cursor to the beginning, right there, and then tell the code to select all that text.
How do we move the cursor to the beginning of the field? That is changing something called the SelStart property. Let's just do it for first name. Right-click, Design View, click on first name, go to the On Click event.
You do not need it if you are tabbing around. We do not have to put it in On Got Focus because if you tab to it, it automatically selects all the text. I want to say move the cursor to the beginning.
So it is going to be FirstName.SelStart = 0, which is before the first character.
Save it, come back out here. Let's close it and open it. Now, if I click anywhere like here - boom - it just moves the cursor to the beginning. See that? Click off of it, click on it, it is at the beginning.
What if I say SelStart = 2? What happens is, click off it, click back on it - boom - it moves it right after the second character. See that? Let's put that back to zero; that's before the first character.
Now, I want to set the number of characters that are selected to be the whole thing. Let's say I want to select the first four characters. I can say FirstName.SelLength = 4. Watch what happens now. Click and it selects the first four characters. See that?
What if you want to select all the characters in there? We know how many characters are in there with the Len property, so I can come over here and say give me Len(FirstName).
If I click off it and then click back on it again, look at that. It selects all of them. Click off it, click back on it anywhere in here and it selects the whole thing. Now I can just type right over the top of it, which is exactly what he wants.
So that is the SelStart and SelLength properties.
Now, I want to be able to apply this to multiple fields in here, not necessarily just first name. I want pretty much every text box in here to have that. I do not want to have to repeat this code everywhere. I want to make my own little function.
I am going to make an event handler function so whatever the active control is, it handles it.
Let's make a function. We are going to make it a function because, to make it an event handler function, it has to be a function. Let's call it a Public Function, because later on we will move it to a global module and then it will work for any form. Let's call it SelAll (Select All). You are not taking any values in, you are not returning anything back, but you have to be a function.
How do we get the current control that the user is working with? That is Screen.ActiveControl. Why Screen? That's just the way the Microsoft people did it. There it is. Screen.ActiveControl.SelStart = 0.
Then Screen.ActiveControl.SelLength equals the length of Screen.ActiveControl.Value.
Yes, I know some of you already know where I am going with this.
So instead of FirstName_Click now, we are going to get rid of that. Now we can call SelAll as an event handler function.
I can come back over here, go to Design View, click on you and bring up your properties. On Click, I can say =SelAll, just like that. I will zoom in so you can see it better: just =SelAll.
The beautiful thing about doing it this way is I can select multiple fields. Watch this. Click. Hold the shift key down, click whatever field you want to have this property, pick them all, and then in the On Click event, go paste with the equal sign in front. Now they all have that as their On Click event. It is the easiest way to do it, and you have one block of code for it. You do not have 500 different events.
Let's save it, close it, open it. Now watch. Anytime I click on any of these fields, it selects all of the text inside that field. Isn't that just lovely?
What happened there? As I said, some of you already know where I am going with this.
It is perfectly fine with just text, because in a text field, the actual text in the box is exactly equal to the same text that is in the field. But with numeric and date values, for example, date sometimes works just right depending on your format, and numbers usually work fine. But something like a currency value, it has formatting in there. The actual value in that credit limit field is just 50000. It does not have the .00, and it does not have the dollar sign.
So what we have to use instead of the Value property is the .Text property. I have covered this in a couple of different videos. The .Text property gives you the actual text in the box, not the value in the underlying field. They are usually the same, but it takes formatting into consideration. So, go back and change .Value to .Text.
Those of you who caught that as I was doing it, sometimes I put something like this in the videos just to teach things. Those of you who already caught that before I did it, you get a lollipop.
Now it's working, it's working, it's working, it's working. Look at that. They worked down here because it is based on the text in there and not the value of the field.
That's it. That is all. That is how you do it. See, it is just two lines of code. But you have to know where to put the two lines of code. This makes it easy, knowing the event procedure and so on.
Furthermore, we are going to cut that out and put it in my global module. I will drop this in the code vault too for you Gold members, so any form can use SelAll.
I can come out here. Let's say you want to do it on your customer list as well. Come in here, Design View, select everybody, open up their properties. Click Properties, On Click =SelAll. Save it, close it, and now boom, boom, boom, boom, boom. Look at that. That is easy.
You have to learn some VBA, folks. Hang out with me. I'll teach you more cool stuff.
If you want to learn lots of cool stuff, I have lots of cool stuff on my website. I have 40 plus developer lessons; hours and hours of us hanging out and having fun programming and learning Access.
If you like this stuff, check it out. I'll put a link down below.
That's going to do it, folks. That is your TechHelp video for today. I hope you learned something. I hope you had some fun too.
Live long and prosper, my friends. I'll see you next time.Quiz Q1. What is the main reason someone might want to select all the text in a text box when clicking on it in Microsoft Access?
A. To make copying the text easier
B. To quickly overwrite the existing text without having to manually select it
C. To prevent accidental editing of the field
D. To enable formatting options for the text
Q2. How does the default behavior of clicking in a text box in Microsoft Access differ from Excel?
A. Access automatically selects all text; Excel places the cursor where clicked
B. Both select all text by default
C. Excel selects all text; Access places the cursor where clicked
D. Both place the cursor where clicked
Q3. Which event in Access is most appropriate for triggering the code to select all text when a field is clicked with the mouse?
A. On Change
B. On Double Click
C. On Click
D. On Got Focus
Q4. What does the SelStart property control in an Access text box?
A. The selected text color
B. The text box's starting value
C. The position of the cursor within the text box
D. The size of the text box
Q5. If you set SelStart to 0, what happens when you click on the field?
A. The last character is selected
B. The cursor moves to the beginning of the text
C. The text box is cleared
D. The text box closes
Q6. What does setting the SelLength property do?
A. Changes the font size
B. Determines how many characters are selected starting from SelStart
C. Adjusts the width of the text box
D. Hides the selected text
Q7. Which function is used to determine the number of characters in a text box in VBA?
A. Count()
B. TextLength()
C. Len()
D. Chars()
Q8. Why is it better to use a custom function (like SelAll) for the select-all behavior rather than repeating code for each control?
A. It makes the database more secure
B. It ensures the code runs more slowly
C. It prevents the need to write duplicate code in multiple places
D. It increases the risk of errors
Q9. What VBA object is used to refer to the currently active control in Access?
A. ActiveForm.Control
B. Me.ActiveControl
C. Screen.ActiveControl
D. UserForm.ActiveControl
Q10. Why is it important to use the .Text property instead of .Value in the custom SelAll function?
A. .Text supports more data types
B. .Text always returns a number
C. .Text includes formatting displayed in the control, unlike .Value
D. .Value is deprecated in newer versions of Access
Q11. Where should you place the SelAll function to make it accessible from any form in your Access database?
A. In the form's On Click event only
B. In a global module
C. In the report's code section
D. On a separate worksheet
Q12. How do you assign the custom SelAll function to multiple text boxes at once in Access?
A. Add SelAll to the form's On Load event
B. Type =SelAll in the On Click event of selected controls
C. Add SelAll to each table field
D. Run SelAll from the Immediate window
Q13. What happens if you use the SelAll function on a currency field that displays $50,000.00, but the underlying value is just 50000, and you use .Value instead of .Text?
A. All displayed characters will be selected, including formatting
B. Only the characters representing the value (no formatting) will be selected
C. The code will return an error
D. Nothing will happen
Q14. What Access property or method would you typically avoid using to trigger select-all behavior when tabbing between fields, based on the video?
A. On Click
B. On Got Focus
C. On Change
D. On Double Click
Q15. What is the benefit of using the same SelAll function in multiple controls across different forms?
A. It lets you secure your database
B. It simplifies maintenance, since updates are made in one place
C. It limits which users can edit fields
D. It automatically creates documentation
Answers: 1-B; 2-C; 3-C; 4-C; 5-B; 6-B; 7-C; 8-C; 9-C; 10-C; 11-B; 12-B; 13-B; 14-B; 15-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 covers how to automatically select all of the text in a text box when you click on it in Microsoft Access. This feature makes it much easier to overwrite data with a single click, mimicking the behavior you see in Excel.
A common question I get is, why would you want the whole text in a field to be selected when you click with the mouse? For example, in Excel, if you click on a cell, the entire content is selected, and you can just start typing to overwrite it. In Access, when you click on a text box, your cursor lands wherever you clicked - in the middle, at the end, or anywhere in the field. If you want to overwrite the data, you usually have to select all of the text manually, which can be a hassle if you use the mouse to navigate forms. It works fine if you tab into the field because Access automatically selects all the text in that case, but a lot of users prefer mouse navigation.
This is a feature I've built into some of my templates, including the account balances template, because I find it makes data entry simpler and quicker. Without it, you're forced to click and drag, or tab off then back into the field to select everything.
To achieve this behavior in Access, it takes just a little bit of VBA programming, but understanding the process is just as important as the code itself. The code is simple, but you need to know which properties and events to use.
If you have never worked with VBA before, I strongly suggest you check out my Intro to VBA video first. It covers all the basics and will help you get up to speed. I also recommend you watch my video on creating your own functions in VBA, as that's exactly what we'll be doing here - making a reusable function that you can apply to multiple controls, such as first name, last name, or any other fields where you want this behavior. My video on event handler functions is also relevant here, since we'll be calling our function directly from the event property. Lastly, some string functions will come into play, so check out my tutorial on those as well.
Let's take a look at the process using my free TechHelp template database, which you can download from my website. The main issue, as described above, is that clicking in a text box places the cursor wherever you clicked, not necessarily selecting the entire text. I want to make it so clicking anywhere in the field selects all of its contents for fast typing.
To do this, we need to use the On Click event of the text box and tell Access to move the cursor to the start of the field, then select all the text inside. This involves using the SelStart property, which controls where the cursor is placed in the text box. Setting SelStart to zero positions the cursor at the start.
Once SelStart is set to zero, we need to select all the text. That's where SelLength comes in. If you set SelLength to the length of the field's text, the whole content gets selected. The length can be determined using the Len function.
So with just these two lines, you can make it so every time you click on the text box, the full text is highlighted and ready to be overwritten.
Now, doing this for a single field is easy, but if you want this feature on multiple fields, it makes sense to create a function so you don't have to repeat the code everywhere. By making a public function, you can call it from the On Click event of as many text boxes as you wish. Inside this function, you use Screen.ActiveControl to refer to the control that just triggered the click event, then set its SelStart and SelLength properties as described. As a function, it can now be referenced by name in any field's On Click event property by simply typing the function name with an equal sign, like =SelAll.
Choosing multiple text boxes and pasting the function call in their On Click event makes the process quick and efficient. Now, every time you click on any of those fields, the entire text is selected.
However, you need to be aware of a small nuance regarding the property you use to get the content length. For text fields, using the Value property is fine. But for fields that might be formatted differently, such as currency or dates, Value might not match what's actually displayed in the text box. For these cases, you want to use the Text property instead, because it reflects how the data appears to the user, formatting included. This way, selecting all the text works correctly regardless of whether the text box is displaying numbers, dates, or formatted currency.
To make this function useful in any form, you should place it in a global module. That way, it becomes available for every form throughout your Access database. You can apply this function to any group of text boxes you like across different forms by editing their On Click property.
Learning basic VBA programming and techniques like this will open up a lot of possibilities in your Access projects, and I encourage you to keep exploring and experimenting.
For comprehensive, step-by-step instructions and a full demonstration, you can find the video for this tutorial on my website at the link below.
Live long and prosper, my friends.Topic List Selecting all text in a text box on click in Access forms
Using the SelStart property to position the cursor
Using the SelLength property to select text
Determining text length with the Len function
Writing a custom event handler function in VBA
Applying a function to multiple controls using On Click
Using Screen.ActiveControl to reference the triggered control
Understanding the difference between the Value and Text properties
Switching from .Value to .Text for formatted data
Assigning a VBA function to the On Click event property
Applying the same event handler to multiple fields at once |
