Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
fill combobox depending on selection from another combobox | Excel Discussion (Misc queries) | |||
Combobox | Excel Discussion (Misc queries) | |||
combobox changes value | Excel Discussion (Misc queries) | |||
Combobox | Excel Programming | |||
Combobox | Excel Programming |