Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use hyperlink to open workbook read only?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use hyperlink to open workbook read only?
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 On Wed, 7 Apr 2010 12:37:27 -0500, "Clif McIrvin" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use hyperlink to open workbook read only?
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 "Gord Dibben" <gorddibbATshawDOTca wrote in message ... 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 On Wed, 7 Apr 2010 12:37:27 -0500, "Clif McIrvin" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Use hyperlink to open workbook read only?
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, drive:pathname 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 "Gord Dibben" <gorddibbATshawDOTca wrote in message ... 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 On Wed, 7 Apr 2010 12:37:27 -0500, "Clif McIrvin" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Open Workbook to Protected/Read-only Warning | Excel Discussion (Misc queries) | |||
Delete Web Toolbar when Open A Hyperlink workbook | Excel Discussion (Misc queries) | |||
Need VBA Code to Open a Workbook in read only | Excel Discussion (Misc queries) | |||
Intra-workbook hyperlink: macro/function to return to hyperlink ce | Excel Discussion (Misc queries) | |||
file open via IE hyperlink causes already open files to shrink and tile | Setting up and Configuration of Excel |