ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Warning Macro (https://www.excelbanter.com/excel-programming/425523-warning-macro.html)

MCheru
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

Barb Reinhardt
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

Chip Pearson
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

Barb Reinhardt
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

MCheru
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

MCheru
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

MCheru
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

Dave Peterson
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

Chip Pearson
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

MCheru
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

MCheru
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


All times are GMT +1. The time now is 01:07 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com