Displaying a dialog box message or equivalent

  • Thread starter Thread starter Bobby
  • Start date Start date
B

Bobby

Hi,

I am trying to get a dialog box or any kind of message pop up or display
when I have a user click on a different tab of an Excel spreadsheet. So
let's say there are 3 sheets to the spreadsheet and when the user clicks on
sheet 2 they are given a message as such "Please do not change any of the
information on this sheet, Mark is responsible for all data on this page" OR
"Please see Mark if you want any information on this page edited/changed."
Basically just a message indicating to the user that this sheet is not to be
modified unless they check with another employee first. Does anyone know
how I can go about doing this?

Thanks in advance,
Bobby
 
Right-click the Excel LOGO (near the File menu), select View Code, enter
this:
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
MsgBox "whatever you want goes here"
End Sub
 
Bobby

Bob's code operates on all sheets in the workbook.

If you want just one specific sheet to pop up the message when selected you
can put the code behind just that one sheet.

Right-click on the sheet tab and "View Code". Paste this into that module.

Private Sub Worksheet_Activate()
MsgBox "whatever you want goes here"
End Sub


Gord Dibben Excel MVP
 
Good afternoon Bobby

This code will bring up a message whenever Sheet 2 is selected. It
utilises Event Procedures so need to be placed in the ThisWorkbook
module of the file. It's fairly easy to understand, so it shouldn't be
too hard to extend upon it to give different messages regarding
different sheets' ownership.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If ActiveSheet.Name = "Sheet2" Then
MsgBox "Please do not change any of the information on this sheet" & _
"Mark is responsible for all data on this page"
End If
End Sub

HTH

DominicB
 
Since workbook_sheetactivate has the sheet passed to it, you might as well use
it:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If lcase(sh.Name) = lcase("Sheet2") Then
MsgBox "Please do not change any of the information on this sheet" & _
"Mark is responsible for all data on this page"
End If
End Sub

(I compared the names using lcase()--just in case.)
 
hey guys thanks a lot! this stuff has been really informative and
helpful...that was real quick to. i definitely appreciate it.

once again thanks!

bobby
 
Back
Top