G
Guest
Hi,
I have a combo box (created using control toolbox) which triggers an event
every time an item is selected from the drop down list (Private Sub
ComboBox1_Change()). I populated the list from a named range on one of he
worksheets. Included toward the top of the macro is the line:
[A1].Activate (or Select)
Without this line, the event does not fire properly since focus remains on
the combo box. Apparently my commands to do something on the sheet (eg
highlight certain cells) does not happen unless focus switches to the sheet.
When I make a manual change to a cell which is part of the named range that
populates the drop down list, the combo box "changes," the macro fires and I
get an error:
" Activate method (or Select method) of Range class failed."
I understand why.
What I can't understand is why the same error is occurs when I close the
file. For some reason, the combo box change sub is triggered upon close and
the Activate method (or Select method) fails.
What am I doing wrong? Is there another way to shift focus to the sheet? Why
does closing the file trigger the combo box change macro?
Any help in solving this will be greatly appreciated. Thanks.
I have a combo box (created using control toolbox) which triggers an event
every time an item is selected from the drop down list (Private Sub
ComboBox1_Change()). I populated the list from a named range on one of he
worksheets. Included toward the top of the macro is the line:
[A1].Activate (or Select)
Without this line, the event does not fire properly since focus remains on
the combo box. Apparently my commands to do something on the sheet (eg
highlight certain cells) does not happen unless focus switches to the sheet.
When I make a manual change to a cell which is part of the named range that
populates the drop down list, the combo box "changes," the macro fires and I
get an error:
" Activate method (or Select method) of Range class failed."
I understand why.
What I can't understand is why the same error is occurs when I close the
file. For some reason, the combo box change sub is triggered upon close and
the Activate method (or Select method) fails.
What am I doing wrong? Is there another way to shift focus to the sheet? Why
does closing the file trigger the combo box change macro?
Any help in solving this will be greatly appreciated. Thanks.