Underline lower-case letters in Text Field

[RudySchneider]

So, I've succeeded in creating a fairly all-inclusive smart shape for an electrical connector that meets my basic needs.  By right-clicking it, I can specify all kinds of basic connector shape details.  By double-clicking on it, I can specify the connector designation, and the (comma-separated) individual pin numbers (or letters) I want to identify.  I'm already using SUBSTITUTE to replace the comma in the string with a line return in order to get the proper character spacing.  What I now want is to automatically underline any lower-case alpha character in the pin number string.  Suggestions?

Connector shape examples and .vsd included.

[wapperdude]

As far as I can tell, there is no function that looks at a letter in a string and says, Ah!, a lower case letter!"

I suspect that you have to take your string, search it character by character, compare it to an alphabetic list of lower case letters.  Then, if your character belongs to the set, underline it.

You might want to take a look at this:  http://en.wikibooks.org/wiki/Visual_Basic/Built_In_String_Functions.  Might give you some ideas. 
Also, macro recorder can give you the coding to underline a specific, selected character.

Wapperdude

[RudySchneider]

Hmmmm...  Definitely NOT the answer for which I was hoping, Wapperdude!

However, I'm undaunted.  I've already managed to do quite a few more things than I anticipated I'd be able to with this and some of the other shapes I've created.

Where there's a will...

[Yacine]

Hi Rudy,
What's the idea behind your question? Do you want to prevent people typing in lower case letters? In this case you can transform automatically the string with the UPPER function.
If the lower case characters really need to be underlined, the are 2 main options:
1) parse the string with a macro (Wapperdudes suggestion)
2) Set up a complex shapesheet in which you would distribute the characters of your string over prepared cells, transform them, then concatenate again. The main shape would not display the actual text anymore, but the so newly created string.

Another idea would consist of using a font, where the lower cases are underlined. If can't find one over the internet, you still can edit a font with an open source editor.

[Yacine]

Putting the intelligence in sub-shapes helps resolving the problem.
The main advantage being, that it is easier to duplicate a sub-shape than a line in the shapesheet.
Each sub-shape has a number defining its position in the string.
This number is created when the original shape is duplicated. The drop event increases the prop.nr field by one.
This same number also defines the position of the sub-shape in its parent. (See the PinY formula)
Checking for lower case is difficult, as there seams to be no ASC function in the shapesheet. So the char had to be compared to a list of chars "a" to "z".
And as lookup did not work case sensitive (contrary to the information on MS site) an additional STRSAME had to be used.
Regards,
Yacine

[wapperdude]

Using the link I provided, plus the macro recorder, it was surprisingly straight forward to get an character underlining macro.  Code below.  Note, the code assumes that you've selected a shape and has no error checking if no shape selected.

Code Select Expand

Sub UndrLine()
    Dim vsoChars As Visio.Characters
    Dim strLen As Integer
    Dim i As Integer
   
    Set vsoChars = ActivePage.Shapes.ItemFromID(1).Characters
    'Note, either of the following will work to obtain the length of the string
    'strLen = Len(vsoChars) - 1
    strLen = vsoChars.CharCount - 1
   
    For i = 0 To strLen
        vsoChars.Begin = i
        vsoChars.End = i + 1
        If vsoChars.Text Like "[a-z]" Then
            vsoChars.CharProps(visCharacterStyle) = 4#
        End If
    Next
End Sub

wapperdude

[Yacine]

A very nice post, Wayne.
Haven't seen the Like statement before and formating the characters object so easily as you did is also new to me.
Cheers,
Yacine

[RudySchneider]

Yacine and Wapperdude ---
Yes, I considered both a macro and a specialized font, like checking if any text was between CHR(97) and CHR (122), along the lines of the "Like" statement Wapperdude used, but was hoping to avoid going that route, if possible.  But hey, use what works, right?

Yacine, in answer to your original question --- some connectors use numbers to identify pins, others letters, and some a mix of both.  On some really dense connectors, they may even use a combination of upper-case, double-upper-case, and lower-case letters.  When putting this information on a drawing, particularly if it ends up being reduced in size for convenience, the difference between some upper-case and lower-case letters (like 'C' or 'O') can tend to be subtle.  So, to make the distinction more obvious, they would underline any lower-case letter.  And since we're talking clarity and legibility here, this also allowed them to use only upper-case characters, so that all characters are the same size.  That is, something like 'A b N o l' becomes 'A B N O L'.

Thanks for both of your inputs.  I'll check 'em out.

[wapperdude]

Ooops.  The code I posted was wrong copy paste.  It has the shape ID hard coded, selection is not option.  Corrected code here:

I show two methods of getting the shape text and two methods of determining text string length.

Code Select Expand

Sub UndrLine()
    Dim vsoShp As Visio.Shape
    Dim vsoChars As Visio.Characters
    Dim strLen As Integer
    Dim i As Integer
   
    Set vsoChars = ActiveWindow.Selection(1).Characters
    Set vsoShp = ActiveWindow.Selection(1)
   
    'Note, either of the following msgbox entries gets the entire shape text:
    'MsgBox vsoShp.Text & Chr(10) & Chr(10) & vsoChars.Text
   
    'Note, either of the following will work to obtain the length of the string
    'strLen = Len(vsoChars) - 1
    strLen = vsoChars.CharCount - 1
   
    For i = 0 To strLen
        vsoChars.Begin = i
        vsoChars.End = i + 1
        If vsoChars.Text Like "[a-z]" Then
            vsoChars.CharProps(visCharacterStyle) = 4#
        End If
    Next
End Sub

wapperdude

[RudySchneider]

Wapperdude ---
With my smart shape, I double-click on it to enter the connector designation and pin numbers.  However, it's not clear to me if or how I can use your subroutine from within the shapesheet. 

[Nikolay]

Well, if you are okay with some weird fun-on-the-bun tricks here is one to allow shapesheet-only solution (no macroces or code at all):

Part 1.
In UNICODE, there is a special character (818), "combined low line". When added to a string, it adds underscore to the symbol it follows.

Part 2.
You can draw the Prop.Text through multiple "SUBSTITUTE" sequentially one by one for each lowercase letter.

Check out the attached drawing

Note - with "combining symbols" it should not necessarily be an underscore - there is a good choice of those
You can try those out by holding ALT+typing symbol's code on the numeric keypad.

Also, I do not expect this "technique" to be outstandingly efficient; probably at least the SUBSTITUTE() calls should be combined to avoid long chain of dependent cells.

[RudySchneider]

Nikolay ---
Efficient?  Who needs efficiency?  It works, and the shapesheet is entirely independent and self-contained!  Excellent!  Thanks so much!

--- Rudy

[wapperdude]

Hi Rudy,
Looks like Nikolay found a creative solution for you!

For sake of completeness, you can add the RUNMACRO fcn to the shapesheet.  Typically, it's put in the Event Double Click cell, but, you're already using that.  So, you could create an Action item, which would show up in the right click context menu of your shape.

For example:  Actions.Row_1:  enter formula into Action cell: =RUNMACRO("ThisDocument.UndrLine").  Then in the Menu cell: ="Underline Lower Case letters".

To run the macro, just right click your shape, click on the "Underline Lower Case letters", and macro executes.  Assumes that macro is named UnderLine, and is located under ThisDocument.

Wanted to share the above in case the need arises to run a macro from shapesheet.

Wapperdude

[RudySchneider]

Thanks, Wapperdude ---
I think I'll go ahead and give your suggestion a try as well, if for no other reason than it's a learning experience.  And who knows, I may need to add it to my repertoire for the future.