clearing cells and checkboxes **ROOKIE HERE**

[Dan]

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]

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]

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










--

Dave Peterson- Hide quoted text -

- Show quoted text -

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