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!

 

[Michael M]

But why do you need the formula...if the code joins the 2 cells for you

 

[ExcelAtEverything]

I think I see where we're having a disconnect here. When I saw that it popped in the wrong store, I clicked on the cell to see if it pulled my formula, and it did not of course. But I thought that the macro just pasted the values of D6 on this same MOR tab. But in fact I now understad that the macro is actually doing the formula work so as to eliminate the need for the formula. But ok so if that's the case, then I guess I need to clear rows 6-8. But even if I did do that, and your macro started off by plugging in "101 Store 1", the problem is that when I enter another new store into the Store Data tab and then press the green button, it plugs i another duplicate "101 Store 1". Does that make sense?

 

[Michael M]

That would only happen if the Store data was on the next line !
By using my code you wouldn't need to add another line on "Store Data" just use the same line !
I can add a line into to the code to clear the line in "Store data" if you want....so it's an empty table each time ?

 

[Michael M]

If you are going to use a new row in "Store Data" each time, try this code

Sub MM1()
Dim lr As Long, r As Long, ws As Worksheet, lr2 As Long
Set ws = Sheets("Store Data")
lr2 = ws.Cells(Rows.Count, "B").End(xlUp).Row
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("B" & lr2).Value & " " & ws.Range("C" & lr2).Value
         End If
    Next r
End With
Application.CutCopyMode = False
End Sub

 

[ExcelAtEverything]

Wow! Ok I didn't realize that's what it was doing. ok makes sense now. Yeah, I'll kill the rest of the table on the Store Data sheet (the prior store entries), butyes an added action to clear the contents woud be great, and maybe even to set the cursor to land back on the MOR sheet at the end would be great (if that's not too much to ask).

Man, if I had known that you were able to make it work this way, I would have not even had a Store Entry tab, and instead I would have just had a few extra rows at the top of the MOR sheet in which to have those "store entry" boxes.. But hindsight is always 20/20 I guess.

 

[Michael M]

Ok, if you want to clear the cells on Store Data use this method, so the table is emptied after a new shop is entered
If you want to get rid of the "Store Data" sheet, let me knw where the new cells will be and I'll adjust the code to suit

Sub MM1()
Dim lr As Long, r As Long, ws As Worksheet, lr2 As Long
Set ws = Sheets("Store Data")
lr2 = ws.Cells(Rows.Count, "B").End(xlUp).Row
ws.Range("B" & lr2 & ":C" & lr2).ClearContents
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("B" & lr2).Value & " " & ws.Range("C" & lr2).Value
         End If
    Next r
End With
ws.Range("B" & lr2 & ":C" & lr2).ClearContents
Application.CutCopyMode = False
End Sub

 

[ExcelAtEverything]

Yes!!!!!! That last one you sent 100% did the trick! That is exactly what I was trying to have happen. Michael M, you are the MAN! No need to change anything else. Thank you SO MUCH as I feel like you've gone far above & beyond on this. Also not sure what happened to Diddi, but thank you for your input as well! This is great!

 

[ExcelAtEverything]

No, I decided to go with your prior option use a new row in "Store Data" each time. I marked it as the solution. But this new info is great, as I can look at that code to help me learn what it does exactly. You're the MAN! Thanks so, so much. WHEW!?

 

[Michael M]

Glad WE could help...

 

[ExcelAtEverything]

Me too!?