Need Help With Resetting ActiveX OptionButtons

[ham123]

Greetings experts,

I have this code and what it does is that it will reset all the required fields in my sheet. However, it is not resetting the ActiveX option buttons. It previously worked for the other type of option button.

The full code:

Sub ClearAll()
    Dim rng As Range
    Dim arrOptButtons, arrChkBoxes, Opt, Chk
   
    If MsgBox("Are you sure that you want to clear all input Cells and reset the Options Buttons and CheckBoxes on the ActiveSheet?", vbQuestion + vbYesNo, "Confirm Please!") = vbNo Then
        MsgBox "Action has been cancelled.", vbExclamation
        Exit Sub
    End If
   
    Set rng = Range("D8, D12, D14, D16, D18, D20, D24, D32, D34, D36, D38, D40, D42, D47, D48, D51, D53, D55, D57, D62, D64, D66, D68, D70, D72, D77, D79, D81, D83, D85, D87")
    rng.ClearContents
   
    arrOptButtons = Array("Option Button 1", "Option Button 2")
   
    arrChkBoxes = Array("Check Box 17", "Check Box 18", "Check Box 19", "Check Box 20")
   
    With ActiveSheet
        For Each Opt In arrOptButtons
            With .OptionButtons(Opt)
                .Value = xlOff
                .LinkedCell = ""
                .Display3DShading = True
            End With
        Next Opt
       
        For Each Chk In arrChkBoxes
            With .CheckBoxes(Chk)
                .Value = xlOff
                .LinkedCell = ""
                .Display3DShading = True
            End With
        Next Chk
    End With
   
    ActiveSheet.OLEObjects.Delete
   
    Range("B31").Select
   
End Sub

Any help is much appreciated!

 

[Belleke]

Like this?

Sub belle()
Dim b As OLEObject
    For Each b In ActiveSheet.OLEObjects
        If TypeName(b.Object) = "OptionButton" Or TypeName(b.Object) = "CheckBox" Then b.Object.Value = False
    Next b
End Sub

 

[ham123]

It works but there is a run-time error..

 

[ham123]

I managed to get rid of the run-time error but now it deletes the ActiveX buttons permanently

Sub ClearAll()
    Dim rng As Range
    Dim arrChkBoxes, Chk
 
    Set rng = Range("D8, D12, D14, D16, D18, D20, D24, D32, D34, D36, D38, D40, D42, D47, D48, D51, D53, D55, D57, D62, D64, D66, D68, D70, D72, D77, D79, D81, D83, D85, D87")
    rng.ClearContents
   
 
    arrChkBoxes = Array("Check Box 17", "Check Box 18", "Check Box 19", "Check Box 20")
 
    With ActiveSheet

        For Each Chk In arrChkBoxes
            With .CheckBoxes(Chk)
                .Value = xlOff
                .LinkedCell = ""
                .Display3DShading = True
            End With
        Next Chk
    End With
 
    ActiveSheet.OLEObjects.Delete

    Range("B31").Select
 Call belle
End Sub

   
   
   
Sub belle()
Dim b As OLEObject
    For Each b In ActiveSheet.OLEObjects
        If TypeName(b.Object) = "OptionButton" Or TypeName(b.Object) = "CheckBox" Then b.Object.Value = False
    Next b
End Sub

 

[ham123]

Is it possible to make exceptions such that this line deletes everything else except for activex controls?
ActiveSheet.OLEObjects.Delete

 

[Belleke]

Does this work for you?

Sub belle()
Dim b As OLEObject, s As Shape
Dim rng As Range
Dim arrOptButtons, arrChkBoxes, Opt, Chk
   If MsgBox("Are you sure that you want to clear all input Cells and reset the Options Buttons and CheckBoxes on the ActiveSheet?", vbQuestion + vbYesNo, "Confirm Please!") = vbNo Then
        MsgBox "Action has been cancelled.", vbExclamation
        Exit Sub
    End If
  
    Set rng = Range("D8, D12, D14, D16, D18, D20, D24, D32, D34, D36, D38, D40, D42, D47, D48, D51, D53, D55, D57, D62, D64, D66, D68, D70, D72, D77, D79, D81, D83, D85, D87")
    rng.ClearContents
  
    arrOptButtons = Array("Option Button 1", "Option Button 2")
  
    arrChkBoxes = Array("Check Box 17", "Check Box 18", "Check Box 19", "Check Box 20")
  
    For Each b In ActiveSheet.OLEObjects
        If TypeName(b.Object) = "OptionButton" Or TypeName(b.Object) = "CheckBox" Then b.Object.Value = False
    Next b
    For Each s In ActiveSheet.Shapes
            If s.Type <> msoOLEControlObject Then s.Delete
    Next s
End Sub

 

[ham123]

Now it doesn't delete option buttons

But it deletes all the other buttons and pictures on my sheet..

 

[ham123]

Hi! I have managed to achieve what I wanted. Below is the full code for your reference:

Sub ResetOptions()

Dim ws As Worksheet
Dim ob As OLEObject
Set ws = Worksheets("Request Form to SCM")
For Each ob In ws.OLEObjects
    If ob.Name = "OptionButton1" Or ob.Name = "OptionButton2" Then
        ob.Object.Value = 0
    End If
Next ob
With ws
    .Rows(12).Hidden = True
    .Rows(14).Hidden = True
    .Rows(15).Hidden = True
    .Rows(16).Hidden = True
    .Rows(17).Hidden = True
End With


End Sub


Sub ClearAll()
    Dim rng As Range
    Dim arrOptButtons, arrChkBoxes, Opt, Chk
  
    If MsgBox("Are you sure that you want to clear all input Cells and reset the Options Buttons and CheckBoxes on the ActiveSheet?", vbQuestion + vbYesNo, "Confirm Please!") = vbNo Then
        MsgBox "Action has been cancelled.", vbExclamation
        Exit Sub
    End If
  
    Dim obj As OLEObject
   Dim ws As Worksheet
   Set ws = Worksheets("Request Form to SCM")   'Sheet with Embedded Objects
   For Each obj In ws.OLEObjects
   If obj.OLEType = 1 Then obj.Delete
   Next obj
  
    Set rng = Range("D8:D87")
    rng.ClearContents
  
   Call ResetOptions

  Dim chkBox As Excel.CheckBox
    Application.ScreenUpdating = False
    For Each chkBox In ActiveSheet.CheckBoxes
            chkBox.Value = xlOff
    Next chkBox
    Application.ScreenUpdating = True

   Rows("20:105").EntireRow.Hidden = True
  

  
  
    Range("D8").Select
  
End Sub