Launch Script When Tab Is Opened

ExcelAtEverything

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

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,297
Office Version
  1. 2010
Platform
  1. Windows
we can try another time... easier for me to solve it bit by bit and get confirmation that it is going in the right direction. often changes come up when the process starts to home in on possible implementation, esp as you are new to VBA
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,111
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
What is the text in cells(D9, D16, D23) on the MOR sheet ?
It's too hard to read for me !
 

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
294
Office Version
  1. 2019
Platform
  1. Windows
Hi Michael M!
Those cells each just say "Notes:". They are just areas where things can be hand-written after being printed.
 

ExcelAtEverything

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

ADVERTISEMENT

Here is the MOR sheet in XL2BB.

Book1.xlsm
ABCDEFGHIJKLMNOPQRST
1
2Report
3
4Yesterday
5LocGS$GS VarGS %OPGM$GM VarGM %OPGM%IPTAUPABSTRSAI$AI%R&EADJ
6101 Loc 1
7102 Loc 2
8103 Loc 3
9Notes:
10
11Month To Date
12LocGS$GS VarGS %OPGM$GM VarGM %OPGM%IPTAUPABSTRSAI$AI%R&EADJ
13101 Loc 1
14102 Loc 2
15103 Loc 3
16Notes:
17
18Year To Date
19LocGS$GS VarGS %OPGM$GM VarGM %OPGM%IPTAUPABSTRSAI$AI%R&EADJ
20101 Loc 1
21102 Loc 2
22103 Loc 3
23Notes:
24
25
MOR
Cell Formulas
RangeFormula
D6:D8D6=CONCATENATE('Store Data'!B4," ",'Store Data'!C4)
D13:D15D13=CONCATENATE('Store Data'!B4," ",'Store Data'!C4)
D20:D22D20=CONCATENATE('Store Data'!B4," ",'Store Data'!C4)
 
Last edited by a moderator:

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
294
Office Version
  1. 2019
Platform
  1. Windows
And here is the STORE DATA sheet (formerly the DATA sheet) in XL2BB...

Book1.xlsm
ABCDEFGHIJ
1
2Location Master List
3Location #Location NameOrg
4101Loc 1237 ?
5102Loc 2839
6103Loc 31193
7
8
9
10
11
12
13
14
15
16
17
18
Store Data
Cells with Data Validation
CellAllowCriteria
B4:B6Whole numberbetween 0 and 9999
D4:D6Whole numberbetween 0 and 9999
 
Last edited by a moderator:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,111
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

So, maybe something like this....UNTESTED !
EDIT:...Had to change cell references
VBA Code:
Sub MM1()
Dim lr As Long, r As Long
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).Resize(3).Insert
            .Sheets("Store Data".Range("B4:D6").Copy .Range("D" & r)
         End If
    Next r
End With
End Sub
 
Last edited:

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
294
Office Version
  1. 2019
Platform
  1. Windows
So wait... I put that into VB under the project title, and under "Sheet1 (MOR)"?
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,111
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Couple of mods required

EDIT:...Had to change cell references
VBA Code:
Sub MM1()
Dim lr As Long, r As Long
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).Resize(3).Insert
            .Sheets("Store Data").Range("B4:D6").Copy .Range("D" & r)
         End If
    Next r
End With
End Sub
 

ExcelAtEverything

Active Member
Joined
Jan 30, 2021
Messages
294
Office Version
  1. 2019
Platform
  1. Windows
So... I put that into VB under the project title, and under "Sheet1 (MOR)"?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,323
Messages
5,635,596
Members
416,868
Latest member
andiwd

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