4

You know how sometimes you see text in an input field prompting for an answer? When you click it, it goes away, but comes back if you deselect without having typed anything.

How do I accomplish this in an Excel text box? Can this be done at all?

I think I could accomplish this if I attach a click macro to the text box and the macro can be able to tell what text box was clicked, but the problem with the click macro is it won't let you select the object first, so you have no idea what was clicked. And I don't want to hard code the text box name in the click macro code.

Improve this question
2
  • 1
    @Wes: "I don't want to hard code the text box name in the click macro code." Why not? That's how I would do it. Given that Excel appears to have no mechanism to return a reference to the object that called an assigned macro (which sucks), that may be the only way to go. Commented Dec 15, 2011 at 7:59
  • 2
    @JFC- what about Application.Caller ? That returns the name of the object triggering the macro. Commented Dec 15, 2011 at 16:08

3 Answers 3

Reset to default
4

I think you're referring to a "placeholder" in a UserForm. The two tricks that I use to achieve this are the "tag" property and Enter and Exit events. First, create your sub modules that will fire upon Enter (focus) and Exit (blur).

Sub TB_enter(TB_name)
    If Len(Me.Controls(TB_name).Tag) = 0 Then
        Me.Controls(TB_name).Tag = Me.Controls(TB_name).Value
        Me.Controls(TB_name).Value = vbNullString
    End If
End Sub
Sub TB_exit(TB_name)
    If Len(Me.Controls(TB_name).Value) = 0 Then
        Me.Controls(TB_name).Value = Me.Controls(TB_name).Tag
        Me.Controls(TB_name).Tag = vbNullString
    End If
End Sub

Next, for each instance of your textbox on your userform, double click on the textbox while in View Object mode. This will automatically create a Private Sub for the change event. Head to the upper right hand corner of the toolbar and change the "Change" drop down list to "Enter". This will create a new Private Sub for the Enter event. Repeat this process again, but select "Exit" to create the Exit event. Now, simply copy the following code into the Enter and Exit events respectively (I've used the default name for the first textbox, but this is name independent when calling the events).

Private Sub TextBox1_Enter()
    TB_enter ActiveControl.Name
End Sub
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    TB_exit ActiveControl.Name
End Sub

If you are placing these calls in an external module, simply replace "Me" with the name of the UserForm.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

I'm not using UserForms, but it's OK it's just a nicety I don't really need.
How exactly are you using this Excel sheet? Are you using ActiveX controls for the text box? Can the user copy and paste these text boxes anywhere in the Excel sheet? You might just want to consider Conditional Formatting a cell and putting Data Validation with an Input Message in the cell. A user can copy and paste this to their heart's content.
0

You could do it with a couple of events. To clear the box on click is pretty easy:

Private Sub TextBox1_GotFocus()
    TextBox1.Text = ""
End Sub

I'd add some other steps, add a worksheet_activate event to set an inital value and set a flag for if the default value is set:

Private Sub Worksheet_Activate()
    Dim textBox1Default As Boolean
    TextBox1.Text = "Please enter some text here"
    textBox1Default = True
End Sub

Then I'd add a TextBox1_Change event to remove the default flag

Private Sub TextBox1_Change()
    If textBox1Default = True Then textBox1Default = False
End Sub

And finally adjust the original GotFocus event to include the flag check:

Private Sub TextBox1_GotFocus()
    If textBox1Default Then TextBox1.Text = ""
End Sub

You'll need to make it work for you, this way if you change tabs it will reset the text but you can have it check that on activate and change it to suit when you want it to reset.

Jesse

Improve this answer

3 Comments

Your solution requires that you have the text box referenced in your code, but I do not have that luxury because these text boxes get copied by the user and there's no way to get an explicit reference to the copied text box. For instance say the copied text box is automatically labeled "text box 2" but your events only apply to text box 1.
You could loop through all text boxes on the active sheet, would be fast but you'd have to use the same text for all of them.
Is there an event that could capture clicking on any text box? Then when that happens be provided a reference to the text box that was clicked?
0

I think proper term for that kind is called "Placeholder". Here is the easy way which I found in another forum. In the format property of the textbox, type this:

@;"Your ghost text to disappear"

Below image is the screenshot of original author's post. screenshot of original author's post

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.