Reset to all default choices on ActiveX controls

[Ron Morris]

Hi,

I have a workbook where I have many ActiveX controls as well as forms controls which will set at certain defaults once the workbook is opened. I would like to set-up a command button named "clear all" that the user could click on to reset all the controls back to their default value.

I have tried recording a macro but it only accepts the changes to indivual cells or forms controls. The ActiveX controls did not change back to their default settings eventhough, when recording, I clicked on them.

Does anyone have any ideas on what I need to do?

Thanks in advance

Ron

 

[Glaswegian]

Ron

You've not mentioned exactly what controls you have so here are a couple of example to clear checkboxes from a sheet. Perhaps you can adapt these for any other controls.

Sub ClearFormsCheckboxes() ' clears the Forms toolbar checkboxes
Dim chBox As CheckBox

For Each chBox In ActiveSheet.CheckBoxes
    If chBox.Value = 1 Then
        chBox.Value = 0
    End If
Next
End Sub
Public Sub UnCheckCheckboxes2()
Dim ole As OLEObject

For Each ole In Worksheets("Sheet1").OLEObjects 'clears Control Toolbox Checkboxes
    If ole.ProgId = "Forms.CheckBox.1" Then
        ole.Object.Value = 0
    End If
Next ole
End Sub

HTH

Regards

 

[Ron Morris]

Hi,

Thanks for the response. I have checkboxes, comboboxes and textboxes on the form as well as forms controls such as option boxes, comboboxes and checkboxes.

I would like one command button to change them back to the defualt settings. For example, the first combobox would be set at the first choice, as well as some of the other controls.

I would also like to mention that the controls are on two separate spreadsheet and not on a user from.

Any ideas?

Ron

 

[Glaswegian]

Ron

Do you want to clear Textboxes of all text, remove the checks from all checkboxes and also clear all option buttons? One way to go might be to put the code for each item in a module and then just call them in one macro. Won't get back to you until tomorrow I'm afraid - I'm having to rebuild my home PC.

Regards

 

[Ron Morris]

Hi,

Thanks again!

I will be clearing all textboxes of text but many of the other controls I will want set back to a particular value.

For example: Instead of checkbox 6 checked, I want checkbox 1
Instead of combobox item 3, I want item 1
and so on, and so on ...

Good luck with the home PC thing!!

Ron

 

[Glaswegian]

Ron

I need a few details.

Which toolbar did you use to create all the controls - Forms Toolbar or Control Toolbox? I also need to know the number of the checkbox you want checked, same for any Options Buttons, the numbers of the comboboxes and the item number to be the default. This would apply to each sheet of course.

Regards

 

[Ron Morris]

Hi,

On the first sheet there will be 3 ActiveX comboboxes
On the 2nd sheet there will be 14 ActiveX comboboxes
16 ActiveX checkboxes
6 ActiveX textboxes (all info cleared)
1 ActiveX Image

10 forms comboboxes
11 forms optionboxes
1 forms checkbox

All boxes with the exception of the textboxes will be set at a default, comboboxes - the first choice.

It is easy enough to record a macro to set the forms controls but I was hoping to tie them all together with one command button.

Thanks,

Ron

 

[Glaswegian]

Hi Ron

Here we go - put all this in one module. You can then add a button to each sheet to run the final macro. I find it easier to keep each routine separate and then call them all within one macro - easier to debug or amend.
Forms Checkboxes

Sub ClearFormsCheckboxes()
Dim chBox As CheckBox
' Clears Forms Toolbar Checkboxes
For Each chBox In ActiveSheet.CheckBoxes
    If chBox.Value = 1 Then
        chBox.Value = 0
    End If
Next
End Sub

Forms Option Buttons

Sub ClearOptButtons()
Dim optBut As OptionButton
'Clears Forms Toolbar Option Buttons
For Each optBut In ActiveSheet.OptionButtons
    optBut.Value = False
Next
End Sub

Toolbox Checkboxes

Sub ClearCheckboxes()
Dim ole As OLEObject
'Clears Control Toolbox Checkboxes
For Each ole In ActiveSheet.OLEObjects
    If ole.ProgId = "Forms.CheckBox.1" Then
        ole.Object.Value = 0
    End If
Next ole
End Sub

TextBoxes

Sub ClearTextBoxes()
Dim txtBox As TextBox
' Clears TextBox text
For Each txtBox In ActiveSheet.TextBoxes
    txtBox.Text = ""
Next txtBox
End Sub

Toolbox Comboboxes

Sub ComboSetOption()
Dim ole As OLEObject
' Set Control Toolbox ComboBoxes to default to first list item
For Each ole In ActiveSheet.OLEObjects
    If ole.ProgId = "Forms.ComboBox.1" Then
    ole.Object.ListIndex = 0
    End If
Next ole
End Sub

Forms ComboBoxes

Sub FormsComboSet()
Dim comBox
' Set Forms ComboBoxes to default to first list item
For Each comBox In ActiveSheet.DropDowns
    comBox.ListIndex = 1
Next comBox
End Sub

and the macro to run it all

Sub ClearAllControls()
Call ClearFormsCheckboxes
Call ClearOptButtons
Call ClearCheckboxes
Call ClearTextBoxes
Call ComboSetOption
Call FormsComboSet
End Sub

HTH

Regards