Creating a hyperlink in part of a cell

[John Marshall, MVP]

I have a routine that populates a three column table (Ref #, Name, Page #)
that works fine. The cell with the Name is hyperlinked. I am now trying to
add a tab leader of dots to the end of the Name cell. I can add the tab and
set the tab leader to dots, but the tab is included in the hyperlink. Any
ideas how to correct this?

Username = UserName & vbTab

ActiveDocument.Hyperlinks.Add Anchor:=NameTable.Cells(RowNum,2).Range,
Address:="", TextToDisplay:=UserName, SubAddress:=BookMarkName, _
ScreenTip:="Click to view user"

NameTable.Cell(RowNum,2).Range.ParagraphFormat.Tabstops.Add
Position:=InchesToPoints(5.4), Alignment:=wdAlignTabRight,
Leader:=wdTabLeaderDots

Any alternate suggestions would also be appreciated.

John... Visio MVP

 

[Shauna Kelly]

Hi John

Something like this (add error checking to taste, and fix line breaks as
needed):

Sub ForJohnM()

Dim oDoc As Word.Document
Dim NameTable As Word.Table
Dim RowNum As Long
Dim UserName As String
Dim BookMarkName As String
Dim oCell As Word.Cell
Dim rngHL As Word.Range

'Get a reference to the ActiveDocument.
'Avoid using 'ActiveDocument' more than once because
'Word is fickle
Set oDoc = ActiveDocument

Set NameTable = oDoc.Tables(1)
RowNum = 1
UserName = "John"
BookMarkName = "Whatever"

'Get the cell to work with
Set oCell = NameTable.Rows(RowNum).Cells(2)

'Add a tab stop with leader dots to the cell
oCell.Range.ParagraphFormat.TabStops.Add
Position:=InchesToPoints(5.4), Alignment:=wdAlignTabRight,
Leader:=wdTabLeaderDots

'Get a range that does *not* include the end-of-cell marker
Set rngHL = oCell.Range
rngHL.MoveEnd Unit:=wdCharacter, Count:=-1

'Add a hyperlink to our range
oDoc.Hyperlinks.Add Anchor:=rngHL, Address:="",
TextToDisplay:=UserName, SubAddress:=BookMarkName, ScreenTip:="Click to
view user"

'Add a tab at the very end of the cell, ie after the hyperlinked
range
oCell.Range.InsertAfter vbTab


End Sub


By the way, if this were mine and if there were lots of these cells, I'd
create a separate paragraph style that includes the tab stop. You'll
then be able to apply the style to the relevant cells. It will make it a
lot faster to run the code and a lot easier to maintain.


Hope this helps.

Shauna Kelly. Microsoft MVP.
http://www.shaunakelly.com/word

 

[alborg]

Hi John, Shauna:

Why even use "vbTab"? For example, you can instantly add 18 rows into a 3
column table (Ref #, Name, Page #) with this code:

Dim rownum As Integer, I As Integer, ii As Integer, Username as String,
pageno as Integer

Username = "John Doe"
pageno = 12 ' or whatever you wish to assign to it

response = MsgBox("Add new rows?", vbQuestion + vbYesNo)
If response = vbYes Then
ii = 0
If IsNull(ActiveDocument.FormFields("admdate1").Result) Then
MsgBox "Please fill in the admission date first!", vbInformation
Else
Do Until ii > 18
ActiveDocument.Unprotect
ActiveDocument.Tables(1).Rows.Add
rownum = ActiveDocument.Tables(1).Rows.Count
For I = 1 To ActiveDocument.Tables(1).Columns.Count
ActiveDocument.FormFields.Add
Range:=ActiveDocument.Tables(1).Cell(rownum, I).Range,
Type:=wdFieldFormTextInput
Next I

ActiveDocument.Tables(1).Cell(ActiveDocument.Tables(1).Rows.Count,
ActiveDocument.Tables(1).Columns.Count - 1).Range.FormFields(1).Result = ii

ActiveDocument.Tables(1).Cell(ActiveDocument.Tables(1).Rows.Count,
ActiveDocument.Tables(1).Columns.Count).Range.Hyperlinks.Add
Anchor:=NameTable.Cells(rownum, 2).Range, Address:="",
TextToDisplay:=UserName, SubAddress:=BookMarkName, ScreenTip:="Click to view
user"

ActiveDocument.Tables(1).Cell(ActiveDocument.Tables(1).Rows.Count,
ActiveDocument.Tables(1).Columns.Count -
1).Range.ParagraphFormat.TabStops.Add Position:=InchesToPoints(5.4),
Alignment:=wdAlignTabRight, Leader:=wdTabLeaderDots

ActiveDocument.Tables(1).Cell(ActiveDocument.Tables(1).Rows.Count,
ActiveDocument.Tables(1).Columns.Count + 1).Range.FormFields(1).Result =
pageno
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, noreset:=True
ii = ii + 1
Loop
End If
End If

*** Neat problem! *** Sorry I haven't tested the above for bugs, but it's
pretty straightforward.

Cheers,
Al

 

[John Marshall, MVP]

The reason for the vbtab is so the name will appear to have a dot leader
that goes to the page number in the next cell. The tab marker is at the end
of the cell and the borders are turned off. So the overall effect is a white
page with several rows. Each row starts with a short reference number and
then a varying length name. Since there can be a lot of white space between
the name and the page number a dot leader appears to go between the name and
the page number. In fact, the tab leader goes to the end of the name cell
rather than the start of the page number.

An alternate solution whould be to combine the name and the page number in
one cell and seperate them by a tab with a dot leader. Since there are no
borders, it would not be obvious that they ar enot in their own columns.

This could be done without the table, but the table is used to allow the
user to sort the table on the ref number or the name. It is also an easy way
to find the information when it is time to update the table. Look for the
bookmarked name of the table, select the table, select the second row,
extend to the end and then delete the rows. You are now left with an empty
table, with only the column headers intact all set to repopulate the table.

John... Visio MVP

Need stencils or ideas? http://visio.mvps.org/3rdparty.htm
Need VBA examples? http://visio.mvps.org/VBA.htm
Visio Wishlist http://visio.mvps.org/wish_list.htm

 

[John Marshall, MVP]

It worked perfectly. The key thing I needed was the oCell and the
InsertAfter. I'm still trying to get more proficient with using Ranges.

Looks like I owe you a couple. Are you going to be traveling in April? ;-)

John... Visio MVP