ComboBox
 

Hi all,

Excel 2003
I have a combo box named cboCategory.
After selecting an item from the list, that item remains "active" and can't
be selected next time.
I've tried adding cboCategory.ListIndex = -1 but this causes an error, as
follows.

Run-time Error 381
Could not get the Column property. Invalid property array index

The code used is as follows:

Private Sub CboCategory_Change()
Application.EnableEvents = False
ActiveCell = CboCategory.Column(1)
ActiveCell.Offset(0, -1) = CboCategory.Column(0)
ActiveCell.Select
Application.EnableEvents = True
End Sub

Any pointers ?
Thanks and regards,
Don

ComboBox
 

I'm guessing that you have a combobox from the control toolbox toolbar on the
worksheet.

This worked for me:

Option Explicit
Dim BlkProc As Boolean
Private Sub CboCategory_Change()
If BlkProc Then Exit Sub
Application.EnableEvents = False
ActiveCell = cboCategory.Column(1)
ActiveCell.Offset(0, -1) = cboCategory.Column(0)
ActiveCell.Offset(1, 0).Select
BlkProc = True
'cboCategory.Value = ""
'or
cboCategory.ListIndex = -1
BlkProc = False
Application.EnableEvents = True
End Sub

The application.enableevents will stop the worksheet_change event from firing
when you change values--but when you change the .listindex to -1, .enableevents
won't stop the cbocategory_change event from firing.

You have to control that yourself.

A typical method is to use a boolean variable and toggle it and check it when
you need to (blkProc was my boolean variable).



Don Lloyd wrote:

Hi all,

Excel 2003
I have a combo box named cboCategory.
After selecting an item from the list, that item remains "active" and can't
be selected next time.
I've tried adding cboCategory.ListIndex = -1 but this causes an error, as
follows.

Run-time Error 381
Could not get the Column property. Invalid property array index

The code used is as follows:

Private Sub CboCategory_Change()
Application.EnableEvents = False
ActiveCell = CboCategory.Column(1)
ActiveCell.Offset(0, -1) = CboCategory.Column(0)
ActiveCell.Select
Application.EnableEvents = True
End Sub

Any pointers ?
Thanks and regards,
Don

--

Dave Peterson

ComboBox
 

Private Sub CboCategory_MouseDown(ByVal Button _
As Integer, ByVal Shift As Integer, ByVal X As Single, _
ByVal Y As Single)
cboCategory.ListIndex = -1
End Sub

--
Regards,
Tom Ogilvy


"Don Lloyd" wrote in message
...
Hi all,

Excel 2003
I have a combo box named cboCategory.
After selecting an item from the list, that item remains "active" and
can't
be selected next time.
I've tried adding cboCategory.ListIndex = -1 but this causes an error,
as
follows.

Run-time Error 381
Could not get the Column property. Invalid property array index

The code used is as follows:

Private Sub CboCategory_Change()
Application.EnableEvents = False
ActiveCell = CboCategory.Column(1)
ActiveCell.Offset(0, -1) = CboCategory.Column(0)
ActiveCell.Select
Application.EnableEvents = True
End Sub

Any pointers ?
Thanks and regards,
Don