A
Ayo
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
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