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,447
Messages
5,340,345
Members
399,370
Latest member
salamon

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top