Create a pop-up message prior to opening sheet
 

I have a small workbook in Excel 2007 and I have some macros for it. I want
to be able to create a pop-up message that says "Reminder: in order for
things to work right, please enable macros..."

I can do this AFTER the workbook is open, but I need to do it prior to it
opening. As in, I double-click to open the file, and then I get the message
prior to the workbook opening completely.

How do I do this?? Thanks!

Create a pop-up message prior to opening sheet
 

As a pop-up message would require the use a macro, you can't write a macro
saying "enable macros".

Possible alternative:
Create a worksheet that is blank, except for your warning message. Then, use
the Workbook_Close event to hide all sheets except for your warning sheet.
Set the Workbook_Open event to unhide your regular sheets and hide the
warning sheet. This way, if someone opens workbook with macros enabled,
everything is fine. If macros are disabled, they only see your warning sheet.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Del" wrote:

I have a small workbook in Excel 2007 and I have some macros for it. I want
to be able to create a pop-up message that says "Reminder: in order for
things to work right, please enable macros..."

I can do this AFTER the workbook is open, but I need to do it prior to it
opening. As in, I double-click to open the file, and then I get the message
prior to the workbook opening completely.

How do I do this?? Thanks!

Create a pop-up message prior to opening sheet
 

Del,

You need to hide the application, (Application.Visible = False), display the
message, and then show the application (Application.Visible = True)

This will do the trick. Put this in your workbook module:

Private Sub Workbook_Open()
With Application
.Visible = False
MsgBox "Test", vbCritical, "This is a test"
.Visible = True
End With
End Sub


"Del" wrote:

I have a small workbook in Excel 2007 and I have some macros for it. I want
to be able to create a pop-up message that says "Reminder: in order for
things to work right, please enable macros..."

I can do this AFTER the workbook is open, but I need to do it prior to it
opening. As in, I double-click to open the file, and then I get the message
prior to the workbook opening completely.

How do I do this?? Thanks!

Create a pop-up message prior to opening sheet
 

Hi,
use this

Private Sub Workbook_Open()
MsgBox "Don't forget to Update!"
End Sub

change the message for yours, to enter the macro, right click on the mouse
over the tab name, view code, then at your upper left look for this workbook,
double click it and copy the above macro in the right side, close the VBA
application, then save the workbook as macro enable, close it and reopen

"Del" wrote:

I have a small workbook in Excel 2007 and I have some macros for it. I want
to be able to create a pop-up message that says "Reminder: in order for
things to work right, please enable macros..."

I can do this AFTER the workbook is open, but I need to do it prior to it
opening. As in, I double-click to open the file, and then I get the message
prior to the workbook opening completely.

How do I do this?? Thanks!

Create a pop-up message prior to opening sheet
 

Further to Luke's suggestion............use this code in Thisworkbook
module.

Private Sub Workbook_Open()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
If sht.Name < "Dummy" Then
sht.Visible = True
End If
Next sht
Sheets("Dummy").Visible = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Application.ScreenUpdating = False
Sheets("Dummy").Visible = xlSheetVisible
For Each sht In ActiveWorkbook.Sheets
If sht.Name < "Dummy" Then
sht.Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
ThisWorkbook.Save
End Sub


Gord Dibben MS Excel MVP

On Tue, 1 Sep 2009 10:08:01 -0700, Luke M
wrote:

As a pop-up message would require the use a macro, you can't write a macro
saying "enable macros".

Possible alternative:
Create a worksheet that is blank, except for your warning message. Then, use
the Workbook_Close event to hide all sheets except for your warning sheet.
Set the Workbook_Open event to unhide your regular sheets and hide the
warning sheet. This way, if someone opens workbook with macros enabled,
everything is fine. If macros are disabled, they only see your warning sheet.

Create a pop-up message prior to opening sheet
 

You may want to change the line

Sheets("Dummy").Visible = False to

Sheets("Dummy").Visible = xlSheetVeryHidden

So's users cannot unhide it from FormatSheetUnhide


Gord

On Tue, 01 Sep 2009 11:52:37 -0700, Gord Dibben <gorddibbATshawDOTca wrote:

Further to Luke's suggestion............use this code in Thisworkbook
module.

Private Sub Workbook_Open()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
If sht.Name < "Dummy" Then
sht.Visible = True
End If
Next sht
Sheets("Dummy").Visible = False
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim sht As Worksheet
Application.ScreenUpdating = False
Sheets("Dummy").Visible = xlSheetVisible
For Each sht In ActiveWorkbook.Sheets
If sht.Name < "Dummy" Then
sht.Visible = xlSheetVeryHidden
End If
Next sht
Application.ScreenUpdating = True
ThisWorkbook.Save
End Sub


Gord Dibben MS Excel MVP

On Tue, 1 Sep 2009 10:08:01 -0700, Luke M
wrote:

As a pop-up message would require the use a macro, you can't write a macro
saying "enable macros".

Possible alternative:
Create a worksheet that is blank, except for your warning message. Then, use
the Workbook_Close event to hide all sheets except for your warning sheet.
Set the Workbook_Open event to unhide your regular sheets and hide the
warning sheet. This way, if someone opens workbook with macros enabled,
everything is fine. If macros are disabled, they only see your warning sheet.