Is there a Insert/Delete Row EventListener?

excel_Bert

New Member
Joined
Jul 14, 2010
Messages
41
Operating System:Windows Vista Program:Excel 2007
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
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Such an event does not exist.

You need to enclose your code in code tags if you seriously expect anybody to read it.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,755
Office Version
365
Platform
Windows
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?
 

excel_Bert

New Member
Joined
Jul 14, 2010
Messages
41
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)
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,755
Office Version
365
Platform
Windows
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.
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,411
Messages
5,511,180
Members
408,829
Latest member
sheshe123

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top