Hyperlink 1004 error (VBA/Excel/2003)

Topic

New Reply

I have a shared job register workbook that has VBA code for creating a hyperlink to related XLS files. This is done through a button on the worksheet.

strSelect = Application.GetOpenFilename("MS Excel Files (*.xls), *.xls", _
                Title:="Please select file to be linked by navigating to it...")

ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=strSelect, TextToDisplay:="X"

The problem is that it works fine BEFORE sharing is activated but then comes up with Run-time error ‘1004’ after sharing is enabled.

Can someone tell me what I need to look at so that the workbook can be used as intended with sharing turned on?

Reply | Quote

Replies

Sharing a workbook is a recipe for disaster in my experience, the workbook becomes prone to corruption. I never use it any more.

Reply | Quote

Thanks Hans, for your sage words of advice regarding shared workbooks. (A bit like Master Documents in Word? That was a dud for me. Never had a lot of success and actually corrupted about 6 chapters of a manual that a team was working on…)

OK, can you tell me more about multiple users of documents? For the workbook in question I just assumed that it needed to be shared but this is how it is to be used.

• Whilst more than one person may use the job register, they will generally be using a different worksheet.
  [/list]Does that cause any problems with more than one person opening it and then trying to save and close their session?

  What are your thoughts about separate workbooks with (dare I say it?) a “master workbook” that collates, via links, all the data? Individual sheets would be just linked to each of the line areas workbooks. Then only one person would need to open and view the “master” whilst individual line managers updated their own job registers.

  Would that work?

Reply | Quote

If you don’t use workbook sharing, only the first user who opens a workbook can edit it, all subsequent users open it read-only. It doesn’t matter whether they look at the same worksheet or different worksheets.

If you create a “master” workbook with links to various other workbooks, anyone can view this “master” while others are viewing or editing the other workbooks. But the changes won’t be “live”. If someone edits a workbook, it must be saved, then the user viewing the “master” must select Edit | Links…, Update Values. But I haven’t seen corruption caused by this approach.

If you really need multi-user capability, consider using a database application such as Access.

Reply | Quote