Excel 2010 message when sheets are selected 1

[PWD]

Good morning, I have been asked to provide something that will pop-up when any particular sheet is selected - something that will explain the contents & purpose of the data on that sheet.

There are hyperlinks to some of these sheets & I wondered if there was anything that could be piggy-backed to them. It has been suggested that I could use a Workbook_SheetActivate event. I have found this code as a starting point

Sub Workbook_SheetActivate(ByVal Sh As Object)
    MsgBox Sh.Name
End Sub

but, unfortunately, I'm pretty clueless when it come to accessing & using Functions so I'm still stuck. In a way I think that some sort of 'Comment' might be more appropriate so the user could also refer to it once they were on the sheet & had 'dismissed' it after it intitially appeared.

Any thoughts gratefully accepted.

Many thanks,
D€$

 

[combo]

You could display modeless userform to display information. If necessary to divide it, use multipage control or a combobox (topics) together with label (descriptions).

If it is possible to use macro button on the sheet, the information can be stored in a text field or a shape, the button will change the visibility of the object.

Sometimes a good no-coding alternative is a messaging in data validation (entry message, in case of selecting specific cells). Still there are comments, they can be displayed when the mouse is above cell, they can be formatted as regular shapes/autoshapes.

combo

 

[UnsolvedCoding]

Or try do something that if the active sheet name changes a message box pops up.

 

[SkipVought]

This can also be a very annoying user defined feature. You also need a way to PERMINENTLY shut it off for specific users.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

OK, I've created a Form named "UserFormExclusions" and code to display it:-

Sub ShowMyForm()
UserFormExclusions.Show
End Sub

Which is a lot further than I was earlier today.

What I'd then need would be code to react when a different sheet were selected/activated from the existing hyperlinks.

I've tried just assigning that macro to the hyperlink but it doesn't fire up the form (ShowModal is False), neither on the sheet with the hyperlink nor the intended target sheet.

Even if I get that bit sorted out, my user would still (ideally) want the form to pop-up without a hyperlink being used.

Many thanks,
D€$

 

[SkipVought]

WHY?

Why would you not pop-up the form in the sheet activate event??? Use a TIMER to control how long the form displays and then is dismissed.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Hi Skip, I was going to post this extra info anyway:-

The hyperlink is on a button & I then assigned a macro to that button. I put a pause in the code & I found out that the code isn't being fired. So what I've done is remove the hyperlink & added

Sheets("Exclusions report").Activate

to the top of the code.

Sub ShowMyForm()
Sheets("Exclusions report").Activate
UserFormExclusions.Show
End Sub

To answer, basically I don't know how to use the sheet activate event. Once I can find that out I think I could then try to refine it with selecting the appropriate form for each activated sheet. Like I said, I'm still pretty green when it comes to using functions.

Many thanks,
D€$

 

[SkipVought]

I'd use ONE FORM and load a Label with the description, from a table, related to the sheet that is activated.

Sub Workbook_SheetActivate(ByVal Sh As Object)
    ufmSheetSplash.Show
End Sub

where form ufmSheetSpalsh in the Activate event grabs the boilerplate verbage for the ActiveSheet and assigns it to the Label.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Sounds really straightforward - and I guess it will be when I figure it all out.
At the moment on the Form I have a Text Box but I can also try a Label. I can decide the Text/Caption with

If ActiveSheet.Name = "Exclusions report" Then

Following your suggestion, I have now created a Table (a Named Range called "Words") on another sheet with 2 columns, Sheet Name & Desired Text; but again, I'm unsure of how to use that. I "know" that I would probably have to loop down the first column until I found the desired sheet name & then use the text from the column next to it but the exact syntax eludes me.

Finally I've still no idea how to use this:-

Sub Workbook_SheetActivate(ByVal Sh As Object)

I apologise for appearing to be thick, but it's not intentional! I REALLY want to get this cracked.

Many thanks,
D€$

 

[PWD]

OK, I've got this:-

    Application.Goto Reference:="Words"
    Selection.Find(What:="Exclusions report", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
        
        MyText = ActiveCell.Offset(0, 1).Value

That will get my wording.

Many thanks,
D€$

 

[SkipVought]

I posted exactly what you put in the workbook_sheetactivate event: ONE STATEMENT

Asuming that you have a table in you workbook like, using NAMED RANGES...
[tt]
SheetName Verbage
Sheet1 Now is the time for all good men...
Sheet2 Cowards die many time before their death...
Sheet3 Reference Table
[/tt]


In the userform ACTIVATE event

    Dim xl As Application
    
    Set xl = Application
    
    Label1.Caption = xl.Index([Verbage], xl.Match(ActiveSheet.Name, [SheetName], 0), 1)
    
    Set xl = Nothing

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

It's after 5 so I'll be finishing for the day; my BASIC problem is I don't know HOW to trigger the workbook_sheetactivate event. Once I crack that, I'll feel I've got a bit further. I'll be back tomorrow.

Many thanks,
D€$

 

[SkipVought]

I don't know HOW to trigger the workbook_sheetactivate event.

When you....

^{drum roll........}

[highlight][red] SELECT A SHEET!!![/red][/highlight] Answering the implied question, "Excel 2010 message when sheets are selected".

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

This realization should open a whole new panaroma of possibilities.

In the workbook are all sorts of events that can be used to trigger stuff that you might want to happen before or after that event occurs.

Events like...
[tt]
Activate
Deactivate
Select
Change
[/tt]
just to name a few, depending on the OBJECT in question (like workbook, worksheet, userform for instance)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PWD]

Pfff. Well, as you might guess, I've been selecting sheets all day long and NOTHING HAPPENS! Hence my thinking that I was missing something. Grrrrrrrrr.

I've checked that none of the sheets are protected - in case that might make a difference.

It's all rather a mystery

I've tried putting the code in each sheet, again to no avail.

What am I doing wrong?

Many thanks,
D€$

 

[PWD]

Heck, I've just stumbled upon the fact that I need this code to be in the "ThisWorkbook" module. It's as if everyone assumes that we'd know this sort of thing. Phew, at least I'm not going nuts any more.

Many thanks,
D€$

 

[combo]

Fired events are handled by class/object modules. Workbook events are recognised and handled by related ThisWorkbook (if not renamed) module.
To avoid errors, select the module and let VBA complete the procedure frame:
- select object (in this case Workbook) from the list of objects (left side code module),
- select event (in this case SheetActivate) from the right list,
- delete default event procedure created if not used,
- write the code.
If there is an argument in the procedure, as 'Sh' in [tt]Sub Workbook_SheetActivate(ByVal Sh As Object)[/tt], you can use it as an extra information from the fired event (sheet activated in the example).

combo

 

[PWD]

Thanks guys, I've been doing some more work on this but I'll be on holiday next week so any more development will have to wait, I guess. combo, I'm unclear (no change there, then!) how to do as you suggest. I tried F2, View Object Browser & that gave me an idea of what's possible as far as coding is concerned, but not how to convert that into code. Sorry.

Many thanks,
D€$

 

[SkipVought]

Here's what happens when a sheet is activated, with respect to the suggested approch.
[tt]
1. As the sheet is selected the ThisWorkbook Workbook_SheetActivate event fires Loading the user form. That is ALL that the above posted code does.

2. As the user form is loaded the userform Activate event fires executing the 4 lines of code, which displays the verbage corresponding to the activesheet from the table on one of the sheets in the label on the userform.
[/tt]
This is an example of using event-driven coding.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[combo]

It's not crear for me what you know and what you have done so far. The object browser is only for wiewing properties, methods and events.
To get event code, when you are in Visual Basic Editor window:
1. display project explorer window,
2. find your vba project, if necessary expand the tree, you should see ThisWorkbook icon, sheets (worksheets, charts) icons and vba forms and modules,
3. either double click or right-click and select 'View Code' of ThisWorkbook module, you should see the standard code pane with two combo boxes at the top,
4. select object from the left (in this case ThisWorkbook) and next the event you need, you should get the structure of event procedure created. It is recognised only in the object's module.
In case of future problems let us know at which step of the above you are in.

combo