macro enforce user enable macro otherwise pop up message and close file

Hasson

Board Regular
Joined
Apr 8, 2021
Messages
154
Office Version
  1. 2016
Platform
  1. Windows
hi experts

I no know if what I ask for it can be possible . so what I want macro in workbook open event . so when open the file and shows
disable or enable macro and the user disable the macro then should pop up message " sorry you have to enable macro" and should close the whole application excel . but I have two things in my mind . first I'm afraid next time when open the file doesn't show two choices to enable the macro or disable then the user can use the file . second I don't do that for any file just by specific name of file to overcome applying for all excel files . I know some body suggests by protect the file . I wouldn't this way. if it's possible can do that as I mentioned will be great

thanks in advance
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,160
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I don't think you can do that. The action to close excel would have to be implemented as a macro, and no macro will run if the user chooses to disable macros. The "Disable" button would not be much good if it allowed the workbook open event to go ahead and run macros anyway.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,473
Office Version
  1. 2013
Platform
  1. Windows
I'm sure you cannot do that.
However, you could design your workbook in such a way that the use of macros is enforced. You could make sure that all worksheets are (very) hidden with the exception of one worksheet, on which the user is explained that he successively has to:
- close the workbook
- enable macros again
- open the workbook again.
When opening the workbook, you could use an event handler to make all worksheets visible again and hide the worksheet with the message.
 

Hasson

Board Regular
Joined
Apr 8, 2021
Messages
154
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

@GWteB thanks .so is not possible vba just do that manually ?
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,473
Office Version
  1. 2013
Platform
  1. Windows
When macros are disabled, you cannot run any macro and there's no way to determine whether macros are disabled or not. That's why I suggested to take that into account when you design your workbook.
 

Hasson

Board Regular
Joined
Apr 8, 2021
Messages
154
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

ok I will suffice with this . thanks so much for your clarification:)
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,160
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I've done something similar to what @GWteB suggests, just not taken to the extremes of hiding worksheets. I've had one worksheet that was designed as a big 'nag' screen telling the user they needed to close the workbook then enable macros the next time. If the user open the workbook without macros enabled, they would get this screen. But mine was intended as a reminder, not an enforcement mechanism.
 

Hasson

Board Regular
Joined
Apr 8, 2021
Messages
154
Office Version
  1. 2016
Platform
  1. Windows
@Domenic thanks . actually before I try this way . I face a problem . sometimes when open the file it doesn't show forces a user to enable macros or not in a workbook . any idea?








2
 

Forum statistics

Threads
1,148,369
Messages
5,746,295
Members
424,006
Latest member
Metal_warrior

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