Linking TextBoxes to labels displayed on Excel/VBA userforms

I know this problem has been solved by many, but I am still having difficulty with the precise syntax.I want to keep the description of my problem simple.
I would like to have a different label (description) displayed dependent upon which textbox is selected or currently has focus on a userform.
If you use the function insert in Excel, for example, depending on which textbox you are entering information into (that is, which one has the current focus) the label which is displayed is different and is for that specific textbox.
Thanks in advance.

Hi Greg:

Thanks so much for responding. I really appreciate it.

Actually it is N textboxes, and N labels, each textbox with its own label.

I will have at most 4 Text Boxes on a userform. I would like to link each of the Text Boxes on the userform to its own, unique label when the Textbox has focus.

Let's take the case of two Text Boxes, TextBox1 and TextBox2.

When TextBox1 has focus (is selected), I would like Label1 to be visible.

When TextBox2 has focus (is selected), I would like Label2 to be visible, a different label than Label1.

I have labels on the userform presently and not linked to a TextBox, but when the Textbox has focus I would like to highlight what needs to be done in maybe slightly different words for clarification for the user.

On the attached, for example, when the textbox for "a" has focus, I would like to have visible another label about "a" that describes it more fully, when the textbox for "M" has focus, I would like a different label visible which describes more about "M", and so on for "b"--each textbox with its own unique label. Of course, if I know how to do it for just 2 text boxes, I can replicate this for more of them.

I would have at most 4 Text Boxes on a userform, but usually 2 or 3 of them.

I have 10 Userforms to develop, but of course once I get one done, the others will be very simple.

Thanks so much again. I hope this describes it well.

Jim

Hi again Jim,

If only everybody described his/her requirements as clearly as you have done!!!

Take a look at the attached workbook and see if it does what you need.

I've included two different UserForms to demonstrate two of the various approaches you can take.

One UserForm contains three TextBoxes and three Labels - the appropriate Label is shown/hidden when a TextBox receives/loses the focus. In this UserForm one label is positioned in the appropriate location, and the other two are "parked out of the way" at Design Time, but are moved to the appropriate location at Run Time. You can of course superimpose all three labels at Design Time and omit the few lines of code associated with positioning the two labels and setting the height of the UserForm, but I have always preferred to position Controls for maximum "designer convenience" and to let the computer worry about repositioning them at Run Time.

The other UserForm contains three TextBoxes and a single common label - the caption of this common label is changed to an appropriate or blank value as TextBoxes receive/lose the focus.

I think either approach will suit your needs, so you can choose whichever is the more convenient for you.

Hope this helps - please let me know how you get on.

Regards,

Greg M

Hi Greg:

This is unbelievable, absolutely incredible. I just joined this service (Excel Forum), and on first use, I strike gold! This is precisely what I am looking for. Thank you, thank you, thank you!!!

I need to look at what you have done--some of the statements are new to me. And you took a little different approach than I did--I think I was "focusing" (pun intended) too much on the visible property of labels, and needed to look elsewhere. It really took me too long as well to get the little graphic in the image box, but I did finally get that figured out.

I am a firm believer that you cannot make userforms careful enough so that they are clear to everyone. So make them as clear as you can. That is precisely what I hope the extra (visible, non-visible) labels will do. Words even like "quantity", "value", "integer", "minimum", "maximum" can be confusing at some level. So the labels you helped me with will popup when the TextBox has focus and will hopefully add explanation to what is required. You have to be so careful--calling the "Mode" the most likely value (or quantity or number or whatever), for example, is absolutely wrong. I might be able to add an explanation when the Mode Textbox has focus along the lines of, "The mode is the value you would see most frequently if you were able to repeat an activity or effort over and over and over again"--or something like that. Even the use of the word "number" in the userform I posted required some thought, as normally I would use "value" in describing it, but "number" does suggest, of course, more of a numeric quantity.

I am not sure how one properly thanks someone on the forum (since this is my first use) when they help like you have. You will have to give me some type of an idea of what I might do for you. For starters, if I can get your last name, I can acknowledge your contribution in the software. In the "about" section of the ribbon, I can acknowledge your help (I will change the wording shown--I was just anxious to get something down). I have two or three friends looking over my shoulder in reviewing this material, and I had already planned to acknowledge them. But do please let me know what I can do further.

What the development will amount to when completed is random number generation routines for various probability distributions (shown). The useforms will help or guide the user in entering material into the respective formulas.

Thank you so much once again. And do please let me know what I can do for you further.

Best,

Jim Patterson

Hi again Jim,

Feedback like yours is what keeps me coming back to this Forum!

Your project seems really interesting and your approach to how it should be presented to Users really impresses me! I would be quite happy to work (absolutely free of charge! )with you on this if you need any further assistance. I will be away from my computer / this forum until the end of next week - feel free to send me a Private Message or post something here and I´ll reply when I´m back.

You seem to have a truly professional approach to your work which is something seen far too infrequently these days!

If you really feel the need to "repay" me, you can click on the button at the bottom of this post to "add to my reputation" on here.

Best regards,

Greg M

Hi Greg:

Thanks again for your help. It is really appreciated!! I think you realize this.

We have an expression (idiom) in America (not sure if it exists in Ireland) of "going to the well too often". It means relying on others perhaps more than you should.

I do have one more issue, but let me work on it first. It is something I should be able to figure out on my own. The other one (you helped with) I could see what needed to be done, but had difficulty searching out just how to do it. I was clueless--like in a maze, I needed someone to tell me to try a different direction and then show me how to do that as you did. I was, well, void of what I really needed, even though I had seen the same operations being done by others. Two excellent texts I had on the subject were not much help on this particular issue, but I think they will be on the other one.

Thanks again. Enjoy your time wherever you are away from your computer.

I will let you know if I can't resolve my other issue, but I think surely I can.

Best, and thanks once more,

Jim

Hi Greg:

I thought I had posted a reply, but I don't see it, so I shall do it again. Sorry if there are two of them.

I implemented your first approach and it works like a charm. Having selectively appearing labels cleans up the userform so much.

I think now I need a course in Graphic Design--there are so many choices to be made with all of the alternatives available. I try and keep it simple and similar to other userforms in Excel.

I was not aware of the Enter and Exit methods of TextBoxes, but your reply taught me so much more than this--picked up several programming tricks along the way.

Let me know if you would like to see the finished product. I need to find and fix the "bug", and add one more userform first, but hope to get them done in the next couple of weeks.

Have a super holiday.

Best,

Jim

Hi again Jim,

Many thanks for all of your feedback, and also for the Reputation increase

Yes, I would DEFINITELY like to see the finished product! If you don't want to post it here just let me know & I'll send you a Private Message with my email address.

As always, please feel free to shout if you think I can help you with anything.

Best regards,

Greg M

Hi Greg:

I will be happy to send you what I have once I get one (this one!) of the userforms in better shape. I can post this on the forum or send it via your private email, whatever you prefer. Then, when I get them all done, I can send you via your private email all twelve.

Once I get a model or format decided upon for this first userform (Triangular distribution), I think I can program one or two of the remaining ones per day, or a minimum of 5 per week. There are small changes in the graphics between the graphics on the ribbon and the ones in the userforms, but they are not too difficult to make. Plus, I need to check my own error checking routines. For example, in the userform I asked you about, I do a check that the user puts in all numbers, that a <= M <= b, and so forth, but an error is cropping -up in my own error checking routine and I need to correct this. If the user puts in all good values—it works fine, but there is some error in my own error checking program at the moment. I need to correct this before sending you or posting a sheet that has this distribution completed.

Not sure why I started with the Triangular Distribution, but I did.

There have been two changes since we first started corresponding.

First, a friend suggested I add the LogNormal distribution, which I will.


Ribbon1.jpg

The same friend suggested I add a Guidelines button—Why would I ever use this distribution, or what is it good for? I can do this also.
I think both are excellent suggestions.

Guidelines1.jpg

Perhaps you can look over my shoulder and tell me what you like and don’t like about this first userform (Triangular distribution). I would find this to be helpful. For example, should the Guidelines button be placed elsewhere (say, above the graphic), or are there other changes that might make it more aesthetically appealing or easier for the user to enter data? I had a frame on the earlier one I sent you, but the frame was causing me a little difficulty in implementing your approach, so I just took it off for the moment. It was non-functioning—just put on to make the form look nicer so I could go back and add it if you like this version better. And so on… Please feel free to share this as well.

Thanks again. Please do let me know if something “jumps out at you” needing improvement. In the meantime, I will work on these two changes in the existing userform and post the results.

Have a nice weekend.

Jim

Hi again Jim,

Many thanks for your latest post - your UserForm gets better all the time!

As always in Excel, there is more than one way to skin a cat, so to get around the difficulties caused by using a Frame, the attached workbook shows how to create what I've called a "pseudo-Frame" - i.e. it looks like a frame but it doesn't act like one, in that it allows more than one of its controls to be selected. It's constructed quite simply by using just an empty Image control and a Label control. I haven't put any controls in the pseudo-Frame - these can be added to suit requirements.

I've also put an untitled Image (box) around the Method and Guidelines CommandButtons as a suggested way of visually linking those buttons - see what you think.

A further suggestion - if you think your Guidelines text might use up too much "real estate" on the UserForm you can use a second UserForm containing only the required text. This second UserForm can be positioned wherever you like (e.g. directly underneath/beside the first UserForm) - it does not have to be superimposed on the first UserForm.

I'll send you a Private Message with my email address so you can send me your (completed or uncompleted ) UserForm whenever it suits.

As always, shout for anything else you need.

Best regards,

Greg M

Hi Greg:

Thanks again for the help.

I like your idea for enclosing the Method and Guidelines command buttons in a separate, marked off area. To me it gives the impression to the user that “you don’t really need these”, where he or she needs the rest.

Eventually, of course, the user won’t even want the userform—he or she will become familiar enough with the formula that it will be entered directly into the spreadsheet.
I have the Method command response in a separate userform. It is not long, but the one about guidelines might be a little longer. It is an excellent suggestion.

I like your frame idea too—it focuses attention where you want it. I put the one I had in the Excel userform for “decoration”, but if used wisely, they do look nice.

I will send you the spreadsheet at your email address.

Thanks again,

Jim

Hi Jim,

Thanks for your email - I'll play with the workbook you attached and email you about how I get on.

Best regards,

Greg M