Opening a Message based on file name

[Bob Folks]

Hi Gang,

I have a excel sheet that opens with a message box. The sheet is a template and is used to create other sheets with different variables. My question is this how do I have this box open whilst the sheets name is "Auto Blank feed sheet" and not open when the sheet has a different name after the new sheet has been made.

Any help would be greatly appreciated.

Cheers
Bob

 

[Paul Ked]

In the sheet module:

Private Sub Worksheet_Activate()
    If ActiveSheet.Name = "Auto Blank feed sheet" Then
        MsgBox "Hi"
    End If
End Sub

 

[Bob Folks]

Hy Paul

thanks for the help. I think I may have gotten tripped over with the terminology. The difference between Workbook and sheet are huge I realize. Will the following code do the same or am I missing something (Like basic knowledge lol)

Private Sub Worksheet_Activate()
    If ActiveWorkbook.Name = "Auto Blank feed sheet.xls" Then
        MsgBox "This sheet was developed for convenience please ensure the feed you use is appropriate", , "Warning!!!"
    End If
End Sub

Cheers
Bob

 

[Paul Ked]

That won't work, sorry! This will though, if you put it in the This Workbook module.

Private Sub Workbook_Open()
    If ThisWorkbook.Name = "[COLOR=#ff0000]Auto Blank feed sheet[/COLOR].xlsm" Then
        MsgBox "This Workbook was developed for convenience please ensure the feed you use is appropriate", , "Warning!!!"
    End If
End Sub

NOTE: You have to save the file as a Macro enabled workbook (.xlsm) and with the same name as in red above.

 

[Bob Folks]

Paul,

Thank you that awesome but I now have a heap of macros that wont run I am being told that I cant run those macros on an active sheet. I have to be honest here this sheet has been inherited by me and has been used for 20 Years through many versions of Excel. It may be time to bight the Bullet and re write the whole thing in the newer Excel 2010 My company has us using.

Cheers
Bob

 

[Paul Ked]

I am being told that I cant run those macros on an active sheet.

What error message?

this sheet has been inherited

Workbook?

It may be time to bight the Bullet and re write the whole thing in the newer Excel 2010 My company has us using.

Macros should be backward compatible

 

[Bob Folks]

Hey Paul,

Yes the massage I am getting is "You can not run this macro on an active sheet". I have about 20 macros that configure the main sheet with in the work book for particular products we make, they have saved a huge amount of time in setup and led to a standardized way of working.

Cheers

Bob

 

[Bob Folks]

Hey Paul,

Mate thank you so much for your help I don't know what I did yesterday that got in the way buy I applied the code you suggested to my .xls file and it did exactly what I wanted. You sir are awesome much thanks.

Cheers
Bob

 

[Paul Ked]

you're welcome