Launch Script When Tab Is Opened

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,164
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
But why do you need the formula...if the code joins the 2 cells for you
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows
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?

1614644010368.png
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,164
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,164
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
If you are going to use a new row in "Store Data" each time, try this code
VBA 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
 
Solution

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Oct 27, 2005
Messages
20,164
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
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
VBA 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
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

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Oct 27, 2005
Messages
20,164
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Glad WE could help... :cool: (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,835
Messages
5,638,621
Members
417,038
Latest member
VBAstudent1977

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top