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

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?

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

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


--
mrice


------------------------------------------------------------------------
mrice's Profile: http://www.excelforum.com/member.php...o&userid=10931
View this thread: http://www.excelforum.com/showthread...hreadid=533859

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

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


"mjm" skrev:

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?

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

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


--
DCSwearingen


------------------------------------------------------------------------
DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=533859

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

I just noticed that my proposed solution will only work if there are
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 Sub


--
DCSwearingen


------------------------------------------------------------------------
DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=533859