Launch Script When Tab Is Opened

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
336
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!
 

ExcelAtEverything

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

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

ExcelAtEverything

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

BatCoder
Joined
Feb 28, 2002
Messages
1,584
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

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

BatCoder
Joined
Feb 28, 2002
Messages
1,584

ADVERTISEMENT

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

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
If there is only going to be 1 line in the Store Data table, try
VBA Code:
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

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

ADVERTISEMENT

1614580389802.png
 

Michael M

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

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Also adjusted the code to format the inserted row
VBA Code:
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

Active Member
Joined
Jan 30, 2021
Messages
336
Office Version
  1. 2019
Platform
  1. Windows
Wow! Michael M, this works beautifully! There is just one lingering issue.Instead of copying the concatenate formulas down, its pulling the values.
 

Forum statistics

Threads
1,148,334
Messages
5,746,154
Members
423,994
Latest member
blzxatly

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