Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Enabling macros | Excel Discussion (Misc queries) | |||
sorting with macros | Excel Discussion (Misc queries) | |||
Transferring toolbars and macros to other computers | Excel Discussion (Misc queries) | |||
The available macros list in XL; how to suppress filename from showing | Excel Discussion (Misc queries) | |||
Macros disappear after a file is imported | Excel Discussion (Misc queries) |