macro

  • Thread starter Thread starter Carl Johnson
  • Start date Start date
C

Carl Johnson

I'm new at this so this might seem elementary but if you don't know you ask
those that do.
I've created a macro that is suppose to clear the input data on a worksheet
when a command button is clicked. However when the msgbox appears asking
them if they wish to continue and they click "yes" nothing happens.I've
included the code that I've written. Any help would be appreciated.

Private Sub cmdclear_Click()
MsgBox "Caution: You are about to delete the information on this worksheet"
& vbNewLine & vbNewLine & "Do you wish to continue?", 4, "Clear"
If response = vbYes Then
copyoffatburner.xls = Application.Run("Clear")
Else: End
End If
End Sub
 
Carl,

There's nothing wrong with the basic "logic" of your coding.
If the user selects "Yes", the line of code:
copyoffatburner.xls = Application.Run("Clear")
Click to expand...
will execute.

Now...if "copyoffatburner.xls" is the file that you're already in,
you don't need to specify it.
If so, also, you don't need to specify "Application.Run"

Private Sub cmdclear_Click()
MsgBox "Caution: You are about to delete the information on this worksheet"
& vbNewLine & vbNewLine & "Do you wish to continue?", 4, "Clear"
If response = vbYes Then
Clear
Else: Exit Sub
End If

Okay, so what's the code in the "Clear" sub???

John
 
One more take:

Private Sub cmdclear_Click()
dim response as long
response = MsgBox("Caution: You are about to delete the information" & _
" on this worksheet" & vbNewLine & vbNewLine & _
"Do you wish to continue?", 4, "Clear")

If response = vbYes Then
worksheets("sheet1").range("Myrangename").clear '.clearcontents
end if

End Sub

This assumes that you named your range "myrangename" via Insert|Name from Excel.

Your original message box just showed the message and response was never
changed.

(and I like .clearcontents to just empty the cells. Clear will remove
formatting, too.)
 
To both you and John for responding to my inquiry. I first tried John's but
it still did not run the macro. I then tried yours Dave and while it worked
unfortunately as you said it also deleted the formatting and comments as
well I,ve again included the code as I wrote it, maybe I am overlooking
something. Again your help would be appreciated.
Private Sub cmdclear_Click()
Dim response As Long
response = MsgBox("Caution: You are about to delete the information on this
worksheet." & _
vbNewLine & vbNewLine & "Do you wish to continue?", 4)

If response = vbYes Then
Worksheets("January").Range("Clear").Clear '.clearcontents
End If

End Sub
 
Back
Top