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!
 

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows
Ok wait... wait... hold on. I think have to stop this whole thing because I am discovering that the macro is not working as I thought it would. I thought I could sort of manipulate the macro system a bit, but I guess I cannot. I thought that I was recording the macro in such a way that it would not place cell references into the macro, but rather just refer to them as "SEECTION". Since it is instead using cell references in many place ("B4". "C6", etc), it defeats the purpose. I was trying to use the macro to insert and extend rows. It works sometimes, but other ties it does not.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,300
Office Version
  1. 2010
Platform
  1. Windows
could you show me the Add new store macro pls. we may be able to remove the undo macro and solve all your problems
 

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows
BTW Michael M, is that "Private Sub CommandButton1_Click() End Sub" text what I will always see when I open a new module? Just want to know for when I do these in the future.
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,164
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
I'm confused.....which isn't hard to do, but The code I provided does nothing but show a MsgBox.
It doesn't affect any cells or layouts.....are you trying to incorporate this macro into some other code you have ??
NO, you may not see those lines in the module, but you have a command button already on the sheet, so the module thinks you want to use it !!
 

ExcelAtEverything

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

ADVERTISEMENT

Diddi & Michael M (& Fluff!),
I had a couple of different threads going here on Mr Excel attempting to try different workarounds, and this question about launching a script when opening the tab is now a non-working workaround. You all gave me great advise of how to do it, but the very reason that I needed it to begin with is now null & void (because the macro that I created which I needed the script to warn about... is not working as I planned. BUT... I've finally had the "come to Jesus" talk with myself about not being so resistant to wading into the deep & unfamiliar waters of VB, and so I'm over it now. In fact, it seems like perhaps just adding some code could turn a weeklong hand-banging task into a simple copy/paste action which resolves everything. So I'm in! (if someone can help me)

To put it as simple as I can, here is the current situation. My thought was that when a new store is grand-opened (which will not necessarily keep running in the same numeric order which follows the store 101, store 102, store 103 pattern), the sheet user would add a new row to the bottom of the Data tab, which would automatically create the same additional new row at the bottom of the Yest/MTD/YTD tabs of the MOR tab). The user would then enter the new store ID info into the new row of the data tab, which would of course appear then in each of the 3 corresponding MOR tab ranges. But going the 'adding code' route seems like it will just make my life tremendously easier, and eliminate the need for all of these failsafe measures & confusing workarounds I've been attempting... which seem to create as many problems as they solve.

This is my code for the macro to create a total of 4 new rows in 2 separate tabs. The code was recorded in, and definitely has some noise in it. While strangely it worked perfectly at times, about 65% of the time it was adding and taking away rows from the wrong places. How can I just make this happen?

Here is the code, as well as 2 current screenshots of the 2 relevant sheets in the workbook.

Sub AddNewRow()
'
' AddNewRow Macro
' This macro first adds a new row to the bottom of Location Master List below (to allow user to enter a new store), then the macro inserts a corresponding new row at on the MOR sheet at the bottom of each (Yest/MTD/YTD) table.
' The MOR tab portion was accomplished by selecting cell D5 to start, then CTRL-click-DOWN ARROW to skip to next sections, and CTRL-SHIFT-RIGHT ARROW to highlight the rows and copy, then arrowing down to the newly created cell and pasting to get all the formatting and everything. The macro will not work if I try to select the whole row, so I had to select the entire length of the table using this method.

'
Range("B3").Select
Selection.End(xlDown).Select
Range("B9:C9").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("B10:C10").Select
Selection.Copy
Range("B9").Select
ActiveSheet.Paste
Range("B10:C10").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("C10").Select
Sheets("MOR").Select
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Sheets("MOR").Select
Range("D5").Select
Selection.End(xlDown).Select
Range("D10").Select
Selection.Copy
Range("D11").Select
ActiveSheet.Paste
Range("D11:S11").Select
Application.CutCopyMode = False
Selection.Copy
Range("D12:S12").Select
Selection.Insert Shift:=xlDown
Range("D11").Select
Application.CutCopyMode = False
Selection.Copy
Range("D12").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range("D21").Select
Application.CutCopyMode = False
Selection.Copy
Range("D22").Select
ActiveSheet.Paste
Range("D22:S22").Select
Application.CutCopyMode = False
Selection.Copy
Range("D23:S23").Select
Selection.Insert Shift:=xlDown
Range("D22").Select
Application.CutCopyMode = False
Selection.Copy
Range("D23").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range("D32").Select
Application.CutCopyMode = False
Selection.Copy
Range("D33").Select
ActiveSheet.Paste
Range("D33:S33").Select
Application.CutCopyMode = False
Selection.Copy
Range("D34:S34").Select
Selection.Insert Shift:=xlDown
Range("D33").Select
Application.CutCopyMode = False
Selection.Copy
Range("D34").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWindow.LargeScroll Down:=-1
Range("E6").Select
Sheets("Data").Select
Range("B10").Select
ActiveCell.FormulaR1C1 = ""
Range("B10").Select
ActiveCell.FormulaR1C1 = "107"
Range("B11").Select
Sheets("MOR").Select
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Sheets("Data").Select
Range("B3").Select
Selection.End(xlDown).Select
Selection.ClearContents

End Sub
 
Last edited by a moderator:

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows
I can find lots of code to insert rows in various ways, but can't seem to find any that show how to do it if the sheet is set up the way mine is (in that I have 3 ranges (Yest/MTD/YTD) stacked on top of one another, and as the sheet grows and more and more stores are added, the code would need to repeatedly look for the correct place to put the new row (which would always be 'at the very bottom' of the list of stores) in each of the 3 ranges.
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,300
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

recorded macros can be "problematic" but they are at least a start in terms of seeing what VBA looks like.. i can have a better look in a couple of hours if you happen to be around to ask/answer questions so i have a good understanding of the task at hand. you might pick up some tips at the same time
 

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows
Diddi, you have an "XL" on your chest with a cape on right now! Thank you! 🙏
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,300
Office Version
  1. 2010
Platform
  1. Windows
ok i have adjusted my cape and popped into a phone box to get changed... hope i live up to the expectations
 

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
300
Office Version
  1. 2019
Platform
  1. Windows
Lmoa! Thanks Diddi! Unfortunately I've been up for about 32 hours straight, and now I'm about to hit the sack. I'll be offline until about 3pm-ish EST while I sleep, eat, & wash. Sorry bro. But definitely shoot me any questions you have and I'll get to them immediately upon my return.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,824
Messages
5,638,540
Members
417,033
Latest member
JKThai

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