![]() |
Excel events appearing unexpectedly
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? |
Excel events appearing unexpectedly
Hi Phyllis,
Can you post a sample of the code causing this problem? -- Regards, OssieMac "Phyllis" wrote: 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? |
Excel events appearing unexpectedly
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" wrote: Hi Phyllis, Can you post a sample of the code causing this problem? -- Regards, OssieMac "Phyllis" wrote: 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? |
Excel events appearing unexpectedly
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 -- Regards, OssieMac |
Excel events appearing unexpectedly
Hi Phyllis, Phyllis;670114 Wrote: ... 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. I haven't tested dynamic named ranges (DNR's) on comboboxes, but DNR's do work in other references (for example as a pivot table's data 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 links (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 -- broro183 Rob Brockett. Always learning & the best way to learn is to experience... ------------------------------------------------------------------------ broro183's Profile: 333 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=187064 http://www.thecodecage.com/forumz/chat.php |
Excel events appearing unexpectedly
OssieMac, That suggestion worked great and allowed me to insert rows in the middle of the range without invoking the combobox event. Thank you. "OssieMac" wrote: 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 -- Regards, OssieMac |
All times are GMT +1. The time now is 11:53 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com