Reset Default Values For Controls on Worksheet

[goss]

Hi all,

Using Excel 2010.

I have a worksheet with 2 combo boxes and 2 scroll bars
I would like to add a button so user may restore to the default values as well as reset to default values before closing

I get this error message;

Run-time error '1004':
Method 'OLEObjects' of object '_Worsheet' failed

Here:

Set ctrlTemp1 = .OLEObjects("ctrlProduct1").Object

What am I doing wrong?
Full code:

Sub ResetControls()
    'Date: 07/04/2011
    'Author: goss
    'Purpose: Reset Controls on Presentation to Default Values

    Dim wbBook As Workbook
    Dim wsPres As Worksheet
    Dim ctrlTemp1 As OLEObject
    Dim ctrlTemp2 As OLEObject
    Dim ctrlTemp3 As OLEObject
    Dim ctrlTemp4 As OLEObject
    
    'Setup Environment
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        Application.Calculation = xlCalculationManual
    
        Set wbBook = ThisWorkbook
        Set wsPres = wbBook.Worksheets("Presentation")
        With wsPres
            Set ctrlTemp1 = .OLEObjects("ctrlProduct1").Object
            Set ctrlTemp2 = .OLEObjects("ctrlProduct2").Object
            Set ctrlTemp3 = .OLEObjects("ctrlScrollLeft").Object
            Set ctrlTemp4 = .OLEObjects("ctrlScrollRight").Object
        End With
    
    'Reset Default Values
        ctrlTemp1.ListIndex = 0
        ctrlTemp2.ListIndex = 0
        ctrlTemp3.Value = 0
        ctrlTemp4.Value = 11
        
    
    'Tidy up
        Set wbBook = Nothing
        Set wsPres = Nothing
        Set ctrlTemp1 = Nothing
        Set ctrlTemp2 = Nothing
        Set ctrlTemp3 = Nothing
        Set ctrlTemp4 = Nothing
        
        Application.ScreenUpdating = True
        Application.Calculation = xlCalculationAutomatic
        Application.DisplayAlerts = True
    
End Sub

 

[Marcelo Branco]

Hi,

Try something like

Dim ctrlTemp1 As [B]Object  '<---not as OLEObject[/B]
Dim ctrlTemp2 As [B]Object[/B]
Dim ctrlTemp3 As [B]Object[/B]
Dim ctrlTemp4 As [B]Object[/B]

I think that ListIndex is not a valid property for ComboBox. Try Value

HTH

M.