Msgbox in worksheet activate event if design mode is turned off

[Thulasiram]

Hello all,

Code given below pops up a msgbox whenever any sheet in the workbook is
activated. How to tweak this code to make the msgbox to pop-up only if
design mode is turned off.

Private Sub Workbook_SheetActivate(ByVal sh As Object)
MsgBox "Please check if the design mode is turned off", vbInformation,
"Excel"
End Sub

 

[NickHK]

Events don't fire in design mode anyway.

NickHK

 

[Thulasiram]

Nick,

What you have said is true. I think I confused between design mode on
and off. Okay, here is the real situation:

For example, I double click an excel file (that contains macros). While
the file is loaded, design mode is turned off. If the user clicks any
sheet, then I want the msgbox to popup saying that "Please check if the
design mode is turned ON"

I require this because, I have lot of worksheet selection change
events. So, when the user enters any information, I would like to alert
the user by asking him to get into design mode. This will prevent
executing the macros..

Please guide in this issue...

 

[NickHK]

When a WB opens, it is not in design Mode.
That MsgBox will prove nothing, as it will only appear if the WB is not in
design mode.

Sounds like you toggle the value of Application.EnableEvents between
true/false, depending if you want the selection change event to fire or not.

NickHK

 

[Thulasiram]

Nick,

Let me go in your way and try to explain my situation..

When a WB opens, it is not in design Mode. - TRUE
That MsgBox will prove nothing, as it will only appear if the WB is not
in design mode - TRUE again

Okay, I open the WB => design mode is turned off and sheet 1 is
activated.

I click sheet2 and still design mode is off. Macros fire when the
design mode is off. I want the design mode to be turned on. So, now I
have two choices.
1. Write a macro to turn the design mode ON when a sheet is activated
by the user. (I dont know if this could be coded)
2. Else alert the user with a msgbox when the user activates a sheet..

I believe I have made myself clear.. Please provide your expertise.

Thanks..

 

[Thulasiram]

Okay! I understood the situation.. Just tried the exercise in the
workbook and I understood what you were saying Nick.

Thanks for your clarifications so far.. You are right!

 

[NickHK]

As you have probably found by now, there is no straight forward way of
forcing design mode in a macro, but events can easily be controlled.
Sometimes you need to look at the situation a different way.

Glad you got the desired result in the end.

NickHK