do not open if read only

thakman

Board Regular
Joined
Jun 12, 2006
Messages
74
i have set up a macro to open a workbook and put some information into it.

i would like to see it up so that when the macro tries to open the book if another user already has the workbook open a message is shown to the user saying that they cannot enter the data at the moment.

in addition how do i stop messages being shown to the user eg do you wish to save changes etc

finally, there is no way presumably to automatically activate macros on the workbook being opened by vba code but can the macros be disabled?

thanks for your help
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
#2

Before the code that asks the user if they want to save changes or whatever, insert

Code:
Application.DisplayAlerts = False

then set it to True afterwards.
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Thakman

1. See:

http://www.mrexcel.com/board2/viewtopic.php?t=287949&highlight=

2. Insert:

Code:
Application.DisplayAlerts = False

in your code.

3. You can certanly run macros contained in other workbooks (eg using Application.Run), but I'm not sure if there is a way to disable the actual macros themselves. You could turn off event macros (eg on Workbook_Open from firing by using in your code:

Code:
Application.EnableEvents = False
'open workbook
Application.EnableEvents = True

You must turn this back on again, as it doesn't reset by default (unlike DisplayAlerts).
 

thakman

Board Regular
Joined
Jun 12, 2006
Messages
74
thank you both

Richard for number 3, when the code opens the other workbook the message box pops up do you wish to enable macro's, i do not wish the user to see this box and if possible either wish to enable the macro's automatically or if this is not possible not enable the macro's and add my data

regards

taz
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Gotcha.

Yes, you can use the AutomationSecurity property (I am assuming you are on xl2003 - I am afraid I can't be sure it will work in earlier versions). To open the target with macros disbaled:

Code:
Dim secAutomation As MsoAutomationSecurity

    secAutomation = Application.AutomationSecurity  'picks up your current settings

    Application.AutomationSecurity = msoAutomationSecurityForceDisable 'turns off macros for a workbook you will open programmatically
    Workbooks.Open "C:\YourWorkbook.xls"  'amend as appropriate - workbook will be open with macros disabled

    Application.AutomationSecurity = secAutomation   'immediately return to your original state (ie so that unforeseen probs dont arise if you susbsequently try to open a workbook, int3nding macros to be enabled)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,495
Messages
5,596,490
Members
414,070
Latest member
DuncanLucas

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top