Launch Script When Tab Is Opened

ExcelAtEverything

Board Regular
Joined
Jan 30, 2021
Messages
232
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!
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,025
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
So something like....Placed in the sheet module that you want to activate
VBA Code:
Private Sub Worksheet_Activate()
MsgBox"This is a Highly Important Worksheet, Please Don't press Buttons that you don't Understand !!!"
   End Sub
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,263
Office Version
  1. 2010
Platform
  1. Windows
It is possible to manage your buttons so they are enabled or disabled. that way you can control the sequence in which users operate your code. it should not be written in such a way that a bad click causes the whole thing to fall over.
 

ExcelAtEverything

Board Regular
Joined
Jan 30, 2021
Messages
232
Office Version
  1. 2019
Platform
  1. Windows
Thanks Michael M.
I can't get that to work. Doesn't seem to do anything. This is the first time I've ever pasted code in from the internet, so bravo to me for jumping in the pool (finally) on the one hand, but on the other hand it's very possible I'm doing something incorrectly.
I'm in Excel 2019 Home/Student.
1) Opened VB on the Developer tab.
2) Right-clicked on the correct sheet that I want the module on, and inserted a new module.
3) Copy/pasted every bit of your code into the module.
4) Re-saved (it was already an XLSM sheet).
Did I make a mistake?
 

ExcelAtEverything

Board Regular
Joined
Jan 30, 2021
Messages
232
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Thanks Diddi! Yeah, I actually thought to myself as I was building those macros that it would be awesome if I could somehow just make it so that one button can't be pressed unless the other had been pressed. But I wasn't able to figure out how to do that. I can record macros just fine, and I can edit what I recoded based on just some common sense, but that's about the extent of my knowledge thus far. Pasting Michael M's code into a module was the first time I had ever even done that. I'd love some help with that.
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,263
Office Version
  1. 2010
Platform
  1. Windows
show me one of the macros you recorded
 

ExcelAtEverything

Board Regular
Joined
Jan 30, 2021
Messages
232
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

I tried to use XL2BB, but it looked really weird when I inserted it, so here is a screenshot, followed by one of the macros.

The following is the code for the UNDO macro. If someone were to press Undo first, it could delete a necessary row.

Sub UndoAddRowAction()
'
' UndoAddRowAction Macro
' This macro will undo the "Add Row" maco in case it was run by mistake.
'

'
Range("B3").Select
Selection.End(xlDown).Select
Range("B10:C10").Select
Selection.Delete Shift:=xlUp
Range("B2:C2").Select
Sheets("MOR").Select
Range("D5").Select
Selection.End(xlDown).Select
Range("D12:S12").Select
Selection.Delete Shift:=xlUp
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range("D22:S22").Select
Selection.Delete Shift:=xlUp
Selection.End(xlDown).Select
Selection.End(xlDown).Select
Range("D32:S32").Select
Selection.Delete Shift:=xlUp
ActiveWindow.LargeScroll Down:=-1
Range("E6").Select
Sheets("Data").Select
End Sub
 
Last edited by a moderator:

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,025
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
You dont need to create a new module
Simply R click on the sheet tab you want to acces...select View code..then paste the code i provided into the RH winfow
 

ExcelAtEverything

Board Regular
Joined
Jan 30, 2021
Messages
232
Office Version
  1. 2019
Platform
  1. Windows
Michael M, when I do that and R-click on the correct sheet, it brings this up. Do I delete this text first?
1614331073057.png
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
20,025
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows
Yep..delete and replace with the code I provided
 

Watch MrExcel Video

Forum statistics

Threads
1,127,062
Messages
5,622,466
Members
415,896
Latest member
Blooze

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