Pop up message box?

  • Thread starter Thread starter Graciegirl
  • Start date Start date
G

Graciegirl

Is there a way to create a pop up message in Excel upon opening a workbook?
I want to create something along the lines of "resave this file as xxxx".
 
Hi,
Alt+F11 to open vb editor. Double click 'this workbook' and paste this in on
the right. The messagebox dies after 4 seconds if a button isn't pressed.


Private Sub Workbook_Open()
Dim MyBox As Object
Dim Duration As Long
Duration = 4 'Seconds
Msg = "Save workbook as ******"
Ttl = " Your Title"
Set MyBox = CreateObject("WScript.Shell")
response = MyBox.PoPup(Msg, Duration, Ttl, vbOKCancel)
End Sub

Mike
 
Easily done but you might prefer to take the user out of the process and
save the workbook as your new name when you close it?

For the pop-up upon opening...................

Private Sub Workbook_Open()
MsgBox "This file must be re-saved as XXXX"
End Sub

To saveas XXXX.xls when closing...................

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Note that any existing "XXXX" workbook is overwritten
If ActiveWorkbook.Name = "XXXX.xls" Then Exit Sub
Application.DisplayAlerts = False
ActiveWorkbook.SaveCopyAs Filename:="XXXX.xls"
ActiveWorkbook.Save 'saves original workbook also
Application.DisplayAlerts = True
End Sub

Both of these go into Thisworkbook module.

Right-click on the Excel Icon left of "file" on the worksheet menu bar and
select "View Code"

Paste whichever event you want into that module.


Gord Dibben MS Excel MVP
 
Back
Top