XL97: Return the "x" coordinate of a given character in a cell?

[VBAjedi]

I would like to set the "Left" property of a textbox to match the "x" coordinate of a given character in a cell. So if the character is the $ symbol, and my cell contains:

Test Text $ More Text

the left edge of my textbox would be between "Test Text" and "More Text". (setting the "Top" property of the text box will be easy)

Any ideas?

VBAjedi

 

[Zathras]

I think you are a little bit out of luck unless you are using a fixed font like Courier. Although you could build a table that holds the width of each character so you could get the exact offset you need. Otherwise, assuming an average character width, you might be able to get away with something like this:
[blue]

Option Explicit

Sub Macro1()
  PlaceNewTextBox ActiveCell
End Sub

Sub PlaceNewTextBox(ACell As Range, Optional ACharacter As String = "$")
Const nWidth = 72
Const nHeight = 18
Const nVOffset = 6
Const nAvgCharWidth = 4.5
Dim nHOffset As Integer
Dim nTop As Integer
Dim nLeft As Integer
  nHOffset = nAvgCharWidth * InStr(ACell.Text, ACharacter)
  nTop = ACell.Top + nVOffset
  nLeft = ACell.Left + nHOffset
  ActiveSheet.OLEObjects.Add ClassType:="Forms.TextBox.1", _
      Link:=False, DisplayAsIcon:=False, Left:=nLeft, _
      Top:=nTop, Width:=nWidth, Height:=nHeight
End Sub

[/color]

 

[VBAjedi]

Zathras,

Last night I came up with a solution that's a bit odd, but works like a charm. I write a string that contains the target cell's first character through the character in question to a cell on a hidden worksheet. Then I apply the font name, size, and style of the target cell to this hidden cell. Lastly, I use Autofit to adjust the hidden cell to the correct width and return its Width property.

Funny solution, but it actually seems to run pretty fast. Besides, I don't have to repeat this little function 50,000 times in two seconds like I'm normally trying to do! LOL

Incidentally, this looks to be the solution to my post thread68-496531 a few months back. At that time you and xlbo helped me rule out my best approach at that time, but we weren't able to come up with a working solution. This should do it! I just pick a marker character that I won't need as part of my actual text, place that character at the appropriate points in my text, and then I can float little text boxes over the correct points in the cell.


VBAjedi