Reference is not valid

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
 
J

JLatham

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?
 
D

Dave Peterson

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

JLatham

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...??
 
A

Ayo

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

Ayo

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

Dave Peterson

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

Ayo

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

Dave Peterson

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Compile error. Need Help with this UDF !!!!!!!!!!!!!!! 3
Need help with wayward ActiveCell.EntireRow.Delete 2
Code only works in Debug mode. Why? 6
Reference two types of named sheet in a code 4
AutoFilter and Sub-total 6
A simple if <> nn then Msgbox "No Match" 5
Search other sheets for numbers & color them 10
Problem with Work_Sheet Change. 10

Top