Is there a Insert/Delete Row EventListener?

excel_Bert

New Member
Joined
Jul 14, 2010
Messages
41
Operating System:Windows Vista Program:Excel 2007
trans.gif
Hi, Everyone!



My main problem is that I don't know whether there is an INSERT or DELETE EVENT that my program can use to listen to, to decide whether the Cleaner Function (UDF) should be employed or not. I am used to working with Languages that provide a wide variety of EventListeners to help you determine what to do when the user does something so the program responds accordingly.
Here, I have a CELL CHANGE listener that is handling most events. I am wondering if there's an Insert/Delete Row EventListener to handle the flow of the Change Sub and avoid the UDF 1004 Error.
Ex. if (insertRow = "true" or deleteRow = "true") then
< code >
Exit Sub
else
Cleaner ' Function Call
End if
If there's no such eventListener how can avoid the UDF 1004 Error.
Structure:
Event Subs go on each individual page.
Other Procedures go in a module.
The Cleaner Functions was designed to clean Cells in Columns H, J and I.
There's a range named pageName & "!Workarea". The pageName is a month's name (Jan) and the Range's amount of Columns is set by the last day of each month.
This named range is used to keep track of any additional inserted or deleted column(s) by assigning the result of the RowInRangeName Function to the global variable NumInsert.
Then by adding NumInsert to OffSet and Maxdays, I expand or shrink ranges Baddr and Jaddr according to the number of inserted or delete Rows.
Ex.
Baddr = "B4:B" & Maxdays + OffSet + NumInsert
Jaddr = "J4:J" & Maxdays + OffSet + NumInsert
Any Ideas on how to solve this problem?
If you'd like to try out the script, I have provided it below.
1 Put the script below on one Excel Programming Sheet
2 Call its tab Jan
3 Use the CreateName Macro to create the "Workarea" range.
4 Try to insert one or more rows between cell 4 and 34. You'll get error 1004
5 then comment out this line:
emptyCell = Cleaner(addr, Maxdays, OffSet, Baddr, Jaddr) 'Sub CALL
in the Worksheet_Change Sub. You'll notice that there's no problem.
Both Functions (RowsInRangeName, Cleaner()) work fine independently, but not together. How can I fix this, in case that there's no Insert/Delete Row EventListener?
----------------------------------------------------------------------------
Public NumInsert As Integer
Public SheetIndex As Integer
Public addr As String
'############### CHANGE EVENT SUB #################
'CALLS SUBS AFTER CELL CONTENT IS CHANGED
'CHECKS FOR RIGHT COLUMN - CELL AND AMOUNT OF CELLS SELECTION
Private Sub Worksheet_Change(ByVal Target As Range)
Dim Baddr As String, Jaddr As String, OffSet As Integer, Maxdays As Integer, emptyCell As Boolean
emptyCell = "False" 'FLAG TO EXIT SUB IF TRUE
'Last the of Month is used to know how many Cells there are in a Column
Maxdays = Day(DateSerial(Year(Date), SheetIndex + 1, 0))
OffSet = 3 'Where the actual workarea begins
totalRows = RowsInRangeName 'Call
originalRows = Maxdays
NumInsert = totalRows - originalRows
'MsgBox NumInsert
Baddr = "B4:B" & Maxdays + OffSet + NumInsert
Jaddr = "J4:J" & Maxdays + OffSet + NumInsert

emptyCell = Cleaner(addr, Maxdays, OffSet, Baddr, Jaddr) 'Sub CALL
If emptyCell = True Then Exit Sub '
'PREVENTS EXECECUTION IF 2 O MORE CELLS ARE SELECTED OR MERGED
If Target.CountLarge > 1 Then Exit Sub
If Intersect(Range(addr), Range(Jaddr)) Is Nothing Then
If Intersect(Range(addr), Range(Baddr)) Is Nothing Then
Exit Sub
End If
End If
col = Range(addr).Column
'If col = 2 Then FormatTime addr Else HowMuch addr
End Sub
'######## TRACKS SELECTIONS AND STORES SHEET NUMBER AND CELL(S) ADDRESS #############
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
SheetIndex = ActiveSheet.Index
addr = Target.Address
'MsgBox "Range(" & addr & ")"
End Sub
'####### NUMBER OF NEW INSERTED ROWS
Public Sub ShowInsertions()
MsgBox NumInsert
End Sub
'##### Counts the Number Rows in Range #####
Public Function RowsInRangeName()
shtName = ActiveSheet.Name
p = Names(shtName & "!Workarea").RefersToRange.Value
totalRows = UBound(p, 1)
RowsInRangeName = totalRows
End Function
'####### CLEAN CELLS CORRESPONDING THE EMPTY ONES ########
Function Cleaner(addr, Maxdays, OffSet, Baddr, Jaddr)
Dim oFlow As Integer
'If glbPageNum < 3 Then Exit Function 'PREVENTS THIS SUB FROM WORKING UNTIL ALL PAGES ARE CREATED
oFlow = Maxdays + OffSet + 1 'Overflows Work Area Limit
For Each objRng In Range(addr) 'CHECKS THAT SELECTION DOESN'T GO BEYOND THE WORK AREA
If objRng.Row >= oFlow Then
MsgBox "Selection Outside of Range " & addr
Exit Function
End If
Next objRng
'------------------------------- Cleans Employer Cells --------------------------
If Intersect(Range(addr), Range(Jaddr)) Is Nothing Then
' Exit Sub
Else
'ERASES SAME ROW HOURS IF EMPLOERS IS EMPTY
'MsgBox "Interset J"
For Each objRgnJ In Range(addr)
Set Amount = objRgnJ.OffSet(rowOffset:=0, columnOffset:=-1) 'PROBLEM AREA.
If objRgnJ.Value = "" Then
Application.EnableEvents = False
Amount.ClearContents
Application.EnableEvents = True
Cleaner = "True"
End If 'Clear Contents
Next objRgnJ
End If ' COL J
'------------------------------- Cleans Time Cells -----------------------------
If Intersect(Range(addr), Range(Baddr)) Is Nothing Then
Exit Function
Else
'ERASES SAME ROW HOURS, EMPLOYER & FEE IF TIME IS EMPTY
'MsgBox "Interset B"
For Each objRgnB In Range(addr)
Set hrsWrkd = objRgnB.OffSet(rowOffset:=0, columnOffset:=6)
Set Employer = objRgnB.OffSet(rowOffset:=0, columnOffset:=8)
Set Amount = objRgnB.OffSet(rowOffset:=0, columnOffset:=7)
If objRgnB.Value = "" Then
Application.EnableEvents = False
hrsWrkd.ClearContents
Employer.ClearContents
Amount.ClearContents
Cleaner = "True"
Application.EnableEvents = True
End If 'Clear Contents
Next objRgnB
End If ' B COL
End Function
' ####### SUB CREATES THE RANGE FOR JANUARY ##########
Public Sub CreateName()
nm = ActiveSheet.Name
ActiveSheet.Names.Add Name:=nm & "!Workarea", RefersTo:="=" & nm & "!$A$4:$K$34"
End Sub
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Such an event does not exist.

You need to enclose your code in code tags if you seriously expect anybody to read it.
 
Upvote 0
As well as enclosing the code in tags some punctuation might be useful, even some spacing to make your post easier to use.

Oh and an explanation in words what you actually want to achieve wouldn't hurt.

As VoG has said there are no built-in events triggered by the specific actions you describe.

However it may be possible to workaround by using some other event that will indirectly be triggered as a result of the actions.

For that the first thing that springs to mind is the Calculate event.

That's commonly used in place of the Change event when there is a formula in a cell and the value changes as a result of it rather than the user chaniging it manually.

By the way what languages are you referring to regarding EventListeners?

Aren't they mainly a Java (script) thing? Perhaps a little bit .Net.

Also is the code you are posting Excel VBA?
 
Upvote 0
As well as enclosing the code in tags some punctuation might be useful, even some spacing to make your post easier to use.

Oh and an explanation in words what you actually want to achieve wouldn't hurt.

As VoG has said there are no built-in events triggered by the specific actions you describe.

However it may be possible to workaround by using some other event that will indirectly be triggered as a result of the actions.

For that the first thing that springs to mind is the Calculate event.

That's commonly used in place of the Change event when there is a formula in a cell and the value changes as a result of it rather than the user chaniging it manually.

By the way what languages are you referring to regarding EventListeners?

Aren't they mainly a Java (script) thing? Perhaps a little bit .Net.

Also is the code you are posting Excel VBA?
Hi, Guys!

Thanks for the feedback. I tried to somehow format my message to be readable, but for some unknown reason it came out like that. I do agree with you, it is very hard to read like that. Should I posted it again? By the way, Norie, you are right, JavaScript uses eventListener as well as ActionScript 3 (Flash)
 
Upvote 0
You should just be able to format a message like you would format any message/email/document.

Just a line or two between sections of the actual post and perhaps keep the length down - it's not uncommon to have to scroll across.

That usually happens when somebody posts an unfeasibly long formula or line of code.

As for posting code, that's quite straightforward.

There are 2 main choices for that:


  1. Enclose the code in code tags manually.
  2. Click Go Advanced, select the code and hit the # button.
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top