.formula hyperlink problem

[ukdane]

Using Excel 2000.

I've found the following code in a thread on this site, and it almost works for me. Almost. But not quite.
I'll post the code first: (which I've adjusted for my own Excel file)
Sub fillFormula()
Dim myRng As Range
Dim lastRw As Long
'get the last row with an entry (could have been done from A10 or B10)
lastRw = ActiveSheet.Range("B10").End(xlDown).Row
With ActiveSheet.Range("A10")
.Formula = "=IF(BA10<>"""",HYPERLINK(""#BA10"",""ALERT""),"" "")"
.AutoFill Destination:=ActiveSheet _
.Range("A10:A" & lastRw&)
End With
End Sub
The problem I have is that in the .Formula line, I need the hyperlink not to link to BA10, but instead to column BA, and the row on which the link appears.

Any ideas for a quick, neat solution would be great. Other than this one problem, this code works fine.

Cheers

[Bob Phillips]

Sub fillFormula()
Dim myRng As Range
Dim lastRw As Long
'get the last row with an entry (could have been done from A10 or B10)
lastRw = ActiveSheet.Range("B10").End(xlDown).Row
With ActiveSheet.Range("A10")
.Formula = "=IF(BA10<>"""",HYPERLINK(""#BA""&ROW(),""ALERT""),"""")"
.AutoFill Destination:=ActiveSheet _
.Range("A10:A" & lastRw&)
End With
End Sub

[ukdane]

Right, a further problem has developed.

I've got the hyperlink code working fine.

BUT I've now been told that the workbook has to be shared.

I notice that hyperlink code won't work for shared workbooks.
But are there any ways around this, so that a hyperlink can be created by a Macro / VBA, even though the workbook is shared?

(for example, a button/menu item to run the code to add a hyperlink, or similar solution.

Cheers

[Bob Phillips]

No, no ideas I am afraid. Inserting and changing hyperlinks is just one of the many features not available in shared workbooks.

[ukdane]

That's odd, because I've seen a similar workbook, which has a worksheet with a button on it, and the button starts a macro, which inserts a link.

Would it work, if the button returns a new variable to an already existing link (Where the variable is the destination of the hyperlink)?

For example, if the button triggers:
Sub FindFolder()
On Error GoTo cancel
Application.EnableCancelKey = xlErrorHandler
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.Show
folder = .SelectedItems(1)
ActiveCell.Formula = "=HYPERLINK(""[" & folder & "]"",""Link"")"
End With
cancel:
End Sub

[ukdane]

I've just run a trial workbook (attached), adding the hyperlink, based on a button, and it does seem to work.

Comments?

[Bob Phillips]

Well I just read what help says, I have no practical experience as I wouldn't touch a shared workbook, but your test seems to work so go with it.

[ukdane]

Using the button method seems to work.
Out of curiosity, why wouldn't you work with shared workbooks?
What do you do if you have 2 or more people who have to work with the same file?
I think there will probably be at least 10 people working with this workbook potentially at the same time, all day! How unstable will that be?

[Bob Phillips]

I wouldn't work with shared workbooks because the functionality is severely crippled, and there are so many problems with them that it makes it non-feasible - I am not here to hand-hold people on Ms' mistakes.

If I want more than one person to access it, I put the data in a database and use Excel as a reporting layer.

With 10 people, I would be amazed if you don't hit problems at some point, and the really bad thing about problems with shared workbooks is that they are often irreversible.