Present a cell content upon pressing a button

English
#1

Hi Calc wizards.

I have developed a simple, calc-based survey tool to assess compliance of factories with certain industry standards. It has the questionnaire sheet with all the questions and a yes/no/not applicable drop down menu after each question to choose from. The data are processed in a second (hidden) sheet and the results are shown in a table and with a graph on a third sheet.

The whole tool is bilingual. This is achieved by using a language selection drop down menu in the questionnaire sheet which links to a 4th sheet, that lists all written entries and their respective translations. All cells are linked to the respective language cells. So far so good, all of this works well.

Now I was asked to introduce detailed explanations to each question. Putting those behind each question would be quite messy, since each explanation could take up a lot of space. So, I was hoping for a solution that would allow me to display only the explanation for the “current question”. My problem is, that it has to be linked to the language choice. I have tested a hyperlink but it is not a very obvious option for less computer literate users. Ideal would be something like a button that sits next to the question and upon being pressed displays the respective explanation in a specified field or shape adjacent to the questionnaire.

Alas, I have no clue how to do this, or if it can be done. If anyone has another suitable idea, I am open to better solutions.

Thanks heaps for your help.

#2

There are many ways to make your wishes come true.
Upload a small file - layout (bilingual), then it will be possible to move on to specific proposals.
The easiest (and obvious) option is cell comments.

1 Like
#3

But comments do not adjust to the language choice.

#4

With macros, we can do a lot…

#5

RMS Compliance Assessment Tool_TT 2.5.xlsx (266.5 KB)

Here the tool at its current state. The information in $Explanations.D4:D103 should be used as explanation. It will be organized equivalent to $Config.C8 ff.

#6

Functional “buttons” mostly need executable code behind. Is it feasible for you to implement the new feature based on user code?
Users then need to either have this code accessible from their local script libraries, or to permit execution of document macros (at least in a somehow limited way).

#7

Permit the execution of macros should be possible as long as it is all integrated in the file as standalone. Users would not be able to first add code to their local libraries.

#8

Why is the .xlsx file format chosen?
Is the same file supposed to be used in both MS Excel and LibreOffice Calc?

#9

Most users will probably use MS office, since they use software provided with their computers. Compatibility is therefore key.

#10

Maybe we’ll try to do that.
On the Inputs sheet, column E will contain comments on the question in English.
After column E, add a new column F with comments in Lao.
When choosing a language, one of the columns E or F is automatically hidden.
When you hover over a cell in column E (F), the comment text will pop up.
The application form will need to be edited in MS Excel.

#11

I don’t fully get you. You say the comments will be in column E/F, and one will be hidden at the time depending on language choice. But at the same time you talk about a popup comment. Isn’t it either or?
The solution with the comment in the same row is not so nice (if I understand you correctly) because you will get many lines of text for each cell, making the whole form ugly and difficult to maneuver.
A hover pop-up sounds more than I had hoped for. But editing in MS Excel works only if it can be done once and then be fix. Users would not be able to do anything technical with it (the sum function is unknown to most).

#12

Try this form in Excel and Calc. Macros must be enabled.
Comments are filled in for the first two questions.
Unfortunately, the forum does not allow you to attach a file with the .xlsm extension. So change the extension from .ods to .xlsm.
RMS Compliance Assessment Tool_TT 2.5_2.ods (282.6 KB)

1 Like
#13

Se usar macros e salvar em Excel, perde tudo. Com macros será arquivo.ods.

If you use macros and save in Excel, you lose everything. With macros it will be .ods file.

#14

Ah, now I get you. This could work. It is not very flexible, but it would do. At the moment the macro does not switch between Columns E and F, or is this a problem on my end? I do not have Excel, so would have to send it to a colleague once it works on LO.
Many thanks for helping me with this, couldn’t do it alone.

#15

Ok. I do not insist on macros if there is an alternative solution.

#16

You can shrink columns E and F and leave them visible. Then macros are not needed and this will simplify the situation. The ability to get support in 2 languages (instead of one) does not hurt anyone. :slightly_smiling_face:

1 Like
#17

You know, while this is not exactly beautiful, it works and is stable. Maybe that is actually quite a good idea. Thank you, I think I will do that. It will require more work from my side, but it is simple and does the job.
Hey, thanks so much for taking the time to think about that. That was helpful.
Best regards!

#18

Comments are a powerful tool in Calc (Excel). They may include texts with arbitrary formatting, images, etc.
If you want, I will form comments in two languages. To do this, you need to complete the Explanations sheet and its translation into Lao.

1 Like
#19

Oh, thanks a lot, but I will just do this manually, I would really feel bad to let you do my work. On top, I still do not have the Lao translation and it may take a couple of days before I do.
You have been of great help, the rest is for me to do. Thank you.

1 Like
#20

Solution alternative…

RMS Compliance Assessment Tool_TT 2.5_2_GS.ods (88.4 KB)

Double click on the cell, for instructions…

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.