Question About a Shared Workbook

[ryguy7272]

Each week I post a shared workbook to a network drive. The file is about
1.5MB. I have an ActiveX command button on a sheet named ‘Password’ and some
code behind that sheet to permit certain individuals to see certain sheets
and others can see other sheets, based on the login. I know Excel does not
have great security, but anyway…

Also, I have some code in ‘ThisWorkbook’:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call HideSheets
End Sub

So, when I close the workbook, all sheets, except for the sheet named
’Password’ are hidden. When I run the workbook from my own computer,
everything works fine. When I post the file on the network, the behavior
become very unpredictable. For instance, sometimes, when the file opens, a
user can’t click on the button named ‘Enter Password’. The user seems to be
locked out of the file. If you close the workbook and reopen it may work the
second time it opens. Sometimes I have to do this several times before the
user can actually click on the button and enter a password. Also, when a
user clicks the ‘x’ in the upper right hand corner to close the file, the
sheets seem to get hidden, which is exactly what I want, and then the file is
saved, but then when another user opens the file, some of the sheets seem to
be randomly unhidden, and people that shouldn’t see some sensitive data are
actually seeing confidential data...before the ‘Enter Password’ button is
even clicked.

Has anyone experienced this kind of erratic behavior? Again, when I run the
file from my computer, everything works fine. When I open the file from the
network, most of the time it works fine, but sometimes I get some very
unpredictable results.

I’d appreciate any help or insight.


Regards,
Ryan---

 

[JP]

It might depend on the macro security of each computer, but I think
the shared nature has something to do with it. Does the workbook need
to be shared?

--JP

Each week I post a shared workbook to a network drive.  The file is about
1.5MB.  I have an ActiveX command button on a sheet named ‘Password’ and some
code behind that sheet to permit certain individuals to see certain sheets
and others can see other sheets, based on the login.  I know Excel doesnot
have great security, but anyway…  

Also, I have some code in ‘ThisWorkbook’:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call HideSheets
End Sub

- snip -

 

[ryguy7272]

Thanks for the comment JP. Yes, it has to be shared, as a couple dozen
people are logging in to the workbook to check the status of some sales
stuff. Yes, we need the macros to control which sheets are displayed when
each user logs in (well, when it works correctly...). I think this project
would have been much better suited for Access, but virtually no one here has
Access on their computers, except me, and virtually no one here knows how to
use Access, except me.

Any other comments, suggestions, etc.

Thanks,
Ryan--

 

[JP]

I understand your problem, but I still don't see why the workbook
needs to be shared, if all they are doing is looking at the worksheet.

What do you mean by "logging on" and how do you tell who is who?

If the data is that sensitive, why not break it out into separate
workbooks? Maybe a re-think of the design is in order. Or store the
data in an Access table, and write some Excel code to conditionally
query the mdb for the necessary data, depending on the requestor?

--JP

 

[ryguy7272]

People are entering information into specific parts of the workbook too.
Nothing data-intensive, but perhaps updating a cell with +$250,000, or
-$90,000, or whatever, and adding comments too. There are probably
alternatives to the method that I'm employing now, but I think it is probably
pretty efficient as it is right now. Querying Access is an option, but I
thought this would just be a short-term solution, which would become obsolete
as some new technologies are rolled out, but roll-outs are always postponed
and very buggy when deployed, so we came up with this interim solution.

Any others have any experience with Excel's 'Shared' feature?

Thanks,
Ryan--

--
RyGuy

I understand your problem, but I still don't see why the workbook
needs to be shared, if all they are doing is looking at the worksheet.

What do you mean by "logging on" and how do you tell who is who?

If the data is that sensitive, why not break it out into separate
workbooks? Maybe a re-think of the design is in order. Or store the
data in an Access table, and write some Excel code to conditionally
query the mdb for the necessary data, depending on the requestor?

--JP