I would like to "lock" a page using a macro/button that says "Submit and
Lock", but I would like to give to display a message first that says "You
will not be able to make any more changes to this page. Are you sure you
want to proceed?" Then I would like for the user to be able to pick "Yes" or
"No" and suspend the macro if the user says "No."

How would I do this using code in an Excel macro?

Thanks in advance for your help!

If InputBox(......) = "No" then
Exit Sub
End If

--
Best wishes,

Jim


"VMcD" wrote:

I would like to "lock" a page using a macro/button that says "Submit and
Lock", but I would like to give to display a message first that says "You
will not be able to make any more changes to this page. Are you sure you
want to proceed?" Then I would like for the user to be able to pick "Yes" or
"No" and suspend the macro if the user says "No."

How would I do this using code in an Excel macro?

Thanks in advance for your help!

here is the Yes/No code...

if msgbox("You will not be able to make any " & _
"more changes to this page. Are you " & _
"sure you want to proceed?", VbYesNo) = VbNo then
exit sub
end if
--
HTH...

Jim Thomlinson


"VMcD" wrote:

I would like to "lock" a page using a macro/button that says "Submit and
Lock", but I would like to give to display a message first that says "You
will not be able to make any more changes to this page. Are you sure you
want to proceed?" Then I would like for the user to be able to pick "Yes" or
"No" and suspend the macro if the user says "No."

How would I do this using code in an Excel macro?

Thanks in advance for your help!

Try pointing your macro button at these!
Regards,
Simon.

sub lockcells()
if msgbox(\"you will not be able to make any more changes to this page
are you sure you want to proceed?\", vbyesno, \"confirm decision\")
vbno then
exit sub
cells.select
selection.locked = true
selection.formulahidden = false
range(\"a1\").select
activesheet.protect drawingobjects:=true, contents:=true
scenarios:=true
activesheet.enableselection = xlnoselection
end sub

sub unlockcells()
if msgbox(\"you are about to unlock cells. are you sure you want t
proceed?\", vbyesno, \"confirm decision\") = vbno then
exit sub
activesheet.unprotect
cells.select
selection.locked = false
selection.formulahidden = false
range(\"a1\").select
end su

--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=57142

That's EXACTLY what I needed! Thanks so much for your quick reply.

"Simon Lloyd" wrote:


Try pointing your macro button at these!
Regards,
Simon.

sub lockcells()
if msgbox(\"you will not be able to make any more changes to this page.
are you sure you want to proceed?\", vbyesno, \"confirm decision\") =
vbno then
exit sub
cells.select
selection.locked = true
selection.formulahidden = false
range(\"a1\").select
activesheet.protect drawingobjects:=true, contents:=true,
scenarios:=true
activesheet.enableselection = xlnoselection
end sub

sub unlockcells()
if msgbox(\"you are about to unlock cells. are you sure you want to
proceed?\", vbyesno, \"confirm decision\") = vbno then
exit sub
activesheet.unprotect
cells.select
selection.locked = false
selection.formulahidden = false
range(\"a1\").select
end sub


--
Simon Lloyd
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
View this thread: http://www.excelforum.com/showthread...hreadid=571422

I would use a "yes no" msgbox to do this.
If MsgBox("You will not be able to make any more changes to this page."
_
& "Are you sure you want to proceed?", vbYesNo, "Locking
Workbook...") _
= vbYes Then
MsgBox "User clicked yes"
Else
MsgBox "User clicked no"
End If

Charles
VMcD wrote:
I would like to "lock" a page using a macro/button that says "Submit and
Lock", but I would like to give to display a message first that says "You
will not be able to make any more changes to this page. Are you sure you
want to proceed?" Then I would like for the user to be able to pick "Yes" or
"No" and suspend the macro if the user says "No."

How would I do this using code in an Excel macro?

Thanks in advance for your help!