reset combobox to blank

[Susan]

this is in a worksheet_open sub. the combobox is the only activex
control on the worksheet. what am i doing wrong? i tried "value" but
that didn't work. but neither is listindex. i originally had it all
in one line but when that didn't work i thought it might need a "with"
statement (ThisWorkbook.Sheets("Income").OLEObjects
("cmbCounty").ListIndex = 0 'Value = "")
thanks a lot for any advice.

With ThisWorkbook.Sheets("Income")
.OLEObjects("cmbCounty").ListIndex = 0 'Value = ""
End With


susan

 

[Rick Rothstein]

Try setting the ListIndex to -1 (minus one) instead of 0.

 

[Susan]

hmmmm. didn't work.

tried msgboxes before & after to see the value and neither mgbox got
triggered, for some odd reason.
the code before & below this section was accessed & performed.
can't set a breakpoint & step through it because when i save & close
the workbook, the breakpoint gets cleared.

MsgBox ThisWorkbook.Sheets("Income").OLEObjects("cmbCounty").Text
With ThisWorkbook.Sheets("Income")
.OLEObjects("cmbCounty").ListIndex = -1
End With
MsgBox ThisWorkbook.Sheets("Income").OLEObjects("cmbCounty").Text

any other ideas? i'm thinking i'm going to have to copy this section
of code to another module & play with it outside of the workbook_open
sub to get it working, then move it back into the workbook_open sub.
a thought - can oleobjects be accessed from a general module??? or
perhaps can they only be accessed from sheet code?
susan

 

[Susan]

i got it

ThisWorkbook.Sheets("Income").cmbCounty.ListIndex = -1

don't know why it doesn't want the "oleobjects" in there, because i
definitely used the activex control, but it works this way.
thanks for all your help!
susan

 

[Peter T]

You can do it that way but it's not always a good idea to hardcode objects
as you are. You almost had it right before, just missing '.Object'

With ThisWorkbook.Sheets("Income")
.OLEObjects("cmbCounty").Object.ListIndex = -1
End With

Regards,
Peter T



i got it

ThisWorkbook.Sheets("Income").cmbCounty.ListIndex = -1

don't know why it doesn't want the "oleobjects" in there, because i
definitely used the activex control, but it works this way.
thanks for all your help!
susan

 

[Susan]

ok, i'll fix it. thanks peter.
susan

You can do it that way but it's not always a good idea to hardcode objects
as you are. You almost had it right before, just missing '.Object'

With ThisWorkbook.Sheets("Income")
  .OLEObjects("cmbCounty").Object.ListIndex = -1
End With

Regards,
Peter T


i got it

ThisWorkbook.Sheets("Income").cmbCounty.ListIndex = -1

don't know why it doesn't want the "oleobjects" in there, because i
definitely used the activex control, but it works this way.
thanks for all your help!
susan







- Show quoted text -

 

[Jacob Skaria]

Hi Susan

The default listindex for for ComboBox, DirListBox, and DriveListBox
controls is -1 which indicates that no item is currently selected .

If this post helps click Yes