Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Excel VBA (UKenglish/office97/sr2)

    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 <img src=/S/headthrob.gif border=0 alt=headthrob width=15 height=15>

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA (UKenglish/office97/sr2)

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

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Excel VBA (UKenglish/office97/sr2)

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Warrington, Cheshire
    Posts
    355
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Excel VBA (UKenglish/office97/sr2)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •