| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Warning Macro
I want to modify this macro so that before it runs, a message box will pop up
and ask are you sure you want to delete? and then have two options yes and no. If the operator clicks yes the contents will delete. If the operator clicks no the contents will not be deleted. I know how to get a message box to come up MsgBox "your message here", but I am not sure how to give it options. Here is the macro I have now. Sub ClearContentsMacro Macro () Application.Goto Reference:="R7C2" Range("B7:H116").Select Selection.ClearContents Selection.ClearContents Range("B2:D2").Select Selection.ClearContents Range("B3:D3").Select Selection.ClearContents Range("B7").Select End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Warning Macro
You can do something like this
Dim Verify as VBMsgBoxResult Verify = Msgbox("Do you want to delete?",vbyesno) if vbyes then 'Delete whatever you want to delete end if "MCheru" wrote: I want to modify this macro so that before it runs, a message box will pop up and ask are you sure you want to delete? and then have two options yes and no. If the operator clicks yes the contents will delete. If the operator clicks no the contents will not be deleted. I know how to get a message box to come up MsgBox "your message here", but I am not sure how to give it options. Here is the macro I have now. Sub ClearContentsMacro Macro () Application.Goto Reference:="R7C2" Range("B7:H116").Select Selection.ClearContents Selection.ClearContents Range("B2:D2").Select Selection.ClearContents Range("B3:D3").Select Selection.ClearContents Range("B7").Select End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Warning Macro
if vbyes then will always be true. Use if Verify = vbyes then Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 13 Mar 2009 07:52:06 -0700, Barb Reinhardt wrote: You can do something like this Dim Verify as VBMsgBoxResult Verify = Msgbox("Do you want to delete?",vbyesno) if vbyes then 'Delete whatever you want to delete end if "MCheru" wrote: I want to modify this macro so that before it runs, a message box will pop up and ask are you sure you want to delete? and then have two options yes and no. If the operator clicks yes the contents will delete. If the operator clicks no the contents will not be deleted. I know how to get a message box to come up MsgBox "your message here", but I am not sure how to give it options. Here is the macro I have now. Sub ClearContentsMacro Macro () Application.Goto Reference:="R7C2" Range("B7:H116").Select Selection.ClearContents Selection.ClearContents Range("B2:D2").Select Selection.ClearContents Range("B3:D3").Select Selection.ClearContents Range("B7").Select End Sub |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Warning Macro
Oops, that was a typo. Thanks for catching it.
"Chip Pearson" wrote: if vbyes then will always be true. Use if Verify = vbyes then Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 13 Mar 2009 07:52:06 -0700, Barb Reinhardt wrote: You can do something like this Dim Verify as VBMsgBoxResult Verify = Msgbox("Do you want to delete?",vbyesno) if vbyes then 'Delete whatever you want to delete end if "MCheru" wrote: I want to modify this macro so that before it runs, a message box will pop up and ask are you sure you want to delete? and then have two options yes and no. If the operator clicks yes the contents will delete. If the operator clicks no the contents will not be deleted. I know how to get a message box to come up MsgBox "your message here", but I am not sure how to give it options. Here is the macro I have now. Sub ClearContentsMacro Macro () Application.Goto Reference:="R7C2" Range("B7:H116").Select Selection.ClearContents Selection.ClearContents Range("B2:D2").Select Selection.ClearContents Range("B3:D3").Select Selection.ClearContents Range("B7").Select End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Warning Macro
Thank you. You're code is great. I am having one problem. Perhaps I made a
mistake. Here is the code I am using. Sub ClearContentsMacro() Dim Verify As VbMsgBoxResult Verify = MsgBox("Do you want to delete?", vbYesNo) If Verify = vbYes Then End If Application.Goto Reference:="R7C2" Range("B7:H116").Select Selection.ClearContents Selection.ClearContents Range("B2:D2").Select Selection.ClearContents Range("B3:D3").Select Selection.ClearContents Range("B7").Select End Sub .......The challenge I am having is that even when I click No all the cells are still getting deleted. "Barb Reinhardt" wrote: You can do something like this Dim Verify as VBMsgBoxResult Verify = Msgbox("Do you want to delete?",vbyesno) if vbyes then 'Delete whatever you want to delete end if "MCheru" wrote: I want to modify this macro so that before it runs, a message box will pop up and ask are you sure you want to delete? and then have two options yes and no. If the operator clicks yes the contents will delete. If the operator clicks no the contents will not be deleted. I know how to get a message box to come up MsgBox "your message here", but I am not sure how to give it options. Here is the macro I have now. Sub ClearContentsMacro Macro () Application.Goto Reference:="R7C2" Range("B7:H116").Select Selection.ClearContents Selection.ClearContents Range("B2:D2").Select Selection.ClearContents Range("B3:D3").Select Selection.ClearContents Range("B7").Select End Sub |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Warning Macro
Thank you for helping out!
"Chip Pearson" wrote: if vbyes then will always be true. Use if Verify = vbyes then Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 13 Mar 2009 07:52:06 -0700, Barb Reinhardt wrote: You can do something like this Dim Verify as VBMsgBoxResult Verify = Msgbox("Do you want to delete?",vbyesno) if vbyes then 'Delete whatever you want to delete end if "MCheru" wrote: I want to modify this macro so that before it runs, a message box will pop up and ask are you sure you want to delete? and then have two options yes and no. If the operator clicks yes the contents will delete. If the operator clicks no the contents will not be deleted. I know how to get a message box to come up MsgBox "your message here", but I am not sure how to give it options. Here is the macro I have now. Sub ClearContentsMacro Macro () Application.Goto Reference:="R7C2" Range("B7:H116").Select Selection.ClearContents Selection.ClearContents Range("B2:D2").Select Selection.ClearContents Range("B3:D3").Select Selection.ClearContents Range("B7").Select End Sub |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Warning Macro
Thank you! You're code is great. I am having one problem. Perhaps I made a
mistake. Here is the code I am using. Sub ClearContentsMacro() Dim Verify As VbMsgBoxResult Verify = MsgBox("Do you want to delete?", vbYesNo) If Verify = vbYes Then End If Application.Goto Reference:="R7C2" Range("B7:H116").Select Selection.ClearContents Selection.ClearContents Range("B2:D2").Select Selection.ClearContents Range("B3:D3").Select Selection.ClearContents Range("B7").Select End Sub .......The challenge I am having is that even when I click No all the cells are still getting deleted. "Barb Reinhardt" wrote: Oops, that was a typo. Thanks for catching it. "Chip Pearson" wrote: if vbyes then will always be true. Use if Verify = vbyes then Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 13 Mar 2009 07:52:06 -0700, Barb Reinhardt wrote: You can do something like this Dim Verify as VBMsgBoxResult Verify = Msgbox("Do you want to delete?",vbyesno) if vbyes then 'Delete whatever you want to delete end if "MCheru" wrote: I want to modify this macro so that before it runs, a message box will pop up and ask are you sure you want to delete? and then have two options yes and no. If the operator clicks yes the contents will delete. If the operator clicks no the contents will not be deleted. I know how to get a message box to come up MsgBox "your message here", but I am not sure how to give it options. Here is the macro I have now. Sub ClearContentsMacro Macro () Application.Goto Reference:="R7C2" Range("B7:H116").Select Selection.ClearContents Selection.ClearContents Range("B2:D2").Select Selection.ClearContents Range("B3:D3").Select Selection.ClearContents Range("B7").Select End Sub |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Warning Macro
Sub ClearContentsMacro()
Dim Verify As VbMsgBoxResult Verify = MsgBox("Do you want to delete?", vbYesNo) If Verify = vbYes Then Application.Goto Reference:="R7C2" Range("B7:H116").Select Selection.ClearContents Selection.ClearContents Range("B2:D2").Select Selection.ClearContents Range("B3:D3").Select Selection.ClearContents Range("B7").Select End If End Sub You could avoid the .select's with something like: Sub ClearContentsMacro2() Dim Verify As Long 'VbMsgBoxResult doesn't work in earlier versions Verify = MsgBox("Do you want to delete?", vbYesNo) If Verify = vbYes Then Range("B7:H116").ClearContents Range("B2:D2").ClearContents Range("B3:D3").ClearContents 'Range("B7").Select 'did you really want to select B7 End If End Sub And you could even boil those 3 range().clearcontents to just one: If Verify = vbYes Then Range("B7:H116,B2:d3").ClearContents End If MCheru wrote: Thank you! You're code is great. I am having one problem. Perhaps I made a mistake. Here is the code I am using. Sub ClearContentsMacro() Dim Verify As VbMsgBoxResult Verify = MsgBox("Do you want to delete?", vbYesNo) If Verify = vbYes Then End If Application.Goto Reference:="R7C2" Range("B7:H116").Select Selection.ClearContents Selection.ClearContents Range("B2:D2").Select Selection.ClearContents Range("B3:D3").Select Selection.ClearContents Range("B7").Select End Sub ......The challenge I am having is that even when I click No all the cells are still getting deleted. "Barb Reinhardt" wrote: Oops, that was a typo. Thanks for catching it. "Chip Pearson" wrote: if vbyes then will always be true. Use if Verify = vbyes then Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 13 Mar 2009 07:52:06 -0700, Barb Reinhardt wrote: You can do something like this Dim Verify as VBMsgBoxResult Verify = Msgbox("Do you want to delete?",vbyesno) if vbyes then 'Delete whatever you want to delete end if "MCheru" wrote: I want to modify this macro so that before it runs, a message box will pop up and ask are you sure you want to delete? and then have two options yes and no. If the operator clicks yes the contents will delete. If the operator clicks no the contents will not be deleted. I know how to get a message box to come up MsgBox "your message here", but I am not sure how to give it options. Here is the macro I have now. Sub ClearContentsMacro Macro () Application.Goto Reference:="R7C2" Range("B7:H116").Select Selection.ClearContents Selection.ClearContents Range("B2:D2").Select Selection.ClearContents Range("B3:D3").Select Selection.ClearContents Range("B7").Select End Sub -- Dave Peterson |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Warning Macro
The problem is that there is nothing between the "If Verify..." and
the "End If". The code tests Verify but takes no action, regardless of the value of Verify. Use code like the following. If Verify = vbYes Then Range("B2:H116").ClearContents End If ' OR use If Verify = vbNo Then Exit Sub End If Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 13 Mar 2009 16:47:01 -0700, MCheru wrote: Thank you. You're code is great. I am having one problem. Perhaps I made a mistake. Here is the code I am using. Sub ClearContentsMacro() Dim Verify As VbMsgBoxResult Verify = MsgBox("Do you want to delete?", vbYesNo) If Verify = vbYes Then End If Application.Goto Reference:="R7C2" Range("B7:H116").Select Selection.ClearContents Selection.ClearContents Range("B2:D2").Select Selection.ClearContents Range("B3:D3").Select Selection.ClearContents Range("B7").Select End Sub ......The challenge I am having is that even when I click No all the cells are still getting deleted. "Barb Reinhardt" wrote: You can do something like this Dim Verify as VBMsgBoxResult Verify = Msgbox("Do you want to delete?",vbyesno) if vbyes then 'Delete whatever you want to delete end if "MCheru" wrote: I want to modify this macro so that before it runs, a message box will pop up and ask are you sure you want to delete? and then have two options yes and no. If the operator clicks yes the contents will delete. If the operator clicks no the contents will not be deleted. I know how to get a message box to come up MsgBox "your message here", but I am not sure how to give it options. Here is the macro I have now. Sub ClearContentsMacro Macro () Application.Goto Reference:="R7C2" Range("B7:H116").Select Selection.ClearContents Selection.ClearContents Range("B2:D2").Select Selection.ClearContents Range("B3:D3").Select Selection.ClearContents Range("B7").Select End Sub |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Warning Macro
Fantastic! Thank you for you're help, this macro is working great now!
"Dave Peterson" wrote: Sub ClearContentsMacro() Dim Verify As VbMsgBoxResult Verify = MsgBox("Do you want to delete?", vbYesNo) If Verify = vbYes Then Application.Goto Reference:="R7C2" Range("B7:H116").Select Selection.ClearContents Selection.ClearContents Range("B2:D2").Select Selection.ClearContents Range("B3:D3").Select Selection.ClearContents Range("B7").Select End If End Sub You could avoid the .select's with something like: Sub ClearContentsMacro2() Dim Verify As Long 'VbMsgBoxResult doesn't work in earlier versions Verify = MsgBox("Do you want to delete?", vbYesNo) If Verify = vbYes Then Range("B7:H116").ClearContents Range("B2:D2").ClearContents Range("B3:D3").ClearContents 'Range("B7").Select 'did you really want to select B7 End If End Sub And you could even boil those 3 range().clearcontents to just one: If Verify = vbYes Then Range("B7:H116,B2:d3").ClearContents End If MCheru wrote: Thank you! You're code is great. I am having one problem. Perhaps I made a mistake. Here is the code I am using. Sub ClearContentsMacro() Dim Verify As VbMsgBoxResult Verify = MsgBox("Do you want to delete?", vbYesNo) If Verify = vbYes Then End If Application.Goto Reference:="R7C2" Range("B7:H116").Select Selection.ClearContents Selection.ClearContents Range("B2:D2").Select Selection.ClearContents Range("B3:D3").Select Selection.ClearContents Range("B7").Select End Sub ......The challenge I am having is that even when I click No all the cells are still getting deleted. "Barb Reinhardt" wrote: Oops, that was a typo. Thanks for catching it. "Chip Pearson" wrote: if vbyes then will always be true. Use if Verify = vbyes then Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 13 Mar 2009 07:52:06 -0700, Barb Reinhardt wrote: You can do something like this Dim Verify as VBMsgBoxResult Verify = Msgbox("Do you want to delete?",vbyesno) if vbyes then 'Delete whatever you want to delete end if "MCheru" wrote: I want to modify this macro so that before it runs, a message box will pop up and ask are you sure you want to delete? and then have two options yes and no. If the operator clicks yes the contents will delete. If the operator clicks no the contents will not be deleted. I know how to get a message box to come up MsgBox "your message here", but I am not sure how to give it options. Here is the macro I have now. Sub ClearContentsMacro Macro () Application.Goto Reference:="R7C2" Range("B7:H116").Select Selection.ClearContents Selection.ClearContents Range("B2:D2").Select Selection.ClearContents Range("B3:D3").Select Selection.ClearContents Range("B7").Select End Sub -- Dave Peterson |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Warning Macro
I see. Thank you for clarifying. I appreciate you're help. It's working
perfect now! "Chip Pearson" wrote: The problem is that there is nothing between the "If Verify..." and the "End If". The code tests Verify but takes no action, regardless of the value of Verify. Use code like the following. If Verify = vbYes Then Range("B2:H116").ClearContents End If ' OR use If Verify = vbNo Then Exit Sub End If Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Fri, 13 Mar 2009 16:47:01 -0700, MCheru wrote: Thank you. You're code is great. I am having one problem. Perhaps I made a mistake. Here is the code I am using. Sub ClearContentsMacro() Dim Verify As VbMsgBoxResult Verify = MsgBox("Do you want to delete?", vbYesNo) If Verify = vbYes Then End If Application.Goto Reference:="R7C2" Range("B7:H116").Select Selection.ClearContents Selection.ClearContents Range("B2:D2").Select Selection.ClearContents Range("B3:D3").Select Selection.ClearContents Range("B7").Select End Sub ......The challenge I am having is that even when I click No all the cells are still getting deleted. "Barb Reinhardt" wrote: You can do something like this Dim Verify as VBMsgBoxResult Verify = Msgbox("Do you want to delete?",vbyesno) if vbyes then 'Delete whatever you want to delete end if "MCheru" wrote: I want to modify this macro so that before it runs, a message box will pop up and ask are you sure you want to delete? and then have two options yes and no. If the operator clicks yes the contents will delete. If the operator clicks no the contents will not be deleted. I know how to get a message box to come up MsgBox "your message here", but I am not sure how to give it options. Here is the macro I have now. Sub ClearContentsMacro Macro () Application.Goto Reference:="R7C2" Range("B7:H116").Select Selection.ClearContents Selection.ClearContents Range("B2:D2").Select Selection.ClearContents Range("B3:D3").Select Selection.ClearContents Range("B7").Select End Sub |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro Warning | New Users to Excel | |||
| Macro warning box | Excel Discussion (Misc queries) | |||
| macro warning | New Users to Excel | |||
| macro warning box | Excel Programming | |||
| Redundant Macro warning or hidden macro? | Excel Programming | |||
Linear Mode
