Hello -

I am a VBA rookie and am trying to write an Excel Macro which will
clear contents of a cell along with any check boxes and option
buttons. I got the first part of the macro correct, but when I tried
adding additional checkboxes and options to it, it just don't work.

Here is my code:

Private Sub CommandButton1_Click()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
If cell.Locked = False Then cell.MergeArea.ClearContents
If OptionButton1.Value = 0 Then OptionButton1.ClearContents
If CheckBox1.Value = 0 Then CheckBox1.ClearContents
Next
End Sub

Thanks for any help someone can provide.
Dan

I think something like this would work ok:

Option Explicit
Private Sub CommandButton1_Click()
Dim cell As Range
Dim OLEObj As OLEObject

For Each cell In Me.UsedRange
If cell.Locked = False Then
cell.MergeArea.Value = ""
End If
Next cell

For Each OLEObj In Me.OLEObjects
If TypeOf OLEObj.Object Is MSForms.OptionButton Then
OLEObj.Object.Value = False
ElseIf TypeOf OLEObj.Object Is MSForms.CheckBox Then
OLEObj.Object.Value = False
End If
Next OLEObj
End Sub




Dan wrote:
>
> Hello -
>
> I am a VBA rookie and am trying to write an Excel Macro which will
> clear contents of a cell along with any check boxes and option
> buttons. I got the first part of the macro correct, but when I tried
> adding additional checkboxes and options to it, it just don't work.
>
> Here is my code:
>
> Private Sub CommandButton1_Click()
> Dim cell As Range
> For Each cell In ActiveSheet.UsedRange
> If cell.Locked = False Then cell.MergeArea.ClearContents
> If OptionButton1.Value = 0 Then OptionButton1.ClearContents
> If CheckBox1.Value = 0 Then CheckBox1.ClearContents
> Next
> End Sub
>
> Thanks for any help someone can provide.
> Dan

--

Dave Peterson

On Feb 19, 5:05 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> I think something like this would work ok:
>
> Option Explicit
> Private Sub CommandButton1_Click()
> Dim cell As Range
> Dim OLEObj As OLEObject
>
> For Each cell In Me.UsedRange
> If cell.Locked = False Then
> cell.MergeArea.Value = ""
> End If
> Next cell
>
> For Each OLEObj In Me.OLEObjects
> If TypeOf OLEObj.Object Is MSForms.OptionButton Then
> OLEObj.Object.Value = False
> ElseIf TypeOf OLEObj.Object Is MSForms.CheckBox Then
> OLEObj.Object.Value = False
> End If
> Next OLEObj
> End Sub
>
>
>
>
>
> Dan wrote:
>
> > Hello -
>
> > I am a VBA rookie and am trying to write an Excel Macro which will
> > clear contents of a cell along with any check boxes and option
> > buttons. I got the first part of the macro correct, but when I tried
> > adding additional checkboxes and options to it, it just don't work.
>
> > Here is my code:
>
> > Private Sub CommandButton1_Click()
> > Dim cell As Range
> > For Each cell In ActiveSheet.UsedRange
> > If cell.Locked = False Then cell.MergeArea.ClearContents
> > If OptionButton1.Value = 0 Then OptionButton1.ClearContents
> > If CheckBox1.Value = 0 Then CheckBox1.ClearContents
> > Next
> > End Sub
>
> > Thanks for any help someone can provide.
> > Dan
>
> --
>
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

Perfect!!! Thanks so much for the help Dave.