Shared Workbook

[Martin]

Hello,

I have a workbook that I need to share with around 50 users in 3 locations.
I have the following code that works on the Workbook_Activate as follows:

Private Sub Workbook_Activate()
Sheets(Array("Main Menu", "H1", "H2", "Help")).Select
Sheets("Main Menu").Activate
With ActiveWindow
.DisplayGridlines = False
.DisplayHeadings = False
.DisplayWorkbookTabs = False
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
End With

Application.DisplayFormulaBar = False

Sheets("Main Menu").Select
Range("M5").Select

Application.CommandBars("Worksheet Menu Bar").Enabled = False
Application.CommandBars("Standard").Enabled = False
Application.CommandBars("Reviewing").Enabled = False
Application.CommandBars("Formatting").Enabled = False
Application.CommandBars("Drawing").Enabled = False
Application.CommandBars("Chart").Enabled = False
Application.CommandBars("Control Toolbox").Enabled = False

ActiveSheet.Calculate

End Sub

This works fine when the workbook is not shared. As soon as I share it the
code doesn't seem to run. I have this code available to run on a short cut
key and I can run it using the shortcut key but I just cant seem to get it to
run on the workbook_activate command once shared.

Can anyone advise how I can overcome this?

Many thanks in advance.

Martin

 

[Jim Thomlinson]

Right in the help file under things you can not do in shared workbooks is a
section on Macros. In short macros and shared workbooks do not get along.
Some stuff will work but lots won't. You will probably have a some difficutly
getting advice on how to proceed because most of the XL programmers avoid
using shared work books as they are just too restrictive.

 

[Dave Peterson]

Your code worked ok for me in my simple test.

You may want to add some debug.print's or msgbox's to your code every few lines
to find out what's causing the trouble.

I added:

Msgbox "Select array of sheets"
Sheets(Array("Main Menu", "H1", "H2", "Help")).Select
.....

msgbox "formula bar"
Application.DisplayFormulaBar = False

.....

Then if the code broke, I could unshare the workbook and add more msgboxes to
find where the code broke.

 

[Dave Peterson]

ps. I tested using xl2003.

Hello,

I have a workbook that I need to share with around 50 users in 3 locations.
I have the following code that works on the Workbook_Activate as follows:

Private Sub Workbook_Activate()
Sheets(Array("Main Menu", "H1", "H2", "Help")).Select
Sheets("Main Menu").Activate
With ActiveWindow
.DisplayGridlines = False
.DisplayHeadings = False
.DisplayWorkbookTabs = False
.DisplayHorizontalScrollBar = True
.DisplayVerticalScrollBar = True
End With

Application.DisplayFormulaBar = False

Sheets("Main Menu").Select
Range("M5").Select

Application.CommandBars("Worksheet Menu Bar").Enabled = False
Application.CommandBars("Standard").Enabled = False
Application.CommandBars("Reviewing").Enabled = False
Application.CommandBars("Formatting").Enabled = False
Application.CommandBars("Drawing").Enabled = False
Application.CommandBars("Chart").Enabled = False
Application.CommandBars("Control Toolbox").Enabled = False

ActiveSheet.Calculate

End Sub

This works fine when the workbook is not shared. As soon as I share it the
code doesn't seem to run. I have this code available to run on a short cut
key and I can run it using the shortcut key but I just cant seem to get it to
run on the workbook_activate command once shared.

Can anyone advise how I can overcome this?

Many thanks in advance.

Martin

 

[Martin]

Thank you both for your responses. I have tried the message box and it fails
on most of the code on my PC but at different stages on another PC.

Jim - you mentioned that developers avoid using shared workbooks. How do
you overcome the sharing problem? I am trying to build a holiday chart that
is accessible by about 50 users in 3 locations so it must be shared.

My only thought was MS Access but that is not ideal either!

Martin

 

[Jim Thomlinson]

Well I stand corrected. Dave is a whiz and if anyone can help with code it is
Dave.

P.S. Dave... You do shared workbooks too! Do your talents know no bounds? I
tried coding to a shared workbook once and gave up in frustration.

 

[ryguy7272]

I worked with shared workbooks, and embedded with lots of code, for about 9
months. Headache beyond belief!! Very erratic; some weeks we had no
problems and some weeks we had nothing but problems. I think the Help menu
says that the max. number of users for shared workbooks is 255, but in my
experience it is much, much, much less and it really depends on the size of
the file. As you may expect, the smaller the file, the better the
performance. I would say, try to avoid doing this in Excel, at all costs.
As an alternative, Access should work quite well in the scenario you
described.

Regards,
Ryan--

 

[Jim Thomlinson]

You hit the nail on the head with Access (or some other database as such). XL
does not handle concurrency well. Databases on the other hand were created
with just that in mind. The issue is often I want an XL front end. In that
case with a bit of ODBC you can read and write to the database. The other
benefit is that since my data is seperate from the front end I can have
multiple different front ends all using the same back end. That means that
multiple different templates can all use the same data. That gets me around
many of the security issues associated with XL...

 

[Martin]

Thank you all for the advice, it is very much appreciated.

I will look into the ODBC solution.

Martin

 

[Dave Peterson]

I can't remember ever using a shared workbook in real life. Too many things are
unavailable and I've read too many stories on how the workbook got corrupted
(coincidence? Maybe, but there are lots of those reports.)

For this, I just shared a test workbook with myself!

 

[Dave Peterson]

I don't have a guess based on the lack of detail.

But I do agree with the others when they suggested a real application that was
created for simultaneous access. (I'm not an access/db user, though.)

 

[Jim Thomlinson]

I actually used 1 once... it was simple beyond belief. So long as the
spreadsheet does not need to do too much it can work. Spreadsheets that don't
need to do much don't come along to often.

 

[Dave Peterson]

Lots start that way, but grow quickly!

I actually used 1 once... it was simple beyond belief. So long as the
spreadsheet does not need to do too much it can work. Spreadsheets that don't
need to do much don't come along to often.