Reference is not valid
 

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

Reference is not valid
 

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

Reference is not valid
 

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

Reference is not valid
 

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

Reference is not valid
 

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
.

Reference is not valid
 

I am still getting the pop up. This was what I had before I had to split the
table into 3 sheets:
Sub createHYPERLINKS()
Dim c As Range
Worksheets("Working Bucket Summaries").Select
For Each c In Worksheets("Working Bucket
Summaries").Range("C4:G13,C17:F26,C30:F39").Cells
c.Select
If c.Value 0 Then
With ActiveSheet
.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:="'Working Bucket Summaries'!" & c.Address
End With
With Selection.Font
.Name = "Calibri"
.FontStyle = "Bold Italic"
.Size = 11
'.Underline = xlUnderlineStyleSingle
'.Color = 16711680
End With
End If
Next c
Worksheets("Working Bucket Summaries").Range("B2") = Date
Worksheets("Working Bucket Summaries").Range("B15") = Date
Worksheets("Working Bucket Summaries").Range("B28") = Date
End Sub
This file that this code is in works fine. Without the pop-up. But now, the
new file with this code:
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

gives me a pop-up, even though once you click th ok button on the pop-up
evrything works just the way it should. The pop-up is annoying and the people
who are going to use this report will not only notice, I am pretty sure I
will get complaints too. That is why I am try really desperately to get this
fix before I send it out.
Thanks guys.

"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
.

Reference is not valid
 

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
.

Reference is not valid
 

If you look at the hyperlink after it's created, what does it look like?

If you delete the hyperlink and recreate it manually, what does that look like?

Is there a difference?

Ayo wrote:

I am still getting the pop up. This was what I had before I had to split the
table into 3 sheets:
Sub createHYPERLINKS()
Dim c As Range
Worksheets("Working Bucket Summaries").Select
For Each c In Worksheets("Working Bucket
Summaries").Range("C4:G13,C17:F26,C30:F39").Cells
c.Select
If c.Value 0 Then
With ActiveSheet
.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:="'Working Bucket Summaries'!" & c.Address
End With
With Selection.Font
.Name = "Calibri"
.FontStyle = "Bold Italic"
.Size = 11
'.Underline = xlUnderlineStyleSingle
'.Color = 16711680
End With
End If
Next c
Worksheets("Working Bucket Summaries").Range("B2") = Date
Worksheets("Working Bucket Summaries").Range("B15") = Date
Worksheets("Working Bucket Summaries").Range("B28") = Date
End Sub
This file that this code is in works fine. Without the pop-up. But now, the
new file with this code:
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

gives me a pop-up, even though once you click th ok button on the pop-up
evrything works just the way it should. The pop-up is annoying and the people
who are going to use this report will not only notice, I am pretty sure I
will get complaints too. That is why I am try really desperately to get this
fix before I send it out.
Thanks guys.

"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
.


--

Dave Peterson

Reference is not valid
 

I think it will be beter if I send you the files, if you don't mind that is.
Its about 4MB. I figure if you can see the file and play with it, look
through the code, you might be able to better understand what's gone wrong.

"Dave Peterson" wrote:

If you look at the hyperlink after it's created, what does it look like?

If you delete the hyperlink and recreate it manually, what does that look like?

Is there a difference?

Ayo wrote:

I am still getting the pop up. This was what I had before I had to split the
table into 3 sheets:
Sub createHYPERLINKS()
Dim c As Range
Worksheets("Working Bucket Summaries").Select
For Each c In Worksheets("Working Bucket
Summaries").Range("C4:G13,C17:F26,C30:F39").Cells
c.Select
If c.Value 0 Then
With ActiveSheet
.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:="'Working Bucket Summaries'!" & c.Address
End With
With Selection.Font
.Name = "Calibri"
.FontStyle = "Bold Italic"
.Size = 11
'.Underline = xlUnderlineStyleSingle
'.Color = 16711680
End With
End If
Next c
Worksheets("Working Bucket Summaries").Range("B2") = Date
Worksheets("Working Bucket Summaries").Range("B15") = Date
Worksheets("Working Bucket Summaries").Range("B28") = Date
End Sub
This file that this code is in works fine. Without the pop-up. But now, the
new file with this code:
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

gives me a pop-up, even though once you click th ok button on the pop-up
evrything works just the way it should. The pop-up is annoying and the people
who are going to use this report will not only notice, I am pretty sure I
will get complaints too. That is why I am try really desperately to get this
fix before I send it out.
Thanks guys.

"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
.


--

Dave Peterson
.

Reference is not valid
 

No thanks to the files.

If you want to post them on a file sharing site like http://www.senduit.com,
then maybe someone will download your file and help.

There are lots of people will open files from others, but I'm not one of them.

If no one offers any help, you can still describe the problem in plain text in
this forum.



Ayo wrote:

I think it will be beter if I send you the files, if you don't mind that is.
Its about 4MB. I figure if you can see the file and play with it, look
through the code, you might be able to better understand what's gone wrong.

"Dave Peterson" wrote:

If you look at the hyperlink after it's created, what does it look like?

If you delete the hyperlink and recreate it manually, what does that look like?

Is there a difference?

Ayo wrote:

I am still getting the pop up. This was what I had before I had to split the
table into 3 sheets:
Sub createHYPERLINKS()
Dim c As Range
Worksheets("Working Bucket Summaries").Select
For Each c In Worksheets("Working Bucket
Summaries").Range("C4:G13,C17:F26,C30:F39").Cells
c.Select
If c.Value 0 Then
With ActiveSheet
.Hyperlinks.Add Anchor:=Selection, Address:="",
SubAddress:="'Working Bucket Summaries'!" & c.Address
End With
With Selection.Font
.Name = "Calibri"
.FontStyle = "Bold Italic"
.Size = 11
'.Underline = xlUnderlineStyleSingle
'.Color = 16711680
End With
End If
Next c
Worksheets("Working Bucket Summaries").Range("B2") = Date
Worksheets("Working Bucket Summaries").Range("B15") = Date
Worksheets("Working Bucket Summaries").Range("B28") = Date
End Sub
This file that this code is in works fine. Without the pop-up. But now, the
new file with this code:
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

gives me a pop-up, even though once you click th ok button on the pop-up
evrything works just the way it should. The pop-up is annoying and the people
who are going to use this report will not only notice, I am pretty sure I
will get complaints too. That is why I am try really desperately to get this
fix before I send it out.
Thanks guys.

"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
.


--

Dave Peterson
.


--

Dave Peterson