Place defined text in this cell if that cell is selected?

I’d like to create a calc sheet that helps me quickly write code to make computer midi tone music representations of songs or music. Here is a screen shot to reference:

------------------------------------------Simple Explanation of What I want this to do------------------------------

Using the Screen shot as a reference:

E1 has 253 entered in to it.

A10 is selected (pressed, clicked).

Script, macro, formula, whatever is needed to do this collects 665 from cell B10, collects 253 from cell E1 and types [:t665,253] in cell D5 since that’s the D cell selected and has less than 128 characters in it.


E1: 654

A7 is selected

[:t785,654] appears in cell (“line”) D5 next to the previous snippet created.

Now cell D5 has [:t665,253][:t785,654] in it.

Here is the file. It’s not as shown in the screen shot because I manually completed the song I was working on writing the code manually and cleared the D cells, I placed example code in the D cells of this file as if C5 then G5 then F5# were pressed/selected with random durations:

==================== Details =======================

For the TTS (Tex-To-Speech) software which is DecTalk I’m trying to get this going for instead of having to manually write each character for each code snippet for each note in a line. What I’m trying to get calc to do for me would save a lot of time and manually typing each code snippet’s character.

To explain and example what I want this to do if I selected or pressed cell A5 I want it to write [:t and the text 895 in cell B5 next to it then a comma, then the numeric value based on the value entered in cell E1 and a left square bracket to close the code snippet so the code it all creates in the D column cell is [:t895,#in_cell E1] . Say the value entered in E1 was 37 (for example) it would type [:t895,37] in a D cell.

Then if I entered 2345 in to cell E1 and selected cell A15 (labelled “B4”) it types another code snippet [:t495,2345] next to it in the same D cell so that D cell now has [:t895,37][:t495,2345] in it. I want this to keep placing the snippets in the same D “line” until all of the code snippets reach 128 characters which causes selecting more notes to be entered in to the next D cell below it.

I already have the D cells validity not allowing any more than 128 characters.

The info I need is how to make selecting a cell do this in the D cells.
I’ve written all this text to explain anything someone kind enough to help me learn how to do this’ curiosity on what it’s for, why, etc. I don’t expect anyone that reads this to also know DecTalk’s coding syntax. The way DecTalks code syntax is and the way the game I’m creating this for is is why I have the sheet designed the way it is. The design cannot change, I’m not asking for design suggestions I’m asking what I’ve plainly asked: How do I make selecting a cell type text in to another cell?

Don’t change the design of the file!!! Please.
Buttons instead of text in column A? okay! Can do. But please don’t try to add more notes, change the layout, or anything else. It’s designed the way it is for a reason that took me several full days of trial and error to design it so it works for the purpose I’m creating this for (DecTalk, a video game - both of which have limitations and other things I don’t expect anyone to know that need to be considered). Changing the layout, design, or any of it’s cosmetics will make your much appreciated effort in helping me with this unable to be used. Don’t waste either of our time ya know?

All I need to know is how to make a button or selecting a cell type the code.

----------------------------------------------Code Snippet Explained-------------------------------------------

[ opens a code snippet for DecTalk. :t tells DecTalk to use computer midi tones instead of phonemes/phonetic speech for the sounds. The first number, 895 in the example which is the A5 note on a piano, is telling DecTalk what note to produce. The number after the comma tells DecTalk how long in milliseconds to play the sound. Then the ] closes the code snippet.

-----------------------------------------------Columns, Etc, Explanation---------------------------------------

Column A is obviously the notes of a piano if you don’t recognize them.

  • DecTalk’s midi tone range capability is numerically 1 through 5000. Below piano note F2 the tones start losing value so I didn’t collect them below that and I have yet to create cells for notes above piano note C6 because that’s all I need for the song I’m in the process of creating at the moment - Mario Brothers Theme song if you’re curious. The first line in D4 is the first measure of the song, the second line in D5 is the second measure of the song. I manually typed every character for those lines.

Column B is DecTalk’s numeric midi code value for the note in column A next to it.

Cell E1 is a numeric value text field that requires a numeric value (validity already set up and done) that I want entered after the comma that’s placed after [:t and the value it enters after that based on what the text in column B is that’s been typed when a cell in column A is selected or pressed.

Cell H1 (Rest field) is another field that I plan to set up to enter only allow a comma, period, or underscore (which is DecTalk syntax for pre-determined time periods of rest) or a numeric value. That’s something I can do after I get the advice I’m looking for as to how to get it doing the above.

The rows that are column D5 through D45 are the text fields for the code, where I want the text produced by what’s selected and entered to be placed. This is for a video game that uses DecTalk TTS. The game chat is where the code for TTS is entered and it only allows 128 characters per message.

Column K is counting the characters in each D column cell/row/line. I have the D column row cells setup to produce an error prompt if there are more than 128 characters.

---------------------------------------------------Additional info / Details-------------------------------------------

I don’t have an issue with having to select a new D column row to start a new line of code when the character limit is reached in the cell above it but if that can also be automated that would be a nice feature to have as well.

Each time I select or press a cell in Column A I want it to enter the [:t#,#####] code snippet next to the previous one in the same D column row until the 128 character limit is reached.

I have more I want this to do that causes me to learn the really cool stuff Calc can to that I can use for other purposes in the future but I want to get this part of it figured out first.

Is this possible to do with calc? I wanted to try it with calc before venturing in to doing it with VB or Flash, JS, or C#. I believe IF functions might do it? I’m going to play around with IF statements and whatever else I can think of that might do it in the mean time until this gets a reply with suggestions.

Thanks in advance! I have confidence in the members of this community that if it’s possible to do someone knows how to do it.

Yes, it can be done with Calc too. But first, let’s talk about possible difficulties and think about how to change the task in order to get around these difficulties. The first problem is the number of notes. As far as I remember, the piano has 88 keys. If you list them all in column A, then they may simply not fit on the screen - you will need to scroll the sheet up or down to press the next note. It is not very difficult to add text to a cell, there are several simple macros that do this. But what if you need to insert an inadvertently skipped note in the middle of an already written line?

In general, your task comes down to editing the text in a cell using a set of predefined templates. You cannot solve this problem with the IF() functions alone, you need a macro. I think that this form of editing the contents of a cell in column D would be convenient.


Thank you very much for the reply! I’ve spent many hours on the design for this project. What you suggested is great but I’ve already found out it isn’t going to work. Been there, tried that. There’s a plethora of reasons it can’t be done with a graphic display which is why I chose to invent the wheel for this with text only.

The durations can’t be buttons they need to be a typed value or code character for the TTS program.

All courtesy and respect included I’m not asking for suggestions on the design I’m asking how to make a selected cell or button collect and place text in a different cell. That’s all I’m asking to learn and all I need as a result for this topic. Please answer using the design and layout I’ve shared. Thank you!

You will need a macro anyway. It will contain a global variable that stores the address of the “edited cell”. The procedure for processing a click on a cell itself is very simple: determine the address of the cell you clicked on, check if this cell is in column A, if so, then take the values ​​from the corresponding cell in column B and in cell E1, form the next [: t #, #####], remember which cell you need to edit (address in a global variable) and add the resulting value to it. If the length of the text in this cell has already reached 128 characters, then change the address in the global variable to the next cell in column D.

Right. I’m aware of the process that needs to be done. What process needs to be done is obvious. How to make calc do that process is what I’m asking for. Please answer the question with step by step instructions as that is what I’m looking for and need.

You have to wait - I’m not sure if this weekend I will find time to describe the whole process step by step. So I don’t waste time creating a test spreadsheet, can you edit your question and add a sample of the existing file?

Done. File attachment under the screenshot. I managed to get a button to do what I want futzing around trying to figure it out but it placed the text in the wrong cell. I didn’t notice it did it until I re-opened the file to put example text in the D cells for you to reference and I’ve deleted all the macros I made as tests messing with it so I don’t know which one did it… laughing at myself
I’ll continue to play around and try figuring it out. I really appreciate the help! Thank you!!!

Well, try this - SongWriter_macro.ods

Just dbl-click cells in column A and see result in D

Please make sure that macros are allowed.

Keep in mind that if in column B opposite the clicked cell there is no value or Length/Time not filled, then the program will not do anything.

If you are really interested in how this is done, then let me know, I will try to tell you about it step by step. If you just need a tool to quickly record a song, then just use it (but keep in mind - this could be done much more conveniently without changing the design)

The first number, 895 in the example which is the A5 note on a piano, is telling DecTalk what note to produce

I am not disputing, I am simply asking - is there an annoying typo here? The fact is that all reference books claim that A5 is twice A4, 440 * 2 = 880, that is, a little deeper than 895. Perhaps this is only a feature of DecTalk. But I had to ask.

That’s great! Thank you! Yes, I am interested in learning. If you would share a step by step I’d appreciate it. I would like to learn how to do this so I can use and apply it to other things. I’m was fluent with many coding languages but haven’t kept up with it. I’ll look at the macro code and see what I can grasp from it in the mean time if you will share a step by step on how to. I did get a button to produce text in a field somehow one of the times I tried doing it but it was putting the text in a cell off the screen so I don’t know what version of the macro I made was the one that worked that needed adjusting.

Is there a way to get it to do it with a single click of the reference cells or does it have to be double click? If that’s the way it has to be that’s the way it has to be. A lot of songs have the same note struck twice…having to click twice to make one note can be dealt with (same as scrolling). It would be more convenient if it can be single-click.

No that’s not a typo. To get the numeric codes for DecTalk I ran DecTalk and started at where the tones frequency should be numerically then used a virtual piano online to compare the tone to the tone the v-piano made. There are quite a few that are off according to what note tone frequencies are in real life. I’m not sure if that’s because DecTalk is off or the virtual piano I used wasn’t tuned to perfect pitch. In my opinion it makes more sense that the piano I used was off. When I noticed the numeric values weren’t following real life or a pattern I decided against starting all over collecting the values. I guess I can change the frequency values to what they should be. That’s something I’ll test and perfect in the near future. I was already on that issue. Those values can be changed any time. I left the values displayed so I can reference them to manually write the code. I might code them in to the macros and remove that column at some point. And I might change the design later on.

There’s good reason I made it just text as I mentioned. Because of the game and DecTalk it was easiest to just do it with text. The documentation for dectalk is 80+ pages someone would have to also read all of and study to know and it doesn’t cover much about :t (tones) it mostly covers other subjects and how the program pronounces words with phonetics/phonemes, they would also have to play the game for several hundred hours to learn for them selves what both DecTalk and the Game’s limitations are and why, at least for now, that’s the design I found to work best.
If you or anyone is interested here is the DecTalk manual and here is the game I’m doing this for.

I’m not sure I would bother being determined to do this calc thing for this if I didn’t think it would be useful to learn and know for other purposes.

A small part of the instructions are in the text of the macro in the form of comments (do not pay attention to the syntax and typos - English is not my native language). It only remains to explain how to connect a macro to a sheet event. You will understand this for yourself if you right-click on the sheet tab and select Sheet Events from the menu.

Yes, you can do it one click at a time, but then another problem arises. The fact is that the macro on the “Change selection” event (as opposed to “Double click”) is triggered several times (three or four, depending on whether you click on a new cell or on a selected one). And instead of one note, we will get several identical tokens in a row in the record. It takes a few tweaks to prevent this, which will complicate the macro code.

By the way, what operating system are you running on? I have conceived another small improvement to the macro, but this can only be done under Windows.