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.
--
eugene

Hi Eugene,

I think your question can not be answered without giving us more information
about the code in the macro. A Combobox-change event generally does not
require any switch of focus to the Worksheet

JB

"eugene" wrote:

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.
--
eugene

Excelerate-nl:

Thanks a million.

I just commented out the offensive line without any ill effect (so far) and
my problem is solved.

Apparently, my original problem was fixed by something else I did, not by
shifting focus to the worksheet, as I thought.

Just goes to show you again how this board is a real time-saver. It probably
would have taken me endless hours to discover what you jsut told me. Thanks
again.

--
eugene


"Excelerate-nl" wrote:

Hi Eugene,

I think your question can not be answered without giving us more information
about the code in the macro. A Combobox-change event generally does not
require any switch of focus to the Worksheet

JB

"eugene" wrote:

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.
--
eugene