vba hyperlink formatting

[DuaneR]

i have created some hyperlinks in a workbook in column "c" using vba using this code.

Sub SetHyperlinkName()
Dim lw As Long
Range("d2").Select
lw = Range("j" & Rows.Count).End(xlUp).Row
Range("d2").Value = "=hyperlink(c2,e2)"
Range("d2:c" & lw).FillDown
End Sub

and then this

Sub PasteValues()
Range("c2").Activate
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Copy
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.PasteSpecial xlPasteValues
End Sub

i now want to open these hyperlinks using this code

Sub HyperOpen()
Dim Cell As Range
Dim r As Integer 'Last used row in column F
On Error Resume Next
'Last used row in column F
r = Range("c65536").End(xlUp).Row
For Each Cell In Range("c2:c" & r) 'Range containing hyperlinks
Cell.Hyperlinks(1).Follow NewWindow:=True, AddHistory:=False
Next Cell
End Sub

the issue is the cells are not formatted and underlined blue as a hyperlink after creating the links. if i manually go to each cell in "c" excel auto formats them and the above code "hyperopen" executes correctly.
one other thing what code do i need to add to "hyperopen"
to run in the background when opening about 15 IE windows.

Thank you in advance
Duane Reynolds

 

[Leith Ross]

Hello Duane,

The links are not in blue because you assigned the formula to the cell's value. You need to use the Formula property instead...

Sub SetHyperlinkName()
   Dim lw As Long
             lw = Range("j" & Rows.Count).End(xlUp).Row
             Range("d2").Fomula = "=HYPERLINK(C2,E2)"
             Range("d2:c" & lw).FillDown
End Sub

Sincerely,
Leith Ross

 

[DuaneR]

Thanks for the fast response I replaced my code with yours and it is throwing a "run-time error 438 object doesn't support this property or method. at
Range("d2").Fomula = "=HYPERLINK(C2,E2)"

Thanks
Duane

 

[RSXchin]

does this work for you? It's just a bit shorter.

Sub SetHyperlinkName()
   Dim lw As Long
             lw = Range("j" & Rows.Count).End(xlUp).Row
             Range("d2:c" & lw).Fomula = "=HYPERLINK(C2,E2)"
End Sub

 

[DuaneR]

that gets the same 438 error
Duane

 

[RSXchin]

get rid of the "formula" part and it works just fine.

Sub SetHyperlinkName()
   Dim lw As Long
             lw = Range("j" & Rows.Count).End(xlUp).Row
             Range("d2:c" & lw)= "=HYPERLINK(C2,E2)"
End Sub

 

[DuaneR]

Here is my entire code
Sub AddLinks()
Columns("c:c").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("c1").Select
ActiveCell.FormulaR1C1 = "Hyperlink"
With ActiveCell.Characters(Start:=1, Length:=9).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("d1").Select
ActiveCell.FormulaR1C1 = "P&C Status"
With ActiveCell.Characters(Start:=1, Length:=10).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = 1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
CopyHyperlink
SetHyperlinkName
PasteValues
' HideColumns
HyperOpen
End Sub

Sub CopyHyperlink()
Dim lw As Long
lw = Range("j" & Rows.Count).End(xlUp).Row
Range("c2").Select
Range("c2").Value = "=concatenate(""http://web1.seattle.gov/DPD/permits...],""&t=2", NewWindow:=True&print=y&pt=0123456"")"
Range("c2:b" & lw).FillDown
End Sub

Sub SetHyperlinkName()
Dim lw As Long
lw = Range("j" & Rows.Count).End(xlUp).Row
Range("d2").Fomula = "=HYPERLINK(C2,E2)"
Range("d2:c" & lw).FillDown
End Sub
Sub HideColumns()
Columns("B:B").Select
Selection.EntireColumn.Hidden = True
Columns("D:D").Select
Selection.EntireColumn.Hidden = True
End Sub

Sub PasteValues()
Range("c2").Activate
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.Copy
Range(ActiveCell, ActiveCell.End(xlDown)).Select
Selection.PasteSpecial xlPasteValues
End Sub

Sub HyperOpen()
Dim Cell As Range
Dim r As Integer 'Last used row in column F
On Error Resume Next
'Last used row in column F
r = Range("c65536").End(xlUp).Row
For Each Cell In Range("c2:c" & r) 'Range containing hyperlinks
Cell.Hyperlinks(1).Follow NewWindow:=True, AddHistory:=False
Next Cell
End Sub

Thanks
Duane

 

[Leith Ross]

Hello Duane,

I think the code could be simplified much more. In VBA is it rare to have to select an object before performing an action on it. I personally would not use the formula hyperlink method as it can not be easily edited using VBA. Not all hyperlinks are the same. Outwardly they appear the same and perform the same function, but from a code standpoint they are not the same.

While hyperlinks added from websites to worksheets can be edited from VBA, you can not edit a hyperlink in cell the same way if is created by a Hyperlink formula in VBA. If you need to edit these links later using VBA then you will have problems.

Sincerely,
Leith Ross

 

[DuaneR]

ok let me elaborate some more. Column d which has the friendly name for the hyperlink has always been formatted and underlined blue , but Hyperlinks(1).Follow does not like the friendly name it prefers the
http://web1.seattle.gov/DPD/permits...6468&t=2", NewWindow:=True&print=y&pt=0123456
to follow. if i go to cell c2 with the http://............0123456 excel autoformats that cell from black text to the underlined hyperlink.
Thanks
Duane

 

[DuaneR]

Hello Duane,

I think the code could be simplified much more. In VBA is it rare to have to select an object before performing an action on it. I personally would not use the formula hyperlink method as it can not be easily edited using VBA. Not all hyperlinks are the same. Outwardly they appear the same and perform the same function, but from a code standpoint they are not the same.

While hyperlinks added from websites to worksheets can be edited from VBA, you can not edit a hyperlink in cell the same way if is created by a Hyperlink formula in VBA. If you need to edit these links later using VBA then you will have problems.

Sincerely,
Leith Ross

Thanks for the help Leith.
I am sure my code not pretty to a seasoned vba pro. I tend to build build small pieces then cobble them together hoping i can make office do what i need it to. creating the hyperlinks knowing the path to the report and inserting the report # has worked well for several years, this is my first attempt at opening a webpage from vb, clicking on the "friemdly links" has always worked. The IT dept will let me know if they change the location or path to the reports.
You guys are unsung heros .
Thanks Again
Duane