Macro Confirmation

AZExcel · Sep 4, 2012

Hello Group

I recorded a macro and assigned it to a button that clears cell contents in a excel sheet.

The sheet is protected except for the cells that allow entry.

The macro works fine, but I was thinking it would probably be a good idea to have the user confirm they want the cells cleared before the macro executes. Is there a way to do this?

Luke M · Sep 4, 2012

Sure thing. Here's a simple setup you could use.

[pre]

Code:

Sub Confirm()
Dim UserChoice As Variant
UserChoice = MsgBox(&#34;Are you sure you want to do that?&#34;, vbYesNo, &#34;CONFIRM&#34;)
If UserChoice = vbYes Then
&#39;DO IT!
Else
&#39;DONT DO IT!
End If
End Sub

[/pre]

AZExcel · Sep 5, 2012

LukeM

Thanks for the reply.. I am very grateful.

Can you tell me how the above code relates to macro code below?

Sub ClearCells()

'

' ClearCells Macro

'

' Keyboard Shortcut: Ctrl+Shift+C

'

Range("B4:C4").Select

Selection.ClearContents

Range("C5").Select

Selection.ClearContents

Range("C6").Select

Selection.ClearContents

Range("C7").Select

Selection.ClearContents

Range("C8").Select

Selection.ClearContents

Range("C9").Select

Selection.ClearContents

Range("C10").Select

Selection.ClearContents

Range("C11").Select

Selection.ClearContents

Range("B13:C13").Select

Selection.ClearContents

Range("C14").Select

Selection.ClearContents

Range("C15").Select

Selection.ClearContents

Range("C16").Select

Selection.ClearContents

Range("B18:C18").Select

Selection.ClearContents

ActiveWindow.SmallScroll Down:=6

Range("B22:C22").Select

Selection.ClearContents

Range("C23").Select

Selection.ClearContents

Range("C24").Select

Selection.ClearContents

Range("C25").Select

Selection.ClearContents

Range("B27:C27").Select

Selection.ClearContents

ActiveWindow.SmallScroll Down:=6

Range("C28").Select

Selection.ClearContents

Range("C29").Select

Selection.ClearContents

Range("C30").Select

Selection.ClearContents

Range("C31").Select

Selection.ClearContents

Range("C32").Select

Selection.ClearContents

Range("C33").Select

Selection.ClearContents

Range("C34").Select

Selection.ClearContents

Range("C35").Select

Selection.ClearContents

Range("B37").Select

Selection.ClearContents

Range("C37").Select

Selection.ClearContents

Range("C38").Select

Selection.ClearContents

Range("C39").Select

Selection.ClearContents

ActiveWindow.SmallScroll Down:=6

Range("B41:C41").Select

Selection.ClearContents

ActiveWindow.SmallScroll Down:=6

Range("B45:C45").Select

Selection.ClearContents

Range("C46").Select

Selection.ClearContents

Range("C47").Select

Selection.ClearContents

Range("C48").Select

Selection.ClearContents

Range("C49").Select

Selection.ClearContents

Range("C50").Select

Selection.ClearContents

Range("C51").Select

Selection.ClearContents

Range("C52").Select

Selection.ClearContents

ActiveWindow.SmallScroll Down:=9

Range("B54:C54").Select

Selection.ClearContents

ActiveWindow.SmallScroll Down:=-57

End Sub

Shaun · Sep 5, 2012

Hi AZExcel

Your code would be changed to:

[pre]

Code:

Sub Confirm()
Dim UserChoice As Variant
UserChoice = MsgBox(&#34;Are you sure you want to do that?&#34;, vbYesNo, &#34;CONFIRM&#34;)
If UserChoice = vbYes Then
Range(&#34;B4:C4&#34;).Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=9
Range(&#34;B54:C54&#34;).Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-57
Else
&#39;DONT DO IT!
End If
End Sub

[/pre]

You will paste in your complete code, obviously.

Cheers

Shaun

AZExcel · Sep 5, 2012

Hey thanks Shaun... I appreciate it very much. I'll update the code and give it a try!!

SirJB7 · Sep 5, 2012

Hi, AZExcel!

I don't think Shaun's code does the same as yours as it just clears B:C cell ranges of rows 4 and 54. It's just an example, I guess. If I'm right, please check with this updated code:

-----

[pre]

Code:

Sub Confirm()
Dim UserChoice As Variant
UserChoice = MsgBox(&#34;Are you sure you want to do that?&#34;, vbYesNo, &#34;CONFIRM&#34;)
If UserChoice = vbYes Then
Union(Range(&#34;B4:C4&#34;), Range(&#34;C5:C11&#34;), Range(&#34;B13:C13&#34;), Range(&#34;C14:C16&#34;), _
Range(&#34;B18:C18&#34;), Range(&#34;B22:C22&#34;), Range(&#34;C23:C25&#34;), Range(&#34;B27:C27&#34;), _
Range(&#34;C28:C35&#34;), Range(&#34;B37:C37&#34;), Range(&#34;C38:C39&#34;), Range(&#34;B41:C41&#34;), _
Range(&#34;B45:C45&#34;), Range(&#34;C46:C52&#34;), Range(&#34;B54:C54&#34;)).ClearContents
Else
&#39;DONT DO IT!
End If
End Sub

[/pre]
-----

Regards!

AZExcel · Sep 5, 2012

Shaun,

I updated the code. I had to edit the range to include all the cells that were listed in the macro code after which everything worked fine.( see below)

Sub Confirm()

Dim UserChoice As Variant

UserChoice = MsgBox("Are you sure you want to do that?", vbYesNo, "CONFIRM")

If UserChoice = vbYes Then

Range("B4:C4").Select

Selection.ClearContents

Range("C5").Select

Selection.ClearContents

Range("C6").Select

Selection.ClearContents

Range("C7").Select

Selection.ClearContents

Range("C8").Select

Selection.ClearContents

Range("C9").Select

Selection.ClearContents

Range("C10").Select

Selection.ClearContents

Range("C11").Select

Selection.ClearContents

Range("B13:C13").Select

Selection.ClearContents

Range("C14").Select

Selection.ClearContents

Range("C15").Select

Selection.ClearContents

Range("C16").Select

Selection.ClearContents

Range("B18:C18").Select

Selection.ClearContents

ActiveWindow.SmallScroll Down:=6

Range("B22:C22").Select

Selection.ClearContents

Range("C23").Select

Selection.ClearContents

Range("C24").Select

Selection.ClearContents

Range("C25").Select

Selection.ClearContents

Range("B27:C27").Select

Selection.ClearContents

ActiveWindow.SmallScroll Down:=6

Range("C28").Select

Selection.ClearContents

Range("C29").Select

Selection.ClearContents

Range("C30").Select

Selection.ClearContents

Range("C31").Select

Selection.ClearContents

Range("C32").Select

Selection.ClearContents

Range("C33").Select

Selection.ClearContents

Range("C34").Select

Selection.ClearContents

Range("C35").Select

Selection.ClearContents

Range("B37").Select

Selection.ClearContents

Range("C37").Select

Selection.ClearContents

Range("C38").Select

Selection.ClearContents

Range("C39").Select

Selection.ClearContents

ActiveWindow.SmallScroll Down:=6

Range("B41:C41").Select

Selection.ClearContents

ActiveWindow.SmallScroll Down:=6

Range("B45:C45").Select

Selection.ClearContents

Range("C46").Select

Selection.ClearContents

Range("C47").Select

Selection.ClearContents

Range("C48").Select

Selection.ClearContents

Range("C49").Select

Selection.ClearContents

Range("C50").Select

Selection.ClearContents

Range("C51").Select

Selection.ClearContents

Range("C52").Select

Selection.ClearContents

ActiveWindow.SmallScroll Down:=9

Range("B54:C54").Select

Selection.ClearContents

ActiveWindow.SmallScroll Down:=-57

Else

'DONT DO IT!

End If

End Sub

As I said earlier, thanks for your help

AZExcel · Sep 5, 2012

SirJB7

I didn't see your post.. thanks so much!!!

I was wondering if there was a way to shortened the code and you provided it. I see you used the term "union" which I will research.. thanks again friend!

Shaun · Sep 5, 2012

Yes, I left out most of the recorded macro. I did edit my post shortly after, which must have been missed.

My apologies.

Cheers

Shaun

AZExcel · Sep 5, 2012

No problem

SirJB7 · Sep 5, 2012

Hi, AZExcel!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!

Macro Confirmation

AZExcel

Member

Luke M

Excel Ninja

AZExcel

Member

Shaun

Member

AZExcel

Member

SirJB7

Excel Rōnin

AZExcel

Member

AZExcel

Member

Shaun

Member

AZExcel

Member

SirJB7

Excel Rōnin