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€$

 

[PWD]

Hi guys, I've got this in the Module "ThisWorkbook":-

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

If UserFormExclusions.Visible = True Then
UserFormExclusions.Hide
End If

If ActiveSheet.Name = "Exclusions report" Then
UserFormExclusions.Show
UserFormExclusions.Label1.Caption = "The sample exclusions report provides a detailed account of all the sample records excluded during the period to which the sample report refers."

ElseIf ActiveSheet.Name = "Wrong telephone number report" Then
UserFormExclusions.Show

UserFormExclusions.Label1.Caption = _
"The wrong telephone numbers report provides a detailed account of all the issued sample records with an invalid telephone number (either not dialling or wrong numbers) "

End If

End Sub

I have resized the label so it fills the form, but can't get it to be smart enough to resize according to the text. Would that be possible?

Oh, also would it be possible to hide the Form if it loses focus, e.g. some other part of the sheet is clicked - for whatever reason? Otherwise it closes fine.

It appears to be doing what I'd hoped for.

Many thanks,
D€$

 

[SkipVought]

Check out the AutoSize property of the Lable object.

Skip,

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

 

[PWD]

Hi Skip. Sorry, I should have said that I'd tried that (I have been on holiday in the meantime ) & it appears to revise the size of the label to the height & width necessary to accomodate the current caption and only autosizes the height when the form is opened.

Many thanks,
D€$

 

[SkipVought]

It holds the Width and varies the Height.

Otherwise what would you expect it to do? Vary both? HOW?

Why is that a problem???

Skip,

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

 

[PWD]

I want it to be magic!! At the moment the Form & Label look like this:-

__________________________________
| _______ |
| | Label | |
| ------- |
| |
| |
| |
|__________________________________|

once I select AutoSize

Before I do this I had stretched the label to fill the form until the text looked right - it filled across & down. I suppose I should modify the current caption until it stretches across to the right all the way.

Many thanks,
D€$

 

[PWD]

Ah, but what about resizing the actual Form? Grrrr.......

Many thanks,
D€$

 

[SkipVought]

Resizing to WHAT?

Start with your form and list box sized as they are.

Pick some text, like

Text1: "Now is the time for all good men to come to the aid of their country."

Text2: "Cowards die many times before their deaths; The valiant never taste of death but once. Of all the wonders that I yet have heard, It seems to me most strange that men should fear; Seeing that death, a necessary end, Will come when it will come."

Now walk thru YOUR LOGIC for setting the LABEL size.

Please post your LOGIC here, when you are done.

Skip,

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

 

[PWD]

Logic? Phew! Dangerous.

OK. The Form is:-
257.25 wide
197.25 high.

The label is:-
246 wide
24 high
and is set to Autosize = "True"

The BackColor is the same for both. If I change the label BackColor I can see that the height changes according to the text.

So I've now put in:-

UserFormExclusions.Show

UserFormExclusions.Label1.Caption = "The sample exclusions report provides a detailed account of all the sample records excluded during the period to which the sample report refers."
[b]
UserFormExclusions.Height = UserFormExclusions.Label1.Height + 25
[/b]

That seems to do the trick

Many thanks,
D€$

 

[PWD]

FYI, here is my 'final' code:-

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

If UserFormExclusions.Visible = True Then
UserFormExclusions.Hide
End If

If ActiveSheet.Name = "Exclusions report" Or ActiveSheet.Name = "Wrong telephone number report" Then

UserFormExclusions.Show

        Select Case ActiveSheet.Name
         Case "Exclusions report"
         UserFormExclusions.Label1.Caption = "The sample exclusions report provides a detailed account of all the sample records excluded during the period to which the sample report refers."
        
         Case "Wrong telephone number report"
            UserFormExclusions.Label1.Caption = _
            "The wrong telephone numbers report provides a detailed account of all the issued sample records with an invalid telephone number (either not dialling or wrong numbers) "
        End Select
        
UserFormExclusions.Label1.TextAlign = 2 'Centred
UserFormExclusions.Height = UserFormExclusions.Label1.Height + 25
        
End If 'If ActiveSheet.Name = "Exclusions report" Or ActiveSheet.Name = "Wrong telephone number report" Then

End Sub

I'm sure it's far from perfect but hey!

Many thanks,
D€$

 

[SkipVought]

Logic? Phew! Dangerous.

???

LOGIC is the basis for developing ANY good solution, computer code or simply a plan to get from A to B in one piece.

I hope that that was said in jest.

Skip,

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

 

[PWD]

In typical British fashion! Thanks for all your help; I sometimes need a prod in the right direction, or a fresh start on a new day to help me think round a solution.

Many thanks,
D€$

 

[SkipVought]

Stiff upper lip, and all that, lads.


Skip,

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