how to validate a combobox?

  • Thread starter Thread starter clay
  • Start date Start date
C

clay

I would like to validate the value in the linked cell of a combobox
every time that combobox is changed by a user. My comboboxes spanning
several sheets are grouped into three's ("optimistic", "medium", and
"pessimistic") and I need to make sure that a user doesn't select a
value for the "optimistic" combobox that is "worse than" the value in
the "medium", and so on.

I have tried (and failed with) several techniques already:

1. Assigning to each combobox an error-checking macro which modifies
an invalid entry. This failed because of what I suspect is a bug in
Excel 2000.... The user will need to insert and delete rows
positioned at the top of the worksheet. For some reason, when a
combobox has a macro assigned to it (even if the subroutine is totally
empty! "Sub emptyMacro() [newline] End Sub"), the value of its
linkedCell is duplicated above/below it when a row above the
linkedCell is inserted/deleted. I can't have cells being duplicated
every time the user inserts or removes a row.

2. Using Excel Events Workbook_SheetCalculate and Workbook_SheetChange
to call my error-checking macro. The former only takes the
recalculated worksheet as a parameter so I can't get to the cell that
was modified and needs checking. The latter is only called when a
user actually enters a value into a cell, but not when a form (like a
combobox) modifies a cell value.

3. Using Data Validation. The data validation provided by Excel is
only invoked when someone enters a value into the cell, but not when a
form modifies a cell value (same problem as in #2).

4. Using control comboboxes rather than form comboboxes. Control
comboboxes put the actual text of the combobox into the linkedCell
rather than a 1,2,3,etc as form comboboxes do. This prohibits
(almost) the use of the "choose" function which I am using to make
most of the calculations in my workbook. The control comboboxes also
do not even have the 'assign macro' option, so each combobox would
require its own subroutine, which is impossible since the number of
comboboxes changes depending upon user inputs (which I have
automated).

5. Using option boxes rather than comboboxes. This right now is my
only option. The problem is simply a matter of appearance--six
buttons in the place of every single combobox seems to lead to more
confusion and scrolling up and down the worksheet than actually
helping the user with this project. I'd really like to stick with
comboboxes if possible.


Any other options or modifications to the options above would be VERY
appreciated. Thank you for reading about my problem and thanks in
advance for any help provided.

clay
 
You need to change the choices offered in a combobox so that only consistent
choices are offered. It is silly to offer a choice in the dropdown and then
tell the user that isn't an acceptable choice. You can use you macro to
adjust other comboboxes to only offer consistent choices.

--
Regards,
Tom Ogilvy

clay said:
I would like to validate the value in the linked cell of a combobox
every time that combobox is changed by a user. My comboboxes spanning
several sheets are grouped into three's ("optimistic", "medium", and
"pessimistic") and I need to make sure that a user doesn't select a
value for the "optimistic" combobox that is "worse than" the value in
the "medium", and so on.

I have tried (and failed with) several techniques already:

1. Assigning to each combobox an error-checking macro which modifies
an invalid entry. This failed because of what I suspect is a bug in
Excel 2000.... The user will need to insert and delete rows
positioned at the top of the worksheet. For some reason, when a
combobox has a macro assigned to it (even if the subroutine is totally
empty! "Sub emptyMacro() [newline] End Sub"), the value of its
linkedCell is duplicated above/below it when a row above the
linkedCell is inserted/deleted. I can't have cells being duplicated
every time the user inserts or removes a row.

2. Using Excel Events Workbook_SheetCalculate and Workbook_SheetChange
to call my error-checking macro. The former only takes the
recalculated worksheet as a parameter so I can't get to the cell that
was modified and needs checking. The latter is only called when a
user actually enters a value into a cell, but not when a form (like a
combobox) modifies a cell value.

3. Using Data Validation. The data validation provided by Excel is
only invoked when someone enters a value into the cell, but not when a
form modifies a cell value (same problem as in #2).

4. Using control comboboxes rather than form comboboxes. Control
comboboxes put the actual text of the combobox into the linkedCell
rather than a 1,2,3,etc as form comboboxes do. This prohibits
(almost) the use of the "choose" function which I am using to make
most of the calculations in my workbook. The control comboboxes also
do not even have the 'assign macro' option, so each combobox would
require its own subroutine, which is impossible since the number of
comboboxes changes depending upon user inputs (which I have
automated).

5. Using option boxes rather than comboboxes. This right now is my
only option. The problem is simply a matter of appearance--six
buttons in the place of every single combobox seems to lead to more
confusion and scrolling up and down the worksheet than actually
helping the user with this project. I'd really like to stick with
comboboxes if possible.


Any other options or modifications to the options above would be VERY
appreciated. Thank you for reading about my problem and thanks in
advance for any help provided.

clay
Click to expand...
 
Tom,

Thanks for your response. Problem is that if I have a macro modify
the combobox each time it is changed by a user (or each time one of
the other two in its group is changed by a user) then I run into the
original problem in my solution #1 (see below) which is that assigning
a macro to a combobox causes this row insertion/deletion error.

Do you have any ideas on how I can run a macro each time a combobox is
changed by a user--whether that macro modifies the choices of the
combobox or modifies something else--without producing this bug that
duplicates cells when a row is inserted/deleted? Thanks again.

clay

I would like to validate the value in the linked cell of a combobox
every time that combobox is changed by a user. My comboboxes spanning
several sheets are grouped into three's ("optimistic", "medium", and
"pessimistic") and I need to make sure that a user doesn't select a
value for the "optimistic" combobox that is "worse than" the value in
the "medium", and so on.

I have tried (and failed with) several techniques already:

1. Assigning to each combobox an error-checking macro which modifies
an invalid entry. This failed because of what I suspect is a bug in
Excel 2000.... The user will need to insert and delete rows
positioned at the top of the worksheet. For some reason, when a
combobox has a macro assigned to it (even if the subroutine is totally
empty! "Sub emptyMacro() [newline] End Sub"), the value of its
linkedCell is duplicated above/below it when a row above the
linkedCell is inserted/deleted. I can't have cells being duplicated
every time the user inserts or removes a row.

2. Using Excel Events Workbook_SheetCalculate and Workbook_SheetChange
to call my error-checking macro. The former only takes the
recalculated worksheet as a parameter so I can't get to the cell that
was modified and needs checking. The latter is only called when a
user actually enters a value into a cell, but not when a form (like a
combobox) modifies a cell value.

3. Using Data Validation. The data validation provided by Excel is
only invoked when someone enters a value into the cell, but not when a
form modifies a cell value (same problem as in #2).

4. Using control comboboxes rather than form comboboxes. Control
comboboxes put the actual text of the combobox into the linkedCell
rather than a 1,2,3,etc as form comboboxes do. This prohibits
(almost) the use of the "choose" function which I am using to make
most of the calculations in my workbook. The control comboboxes also
do not even have the 'assign macro' option, so each combobox would
require its own subroutine, which is impossible since the number of
comboboxes changes depending upon user inputs (which I have
automated).

5. Using option boxes rather than comboboxes. This right now is my
only option. The problem is simply a matter of appearance--six
buttons in the place of every single combobox seems to lead to more
confusion and scrolling up and down the worksheet than actually
helping the user with this project. I'd really like to stick with
comboboxes if possible.


Any other options or modifications to the options above would be VERY
appreciated. Thank you for reading about my problem and thanks in
advance for any help provided.

clay
Click to expand...
 
Tom,

Thanks for your response. Problem is that if I have a macro modify the
combobox each time it is changed by a user (or each time one of the
other two in its group is changed by a user) then I run into the
original problem in my solution #1 which is that assigning a macro to a
combobox causes this row insertion/deletion error.

Do you have any ideas on how I can run a macro each time a combobox is
changed by a user--whether that macro modifies the choices of the
combobox or modifies something else--without producing this bug that
duplicates cells when a row is inserted/deleted? Thanks again.

clay
 
SORRY about that. Here I am worried about duplicating cell values, so I
go ahead and duplicate a posting. Won't happen again.

clay
 
I have never seen the problem you speak of, so I can't say how to fix it.
It certainly doesn't sound like normal behavior - especially with a control
from the forms toolbar.
 
Thanks for trying anyway. Does anyone else know how to run a macro
every time a user changes a combobox -- without avoiding this weird
problem?

(The problem--in Excel 2000--is that when I insert/remove a row anywhere
above the linkedCell of a combobox that has any macro assigned to it,
the value of the linkedCell is duplicated onto the cell above/below it.
Has anyone been able to reproduce this problem or possibly explain a
solution or work-around?)

Thanks.

clay
 
Back
Top