Excel VBA (UKenglish/office97/sr2)

Topic

New Reply

Help needed please.

When data validation has been added to particular Excel cells, the “ToolTip” window appears completely black on some of our end users workstations. As I understand it, this is a result of the users profile having the display options set to Black background for the tooltips (Displayschemes etcetera).
In our office, this ain’t a problem, we can reset the tooltips to a light colour and voila, the data validation “Help” text appears.
However, when we send workbooks out for external contractors (in particular for those persons with profiles which do not allow display changes e.g. in NT4.0 etcetera) we want to be sure we cover all eventualities for errors, hence we would like to set the tooltip background using vba via the excel application when the workbook is opened. I’ve managed to identify the constant vbInfoBackground as the value I want to change but I’m not sure as it is actually possible from within the workbook. Anyone come across this before?

******
try it for yourself by setting the Displaytooltips background to black.
after applying this you’ll see that the mouse over hover shows black tooltips, (probably with grey text)
Now go to excel and apply a data validation to a cell and include some text as the help text when the cell is entered.
Can you see anything? or is it just a black window?
*********

Any help would be mush appreciated
TIA

Alan

Reply | Quote

Replies

vbInfoBackground is a constant and can not be changed by VBA code. I do not see a way to change the color from VBA.

Reply | Quote

Hi,
You can do this using the Windows API (though this may depend on Operating System). The code would be something like:

Public Declare Function SetSysColors Lib “user32” Alias _
“SetSysColors” (ByVal nChanges As Long, lpSysColor As _
Long, lpColorValues As Long) As Long

at the start of your module, and then:

Sub tooltipColours()
Dim retval
retval = setsyscolors(1, 24, RGB(255, 255, 225))
End Sub

to actually implement it. You can change the RGB values according to what colour you want (the values above are that default pale yellow)
Hope that helps.

Reply | Quote

For a while I had resigned myself to the fact that it could not be achieved.
Not so in this case.
Many thanks Rory.

Reply | Quote