Use hyperlink to open workbook read only?

[Clif McIrvin]

Excel 2003 SP3 on XP Pro SP3

I have a workbook I have created to use as an index to infrequently used
workbooks using hyperlinks to create a point and click Document Index.
It works well for why I created it; now I'd like the ability to encode
some of the links to open read-only instead of read-write. I didn't find
anything in the help files ... is this possible?

Thanks in advance!

Clif

 

[Gord Dibben]

How many of these do you have and do they all point to same path but
different workbook?

I'm thinking of an alternative to Hyperlinks.

Workbook names only in cells and event code to select a cell, open the
workbook read-only or not depending upon code.

Sample code.............in sheet module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim mypath As String
mypath = "C:\Program Files\Microsoft Office\Exceldata\"
Select Case Target.Address
Case "$E$4", "$E$5", "$E$6"
Workbooks.Open Filename:=mypath & Target.Value, ReadOnly:=True
Case "$E$7", "$E$8"
Workbooks.Open Filename:=mypath & Target.Value
End Select
End Sub


Gord Dibben MS Excel MVP

 

[Clif McIrvin]

I was wondering if Workbooks.Open would be the answer.

I'll need to think on how to do this; something like your suggestion.

I have the tools needed to do the coding; I just wanted to confirm that
there wasn't a "built-in" mechanism available before I did so <smile>.

To answer your question, no the path is not always the same, and I have
the links in an auto-filter list so I can manipulate it at will.
Neither are insurmountable problems, tho.

Thanks for the answer!

Clif

 

[Clif McIrvin]

In case someone else finds this thread in the archives, and might find
this useful, the code that I ended up with follows - something of a
variation on Gord Dibben's suggestion (also below).

Right now, my list of documents is only a dozen or so, but who knows how
long it might grow<smile>.

These documents are scattered all over the company file server, so there
are an unknown number of different paths.

I have enabled the AutoFilter tool on my list .. I often find that to be
quite useful!

The worksheet layout relevant to the code that follows is:
Column A is the list of fully qualified (UNC for files on the server,
driveathname for local files) pathnames to the various documents with
the width set just wide enough to display the server name.
Column C is a list of descriptive titles so I can recognize the
document.
Cell G1 contains the text: "Double Click Description to Open", and H1
contains "Read Only".
My AutoFilter and list are in columns A-E, so G1:H1 are outside the
AutoFilter range.

(Quite by accident, when testing my code I attempted to open a document
that another user had open, and discovered that the normal Excel "This
workbook is locked by another user" message is suppressed, and the
document always opens Read Only regardless of the ReadOnly:= value. That
took me a bit to figure out ... I couldn't fathom why the Workbooks.Open
appeared to be ignoring the ReadOnly:= parameter!)

========= Begin Code

Option Explicit
Const RO As String = "Read Only"
Const RW As String = "Read/Write"
Const ROFlag As String = "$H$1"

Const Description As Long = 3 ' Description Column
Const Link As Long = 1 ' Pathname Column

Private Sub Worksheet_BeforeDoubleClick(ByVal _
Target As Range, Cancel As Boolean)
With Target
Select Case .Column
Case Description
If VarType(.Value) = vbString Then
' assume is a valid pathname
Cancel = True
Workbooks.Open Filename:= _
.EntireRow.Cells(Link).Value, _
ReadOnly:=(Range(ROFlag) = RO)
End If
End Select
End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal _
Target As Range)

With Target
Select Case .Address
Case ROFlag
' Toggle Read Only Control Flag in Worksheet
Select Case .Value
Case RO
.Value = RW
Case RW
.Value = RO
End Select
End Select
End With

End Sub

======== End Code

Enjoy!
Clif