Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p><strong>Note</strong>: I have been referring people to this link quite often now so I will make this a one stop post for <code>Worksheet_Change</code>. Every now and then, when I get the time, I will add new content to this so people can benefit for it.</p> <hr> <p>I always recommend this when using <code>Worksheet_Change</code></p> <ol> <li><p>You do not need the sheet name. It is understood that the code is to be run on current sheet <strong>UNLESS</strong> you are trying to use another sheet as a reference. Is "testpage" the Activesheet name or is it a different sheet?</p></li> <li><p>Whenever you are working with <code>Worksheet_Change</code> event. Always switch <code>Off</code> events if you are writing data to the cell. This is required so that the code doesn't go into a possible endless loop</p></li> <li><p>Whenever you are switching off events, use error handling else if you get an error, the code will not run the next time.</p></li> </ol> <p>Try this</p> <pre><code>Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Whoa Application.EnableEvents = False Range("A1:A8").Formula = "=B1+C1" Letscontinue: Application.EnableEvents = True Exit Sub Whoa: MsgBox Err.Description Resume Letscontinue End Sub </code></pre> <p>Few other things that you may want to know when working with this event.</p> <p>If you want to ensure that the code doesn't run when more than one cell is changed then add a small check</p> <pre><code>Private Sub Worksheet_Change(ByVal Target As Range) '~~&gt; For Excel 2003 If Target.Cells.Count &gt; 1 Then Exit Sub ' '~~&gt; Rest of code ' End Sub </code></pre> <p>The <code>CountLarge</code> was introduced in Excel 2007 onward because <code>Target.Cells.Count</code> returns an <code>Integer</code> value which errors out in Excel 2007 becuase of increased rows/columns. <code>Target.Cells.CountLarge</code> returns a <code>Long</code> value.</p> <pre><code>Private Sub Worksheet_Change(ByVal Target As Range) '~~&gt; For Excel 2007 If Target.Cells.CountLarge &gt; 1 Then Exit Sub ' '~~&gt; Rest of code ' End Sub </code></pre> <p>To work with all the cells that were changed use this code</p> <pre><code>Private Sub Worksheet_Change(ByVal Target As Range) Dim aCell As Range For Each aCell In Target.Cells With aCell '~~&gt; Do Something End With Next End Sub </code></pre> <p>To detect change in a particular cell, use <code>Intersect</code>. For example, if a change happens in Cell <code>A1</code>, then the below code will fire</p> <pre><code>Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1")) Is Nothing Then MsgBox "Cell A1 was changed" '~~&gt; Your code here End If End Sub </code></pre> <p>To detect change in a particular set of range, use <code>Intersect</code> again. For example, if a change happens in range <code>A1:A10</code>, then the below code will fire</p> <pre><code>Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Range("A1:A10")) Is Nothing Then MsgBox "Cell in A1:A10 range was changed" '~~&gt; Your code here End If End Sub </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POWhy MS Excel crashes and closes during Worksheet_Change Sub procedure?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USSiddharth Rout
    1. USSiddharth Rout
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POWhy MS Excel crashes and closes during Worksheet_Change Sub procedure?
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COthanks Siddharth when i removed the sheet name it worked fine without any crashes
      singulars
      1. PO
      1. USderek
    2. COGr8! I hope you switched off/on the events as well as suggested above?
      singulars
      1. PO
      1. USSiddharth Rout
    3. CONo will i have to do that aswell ?
      singulars
      1. PO
      1. USderek
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload