VBA code to popup a msg Box when ever I open a excel sheet save

[vengal1987]

Hi All,


I want a VBA code to "popup a msg Box" when ever I open a excel sheet and save a excel sheet ?


Could any one plesae help on this ?


Thanks,

VengaL1987

 

[Smallman]

Hi Vengal1987


In the worksheet object where you wan the code to run from put the following;


Private Sub Worksheet_Activate()

MsgBox ActiveSheet.Name

End Sub


Be aware that it can be extremely annoying if a message pops up every time a sheet is clicked on.


Now in the workbook object put the following;


Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

MsgBox "About to Save"

End Sub


Take care


Smallman

 

[vengal1987]

Hi Smallman thanks for quick reply.


However can u pls help on code part as I am unable put the above code in my macro.


Below is my macro code:

------------------------------------------------------------------------------------

Sub macro()


Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets


With ws.PageSetup


'.LeftFooter

.CenterFooter = "Limited access only"

'.RightFooter


End With

Next ws

End Sub


--------------------------------------------------------------------------------------------


can you pls help me.

 

[SirJB7]

Hi, vengal1987!


First, a little clarification:


a) Workbooks can be opened, saved & closed. The events that handle that are:

Private Sub Workbook_Open()

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Private Sub Workbook_BeforeClose(Cancel As Boolean)

placed in the workbook class module ThisWorkbook.


b) Worksheets can be created (added), selected, calculated & deselected, The related events are:

Private Sub Workbook_NewSheet(ByVal Sh As Object)

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

placed in the workbook class module ThisWorkbook.


c) Worksheets can be selected, calculated, deselected. The related events are:

Private Sub Worksheet_Activate()

Private Sub Worksheet_Calculate()

Private Sub Worksheet_Deactivate()

placed in the worksheet class module Sheet1 (e.g.)


So, within any of those procedures (that'll depend on your requirements and preferences) you may place a code like that of Smallman's example:

-----

MsgBox "About to do something"

-----

and replace "do something" by the actual event triggered, and then test if you're comfortable with the few (or the bunch of, depending on your selections) messages that you receive.


Hope it helps.


Regards!


PS: Regarding the code posted in your last comment it isn't related to the topic title and your first post, but much more to this other topic of yours:

http://chandoo.org/forums/topic/footer

which has been thoroughly responded by b(ut)ob(ut)hc and others, as follows:

http://chandoo.org/forums/topic/footer#post-127078

so, please:

1) Don't mix topics and keep comments about the same subject within a unique thread.

2) Ask once and again whenever needed if you don't understand answers or suggestions.