Excel: Anchor or non-printing marker in text string? Odd Request!

[VBAjedi]

Hi all!

Is there a way to insert an anchor or non-printing marker character into a text string in a cell?

I am trying find a way (which will probably require VBA) to make another value be displayed directly over a given point in a string, but don't know how to indicate to Excel where that point is.

For example, I'd like to display something like:

4
This is a test string

and make it so that if you add/delete words from the string, the 4 stays positioned over the "s".

Wierd request, I know. And yes, this needs to be done in Excel, not Word.

Thanks!

VBAjedi

 

[Zathras]

Well, as you can see from your post. The first thing you would have to do is set the font to a fixed-format (for example, Courier). Then you would have to be able to describe the logic for which "s" to mark. From your example, it is not the first "s" nor the last "s" --- so why is the mark where it is???

If you use VBA, you might be able to get away from the fixed font requirement by printing "white on white" up to the point where you want the "4" to appear.

 

[VBAjedi]

Zathras,

LOL - I just assumed that Tek-Tips used a fixed font. The 4 was over the first "s" when I wrote the post.

The key issue as I see it is how to insert an invisible marker in the text string. My application will have multiple marker-points per line, and might have fifty or more in the whole document. I don't think keying on the visible letters will work, I need a single invisible character that I can search for as a marker.

Any ideas?

VBAjedi

 

[Zathras]

It would help if you could provide a specific example of what you are trying to accomplish. Do you have blank rows between all the lines of your text? If not, where is the "4" to be displayed? Just during printing? Sorry to be so dense, but I just don't get it.

Grasping at straws:
Perhaps a visible marker (e.g. "|&quot could be used and then removed just prior to printing or whatever. Or, maybe you can use Comments in some way.

In any event, it sounds like you will need to use VBA.

 

[VBAjedi]

Zathras,

It's a music thing. I'm putting song lyrics on every other line, with chord letters on the lines above. There might be five or ten chords to a line, and each needs to be precisely locked above a specific point in the text. My current method is to just use the space bar, but that has numerous issues and drawbacks (example, font changes destroy your spacing). An example of what I'm after:

[tt]
G C D D7 C G
Some songs lyrics, placed just right,
C G D7 G
Adding chords, took all night!
[/tt]

Presently, if I change the font, or add a word, or anything like that, the chords spacing gets whacked. I want to find a way to lock them over their proper place (preferrably not just for printing, either)! I've been thinking about it for a while now.

VBAjedi

 

[xlbo]

VBAJedi - this may be too simplistic but given that there are 256 columns you can utilise in excel, could you not just use 1 cell per letter and make the cell widths small ??
Taht way you would not need to do what you are detailing but rather, just enter the chords into the appropriate cells
If you then needed to enter words etc, all you would need to do is insert 2 lines of cells (one for chord and one for letters) and Bob's your mother's brother
Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Zathras]

This is all in one column? Column A? Words on even numbered rows, chords on odd? No extra blank lines?

I look at this sort of issue mainly as a user-interface thing. It would be (relatively) easy to shadow the text in a VBA array and then keep track there of what Chord marks go where. The trick is setting up a way for you to do the data marking. I'm intrigued by the challenge, but would like to have complete specs before starting. E.g., how do I know which rows have words and which rows have chords.

Also, which version of Excel?

I have a couple of ideas. I see a form (If Excel 2K, the form could be modeless) with a text box and a grid of chord spellings; Click on a chord and click in the text box where the chord should go. (With a text box, you can get the x/y coordinates of the click.) Then create a line with the chords in black text and the rest in white text.

It comes out looking something like this:
'SGme soCgs lDriD7 plCced jus Gight,
'Some songs lyrics, placed just right,
which you can copy into a cell, and in edit mode, select the non-chord characters and change the text color to white to see what I mean. Select any (non-fixed) font you want.

Of course, if you don't mind using Courier, the job is even easier. (With variable-pitch fonts, you end up replacing lower-case letters with upper-case chord letters and so have to make minor adjustments as you go.)

Geoff: Excellent idea, but that would be the same as using a fixed font. Hard to type in, too. But if we go that way, I would still use a form / textbox as the user interface, so that wouldn't be that much of a problem.

 

[VBAjedi]

Zathras:
It is indeed an unique challenge. Some additional input:
[ul][li]I don't care what columns data is in. I'll probably hide col headers and gridlines anyway[/li][li]Yes, there will be blank rows. No, I can't predict where. [/li][li]No way to predict which rows will contain lyrics and which will contain chords. Needs to be user-indicated. I thought of using column A as a "c" or "l" indicator column that the user could change as desired.[/li][li]Version is Excel 97[/li][li]The lyrics will be in one font, the chords will be in another. Possibly different font sizes too. (thus reinforcing the need for some kind of marker in the lyrics text).[/li][li]Your white/black font idea is interesting. But I need to be able to programmatically identify what letters refer to chords because of another function I want: transposing. I want to be able to tell Excel to transpose the chords up a key, and it will rename all the chords appropriately (obviously linked to some behind-the-scenes transposing tables). Having white text mixed in there might prove to be a problem.[/li][/ul]

xlbo:
Regarding the columns idea: I have been there and done that, but you have given me another idea to try. My former attempt had narrow columns, with every other row being merged cells so lyrics could be entered in a single cell.
I hadn't thought of having the lyrics entered one letter to a cell.

The only way I could see that working, though, is if there is an event generated when a letter is typed in a cell (BEFORE enter or tab is pushed). I could use that event to trigger an auto-tab routine, which would allow the user to feel like they were typing in a single cell (even though it would be auto-tabbing to the next cell for each letter).

Thanks for the brainstorming so far!

VBAjedi

 

[xlbo]

Weeeell - from the extra info I don't think my idea would be the way to go (didn't realise you were developing an app as opposed to something for your own amusement)
But, here is one way round (BTW there is no BEFORE change event that I know of)
If the user types into 1 cell, the change event could be handled thus:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Application.EnableEvents = False
numcells = Len(Target.Text)
For i = 2 To numcells
Target.Offset(0, i - 1).Value = Mid(Target.Text, i, 1)
Next i
Target.Value = Left(Target.Text, 1)
Application.EnableEvents = True
Target.Offset(0, numcells).Select
End Sub

as I said, I don't think this is the way to go. reckon Zathras' idea of userform entry is sound tho I'm not sure how you'd position the chord in the 1st instance, nevermind reposition it...
Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[Zathras]

Positioning the first chord is easy (pre-select the chord, trap the mouse event, convert the x-value to the correct number of spaces, and voila). Keeping it there when positioning others is the problem.

VBAJedi, I think you're asking for more than Excel can deliver. Surely there are commercial packages that already do this sort of thing.

If it were for a specific font (or fonts), it might be possible to come close. Even then, the action would be difficult: Select a line, click a button to open a form, change the text on the line, reposition the chords (with the mouse), close the form. And, moving bits of text from one line to another would be another problem. Excel just doesn't provide enough ways to do stuff. Maybe if you use a full-powered programming language (Delphi? VB? C++?), but not in Excel. Sorry.

 

[VBAjedi]

Thanks, Zathras and xlbo! I agree that I'm asking a lot. But I've been thinking about this one for a long time, and I'm not quitting yet. I'll just have to think of another approach. There's no rush on this thing.

Thanks again for your thoughts. They have been valuable (hey, at least I know two approaches that WON'T work!)

LOL
VBAjedi