I think you're going to run into SOME trouble
working with groups like this
They're intended to HELP users with problems,
not be a source of free programming.
Let's break down my explanation:
"You are currently testing your "response" variable
for vbYes and vbNo"
Your macro loops through all sheets:
For I = 1 to Sheets.Count
Sheets(I).Activate
You then post a "Message Box" and store the response in a variable called
"response":
response = MsgBox("Do you want to protect this sheet?", vbYesNo)
You then TEST the value of the "response" variable to see if the value is vbYes
or vbNo:
If response = vbYes Then
...
ElseIf response = vbNo Then
...
End If
I suggested adding:
Else
exit for
into this test:
If response = vbYes Then
...
ElseIf response = vbNo Then
...
Else
Exit For
End If
Result included below...
Paul
________________________________
From: DHAMODHARAN KARTHIKEYAN <deekart...@gmail.com>
To: excel-macros@googlegroups.com
Sent: Thu, May 16, 2013 11:39:32 AM
Subject: Fwd: $$Excel-Macros$$ PROTECT EACH SHEET WITH MSG BOX.
Dear Friend,
First, I'd like to mention my thanks for your time and effort on my request.
Now, I need to say that I do not have knowledge at all in VBA Codes.
(Honestly, due to lack of knowledge, I could not understand your valid
suggestion on my requirement).
If I come across something useful, I have the habit of making it as a
collection,
as I have interest on subjects like Word, Excel (Macros), PP, Access,
Photoshop,
etc.
Thus, I posted the following codes for my use :
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- -
Sub PROTECT_EACH_SHEET()
' Loop through all sheets in the workbook
For i = 1 To Sheets.Count
' Activate each sheet in turn.
Sheets(i).Activate
response = MsgBox("Do you want to protect this sheet?", vbYesNo)
If response = vbYes Then
ActiveSheet.PROTECT , DrawingObjects:=True, _
Contents:=True, Scenarios:=True
ElseIf response = vbNo Then
MsgBox ("Sheet not protected")
Else
Exit for
End If
Next i
End Sub
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- -
If you can do the appropriate changes here itself, I am able to copy it and use.
Subject to your time, convenience and availability, you may do the needful.
With Regards,
DK.
---------- Forwarded message ----------
From: Paul Schreiner <schreiner_p...@att.net>
Date: Thu, May 16, 2013 at 8:45 PM
Subject: Re: $$Excel-Macros$$ PROTECT EACH SHEET WITH MSG BOX.
To: excel-macros@googlegroups.com
You are currently testing your "response" variable
for vbYes and vbNo
If you simply add:
Else
Exit for
then, whenever neither the Yes button, or the No button is pressed
(like when you hit "Escape" or "cancel" the window)
then the loop will exit.
Paul
-----------------------------------------
“Do all the good you can,
By all the means you can,
In all the ways you can,
In all the places you can,
At all the times you can,
To all the people you can,
As long as ever you can.” - John Wesley
-----------------------------------------
________________________________
From: DHAMODHARAN KARTHIKEYAN <deekart...@gmail.com>
To: excel-macros@googlegroups.com
Sent: Thu, May 16, 2013 10:10:53 AM
Subject: $$Excel-Macros$$ PROTECT EACH SHEET WITH MSG BOX.
Dear Friend(s),
SUB : PROTECT EACH SHEET WITH MSG BOX.
I have a request on the following VBA Codes (this, I have collected from our
group or somewhere, I do not remember) :
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- -
Sub PROTECT_EACH_SHEET()
' Loop through all sheets in the workbook
For i = 1 To Sheets.Count
' Activate each sheet in turn.
Sheets(i).Activate
response = MsgBox("Do you want to protect this sheet?", vbYesNo)
If response = vbYes Then
ActiveSheet.PROTECT , DrawingObjects:=True, _
Contents:=True, Scenarios:=True
ElseIf response = vbNo Then
MsgBox ("Sheet not protected")
End If
Next i
End Sub
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- -
I expect the existing VBA Codes to further improve like ‘when I press ESC key,
it should stop, as the existing one goes till it reaches the last sheet’.
As I have more than 50 sheets (having the collection of EXCEL MACROS VBA CODES)
in my file, I look for STOP, if ESC key is pressed.
Subject to your time, convenience and availability, you may do the needful.
With Regards,DK.--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members
are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members
are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members
are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
