Just a note, the error doesn't occur when I create the the hyper links, it
occurs when I click the cell with the hyperlink, when I try to run the:
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) subroutine.
And it occurs even before the subroutine starts.

"JLatham" wrote:

and I'll be that fixes it.
And I thought as you that it's linking to the c.address - but convinced
myself that the it was working properly. But I the fix is back at the checks
for ws.Name, but after looking at the code even more, I don't know. But in
the ElseIf statement, the ws does get selected, but as you say, the shortcut
points to the same cell on ws. So I think after he gets the links formatted
correctly, then there's going to need to be some work done on getting them
placed in the right worksheet, and pointing to the right one.
But one step at a time...??

"Dave Peterson" wrote:

How about trying:

SubAddress = "'" & ws.name & "'!" & c.address

Sometimes, you need to surround the worksheet name with apostrophes -- when it
has spaces, when it's a number, when it looks like an address, ....


One more question though...

Am I reading your code correctly -- did you really want to add a hyperlink that
links to the cell with the hyperlink? That seems kind of weird to me.


Ayo wrote:

I tried that but I'm still getting the "Reference is not valid" pop up.

"JLatham" wrote:

The SubAddress needs to have a ! symbol between the sheet name and the cell
address, so
SubAddress:=ws.Name & "!" & c.Address
should fix it all up?

"Ayo" wrote:

I get the above message when I click on an hyperlink cell with the following
codes:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim strRow As Long, endRow As Long, c As Range
Dim marketName As String
Application.ScreenUpdating = False
Call clearSheet
With ActiveCell
''''''' ****** GET THE START AND END ROWS FROM THE LOOKUP TABLE
SHEET ****** '''''''
''''''' ******
&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&&& &&&& ****** '''''''
Worksheets("Lookup Tables").Visible = True
For Each c In Worksheets("Lookup Tables").Range("B19:B77")
If c = Range("C" & .Row) Then
strRow = c.Offset(0, 2)
endRow = c.Offset(0, 3)
marketName = c
Exit For
End If
Next c
Worksheets("Lookup Tables").Visible = False
Worksheets("Bucket SiteList").Range("B3") = Date
marketName = Me.Range("C" & .Row)
'''''''''' ********** DDS WORKING BUCKET DATA SECTION **********
''''''''''
'''''''''' ********** @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ **********
''''''''''
Worksheets("Bucket SiteList").Range("B2") = Me.Range("G2")
If Left(.Address, 2) = "$E" Then
Worksheets("Bucket SiteList").Range("C3") = "OPEN WORK ORDERS"
Worksheets("Bucket SiteList").Range("E4") = "LAST ENTRY DATE"
Call get_workOderSTATUS(marketName, "Open", strRow, endRow)

ElseIf Left(.Address, 2) = "$F" Then
Worksheets("Bucket SiteList").Range("C3") = "CLOSED WORK ORDERS"
Worksheets("Bucket SiteList").Range("E4") = "LAST ENTRY DATE"
Call get_workOderSTATUS(marketName, "Closed", strRow, endRow)

ElseIf Left(.Address, 2) = "$G" Then
Worksheets("Bucket SiteList").Range("C3") = Me.Range("G3") & ",
" & Me.Range("F3")
Worksheets("Bucket SiteList").Range("E4") = "Note"
Call taskCOMPLETED(marketName, strRow, endRow, "G", "J", 4)

ElseIf Left(.Address, 2) = "$H" Then
Worksheets("Bucket SiteList").Range("C3") = Me.Range("G3") & ",
" & Me.Range("H3")
Worksheets("Bucket SiteList").Range("E4") = "Note"
Call taskPENDING_DDS(marketName, strRow, endRow, "G", "J", 4)

ElseIf Left(.Address, 2) = "$I" Then
Worksheets("Bucket SiteList").Range("C3") = Me.Range("G3") & ",
" & Me.Range("I3")
Worksheets("Bucket SiteList").Range("E4") = "Note"
Call taskPENDING_DDS(marketName, strRow, endRow, "G", "J", 4, 9)

ElseIf Left(.Address, 2) = "$J" Then
Worksheets("Bucket SiteList").Range("C3") = Me.Range("G3") & ",
" & Me.Range("J3")
Worksheets("Bucket SiteList").Range("E4") = "Note"
Call taskPENDING_DDS(marketName, strRow, endRow, "G", "K", 4, 10)

ElseIf Left(.Address, 2) = "$K" Then
Worksheets("Bucket SiteList").Range("C3") = Me.Range("G3") & ",
" & Me.Range("K3")
Worksheets("Bucket SiteList").Range("E4") = "Note"
Call taskPENDING_DDS(marketName, strRow, endRow, "G", "L", 4, 11)
End If
End With
Worksheets("Bucket SiteList").Select
ActiveSheet.Range("C3:E3").Select
With Selection
.WrapText = False
End With
ActiveSheet.Range("B5").Select
Application.ScreenUpdating = True
End Sub

This is the code that I used to hyperlink the cells:
Sub createHYPERLINKS()
Dim c As Range, ws As Worksheet

For Each ws In ThisWorkbook.Worksheets
If ws.Name = "DDS Bucket" Then
ws.Select
For Each c In ws.Range("E4:K62").Cells
c.Select
If c.Value 0 Then
With ActiveSheet
.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:=ws.Name & c.Address
End With
With Selection.Font
.Name = "Calibri"
.FontStyle = "Bold Italic"
.Size = 11
'.Underline = xlUnderlineStyleSingle
'.Color = 16711680
End With
End If
Next c
ws.Range("B2") = Date
ElseIf ws.Name = "SWOPS_FOPS Bucket" Or ws.Name = "TRANSPORT_SDDI
Bucket" Then
ws.Select
For Each c In ws.Range("E4:I62").Cells
c.Select
If c.Value 0 Then
With ActiveSheet
.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:=ws.Name & c.Address
End With
With Selection.Font
.Name = "Calibri"
.FontStyle = "Bold Italic"
.Size = 11
'.Underline = xlUnderlineStyleSingle
'.Color = 16711680
End With
End If
Next c
ws.Range("B2") = Date
End If
Next ws
End Sub

The codes still work fine, when I click the "Ok" button on the "Reference is
not valid" dialog window.
Any ideas will be greatly appreciated.
Thank you

--

Dave Peterson
.