Excel events appearing unexpectedly

[Phyllis]

I am writing vba 6.5 code in excel 2003 and I was getting an error about a
subscript out of range when executing a worksheet_change event. I have put
traces in my code to print when I execute code out of different events.

My subscript problem is happening because when I do a change on this one
worksheet and it executes the worksheet_change event, it is also showing that
a change event is occurring on another spreadsheet that isn't even active.
What is occurring to cause other events to trigger when not even on the
worksheet where those events are assigned?

 

[OssieMac]

Hi Phyllis,

Can you post a sample of the code causing this problem?

 

[Phyllis]

Thank you OssieMac,
Sorry I didn't include some code, but I have alot and wasn't sure what to
include since the problem is across multiple worksheets. In the meantime, I
have done some experimenting and determined the following.

1) When I insert a row in a worksheet that contains a named range, the
named range is updated automatically with a new RefersTo and the combo box on
the other worksheet that references that named range in the "Listfillrange"
causes a combo box event.
2) If i simply add a row to the end of the worksheet that contains the
named range, the named range does not get updated. Therefore no combo box
event occurs on the other worksheet and the combo box does not get refreshed
with the new rows
3) I was told in another discussion group, that I would need to
programmatically update the named range when I added rows as follows.
ActiveWorkbook.Names("shipperlist").RefersTo = "='SHIPPER
CONSIGNEE'!$A$2:$E$" & countrows. I programmatically count the rows when the
sheet is deactivated and update the refers to. This works and always keeps
the combo box list on the other worksheet refreshed.
4) It appears to be a catch 22. Excel will automatically update the named
range if you "insert" a row but not if you add it to the end of the rows.
5) I have resolved the problem by simply not allowing an "insert" on that
worksheet, but requiring the new rows be added at the end.
6) It would be nice if excel would automatically update the range when rows
are added and there might be a way to do this since I am new to VBA.

Didn't mean to be so verbose but if this makes sense and you know of this
problem and a better solution, please advise.

 

[OssieMac]

Hi Phyllis,

I suspected something similar that was causing the event to fire and hense
my reason for asking to see the code. However, try turning off events while
the code is making the changes that are causing the problem.

Private Sub Worksheet_Change(ByVal Target As Range)
'Insert following prior to code causing event
'to fire
On Error GoTo ReEnableEvents
Application.EnableEvents = False

'Your other code in here


'Insert following after your other code.
ReEnableEvents:
Application.EnableEvents = True
End Sub

If you use the above and for any reason the code stops before reenabling the
events then the events remain turned off until you either turn them back on
with code or restart excel. Therefore, insert the following sub somewhere and
just place the cursor in the sub and press F5.

Sub Re_Enable_Events()
Application.EnableEvents = True
End Sub

 

[broro183]

Hi Phyllis,

...
3) I was told in another discussion group, that I would need to
programmatically update the named range when I added rows as follows.
ActiveWorkbook.Names("shipperlist").RefersTo = "='SHIPPER
CONSIGNEE'!$A$2:$E$" & countrows. I programmatically count the row

when the sheet is deactivated and update the refers to. This works an
always keeps the combo box list on the other worksheet refreshed.

4) It appears to be a catch 22. Excel will automatically update th named
range if you "insert" a row but not if you add it to the end of th rows.
5) I have resolved the problem by simply not allowing an "insert" o that
worksheet, but requiring the new rows be added at the end.
6) It would be nice if excel would automatically update the range whe rows
are added and there might be a way to do this since I am new to VBA.

I haven't tested dynamic named ranges (DNR's) on comboboxes, but DNR'
do work in other references (for example as a pivot table's dat
source). If they work, DNR's would overcome the issues in 3) & 4
without using VBA. To read about DNR's have a look at the below link
(esp the 2nd & 3rd ones, which use Index rather than Offset):
'Excel Names -- Excel Named Ranges
(http://www.contextures.com/xlNames01.html)
'Excel -- Names -- Create Dynamic Ranges With a Macro
(http://www.contextures.com/xlNames03.html)
'Excel Data Validation -- Dependent Lists With INDEX
(http://contextures.com/xlDataVal15.html)

hth
Rob

 

[Phyllis]

OssieMac,

That suggestion worked great and allowed me to insert rows in the middle of
the range without invoking the combobox event. Thank you.