Opening a Message based on file name

Bob Folks

Board Regular
Joined
Jun 25, 2015
Messages
65
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

Active Member
Joined
Jun 4, 2015
Messages
441
In the sheet module:
Code:
Private Sub Worksheet_Activate()
    If ActiveSheet.Name = "Auto Blank feed sheet" Then
        MsgBox "Hi"
    End If
End Sub
 

Bob Folks

Board Regular
Joined
Jun 25, 2015
Messages
65
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)

Code:
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

Active Member
Joined
Jun 4, 2015
Messages
441
That won't work, sorry! This will though, if you put it in the This Workbook module.

Code:
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

Board Regular
Joined
Jun 25, 2015
Messages
65
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.:rolleyes::banghead:

Cheers
Bob
 

Bob Folks

Board Regular
Joined
Jun 25, 2015
Messages
65
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

Board Regular
Joined
Jun 25, 2015
Messages
65
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
 

Forum statistics

Threads
1,078,393
Messages
5,339,936
Members
399,340
Latest member
JasonT903

Some videos you may like

This Week's Hot Topics

Top