• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macro Confirmation

AZExcel

Member
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?
 
Sure thing. Here's a simple setup you could use.

[pre]
Code:
Sub Confirm()
Dim UserChoice As Variant
UserChoice = MsgBox("Are you sure you want to do that?", vbYesNo, "CONFIRM")
If UserChoice = vbYes Then
'DO IT!
Else
'DONT DO IT!
End If
End Sub
[/pre]
 
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
 
Hi AZExcel


Your code would be changed to:

[pre]
Code:
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
ActiveWindow.SmallScroll Down:=9
Range("B54:C54").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-57
Else
'DONT DO IT!
End If
End Sub
[/pre]

You will paste in your complete code, obviously.


Cheers


Shaun
 
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("Are you sure you want to do that?", vbYesNo, "CONFIRM")
If UserChoice = vbYes Then
Union(Range("B4:C4"), Range("C5:C11"), Range("B13:C13"), Range("C14:C16"), _
Range("B18:C18"), Range("B22:C22"), Range("C23:C25"), Range("B27:C27"), _
Range("C28:C35"), Range("B37:C37"), Range("C38:C39"), Range("B41:C41"), _
Range("B45:C45"), Range("C46:C52"), Range("B54:C54")).ClearContents
Else
'DONT DO IT!
End If
End Sub
[/pre]
-----


Regards!
 
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
 
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!
 
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
 
Hi, AZExcel!

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

Regards!
 
Back
Top