Am I asking too much of my macro's
 

I am using a spread sheet which contains a number of combi boxes (approx. 30)
Once I've selected all requirments in these combi boxes, I run a macro to
copy the selections to another sheet. I then need all the combi boxes to
return to their previous/original selections, ready to start the process
again.
I tried to record a macro to return them all to their original selections
but its not recording any of my 30 selections. It takes far to long doing
this manually each time. Thanks for any suggestions

Control Toolbox Toolbar Combobox:

Dim obj as OleObject
for each obj on Activesheet.OleObjects
if type of obj.Object is MSForms.combobox then
obj.Object.ListIndex = -1
end if
next


Forms toolbar DropDown Box:

Dim dBox as DropDown
for each dbox in Activesheet.Dropdowns
dbox.Index = 0
Next

If they 're data validation dropdowns, then clearcontents on the cell.

--
Regards,
Tom Ogilvy

"loulou" wrote in message
...
I am using a spread sheet which contains a number of combi boxes (approx.
30)
Once I've selected all requirments in these combi boxes, I run a macro to
copy the selections to another sheet. I then need all the combi boxes to
return to their previous/original selections, ready to start the process
again.
I tried to record a macro to return them all to their original selections
but its not recording any of my 30 selections. It takes far to long doing
this manually each time. Thanks for any suggestions

Typo alert!

Dim dBox As DropDown
For Each dBox In ActiveSheet.DropDowns
dBox.ListIndex = 0
Next

..index replaced with .listindex



Tom Ogilvy wrote:

Control Toolbox Toolbar Combobox:

Dim obj as OleObject
for each obj on Activesheet.OleObjects
if type of obj.Object is MSForms.combobox then
obj.Object.ListIndex = -1
end if
next

Forms toolbar DropDown Box:

Dim dBox as DropDown
for each dbox in Activesheet.Dropdowns
dbox.Index = 0
Next

If they 're data validation dropdowns, then clearcontents on the cell.

--
Regards,
Tom Ogilvy

"loulou" wrote in message
...
I am using a spread sheet which contains a number of combi boxes (approx.
30)
Once I've selected all requirments in these combi boxes, I run a macro to
copy the selections to another sheet. I then need all the combi boxes to
return to their previous/original selections, ready to start the process
again.
I tried to record a macro to return them all to their original selections
but its not recording any of my 30 selections. It takes far to long doing
this manually each time. Thanks for any suggestions

--

Dave Peterson

And another <vbg:

Dim obj As OLEObject
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.combobox Then
obj.Object.ListIndex = -1
End If
Next

TypeOf (one word)
"In" instead of "On" in the "For each" line.

Tom Ogilvy wrote:

Control Toolbox Toolbar Combobox:

Dim obj as OleObject
for each obj on Activesheet.OleObjects
if type of obj.Object is MSForms.combobox then
obj.Object.ListIndex = -1
end if
next

Forms toolbar DropDown Box:

Dim dBox as DropDown
for each dbox in Activesheet.Dropdowns
dbox.Index = 0
Next

If they 're data validation dropdowns, then clearcontents on the cell.

--
Regards,
Tom Ogilvy

"loulou" wrote in message
...
I am using a spread sheet which contains a number of combi boxes (approx.
30)
Once I've selected all requirments in these combi boxes, I run a macro to
copy the selections to another sheet. I then need all the combi boxes to
return to their previous/original selections, ready to start the process
again.
I tried to record a macro to return them all to their original selections
but its not recording any of my 30 selections. It takes far to long doing
this manually each time. Thanks for any suggestions

--

Dave Peterson