Launch Script When Tab Is Opened

[ExcelAtEverything]

Hello all!

Is there a way to launch a script or message every time a particular tab (DATA1) is selected/opened? I know it can be done when opening a workbook, but I need it to occur when opening a tab. What I'm trying to do is to create a "warning" sort of message each time the database tab (DATA1) is opened. If the person runs were to open that particular tab and just start pressing the buttons (which run macros) without doing it in the correct order, it could break the sheet by disorganizing the table I have on the main (RANKER) sheet. On the other hand I can't lock or hide it because they will need to use it periodically (when a new store comes online & opens).

Thanks!

 

[ExcelAtEverything]

- It needs to only insert 1 row at the bottom of each range on the MOR sheet.
Because new stores don't open in groups. 1 may open in a few months, then another in 6 month, then another in 2 months, etc.

- It needs to insert a row at the bottom of the range on the "Store Data" sheet.
Because since that new store would need to be added 3 times on the MOR sheet (Yesterday, MTD, and YTD), and for the sake of consistency, I wanted it to be entered only once on the data tab so that I could better control how it then appears on the MOR.

- In the D column of the MOR sheet, it also needs to copy down the "Concatenate" formula from the rest of the D cells above.
Because the code was NOT concatenating when I ran it. If the code did, then sure I would be rid of the concatenate need.

 

[ExcelAtEverything]

Still don't get it. Clicked on 'the button'? What button??? Why did this make the thread famous, and wtf is a sticky? LolI feel like I'm the new guy in a long-standing clique. ?

 

[smozgur]

Still don't get it. Clicked on 'the button'? What button??? Why did this make the thread famous, and wtf is a sticky? LolI feel like I'm the new guy in a long-standing clique. ?

@ExcelAtEverything - when you sent the email to request content removal yesterday (and also reported the post), I edited the posts on my phone since I was away from my computer by considering your request was urgent. Then I accidentally marked the thread to be displayed at the top of all questions in the Excel Questions forum (what sticky thread means). So, your question was kind of a featured post for a while (of course all sensitive content was already removed).

We are just having some fun combined with my mistake and the server outage that happened at the same time yesterday. So, there is nothing to worry about.

By the way, I never received a reply to my email that I have sent confirming the sensitive data removal. Everything looks ok? No sensitive data left, right?

 

[ExcelAtEverything]

@ExcelAtEverything - when you sent the email to request content removal yesterday (and also reported the post), I edited the posts on my phone since I was away from my computer by considering your request was urgent. Then I accidentally marked the thread to be displayed at the top of all questions in the Excel Questions forum (what sticky thread means). So, your question was kind of a featured post for a while (of course all sensitive content was already removed).

We are just having some fun combined with my mistake and the server outage that happened at the same time yesterday. So, there is nothing to worry about.

By the way, I never received a reply to my email that I have sent confirming the sensitive data removal. Everything looks ok? No sensitive data left, right?

Yes sorry! I was in my studio mixing a song when that was going on, so I didn't have a chance to reply. But I did see it was done and really appreciate the urgency you placed on it. I LOVE this site! I wish I used it more back when I was working in retail and was rocking out Excel sheets every other day. I'd probably be light years ahead right now! Thanks for schooling me on that sticky situation. (Huh??? Did you see what I did there?)
Thanks again, Smozgur!

 

[smozgur]

Yes sorry! I was in my studio mixing a song when that was going on, so I didn't have a chance to reply. But I did see it was done and really appreciate the urgency you placed on it. I LOVE this site! I wish I used it more back when I was working in retail and was rocking out Excel sheets every other day. I'd probably be light years ahead right now! Thanks for schooling me on that sticky situation. (Huh??? Did you see what I did there?)
Thanks again, Smozgur!

It happens to all of us. Glad to be able to help.

Welcome to the MrExcel Message Board!

 

[Michael M]

If there is only going to be 1 line in the Store Data table, try

Sub MM1()
Dim lr As Long, r As Long, ws As Worksheet
Set ws = Sheets("Store Data")
With Sheets("MOR")
    lr = .Cells(Rows.Count, "D").End(xlUp).Row
For r = lr To 6 Step -1
        If .Range("D" & r).Value = "Notes:" Then
            .Rows(r).Insert
           .Range("D" & r).Value = ws.Range("B4").Value & " " & ws.Range("C4").Value
         End If
    Next r
End With
End Sub

 

[ExcelAtEverything]

 

[Michael M]

Where are you putting the code?
AND
is there any text / lines of code before the Sub Addnewrow line or after the End Sub line ?
the error is suggesting there are lines of code or text outside the Sub !!
The code works fine for me

 

[Michael M]

Also adjusted the code to format the inserted row

Sub MM1()
Dim lr As Long, r As Long, ws As Worksheet
Set ws = Sheets("Store Data")
With Sheets("MOR")
    lr = .Cells(Rows.Count, "D").End(xlUp).Row
For r = lr To 6 Step -1
        If .Range("D" & r).Value = "Notes:" Then
            .Rows(r).Insert
            .Rows(r - 1).Copy
            .Rows(r).EntireRow.PasteSpecial Paste:=xlPasteFormats
           .Range("D" & r).Value = ws.Range("B4").Value & " " & ws.Range("C4").Value
         End If
    Next r
End With
Application.CutCopyMode = False
End Sub

 

[ExcelAtEverything]

Wow! Michael M, this works beautifully! There is just one lingering issue.Instead of copying the concatenate formulas down, its pulling the values.