File shared on network, can it be opened READ ONLY from all but one computer?

[wojo]

I would like to have only one, maybe two computers on the network, have
the ability to open the Excel file with read and write capability. I
want all other computers on the network to have the ability to open the
file in a READ ONLY status. I do NOT want the user to have the option
of read only.

Because I don't know if this can be done, I have made a "linked" file,
which is read only, for users on the network. This works OK, but my
file has forumlas with don't work on the linked file, without
converting the links to values. I want the networked users to be able
to manipulate the data (so they can check out different numerical
scenarios). I made a macro that opens the original file, copies the
formulas, then pastes the formulas onto the read only file. This
allows the data to calculate when the user changes the value of cells.

To ensure the user does not create numerous "copies" of the read only
file, I have a BUTTON on the sheet, that closes the file, without
prompting the user to save.

Is there a better way? Can the original file be made read only, for
networked users?

Thanks in advance. Jo

[Dave Peterson]

I think the easiest solution is to get others to do the work for you!

Can you to your IT folks to get a network share created that only certain people
have read/write access? Leave it open (for reading) for all the others.

======
Alternatively, you could use File|SaveAs|Tools|General Options and give the
workbook a password to modify (not to open). Then share that password with your
trusted coworkers.

But with that technique, someone could save the file as a new name (same folder,
even). And use Windows explorer to delete the original and rename the copy to
the original's name.

If you can get your IT people to help out, your file will be much safer.

wojo wrote:
>
> I would like to have only one, maybe two computers on the network, have
> the ability to open the Excel file with read and write capability. I
> want all other computers on the network to have the ability to open the
> file in a READ ONLY status. I do NOT want the user to have the option
> of read only.
>
> Because I don't know if this can be done, I have made a "linked" file,
> which is read only, for users on the network. This works OK, but my
> file has forumlas with don't work on the linked file, without
> converting the links to values. I want the networked users to be able
> to manipulate the data (so they can check out different numerical
> scenarios). I made a macro that opens the original file, copies the
> formulas, then pastes the formulas onto the read only file. This
> allows the data to calculate when the user changes the value of cells.
>
> To ensure the user does not create numerous "copies" of the read only
> file, I have a BUTTON on the sheet, that closes the file, without
> prompting the user to save.
>
> Is there a better way? Can the original file be made read only, for
> networked users?
>
> Thanks in advance. Jo

--

Dave Peterson

[Dave Peterson]

Can you to your ...
should be
Can you _talk_ to your ...

Dave Peterson wrote:
>
> I think the easiest solution is to get others to do the work for you!
>
> Can you to your IT folks to get a network share created that only certain people
> have read/write access? Leave it open (for reading) for all the others.
>
> ======
> Alternatively, you could use File|SaveAs|Tools|General Options and give the
> workbook a password to modify (not to open). Then share that password with your
> trusted coworkers.
>
> But with that technique, someone could save the file as a new name (same folder,
> even). And use Windows explorer to delete the original and rename the copy to
> the original's name.
>
> If you can get your IT people to help out, your file will be much safer.
>
> wojo wrote:
> >
> > I would like to have only one, maybe two computers on the network, have
> > the ability to open the Excel file with read and write capability. I
> > want all other computers on the network to have the ability to open the
> > file in a READ ONLY status. I do NOT want the user to have the option
> > of read only.
> >
> > Because I don't know if this can be done, I have made a "linked" file,
> > which is read only, for users on the network. This works OK, but my
> > file has forumlas with don't work on the linked file, without
> > converting the links to values. I want the networked users to be able
> > to manipulate the data (so they can check out different numerical
> > scenarios). I made a macro that opens the original file, copies the
> > formulas, then pastes the formulas onto the read only file. This
> > allows the data to calculate when the user changes the value of cells.
> >
> > To ensure the user does not create numerous "copies" of the read only
> > file, I have a BUTTON on the sheet, that closes the file, without
> > prompting the user to save.
> >
> > Is there a better way? Can the original file be made read only, for
> > networked users?
> >
> > Thanks in advance. Jo
>
> --
>
> Dave Peterson

--

Dave Peterson

[wojo]

Here's the unique problem. Almost everyone needs access to the
program, BUT... management wants them Only to have access when they are
on a particular computer (one that is available to the person "in
charge"). I know, it sounds weird, but they have a little 'control'
if the person is working at that computer (visible to everyone),
whereas, when they are at other computers, they are not.

Jo

[Dave Peterson]

You could do something with the pc's name, but that would depend on macros. And
since macros can be disabled, it wouldn't always work.

This may work if macros are enabled.

Option Explicit
Sub auto_open()

Dim WSH As Object
'Dim WSH As IWshRuntimeLibrary.WshNetwork

Dim OkToContinue As Boolean

Set WSH = CreateObject("WScript.network")
'Set WSH = New IWshRuntimeLibrary.WshNetwork

OkToContinue = False
Select Case LCase(WSH.computername)
Case Is = "abcd", "efgh", "ghij"
OkToContinue = True
End Select

With ThisWorkbook
If OkToContinue = False _
And .ReadOnly = False Then
MsgBox "Hello: " & WSH.UserName & vbLf & _
"This workbook will be changed to readonly" & vbLf & _
"You won't be able to save any changes!"
.ChangeFileAccess xlReadOnly
End If
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

ps. I have a button on my toolbar that will toggle readonly mode. Anyone could
add that same button to their toolbar and be able to do the same thing. (No
knowledge of VBA would be required.)

Add a:
msgbox wsh.computername
to see what the names of the pc(s) are.

wojo wrote:
>
> Here's the unique problem. Almost everyone needs access to the
> program, BUT... management wants them Only to have access when they are
> on a particular computer (one that is available to the person "in
> charge"). I know, it sounds weird, but they have a little 'control'
> if the person is working at that computer (visible to everyone),
> whereas, when they are at other computers, they are not.
>
> Jo

--

Dave Peterson

[wojo]

It will be a while before I can try this. I am on vacation, away from
work, YEAH! I'll come back to post my results in a few weeks. Thank
you Dave for all of your assistance, with this and my other questions
too!

You are great and the help is wonderful.
Jo