How to set tip in text box in Excel

Topic

New Reply

Is it possible to set a Control Tip for a Text Box in Excel 2003 ?

I thought I could use the ControlTipText property but get:

Run-time error 438: Object doesn’t support this property or method.

Reply | Quote

Replies

Hi Martin

There are a few ways to do this.
If you use an ActiveX textbox (from the Controls) rather than from the Forms controls, then you can use the mousemove event etc etc.

zeddy

Reply | Quote

Martin,

The Control Tip property works in 2010 .xlsm file and also when saved as 2003 .xls file in compatibility mode.
40520-ctrltipproperty

If it is present in 2003 there must be another reason for the error than it not being supported.

Here’s my test file in .xls format.: 40521-ControlTipExample

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

If you are using an active x textbox on a worksheet, you can place a comment in the cell behind the textbox. The comment will appear when the mouse hovers over it.

HTH,
Maud

40523-control-Tip

Reply | Quote

RG: thank you.
Maybe I have explained this badly – it’s the Command Button (the one captioned Press to display form) that I want to show the Control Tip.

Maud: thank you too !
I was toying with the idea of a comment but hadn’t experimented with it combined with an ActiveX textbox. I’ll have a play with that.
OK, this is the first time I’ve used an ActiveX textbox so be gentle with me !
I’ve created one – it behaves as I would expect when in design mode but in user mode nothing happens when I hover over it (with a commented cell underneath) and if I click on the textbox it goes straight into edit mode . . . what am I doing wrong ?

What I am trying to achieve here is a little help/advice for the user before pressing a button – or a textbox with an assigned macro.

Reply | Quote

Hi Martin

see attached file for example of displaying a ‘help tip’ when moving a mouse over a macro button.

In this example file, I added an ActiveX command button.
I named the button as ‘button1’ using the ‘name’ box (to the left of the formula bar)
I added sheet code for the mouseover event.

I also added two ‘form’ buttons to allow you to manually display the ‘help’ shape (to allow you to edit the required help text)
Have a play with this and see if it does what you want.

zeddy

Reply | Quote

Hi Martin

..here’s another method, using hyperlink assigned to a ‘green triangle’

This one is in Excel2003 file format

zeddy

Reply | Quote

Martin,

You can have a cell comment behind a form button as well

40527-ctrl-tip

Reply | Quote

Martin,

Apparently out of design mode for an active X control the comment will no longer appear. But it will work always for a form control.

Reply | Quote

Hi Z,

I couldn’t convert the .xlsm file but the hyperlink example works, thanks.

I’m working on how I can adapt this to my requirement . . . . when I create a hyperlink to a shape (so that I can add a screen tip), I have to put in an address – to which I get sent if I happen to click on the shape.

Why doesn’t this happen in your example ? I know I’ll kick myself when (if) you tell me :rolleyes:

OR . . . can the hyperlink target address be a Macro ??? If so how would I reference it ?

I see the target can be a Defined Name, so I wonder if I can slip some code in there perhaps ?

So many questions . . .

Reply | Quote

Hi Martin

Lets start by getting the code from the .xlsm version working with your Excel2003.
First off, I couldn’t create this in Excel2003 because I have an ongoing problem with my Excel2003.
I am unable to add any ActiveX controls to a worksheet in Excel2003.

open this attached Excel2003 file.

Lets assume that you can add an ActiveX command button to a worksheet.
If you can, then:
1. add an ActiveX command button to the sheet
2. name the command button as ‘button1’ using the name box (to left of formula bar)

Edit the help text in shape1 to suit your needs.
Then hide it, using the

Hidden Content

You must be logged in to see hidden content.

button

Then move your mouse over the command button you added.

zeddy

Reply | Quote

Hi Martin

For the hyperlink version, I used a cell address for the hyperlink address.
I then added the screen tip text.
I then deleted the cell address in the hyperlink.

zeddy

Reply | Quote

Hmmm – if I delete the hyperlink address and close the dialog – it just stays what it was . . . working on this though.

Reply | Quote

Hi Martin

..don’t forget to try post#11

Now, in this attached Excel2003 file, I set the hyperlink to move to cell [K1]
Then I use the Worksheet_SelectionChange event to test for this cell address, and if so, run the specified macro.

Does that help?

zeddy

Reply | Quote

Thanks Zeddy,

I’ve got there – thanks for sticking with this.

A curious thing I came across – launching the code by clicking on the shape didn’t work the second time !

Strictly, it doesn’t work if the target cell of the hyperlink is already selected – presumably because in that case there is no change.

I’ve worked around this, somewhat crudely, in the attached.

The triggers for change events in Excel have always led me astray one way or another, so this one is consistent I suppose.

PS I now have to be careful about clicking in K1 :rolleyes:

Reply | Quote

Hi Martin

..hide the column [K] and you won’t be able to click it!
Try it.

Now seriously, you could use any cell in a hidden row or a hidden column.
Also, the routine you assign could also put the cellpointer into a new location too.

zeddy

Reply | Quote

Indeed, it isn’t really a problem.

In the sample I posted you’ll see that I have already pointed to another cell.

Thanks again.

Martin

Reply | Quote

Hi Martin

So, here is another sneaky method for showing a ‘screen tip’:

1. select any empty cell
2. from the menu insert>chart
3. click Finish
You now have an empty blank chart object.

4. Right-click the empty chart object to assign your macro.
5. Insert your graphic image into the empty chart (e.g. use copy and paste)
6. Adjust size of chart area (containing your image) to suit
7. Select your graphic image.
8. Assign a name to the image using the Name box (to left of formula bar)
9. The assigned name becomes your tooltip text!

See example attached.

zeddy

Reply | Quote

Z,

Thanks: lateral thinking + persistence solves any Excel challenge !

I’ll try this out later.

Martin

Reply | Quote

Hi Martin

..and when you’ve had a look at that method in the v2c file, I have another one.

zeddy

Reply | Quote