Cancel of text box leaves sheet unprotected
 

Macro I am using unprotects my sheet so that it can run then uses a msg box
followed by an info box entry as a component of the save name for a new file.

Problem happens when user tells the msg box no, OR cancels out of the info
box, Sheet is left unprotected. I have not been able to resolve myself or
piece together code from your answers to other questions to stop this problem.

Code I have is:

Sub saveit()
ActiveSheet.Unprotect Password:="'"
Sheets("dms wizard").Select
ActiveSheet.Unprotect Password:="'"

response = MsgBox("Generate New System DMS?" _
, vbYesNo, "Save As")
If response = 6 Then
Name = InputBox("Enter System Name", "Path Will Be L:\E&T Pillar\KCS
Library\DMS\<Dept Name\<System Name.DMS")
If Name = "" Then End
ActiveWorkbook.SaveAs Filename:="L:\E&T Pillar\KCS Library\DMS\" &
Range("C8").Value & "\" & Name & ".DMS.xls"

Can you help me reprotect in the event of a "no" or a cancel?
--
THANKS!

Steve

Cancel of text box leaves sheet unprotected
 

Sub seeIfThishelps()
If MsgBox("Generate New System DMS?", _
vbYesNoCancel + vbQuestion, "Save As") < vbYes Then
MsgBox "This is what happens if user clicks no or cancel"
Exit Sub
End If
MsgBox "This is what happens if user clicks yes"

End Sub

"Steve_n_KC" wrote:

Macro I am using unprotects my sheet so that it can run then uses a msg box
followed by an info box entry as a component of the save name for a new file.

Problem happens when user tells the msg box no, OR cancels out of the info
box, Sheet is left unprotected. I have not been able to resolve myself or
piece together code from your answers to other questions to stop this problem.

Code I have is:

Sub saveit()
ActiveSheet.Unprotect Password:="'"
Sheets("dms wizard").Select
ActiveSheet.Unprotect Password:="'"

response = MsgBox("Generate New System DMS?" _
, vbYesNo, "Save As")
If response = 6 Then
Name = InputBox("Enter System Name", "Path Will Be L:\E&T Pillar\KCS
Library\DMS\<Dept Name\<System Name.DMS")
If Name = "" Then End
ActiveWorkbook.SaveAs Filename:="L:\E&T Pillar\KCS Library\DMS\" &
Range("C8").Value & "\" & Name & ".DMS.xls"

Can you help me reprotect in the event of a "no" or a cancel?
--
THANKS!

Steve

Cancel of text box leaves sheet unprotected
 

Thanks Mike! Code works great but I'm not quite smart enough to be able to
get it to do what I need.
If I add it in addition to existing code, it didn't work on NO/Cancel
selection of my Msg Box, but did give me a 2nd Msg Box that and I was able to
make it work if No/Cancel was chosen for that box.
I've tried about 50 variations of blending the 2 codes together with the
varying degrees of negative results...
The closest I got was with this:
sub saveit()
ActiveSheet.Unprotect Password:="'"
Sheets("dms wizard").Select
ActiveSheet.Unprotect Password:="'"

response = MsgBox("Generate New System DMS?" _
, vbYesNoCancel, "Save As")
If response = 6 Then
Name = InputBox("Enter System Name", "Path Will Be L:\E&T Pillar\KCS
Library\DMS\<Dept Name\<System Name.DMS")
If Name = "" Then End
If MsgBox("Generate New System DMS?", _
vbYesNoCancel + vbQuestion, "Save As") < vbYes Then
MsgBox "You Have Chosen to NOT Generate DMS-Make any desired Changes and
Select Green Button When Ready"
ActiveSheet.Protect Password:="'", DrawingObjects:=True, Contents:=True,
Scenarios:=True
Exit Sub
End If

Which as I said worked on the 2nd box but not the 1st. Any Ideas on what I
need to do to get my "Input box save" from Yes and an immediate Protect Sheet
and exit sub from No, Cancel or X'ing out???

--
THANKS AGAIN for the response!

Steve


"Mike" wrote:

Sub seeIfThishelps()
If MsgBox("Generate New System DMS?", _
vbYesNoCancel + vbQuestion, "Save As") < vbYes Then
MsgBox "This is what happens if user clicks no or cancel"
Exit Sub
End If
MsgBox "This is what happens if user clicks yes"

End Sub

"Steve_n_KC" wrote:

Macro I am using unprotects my sheet so that it can run then uses a msg box
followed by an info box entry as a component of the save name for a new file.

Problem happens when user tells the msg box no, OR cancels out of the info
box, Sheet is left unprotected. I have not been able to resolve myself or
piece together code from your answers to other questions to stop this problem.

Code I have is:

Sub saveit()
ActiveSheet.Unprotect Password:="'"
Sheets("dms wizard").Select
ActiveSheet.Unprotect Password:="'"

response = MsgBox("Generate New System DMS?" _
, vbYesNo, "Save As")
If response = 6 Then
Name = InputBox("Enter System Name", "Path Will Be L:\E&T Pillar\KCS
Library\DMS\<Dept Name\<System Name.DMS")
If Name = "" Then End
ActiveWorkbook.SaveAs Filename:="L:\E&T Pillar\KCS Library\DMS\" &
Range("C8").Value & "\" & Name & ".DMS.xls"

Can you help me reprotect in the event of a "no" or a cancel?
--
THANKS!

Steve