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!

 

[diddi]

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

 

[ExcelAtEverything]

I'm back

 

[Michael M]

What is the text in cells(D9, D16, D23) on the MOR sheet ?
It's too hard to read for me !

 

[ExcelAtEverything]

Hi Michael M!
Those cells each just say "Notes:". They are just areas where things can be hand-written after being printed.

 

[ExcelAtEverything]

Here is the MOR sheet in XL2BB.

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1
2				Report
3
4				Yesterday
5				Loc	GS$	GS Var	GS %OP	GM$	GM Var	GM %OP	GM%	IPT	AUP	ABS	TRS	AI$	AI%	R&E	ADJ
6				101 Loc 1
7				102 Loc 2
8				103 Loc 3
9				Notes:
10
11				Month To Date
12				Loc	GS$	GS Var	GS %OP	GM$	GM Var	GM %OP	GM%	IPT	AUP	ABS	TRS	AI$	AI%	R&E	ADJ
13				101 Loc 1
14				102 Loc 2
15				103 Loc 3
16				Notes:
17
18				Year To Date
19				Loc	GS$	GS Var	GS %OP	GM$	GM Var	GM %OP	GM%	IPT	AUP	ABS	TRS	AI$	AI%	R&E	ADJ
20				101 Loc 1
21				102 Loc 2
22				103 Loc 3
23				Notes:
24
25
MOR

Cell Formulas
Range	Formula
D6:D8	D6	=CONCATENATE('Store Data'!B4," ",'Store Data'!C4)
D13:D15	D13	=CONCATENATE('Store Data'!B4," ",'Store Data'!C4)
D20:D22	D20	=CONCATENATE('Store Data'!B4," ",'Store Data'!C4)

 

[ExcelAtEverything]

And here is the STORE DATA sheet (formerly the DATA sheet) in XL2BB...

Book1.xlsm
	A	B	C	D	E	F	G	H	I	J
1
2		Location Master List
3		Location #	Location Name	Org
4		101	Loc 1	237 ?
5		102	Loc 2	839
6		103	Loc 3	1193
7
8
9
10
11
12
13
14
15
16
17
18
Store Data

Cells with Data Validation
Cell	Allow	Criteria
B4:B6	Whole number	between 0 and 9999
D4:D6	Whole number	between 0 and 9999

 

[Michael M]

So, maybe something like this....UNTESTED !
EDIT:...Had to change cell references

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]

So wait... I put that into VB under the project title, and under "Sheet1 (MOR)"?

 

[Michael M]

Couple of mods required

EDIT:...Had to change cell references

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]

So... I put that into VB under the project title, and under "Sheet1 (MOR)"?