How to get notified when user inserts cells, rows, or columns in W

I'm making a client to Excel that keeps references to cells on worksheets. 
These references must be updated if the user inserts cells to the left or 
above the referenced cells. I have not been able to find this information, 
neither in the Range that accompanies the Change notification nor in any of 
the Worksheet, Workbook, or Application properties. Can anybody help me?
0
Utf
3/18/2010 3:29:02 PM
excel.programming 6508 articles. 2 followers. Follow

4 Replies
642 Views

Similar Articles

[PageSpeed] 27

Using the Worksheet_Change event, you could probably evaluate the row and 
column values of the target cell against the criteria cell:

If Target <> (Reference Cell) Then
   If Target.Row <=( Reference Cell).Row And _
     Target.Column <= (Reference Cell).Column Then
         'Update code here
   End If
End If

The outer If statement avoids inadvertant changes to the reference cell. 
The inner statement then checks to see if the target cell that is changed is 
to the left or above the reference cell.


"sbo" <sbo@discussions.microsoft.com> wrote in message 
news:089BA254-2C80-4C5F-A157-36950842D2C9@microsoft.com...
> I'm making a client to Excel that keeps references to cells on worksheets.
> These references must be updated if the user inserts cells to the left or
> above the referenced cells. I have not been able to find this information,
> neither in the Range that accompanies the Change notification nor in any 
> of
> the Worksheet, Workbook, or Application properties. Can anybody help me? 


0
JLGWhiz
3/18/2010 5:55:45 PM
The problem is that the Change event is emitted in many situations, both when 
deleting and inserting cells and when changing cell contents. Furthermore, 
the insertion (or deletion) may push cells down (up) or right (left). So more 
accurate information is needed. I also thought of intercepting the user 
action that initiated the change but it seems hopeless to catch all scenarios.

"JLGWhiz" wrote:

> 
> Using the Worksheet_Change event, you could probably evaluate the row and 
> column values of the target cell against the criteria cell:
> 
> If Target <> (Reference Cell) Then
>    If Target.Row <=( Reference Cell).Row And _
>      Target.Column <= (Reference Cell).Column Then
>          'Update code here
>    End If
> End If
> 
> The outer If statement avoids inadvertant changes to the reference cell. 
> The inner statement then checks to see if the target cell that is changed is 
> to the left or above the reference cell.
> 
> 
> "sbo" <sbo@discussions.microsoft.com> wrote in message 
> news:089BA254-2C80-4C5F-A157-36950842D2C9@microsoft.com...
> > I'm making a client to Excel that keeps references to cells on worksheets.
> > These references must be updated if the user inserts cells to the left or
> > above the referenced cells. I have not been able to find this information,
> > neither in the Range that accompanies the Change notification nor in any 
> > of
> > the Worksheet, Workbook, or Application properties. Can anybody help me? 
> 
> 
> .
> 
0
Utf
3/18/2010 6:42:01 PM
I mis-read the insert cells bit.  Have you tried using named ranges to 
overcome the reference changes caused by the user inserting cells, rows or 
columns?  The named range, although moved to a different relative location 
on the sheet will keep the name and can be referred to by name in formulas 
and code.



"sbo" <sbo@discussions.microsoft.com> wrote in message 
news:1B46B948-E00D-4169-893D-D5B259346E9D@microsoft.com...
> The problem is that the Change event is emitted in many situations, both 
> when
> deleting and inserting cells and when changing cell contents. Furthermore,
> the insertion (or deletion) may push cells down (up) or right (left). So 
> more
> accurate information is needed. I also thought of intercepting the user
> action that initiated the change but it seems hopeless to catch all 
> scenarios.
>
> "JLGWhiz" wrote:
>
>>
>> Using the Worksheet_Change event, you could probably evaluate the row and
>> column values of the target cell against the criteria cell:
>>
>> If Target <> (Reference Cell) Then
>>    If Target.Row <=( Reference Cell).Row And _
>>      Target.Column <= (Reference Cell).Column Then
>>          'Update code here
>>    End If
>> End If
>>
>> The outer If statement avoids inadvertant changes to the reference cell.
>> The inner statement then checks to see if the target cell that is changed 
>> is
>> to the left or above the reference cell.
>>
>>
>> "sbo" <sbo@discussions.microsoft.com> wrote in message
>> news:089BA254-2C80-4C5F-A157-36950842D2C9@microsoft.com...
>> > I'm making a client to Excel that keeps references to cells on 
>> > worksheets.
>> > These references must be updated if the user inserts cells to the left 
>> > or
>> > above the referenced cells. I have not been able to find this 
>> > information,
>> > neither in the Range that accompanies the Change notification nor in 
>> > any
>> > of
>> > the Worksheet, Workbook, or Application properties. Can anybody help 
>> > me?
>>
>>
>> .
>> 


0
JLGWhiz
3/18/2010 8:46:37 PM
Thank you for this suggestion, which I think is very good. I my scenario I'm 
a bit worried about how Excel would handle thousands of named ranges, but I 
should give it a try since there apparently is no other solution. So: thank 
you very much for your help!

"JLGWhiz" wrote:

> I mis-read the insert cells bit.  Have you tried using named ranges to 
> overcome the reference changes caused by the user inserting cells, rows or 
> columns?  The named range, although moved to a different relative location 
> on the sheet will keep the name and can be referred to by name in formulas 
> and code.
> 
> 
> 
> "sbo" <sbo@discussions.microsoft.com> wrote in message 
> news:1B46B948-E00D-4169-893D-D5B259346E9D@microsoft.com...
> > The problem is that the Change event is emitted in many situations, both 
> > when
> > deleting and inserting cells and when changing cell contents. Furthermore,
> > the insertion (or deletion) may push cells down (up) or right (left). So 
> > more
> > accurate information is needed. I also thought of intercepting the user
> > action that initiated the change but it seems hopeless to catch all 
> > scenarios.
> >
> > "JLGWhiz" wrote:
> >
> >>
> >> Using the Worksheet_Change event, you could probably evaluate the row and
> >> column values of the target cell against the criteria cell:
> >>
> >> If Target <> (Reference Cell) Then
> >>    If Target.Row <=( Reference Cell).Row And _
> >>      Target.Column <= (Reference Cell).Column Then
> >>          'Update code here
> >>    End If
> >> End If
> >>
> >> The outer If statement avoids inadvertant changes to the reference cell.
> >> The inner statement then checks to see if the target cell that is changed 
> >> is
> >> to the left or above the reference cell.
> >>
> >>
> >> "sbo" <sbo@discussions.microsoft.com> wrote in message
> >> news:089BA254-2C80-4C5F-A157-36950842D2C9@microsoft.com...
> >> > I'm making a client to Excel that keeps references to cells on 
> >> > worksheets.
> >> > These references must be updated if the user inserts cells to the left 
> >> > or
> >> > above the referenced cells. I have not been able to find this 
> >> > information,
> >> > neither in the Range that accompanies the Change notification nor in 
> >> > any
> >> > of
> >> > the Worksheet, Workbook, or Application properties. Can anybody help 
> >> > me?
> >>
> >>
> >> .
> >> 
> 
> 
> .
> 
0
Utf
3/19/2010 7:42:01 AM
Reply:

Similar Artilces:

create logical user
I was wondering if it is possible to create a new Logical User for workflow purposes. For Example, in my workflow I want send mail to the [owners manager] and [practice manager] - is it possible to create this new logical user by referencing a field on the Lead & Opportunity forms... As far as I know, there is no way to do this. -- Matt Parks MVP - Microsoft CRM "chad.buser@ncsi.cc" <chadbuserncsicc@discussions.microsoft.com> wrote in message news:B4D2F6D7-1ACE-4B0E-85CF-AFB034E47633@microsoft.com... I was wondering if it is possible to create a new Logical User f...

Getting URL Cache Information
Hi, I have to get the URL information for an image. I dont know how to get it. Kindly help me. Thanks a lot. ...

lookup in one sheet and insert in second if not found
I have created a workbook with two sheets in it. I enter all my order in sheet one and the sheet two totals them. The column on my sheets ar as follow�. Sheet one STYLE SMALL MEDIUM LARGE 100 1 1 1 101 1 1 1 100 1 1 1 102 Sheet two STYLE SMALL MEDIUM LARGE 100 2 2 2 101 1 1 1 I have used the SUMIF command in the second sheet, which is correct Therefore my totals are correct. The only problem I am having is thi ��.that I have to enter the style numbers in the second sheet as well. What I want to do is, to have some kind...

See if cell is in Range
Thanks for taking the time to read my question. I'm passing a string that is a cell reference to a function. In that function I want to determine if that cell reference is within a predetermined range. Not sure how to do that. Right now I have: Function CheckRange(TheSheet As String, TheCell As Range) As Boolean If TheSheet = "Sheet1" Then if TheCell In Range("B4:B30") then 'This line is red as it is incorrect CheckRange = True End If What do I use instead of "In"? Thanks, Brad Brad Use something like: If In...

Editing in a cell
I am having a problem with a newly created workbook. When I go to a cell that has data in it and try to add more data to the existing string I lose all the previous information. The work sheet that I am working in is protected and I have "edit directly in cell" checked in the options section. It is almost like my double click (as well as F2) are being treated as a single click. Thank you I figured it out. For some reason "Hide" is selected for the unlocked cells. "John English" wrote: > I am having a problem with a newly created workbook. > > Wh...

How do I insert a clip art picture into a text box in word 2003
Trying to insert a picture into a text box in Word 2003 with very little success. Using Word as some students in the community do not have access to Publisher. Trying to create 4 invitations to a page. Want to use a text box to include text and graphics and then copy and paste four times. Help. Hi Lindas you'ld be better of posting to microsoft.public.word.newusers rather than an excel newsgroup personally, i would use a 2x2 (or 1x4) table in word for this rather than text boxes. Cheers JulieD "Lindas" <Lindas@discussions.microsoft.com> wrote in message news:...

Money signs appear in my Cell and I don't want them there
Im trying to enter the numbers 2.9 in cell like AD and it turns it into $2.90. That is not what I need, how do I stop that? Format as General instead of currency -- Regards, Peo Sjoblom http://nwexcelsolutions.com "kate" <kate@discussions.microsoft.com> wrote in message news:E9EEA936-437C-486F-A2D3-2385EF6BD2D0@microsoft.com... > Im trying to enter the numbers 2.9 in cell like AD and it turns it into > $2.90. That is not what I need, how do I stop that? ...

How do I get a cell to remember the last entry and add to it?
I am using Excel 2000. I want my cell to remember the number in it and add an additional number each time I need to retotal. Jab Sounds like you want that cell to be an accumlator cell. Simple question with complex results. You can have a cumulative total in a cell if you have a separate source cell for adding a new total to the original. Use at your own risk. I am Posting this just to show you how it can be done, not as a good solution. You would be much better off to have another column so you can keep track of past entries. Goes like this: =IF(CELL("address")="$C$4&...

getting message interface error and can't send or receive emails
If I try to move or delete an email I get a message - "The message interface has returned an unknown error. If this problem persists restart outlook." I have restarted outlook several times and also re-booted my PC several times. Still to no avail I cannot send or receive emails now either. While Outlook is closed, rename the Outcmd.dat and *.srs files, then open Outlook again and see if this helped. If not, please post back. Also, please post what version of Outlook you're using. If you have reached the limit on your PST, that could cause the error as well. -- Kathleen...

Migrating Users to New Exchange 2003 Server
Is there a way to change which server that Outlook 2000, 2002, 2003 looks at wihtout having to redo their profiles?? We have an Exchange 2000 server, E2K, and we are migrating some users, not all, to a new server that is coexisting, E2K3. I want to be able to move groups of users without having to go to their desktops and reconfigure Outlook. Thanks in advance, Russell ...

SELF Group vs. User permission to Mailbox
Came in this morning to a helpdesk call indicating that the user was getting the following message when trying to open their Outlook 2007 client: "Cannot open your default e-mail folders. Microsoft Exchange is not available. Either there are network problems or the Exchange computer is down for maintenance." In my Exchange server Application Log I see several instances of the following related to the affected user's mailbox: Event Type: Error Event Source: MSExchangeIS Event Category: General Event ID: 9646 Date: 11/27/2009 Time: 7:07:14 AM User: N/A ...

Changing the user password without the use of SA or DYNSA accounts
Is there any way to save a user password in GP v10 without the use of the SA or DYNSA login? I would like to grant security to one user to do this. Currently the save button is greyed out unless they log in as SA or DYNSA. Thank you You can do this by granting that user sysadmin rights in SQL management Studio. In SMS, expand the Security folder, then logins, then find that user, right click the user, go to properties, click server roles and check the box for sysadmin. "Junior De Alba" wrote: > Is there any way to save a user password in GP v10 without the use of the S...

65000 Row Limitation from Access to Excel
This question has been asked a couple times, but the answer seems to be unclear so I will ask again. I have query results in Access that I would like to work with in Excel - I like Excel pivot tables better than Access pivot tables. If I highlight the 287K line Access table, right click on copy, then paste into the 1 million plus row spreadsheet capacity in Excel, it only pastes 65,000 lines. Exports from Access to the million row plus spreadsheet work similarly only exporting 65,000 lines of the much larger table. I can successfully export the data from Access to a text file, then impor...

Bold report column based on month
I have a report with a column for each month. (Jan, Feb, Mar...) I'd like to bold the data in the column that the report is run for. I placed this code in the on Format event of the report to test, the code is executing ok but the columns end up all in normal weight. m = DatePart("m", dt) Select Case m Case 1 Me.Jan.FontWeight = vbBold Me.Feb.FontWeight = vbNormal Me.Mar.FontWeight = vbNormal Me.Apr.FontWeight = vbNormal Me.May.FontWeight = vbNormal Me.Jun.FontWeight = vbNormal Me.Jul.FontWeight = vbNormal Me.Aug.FontWeight = vbNormal Me...

how to get size of text in a Dialog?
MessageBoxes usually size themself according to the text displayed, depending on fontsize and number of chars. can anyone please explain (or point me to an URL where it is explained) how they do it? To keep it simple, lets say we have just a CStatic and a Text in it. How do i get the "optimal" values for the region for MoveWindow() ? ".rhavin grobert" <clqrq@yahoo.de> wrote in message news:1158252329.933561.100380@d34g2000cwd.googlegroups.com... > MessageBoxes usually size themself according to the text displayed, > depending on fontsize and number of char...

looking for range of text in a single cell
I just started a new job and my company already had a file with abou 16,000 records in it (called Products). This file contains all of th parts numbers that they carry. In this file there is one column calle "description". This cell contains the dimensions, color name and som other information such as if the piece is flat or rounded. I hav another file that has about 400 records and this file tells me "colo name" as well as the "type" of stone that it is (called Stone_Type). What I am trying to do is add a field to the Products file that wil say stone type. ...

getting calendar event reminder for deleted event
I keep getting a reminder for an event that has been deleted from my calendar in Outlook 2002. The event is past due but has been deleted so I get an error message saying there has been an error dismissing the reminder. How can I repair this? I've tried running Scanpst.exe but no joy. thanks, Larry larry.no...@gmail.com wrote: > I keep getting a reminder for an event that has been deleted from my > calendar in Outlook 2002. The event is past due but has been deleted > so I get an error message saying there has been an error dismissing the > reminder. > > How can I...

Getting Excel 2000 Options to be remembered
Two questions really: 1. How do I get options for a particular workbook to be saved? I am findng that the option to have R1C1 Reference style to be ticked does not get saved with the workbook but other options do. 2. How can I get options to be either global for all workbooks or for a range of workbooks? Thanks Mervyn Mervyn Unfortunately, R1C1 style is one of those options that is set by the first workbook that is opened in a session. Calculation Mode is another. You could set the R1C1 style in your Personal.xls which will open hidden with each session of Excel, thus ensuring that th...

Inserting pictures in a CHM file
With the HTML Help Workshop from MS, I'm trying to add pictures to some of my pages with mixed results. Small pictures seem to work ok but larger ones (>500 kb) don't. After compiling the help file, the frame is present with no picture. Can anyone help? Best regards, Fred Fred, > With the HTML Help Workshop from MS, I'm trying to add pictures to some of > my pages with mixed results. Small pictures seem to work ok but larger ones > (>500 kb) don't. After compiling the help file, the frame is present with > no picture. What tool are you using to ...

To have a 'fixed cell' be equal to the last data entered cell in a column
Good Evening All, I have a worksheet, example below. I have frozen the panes to always show rows 1-3. I wish the cell A3 to be the same as the last 'non-blank' cell in Column A.(See explanation below). A B 1 2 Header Header 3__________________ 4 M1004 5 M1005 6 M1002 7 M1003 8 M1006 9 M1001 10 So in this case, at present, A3 would be M1001,(A9), but when I enter a new value in A10, (eg M1008), I would like A3 to automatically update to A10 ie M1008. I think that maybe INDIRECT or OFFSET maybe involved but am fairly unfamiliar with ...

Help Freezing Multiple Rows
In earlier versions, you clicked below the rows you wanted frozen in place or to the right of the rows you wanted frozen. Now I see only how to freeze row 1. I have a main title in row 1, row 2 is blank and row 3 has column headings, so I want row 3 to stay frozen in place. I am having trouble accomplishing this and need help, please. Thank you. -- Virgo click on cell A4 then on the menu bar Window/Freeze Panes "Virgo" wrote: > In earlier versions, you clicked below the rows you wanted frozen in place or > to the right of the rows you wanted frozen....

Comparing columns and extracting data
I've got two columns with lots of data in each. Some unique, some the same. Short example: Col A cat cow dolphin dog fish horse snake zebra Col B bear cow dolphin dog fish hamster monkey zebra I would like to create additional columns with the following stipulations: Col C (what's common in both A & B) Col D (what's in A but not B) Col E (what's in B but not A) Any help is appreciated. These are all array formulas. **Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) > Col C (what's common in both A & B) Ass...

How to get email statistics from exch2003?
Hi there, I would like to know for last month, 1. how many incoming internet emails 2. how many outgoing internet emails Wondering how can I check that on exchange2003? Many thanks! On Fri, 13 May 2005 10:57:31 +0800, "Zac" <zacfang@hotmail.com> wrote: >Hi there, > >I would like to know for last month, > >1. how many incoming internet emails > >2. how many outgoing internet emails > >Wondering how can I check that on exchange2003? > >Many thanks! > You can get this information if you have Message Tracking enabled on your servers. I...

add row
Hello, I have a spreadsheet that has 250 rows, I have to add a blank row after every fifth row, is there a formula to apply to my spreadsheet. Manually is quite a hassle. Thank you, all -- smile Sub addrowevery5() counter = 6 Do Until Cells(counter, "a") = "" Rows(counter).Insert counter = counter + 6 Loop End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "israel" <israel@discussions.microsoft.com> wrote in message news:040FF41D-ECA7-40CA-A17F-2AD1FCFBE89A@microsoft.com... > Hello, > > I have a spread...

Can a user update his own information
I've played around with the permissions but cannot seem to give a user permission to update his own phone numbers, email, etc. in CRM. This doesn't seem like an IT function at all but we keep having to update this information for our CRM users. Please help. ...