do not open if read only

[thakman]

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

 

[VoG]

#2

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

Application.DisplayAlerts = False

then set it to True afterwards.

 

[Richard Schollar]

Hi Thakman

1. See:

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

2. Insert:

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:

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]

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]

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:

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)

 

[thakman]

thanks