|
Shared Workbook
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 |
Shared Workbook
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. -- HTH... Jim Thomlinson "Martin" wrote: 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 |
Shared Workbook
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. Martin wrote: 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 -- Dave Peterson |
Shared Workbook
ps. I tested using xl2003.
Martin wrote: 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 -- Dave Peterson |
Shared Workbook
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 "Dave Peterson" wrote: ps. I tested using xl2003. Martin wrote: 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 -- Dave Peterson |
Shared Workbook
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. -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: 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. -- HTH... Jim Thomlinson "Martin" wrote: 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 |
Shared Workbook
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-- -- RyGuy "Martin" wrote: 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 "Dave Peterson" wrote: ps. I tested using xl2003. Martin wrote: 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 -- Dave Peterson |
Shared Workbook
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... -- HTH... Jim Thomlinson "Martin" wrote: 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 "Dave Peterson" wrote: ps. I tested using xl2003. Martin wrote: 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 -- Dave Peterson |
Shared Workbook
Thank you all for the advice, it is very much appreciated.
I will look into the ODBC solution. Martin "Jim Thomlinson" wrote: 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... -- HTH... Jim Thomlinson "Martin" wrote: 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 "Dave Peterson" wrote: ps. I tested using xl2003. Martin wrote: 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 -- Dave Peterson |
Shared Workbook
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! Jim Thomlinson wrote: 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. -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: 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. -- HTH... Jim Thomlinson "Martin" wrote: 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 -- Dave Peterson |
Shared Workbook
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.) Martin wrote: 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 "Dave Peterson" wrote: ps. I tested using xl2003. Martin wrote: 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 -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 02:04 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com