Msg Boxes

[Odyrus]

Good day,

I'm familiar with the code for creating a msg box however I want to limit the msg to a specific range and exclude sheet 1 and sheet 2 of my workbook.

I'm not sure how I accomplish this? Or, if when I add new sheets the msg will still appear (which is what I wish).

Does this go in the workbook portion of VB?

Thanks for any assistance!

[Bob Phillips]

What exactly do you mean by ... I want to limit the msg to a specific range and exclude sheet 1 and sheet 2 of my workbook?

[shrivallabha]

It will depend specifically on: when do you want the MsgBox to appear e.g. clicking on the specific range i.e. response to a specific user action?

You will need to use ThisWorkbook module.

[Odyrus]

Sorry for lack of specifics:

As I add new sheets to my workbook I'd like the range E12:AY44 to display a msg when any cell is clicked. There are 2 worksheets in my workbook which should be omitted from this msg box coding.

My thinking was that I'd have to add the code to ThisWorkbook section of my VB menu with an exclusion for those 2 sheets. I'm likely wrong.

Hope this is more specific.

Cheers!

[Bob Phillips]

[vba]

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Select Case Sh.Name

'<<<<<<<<<<<<< change sheet names in next line
Case "Sheet1", "Sheet2": ' do nothing

Case Else:

If Not Intersect(Target, Sh.Range("E12:AY44")) Is Nothing Then

MsgBox "Some text in here", vbOKOnly + vbInformation, "My title text"
End If
End Select
End Sub
[/vba]

This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

[Odyrus]

xld,

cheers!

[Odyrus]

I have just encountered a problem I didn't anticipate which, sadly, happens far to often.

The msg box works fine however I have a sort macro for the same range that unmerges and merges cells when new data is added. What happens now is that for every cell being unmerged (selected) it fires off that msg box code.

It's my hope there's an easy solution. Is it possible to make the msg box prompt a single event? That is to say, have it only appear once when a cell is clicked with in that range instead of multiple times?

I also have a macro that copies sheets, which fires the code. Is it possible to ignore it in this case?

Very much appreciative of your help!

[Bob Phillips]

I don't see this behaviour, I only get it the once.

[Odyrus]

Let me try to be more specific.

Range E12:AC12 is merged all the way down to E44:AC44. These 33 rows are divided into 4 categories. When the user enters a new milestone into one of the blank spaces within one of those 4 categories I have a sort macro that unmerges the cells in that section, sorts, then remerges. When the sort macro is used it fires the msg box multiple times.

Hope this makes sense...


I will try to post an example workbook (didn't realize I could do that..lol)



So the 2 issues I have are:
1. From my summary sheet, adding a new sheet fires the msg box. I'd like to avoid that.
2. Using the sort macro on the report sheets fires the macro. I'd like to avoid that also.