I have Created a Sheet for people to enter specs, and it automatically
Creates, P/N's and Descriptions. Well The people I have created this
for, are not so Bright at times. So there for I want to create a Button
that Reset's all Combo-Box list's back to the top selection.
Basically you have
Colors
Black
Blue
Green.

They select blue, Then when they are finished, they just hit a button
to Select color again.


See I have about 15 Combo Boxs Created, so its hard for them to go back
and select the Original Settings. Well I know that they can just close
the File and open the Original, but They sometimes Save over the top of
that one, so I have to constantly send them the Original.
So this is why I'd like to create a reset Combo-Boxes Button.

I have tried recording macro's, and it does not work with, Control Tool
Box, or Forms.

Anyone have any Suggestions?

How about one of these:

Option Explicit
Sub testme04()
Dim myDropDown As DropDown
For Each myDropDown In Worksheets("sheet1").DropDowns
myDropDown.Value = 1
Next myDropDown
End Sub

Sub testme05()
Dim OLEObj As OLEObject
For Each OLEObj In Worksheets("sheet1").OLEObjects
If TypeOf OLEObj.Object Is MSForms.ComboBox Then
OLEObj.Object.ListIndex = 0
End If
Next OLEObj
End Sub

Top one is for Forms and the bottom one for controltoolbox toolbar.

noirnor wrote:

I have Created a Sheet for people to enter specs, and it automatically
Creates, P/N's and Descriptions. Well The people I have created this
for, are not so Bright at times. So there for I want to create a Button
that Reset's all Combo-Box list's back to the top selection.
Basically you have
Colors
Black
Blue
Green.

They select blue, Then when they are finished, they just hit a button
to Select color again.

See I have about 15 Combo Boxs Created, so its hard for them to go back
and select the Original Settings. Well I know that they can just close
the File and open the Original, but They sometimes Save over the top of
that one, so I have to constantly send them the Original.
So this is why I'd like to create a reset Combo-Boxes Button.

I have tried recording macro's, and it does not work with, Control Tool
Box, or Forms.

Anyone have any Suggestions?

--

Dave Peterson

Thank you so Much, it works Wonderful.

This is what I have below now, Works Just Perfectly.



Sub Reset()

Dim OLEObj As OLEObject
For Each OLEObj In Worksheets("Spec Sht").OLEObjects
Worksheets("Data Sht").OLEObjects
If TypeOf OLEObj.Object Is MSForms.ComboBox Then
OLEObj.Object.ListIndex = 0
End If
Next OLEObj


ActiveWindow.SmallScroll Down:=15
Range("B26").Select
Selection.ClearContents
Range("A27:C30").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-27
Range("B3:B6").Select
Selection.ClearContents
Range("C3").Select
Range("C17:C20").Select
Selection.ClearContents
Range("C3").Select
Selection.ClearContents
Range("B4").Activate

End Sub

After I posted it, I knew you were going to say that, so I figured that
one out. Thanks. You have been alot of help. I might be asking for
more In lil while. ;)

Thanks Again,
noirnor

Just to show you what I have now.

Sub Reset()

Dim OLEObj As OLEObject
For Each OLEObj In Worksheets("Spec").OLEObjects
Worksheets("Data").OLEObjects
If TypeOf OLEObj.Object Is MSForms.ComboBox Then
OLEObj.Object.ListIndex = 0
End If

Next OLEObj
For Each OLEObj In Worksheets("Spec").OLEObjects
If TypeOf OLEObj.Object Is MSForms.TextBox Then
OLEObj.Object.Text = Clear
End If
Next OLEObj

Range("B26:C26,B3:B6,C3,C17:C20").Select
Selection.ClearContents
Range("B4").Select
Range("B4").Activate
End Sub

You don't have to select that range to clearcontents.

Range("B26:C26,B3:B6,C3,C17:C20").ClearContents
Range("B4").Select
Range("B4").Activate

And I'm not sure what the .activate does that .select didn't. (I'd either drop
the last line or the last two lines--why change the selection from what the user
already had???)




noirnor wrote:

Just to show you what I have now.

Sub Reset()

Dim OLEObj As OLEObject
For Each OLEObj In Worksheets("Spec").OLEObjects
Worksheets("Data").OLEObjects
If TypeOf OLEObj.Object Is MSForms.ComboBox Then
OLEObj.Object.ListIndex = 0
End If

Next OLEObj
For Each OLEObj In Worksheets("Spec").OLEObjects
If TypeOf OLEObj.Object Is MSForms.TextBox Then
OLEObj.Object.Text = Clear
End If
Next OLEObj

Range("B26:C26,B3:B6,C3,C17:C20").Select
Selection.ClearContents
Range("B4").Select
Range("B4").Activate
End Sub

--

Dave Peterson