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

Events don't fire in design mode anyway.

NickHK

"Thulasiram" wrote in message
oups.com...
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

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 wrote:
Events don't fire in design mode anyway.

NickHK

"Thulasiram" wrote in message
oups.com...
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

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" wrote in message
ups.com...
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 wrote:
Events don't fire in design mode anyway.

NickHK

"Thulasiram" wrote in message
oups.com...
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

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..

NickHK wrote:
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" wrote in message
ups.com...
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 wrote:
Events don't fire in design mode anyway.

NickHK

"Thulasiram" wrote in message
oups.com...
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

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!

Thulasiram wrote:
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..

NickHK wrote:
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" wrote in message
ups.com...
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 wrote:
Events don't fire in design mode anyway.

NickHK

"Thulasiram" wrote in message
oups.com...
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

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

"Thulasiram" wrote in message
ups.com...
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!

Thulasiram wrote:
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..

NickHK wrote:
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" wrote in message
ups.com...
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 wrote:
Events don't fire in design mode anyway.

NickHK

"Thulasiram" wrote in message
oups.com...
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