How do I insert a tooltip into an Excel cell?

How can I insert a tooltip to an Excel cell by dxl- and ole-code?

Note: It is possible to achieve this by using a hyperlink. But I do not want to insert a hyperlink.

Re: How do I insert a tooltip into an Excel cell?
Mathias Mamsch - Fri Apr 21 10:23:40 EDT 2017

Yes it is. For the specific OLE commands you will need to look on some Office Forum. Once you have the OLE commands, we can help to translate them to DXL. Regards, Mathias

Re: How do I insert a tooltip into an Excel cell?
MarcoLuk - Fri Apr 21 10:33:48 EDT 2017

Ok, I recorded a vba macro in Excel by adding a tooltip "Tooltip" to an Excel cell:

Sub Tooltip()
'
' Tooltip Makro
'

'
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = "Tooltip"
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
End Sub

Re: How do I insert a tooltip into an Excel cell?
Mathias Mamsch - Fri Apr 21 12:16:18 EDT 2017

So once you get your "Range" OleAutoObj pointing to your cell, you get the validation property (you need to check if this is a Property or a Method):

e.g.:

checkError oleGet(objRange, "Validation", objValidation)

and then you can simply translate the calls like

oleMethod(objValidation, "Delete"); 
OleAutoArgs args = create(); 
put(args, ...) // put the parameters in
oleMethod(objValidation, "Add", args); 
olePut(objValidation, "IgnoreBlank", true); 
...

See other Excel OLE examples. Hope this helps, regards, Mathias

Re: How do I insert a tooltip into an Excel cell?
MarcoLuk - Mon Apr 24 04:43:20 EDT 2017

checkError oleGet(objRange, "Validation", objValidation)

oleMethod(objValidation, "Delete"); 
OleAutoArgs args = create(); 
put(args, ...) // put the parameters in
oleMethod(objValidation, "Add", args); 
olePut(objValidation, "IgnoreBlank", true); 
...

Ok, thanks. I am now using this dxl-code:

checkRes(oleGet(objSheet, "Validation", objValid))

if (null objValid) {

log_error(

"Unable to get validation object", true, true)

halt

}

checkRes(oleMethod(objValid, "Delete"))

clear(args)

put(args, "Type", 0) //xlValidateInputOnly)

put(args, "AlertStyle", 1) // xlValidAlertStop)

put(args, "Operator", 1) //xlBetween)

checkRes(oleMethod(objValid, "Add", args))

checkRes(olePut(objValid, "IgnoreBlank", true))

checkRes(olePut(objValid, "InCellDropdown", true))

checkRes(olePut(objValid, "InputTitle", ""))

checkRes(olePut(objValid, "ErrorTitle", ""))

checkRes(olePut(objValid, "InputMessage", s_tooltip))

checkRes(olePut(objValid, "ErrorMessage", ""))

checkRes(olePut(objValid, "ShowInput", true))

checkRes(olePut(objValid, "ShowError", true))

But this code throws the ole-error CRCRD5311W.

Can you tell me what may be wrong?

Regards,

Marco Hof

Re: How do I insert a tooltip into an Excel cell?
Mathias Mamsch - Mon Apr 24 04:56:37 EDT 2017

It seems to me you are trying to put a tooltip to the sheet? Try to get a Range / Cell from the sheet and put a tooltip on that. For examples on getting a range you should find something on the forum. Look for "OleAutoObj Range etc". Regards, Mathias

Edit: Also please tell us, which of the OLE calls gives you the error!

Re: How do I insert a tooltip into an Excel cell?
MarcoLuk - Mon Apr 24 05:20:11 EDT 2017

Ok, here is my whole code:

void SetExcelCell(int i_row, int i_col, string s, string s_tooltip) {

OleAutoArgs args = create

OleAutoObj objCell = null

OleAutoObj objRange = null

OleAutoObj objValid = null

closeIfNonNull objCell

clear(args)

put(args, (IntToCol i_col) i_row "")

checkRes(oleGet(objSheet, cMethodRange, args, objCell))

if (null objCell) {

log_error(

"Unable to get cell object", true, true)

halt

}

// Excel considers string starting with an equal character (=) to be an excel formula

// hence it throws an error "OLE method failed". To avoid this we append "'" before the string.

if(s[0:0] == "=")

{s = "'" s

}

checkRes(olePut(objCell, cPropertyValue, s))

checkRes(oleGet(objCell, cPropertyRange, objRange))

if (null objRange){

log_error(

"Unable to get the range object", true, true)

halt

}

checkRes(oleGet(objRange, "Validation", objValid))

if (null objValid) {

log_error(

"Unable to get validation object", true, true)

halt

}

checkRes(oleMethod(objValid, "Delete"))

clear(args)

put(args, "Type", 0) //xlValidateInputOnly)

put(args, "AlertStyle", 1) // xlValidAlertStop)

put(args, "Operator", 1) //xlBetween)

checkRes(oleMethod(objValid, "Add", args))

checkRes(olePut(objValid, "IgnoreBlank", true))

checkRes(olePut(objValid, "InCellDropdown", true))

checkRes(olePut(objValid, "InputTitle", ""))

checkRes(olePut(objValid, "ErrorTitle", ""))

checkRes(olePut(objValid, "InputMessage", s_tooltip))

checkRes(olePut(objValid, "ErrorMessage", ""))

checkRes(olePut(objValid, "ShowInput", true))

checkRes(olePut(objValid, "ShowError", true))

}

It does still throw the OLE error. Is the range Determination still wrong?

Regards

Marco Hof

Re: How do I insert a tooltip into an Excel cell?
Mathias Mamsch - Mon Apr 24 05:36:28 EDT 2017

Please use code blocks or files to post code. Where does the error occur? The following code seems to work for me: 

void checkRes(string s) { if (!null s) error s }

OleAutoObj objExcel= oleGetAutoObject("Excel.Application") // Connect With Excel
 
if(null objExcel) error "Please open excel!"
 
OleAutoObj objWorkbook = null
OleAutoObj objSheet= null
OleAutoObj objRange= null
OleAutoObj objValid= null

OleAutoArgs args = create
 
oleGet(objExcel,"ActiveWorkbook",objWorkbook)
oleGet(objWorkbook,"ActiveSheet",objSheet)

clear args
put (args, "A1")
oleGet(objSheet, "Range", args,objRange)

olePut(objRange, "Value","Hallo");

checkRes(oleGet(objRange, "Validation", objValid))
checkRes(oleMethod(objValid, "Delete"))

clear(args)
 
put(args, "Type", 0) //xlValidateInputOnly)
put(args, "AlertStyle", 1) // xlValidAlertStop)
put(args, "Operator", 1) //xlBetween)

checkRes(oleMethod(objValid, "Add", args))
checkRes(olePut(objValid, "IgnoreBlank", true))
checkRes(olePut(objValid, "InCellDropdown", true))
checkRes(olePut(objValid, "InputTitle", ""))
checkRes(olePut(objValid, "ErrorTitle", ""))
checkRes(olePut(objValid, "InputMessage", "HUHU!"))
checkRes(olePut(objValid, "ErrorMessage", ""))
checkRes(olePut(objValid, "ShowInput", true))
checkRes(olePut(objValid, "ShowError", true))

Regards, Mathias

Re: How do I insert a tooltip into an Excel cell?
MarcoLuk - Mon Apr 24 05:52:12 EDT 2017

void checkRes(string s) { if (!null s) error s }

OleAutoObj objExcel= oleGetAutoObject("Excel.Application") // Connect With Excel
 
if(null objExcel) error "Please open excel!"
 
OleAutoObj objWorkbook = null
OleAutoObj objSheet= null
OleAutoObj objRange= null
OleAutoObj objValid= null

OleAutoArgs args = create
 
oleGet(objExcel,"ActiveWorkbook",objWorkbook)
oleGet(objWorkbook,"ActiveSheet",objSheet)

clear args
put (args, "A1")
oleGet(objSheet, "Range", args,objRange)

olePut(objRange, "Value","Hallo");

checkRes(oleGet(objRange, "Validation", objValid))
checkRes(oleMethod(objValid, "Delete"))

clear(args)
 
put(args, "Type", 0) //xlValidateInputOnly)
put(args, "AlertStyle", 1) // xlValidAlertStop)
put(args, "Operator", 1) //xlBetween)

checkRes(oleMethod(objValid, "Add", args))
checkRes(olePut(objValid, "IgnoreBlank", true))
checkRes(olePut(objValid, "InCellDropdown", true))
checkRes(olePut(objValid, "InputTitle", ""))
checkRes(olePut(objValid, "ErrorTitle", ""))
checkRes(olePut(objValid, "InputMessage", "HUHU!"))
checkRes(olePut(objValid, "ErrorMessage", ""))
checkRes(olePut(objValid, "ShowInput", true))
checkRes(olePut(objValid, "ShowError", true))

Ok, thanks. I will try this.

Another issue: Is it possible to extend that code, so that the tooltip of the Excel cell will automatically be shown at the mouse pointer moving over the cell?

Re: How do I insert a tooltip into an Excel cell?
Mathias Mamsch - Mon Apr 24 06:40:20 EDT 2017

This would again be an issue for an Office forum, until you come up with some VBA or such, that we can help to translate to DXL. Regards, Mathias

Re: How do I insert a tooltip into an Excel cell?
MarcoLuk - Mon Apr 24 08:57:44 EDT 2017

Ok, I will try this at another time.

Now the code works, I actually get the tooltip in every Excel cell, but I still get the OLE-error-message once (only one time at the beginning of the Export).

What can still be wrong?

Re: How do I insert a tooltip into an Excel cell?
Mathias Mamsch - Mon Apr 24 10:10:20 EDT 2017

You are probably calling your functions with invalid arguments once. Debug what exactly you are passing to your ole functions and you will find the problem (invalid object, invalid range, etc.) Regards, Mathias

Re: How do I insert a tooltip into an Excel cell?
MarcoLuk - Mon Apr 24 12:41:43 EDT 2017

Hello Matthias,

unfortunately I do not have a debugger within the Eclipse IDE.

I cannot find any invalid objects or ranges, they should be valid.

The error is being thrown not at the exact begin, but later.

If I confirm the ole-error with "continueing yes to all", the cells are being written with the tooltips regardless of the ole error.

Note: If I comment out the section, the OLE-error will not be thrown, but therefore no tooltip of course:

checkRes(olePut(objValid, "IgnoreBlank", true))
checkRes(olePut(objValid, "InCellDropdown", true))
checkRes(olePut(objValid, "InputTitle", ""))
checkRes(olePut(objValid, "ErrorTitle", ""))
checkRes(olePut(objValid, "InputMessage", "HUHU!"))
checkRes(olePut(objValid, "ErrorMessage", ""))
checkRes(olePut(objValid, "ShowInput", true))
checkRes(olePut(objValid, "ShowError", true))

Do you have a further idea or advice for me?

Re: How do I insert a tooltip into an Excel cell?
Mathias Mamsch - Mon Apr 24 12:46:14 EDT 2017

With debugging, I do not mean that you need a debugger ... You can use print statements to trace your OLE calls. When I need to guess, you are probably passing a worksheet instead of a range or something like that, for the first iteration. Regards, Mathias

Re: How do I insert a tooltip into an Excel cell?
MarcoLuk - Tue Apr 25 03:35:04 EDT 2017

Yes, I used print statements, but I cannot find the error either.

I also cannot detect that I pass a worksheet or anything other than a range (objCell is the range).

Here my current code:

//--------------------------------------------------------------

//Makes the same like the function above, in addition a tooltip.

void SetExcelCell(int i_row, int i_col, string s, string s_tooltip) {

OleAutoArgs args = create

OleAutoObj objCell = null

OleAutoObj objValid = null

closeIfNonNull objCell

clear(args)

put(args, (IntToCol i_col) i_row "")

checkRes(oleGet(objSheet, cMethodRange, args, objCell))

if (null objCell) {

log_error(

"Unable to get cell object", true, true)

halt

}

// Excel considers string starting with an equal character (=) to be an excel formula

// hence it throws an error "OLE method failed". To avoid this we append "'" before the string.

if(s[0:0] == "=")

{

s = "'" s

}

checkRes(olePut(objCell, cPropertyValue, s))

closeIfNonNull objValid

checkRes(oleGet(objCell, "Validation", objValid))

if (null objValid) {

log_error("Unable to get validation object", true, true)

halt

}

checkRes(oleMethod(objValid, "Delete"))

clear(args)

put(args, "Type", 0) //xlValidateInputOnly)

put(args, "AlertStyle", 1) // xlValidAlertStop)

put(args, "Operator", 1) //xlBetween)

checkRes(oleMethod(objValid, "Add", args))

checkRes(olePut(objValid, "IgnoreBlank", true))

checkRes(olePut(objValid, "InCellDropdown", true))

checkRes(olePut(objValid, "InputTitle", ""))

checkRes(olePut(objValid, "ErrorTitle", ""))

checkRes(olePut(objValid, "InputMessage", s_tooltip))

checkRes(olePut(objValid, "ErrorMessage", ""))

checkRes(olePut(objValid, "ShowInput", true))

checkRes(olePut(objValid, "ShowError", true))

}

For initialization I use following code:

bool InitExcel(string s_excel_template) {

log_debug(Excel template = " s_excel_template)

OleAutoArgs args = create

objExcel = connectToApp(cObjExcelApplication, "Excel")

if (null objExcel) return false

makeVisible objExcel

// get workbooks collection

checkRes(oleGet(objExcel, cPropertyWorkbooks, objWorkbooks))

if (null objWorkbooks) {

log_error(

"Unable to get workbooks collection", true, true)

return false

}

// open XLT template file

put(args, s_excel_template)

checkRes(oleMethod(objWorkbooks, "Open", args))

// get active workbook

checkRes(oleGet(objExcel, cPropertyActiveWorkbook, objWorkbooks))

if (null objWorkbooks) {

log_error("Unable to get active workbook", true, true)

return false

}

// get active sheet

checkRes(oleGet(objWorkbooks, cPropertyActiveSheet, objSheet))

if (null objSheet) {

log_error("Unable to get active sheet", true, true)

return false

}

return true

}

Re: How do I insert a tooltip into an Excel cell?
Mathias Mamsch - Tue Apr 25 04:35:46 EDT 2017

The problems I am having here to help you is: 

- I have not a working example to reproduce the error

- I do not have a clear error description with DXL error message and line number inside the code

So what you need to come up with at least for anyone to help you would be: 

a) either a working minimal example that will reproduce the problem

or  b) A very detailed description about  

- where exactly the error occurs and when

- what the error message is

- what exact parameters are passed to the OLE functions

You should also google for the OLE error code - most of the times you will find something like "this error happens when the worksheet is not visible and you try to call xy" ... 

Regards, Mathias