Function to detect macro status

[Kelvin Stott]

In some versions of Excel the prompt/option to enable macros is not very visible, therefore I would like to add a more visible reminder within my Excel document to enable macros if they are currently disabled.

Is there any simple function or formula I could add within a cell that will display a given text message, depending on whether macros are currently enabled or disabled? In other words, is there some kind of macro status function, for example:

=IF(MACROS()=TRUE,"Macros are enabled","Please enable macros now")

Any help would be appreciated, thanks!

 

[ango858]

One thing I did was I created a tab that was pretty much black except for across the sheet it said "Please enable Macros" as soon as macros enabled I had written a routine that instantly closed that tab and opened the tab that should be opened. In an instance where macros are automatically enabled the macro kicks in automatically and the appropriate sheet showed first. I hope this helps.

 

[ango858]

Sorry, another idea i just had was that you have a cell value, lets say A1 = disabled, however upon opening the worksheet you can write a macro that changes the value to "enabled" this would only change if macros are enabled.

copy and paste this in the vb code of your workbook not a sheet

Private Sub Workbook_Open()

Activesheet.range("A1").value = "Macros enabled"

End Sub

Private Sub workbook_beforeclose(cancel As Boolean)

Activesheet.range("A1").value = "Macros disabled"

End Sub



you will also want to add "disabled" once you close so that it changes back:

 

[Kelvin Stott]

At first I was thinking "how could a disabled macro tell you that macros are disabled?". But now I realize you've flipped round the logic so that the reminder to turn on macros appears by default, and disappears when the macros are enabled. Very clever!

So, this is exactly what I needed, thanks!

 

[Kelvin Stott]

But hang on ... this doesn't quite work if you first save the file, and then close it without saving: the message to enable macros does not appear when you re-open the file with macros disabled.

I even tried putting the same code in Workbook_BeforeSave, rather than Workbook_BeforeClose, but then the reminder to enable macros pops back up on save, when the macros are still enabled.

Any more ideas to fix this loop-hole???

 

[ango858]

Sorry, another idea i just had was that you have a cell value, lets say A1 = disabled, however upon opening the worksheet you can write a macro that changes the value to "enabled" this would only change if macros are enabled.

copy and paste this in the vb code of your workbook not a sheet

Private Sub Workbook_Open()

Activesheet.range("A1").value = "Macros enabled"

End Sub

Private Sub workbook_beforeclose(cancel As Boolean)

Activesheet.range("A1").value = "Macros disabled"

End Sub



you will also want to add "disabled" once you close so that it changes back:

how about:

Private Sub workbook_beforeclose(cancel As Boolean)
dim myname as string

myname = thisworkbook.name


Activesheet.range("A1").value = "Macros disabled"

workbooks(Myname).save

End Sub

 

[Kelvin Stott]

Thanks! Could you please explain what this does and how it works? I ask because I like to understand and learn so that I can solve more problems myself, rather than just ask others to solve all my problems.

Thanks.

 

[Kelvin Stott]

OK, I think I understand what this does now, but what if someone wants to close the file without saving?

 

[RoryA]

If you want to use a formula, create a UDF like:

Function MacrosEnabled() As String
   MacrosEnabled = "Macros are enabled"
End Function

then in a cell use:
=IF(ISERROR(MacrosEnabled()), "Macros are disabled! Please enable them",MacrosEnabled())

 

[ango858]

OK, I think I understand what this does now, but what if someone wants to close the file without saving?

since macros should already be enabled, you can give them an option, such as:

Private Sub workbook_beforeclose(cancel As Boolean)
dim myname as string

myname = thisworkbook.name


Activesheet.range("A1").value = "Macros disabled"

isanswer = msgbox("do you wish to save the file before closing?", vbyesno, "close file")

if isanswer = vbno then
exit sub

else
workbooks(Myname).save

end if
End Sub

basically it will put "macros disabled" in A1, however if the user selects "no" on the message box it will not save that change and will most likely show "enabled" the next time someone opens it (unless it was not saved at all during that occurance)
if "yes" is selected then the file will save prior to close