Macro to clear combo box values
 

I have 16 combo boxes (ComboGame1 - ComboGame16) which allows the user to
pick between two NFL teams. My trouble is that the only way I can get them
back to blank is to go into each one individually and clear the values. I've
tried with no success to record a macro of doing this but in the end that is
what I want. A button "Clear Winners" that once pushed, will set the value
of each of those 16 combo boxes to null. Can you help?

Macro to clear combo box values
 

I generally use lists in columns from a separate worksheet as my data source
for combo boxes. If I leave the top row empty and then include the empty
cell as part of the range given as the row source, I can clear combo boxes
by setting each ListIndex equal to 0. There may well be a better way but
this works well for me.

Steve


"Carpie" wrote in message
...
I have 16 combo boxes (ComboGame1 - ComboGame16) which allows the user to
pick between two NFL teams. My trouble is that the only way I can get
them
back to blank is to go into each one individually and clear the values.
I've
tried with no success to record a macro of doing this but in the end that
is
what I want. A button "Clear Winners" that once pushed, will set the
value
of each of those 16 combo boxes to null. Can you help?

Macro to clear combo box values
 

I generally do that also so that the list box can start empty and so that the
user can drop down and choose the empty cell to empty it again if necessary.
But even if I did that (left a blank cell in the range), is there a way that
I could have a macro change ALL of the list boxes back to the blank cell?

"Steve Yandl" wrote:

I generally use lists in columns from a separate worksheet as my data source
for combo boxes. If I leave the top row empty and then include the empty
cell as part of the range given as the row source, I can clear combo boxes
by setting each ListIndex equal to 0. There may well be a better way but
this works well for me.

Steve


"Carpie" wrote in message
...
I have 16 combo boxes (ComboGame1 - ComboGame16) which allows the user to
pick between two NFL teams. My trouble is that the only way I can get
them
back to blank is to go into each one individually and clear the values.
I've
tried with no success to record a macro of doing this but in the end that
is
what I want. A button "Clear Winners" that once pushed, will set the
value
of each of those 16 combo boxes to null. Can you help?

Macro to clear combo box values
 

Carpe-

Have you tried using a linked cell and having the code set the value of the
linked cell to blank?

somthing like this

Sub setcombosblank()

Dim obj As OLEObject

For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.ComboBox Then
obj.LinkedCell = "A1"
ActiveSheet.Range("A1").Value = ""
obj.LinkedCell = ""
End If
Next

End Sub

Works for me..

HTH

Matt



"Carpie" wrote in message
...
I generally do that also so that the list box can start empty and so that
the
user can drop down and choose the empty cell to empty it again if
necessary.
But even if I did that (left a blank cell in the range), is there a way
that
I could have a macro change ALL of the list boxes back to the blank cell?

"Steve Yandl" wrote:

I generally use lists in columns from a separate worksheet as my data
source
for combo boxes. If I leave the top row empty and then include the empty
cell as part of the range given as the row source, I can clear combo
boxes
by setting each ListIndex equal to 0. There may well be a better way but
this works well for me.

Steve


"Carpie" wrote in message
...
I have 16 combo boxes (ComboGame1 - ComboGame16) which allows the user
to
pick between two NFL teams. My trouble is that the only way I can get
them
back to blank is to go into each one individually and clear the values.
I've
tried with no success to record a macro of doing this but in the end
that
is
what I want. A button "Clear Winners" that once pushed, will set the
value
of each of those 16 combo boxes to null. Can you help?

Macro to clear combo box values
 

You my friend are a genius. Works like a champ. Thanks and kudos!

"Matt Williamson" wrote:

Carpe-

Have you tried using a linked cell and having the code set the value of the
linked cell to blank?

somthing like this

Sub setcombosblank()

Dim obj As OLEObject

For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MSForms.ComboBox Then
obj.LinkedCell = "A1"
ActiveSheet.Range("A1").Value = ""
obj.LinkedCell = ""
End If
Next

End Sub

Works for me..

HTH

Matt



"Carpie" wrote in message
...
I generally do that also so that the list box can start empty and so that
the
user can drop down and choose the empty cell to empty it again if
necessary.
But even if I did that (left a blank cell in the range), is there a way
that
I could have a macro change ALL of the list boxes back to the blank cell?

"Steve Yandl" wrote:

I generally use lists in columns from a separate worksheet as my data
source
for combo boxes. If I leave the top row empty and then include the empty
cell as part of the range given as the row source, I can clear combo
boxes
by setting each ListIndex equal to 0. There may well be a better way but
this works well for me.

Steve


"Carpie" wrote in message
...
I have 16 combo boxes (ComboGame1 - ComboGame16) which allows the user
to
pick between two NFL teams. My trouble is that the only way I can get
them
back to blank is to go into each one individually and clear the values.
I've
tried with no success to record a macro of doing this but in the end
that
is
what I want. A button "Clear Winners" that once pushed, will set the
value
of each of those 16 combo boxes to null. Can you help?