Is there a way to force a pop up when spreadsheet is open?

M

mjm

I often get the pop-up "Do you want to enable macros?" when I open a
spreadsheet. I would like to create one that says "I have added a new tab to
the spreadsheet". How do I create a pop-up that appears when a spreadsheet is
open?
 
M

mrice

You need to create a macro linked to the workbook_open event on the
workbooks VBA tab to flash up the message box.

Private Sub Workbook_Open()
MsgBox "Hello"
End Sub
 
T

Therese

Hi
You can also go to dat<datavalid and proceed from there, that's what I'd do.
Hey
 
D

DCSwearingen

This may not be as efficient and as elegant as possible, but it works.

*~*~*~*~*~*~*~*~*~
Global nSheets As Integer

'Run one time to initialize the original count of sheets.
Sub InitializeCount()
Dim sh As Object, shCount As Integer
shCount = 0
For Each sh In ActiveWorkbook.Sheets
shCount = shCount + 1
Next sh
ActiveWorkbook.Names.Add Name:="myCount", RefersToR1C1:=shCount
End Sub

Sub Auto_Open()
Dim myNum, myValue As Integer
myNum = ActiveWorkbook.Names("myCount").Value
myValue = Right(myNum, 1)
CountSheets
If nSheets > myValue Then
newSheetMessage
ActiveWorkbook.Names.Add Name:="myCount",
RefersToR1C1:=nSheets
End If
End Sub

Sub CountSheets()
Dim sh As Object
nSheets = 0
For Each sh In ActiveWorkbook.Sheets
nSheets = nSheets + 1
Next sh
End Sub

Sub newSheetMessage()
Msg = "A new sheet has been added."
Style = vbOKOnly + vbExclamation
Title = "Important Information"
Response = MsgBox(Msg, Style, Title)
End Sub
 
D

DCSwearingen

I just noticed that my proposed solution will only work if there ar
less than 10 worksheets due to only using the right most character.

Try this instead.

*~*~*~*~*~*~*~*~*~

Global nSheets As Integer

'Run one time to initialize the original count of sheets.
Sub InitializeCount()
Dim sh As Object, shCount As Integer
shCount = 0
For Each sh In ActiveWorkbook.Sheets
shCount = shCount + 1
Next sh
ActiveWorkbook.Names.Add Name:="myCount", RefersToR1C1:=shCount
End Sub

Sub Auto_Open()
Dim myNum, myValue As Integer, myLen As Integer
myNum = ActiveWorkbook.Names("myCount").Value
myLen = Len(myNum)
myNum = Right(myNum, myLen - 1)
CountSheets
If nSheets > myValue Then
newSheetMessage
ActiveWorkbook.Names.Add Name:="myCount", RefersToR1C1:=nSheets
End If
End Sub

Sub CountSheets()
Dim sh As Object
nSheets = 0
For Each sh In ActiveWorkbook.Sheets
nSheets = nSheets + 1
Next sh
End Sub

Sub newSheetMessage()
Msg = "A new sheet has been added."
Style = vbOKOnly + vbExclamation
Title = "Important Information"
Response = MsgBox(Msg, Style, Title)
End Su
 
Top