Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>The cleanest solution would be this:</p> <p>Add an event handler at the workbook level to capture cells being changed; in the handler, do these things:</p> <ul> <li>turn off event handling (you are going to change the worksheet and don't want to go into an infinite loop)!</li> <li>turn off screen updating</li> <li>insert a row on the front sheet, in row 1</li> <li>enter a copy of the changed row there</li> <li>add the user who changed it, and the date/time, in an additional column (if you would like)</li> <li>return to the original selection</li> <li>turn on screen updating</li> <li>turn on event handling</li> </ul> <p>Here are the step-by-step instructions (example file can be downloaded from <a href="http://www.floris.us/SO/download/XLexample.xlsm" rel="nofollow">http://www.floris.us/SO/download/XLexample.xlsm</a>) - assuming Excel 2010 on PC. Other versions will have mostly small differences...</p> <ol> <li>Make sure your file is saved as <code>.xlsm</code> format - this tells Excel there are macros</li> <li>Create a backup of the file before adding all this stuff - in case you mess something up!</li> <li>Close all other files (for now) - see earlier comment</li> <li>Make sure your file has four worksheets: "summary", "widgets", "things", and "stuff" (or whatever names you find helpful - I will refer to them by these names, rather than "Sheet1" etc.)</li> <li>Right-click on the tab of "widgets", and select "View code"</li> <li>paste the following code into the "code" window for the worksheet:</li> </ol> <p>. </p> <pre><code>Private Sub Worksheet_Change(ByBal Target as Range) On Error GoTo procErr process_change Target Exit Sub procErr: MsgBox "Got an error: " &amp; Err.Description Err.Clear Application.EnableEvents = True End Sub` </code></pre> <ol> <li>Repeat the above steps for each of the "data" worksheets: "things", and "stuff" (but NOT for "summary")</li> <li>While the Visual Basic Editor is open (that's where you did all that pasting), insert a new code module in the workbook, using Insert->Module</li> <li>Paste the following code in the module you created:</li> </ol> <p>.</p> <pre><code>Option Explicit Sub process_change(ByVal Target As Range) ' when a cell is changed on one of the worksheets, this function is called ' it copies the most recently changed row ' and inserts it on the second line of the "summary" worksheet ' right below the headers ' if the headers include "changed by" and/or "last changed" (exactly) ' then that column will be updated with the (windows) user name and date, respectively ' similarly, if a column named "source" exists, it will contain the address of the row ' (sheet name / row number). In that case, if there was an earlier occurrence of the same row ' (multiple edits), the earlier occurence is removed ' you may use this code as is - but there is no warranty as to its useability Dim s1 As Worksheet, s2 As Worksheet Dim srcAddress As String Dim oldSelection As Range ' don't update screen during processing - prevent "flickering" Application.ScreenUpdating = False ' set to True when debugging ' don't accept events until we're done Application.EnableEvents = False ' store old selection Set oldSelection = Selection Dim ri As Integer ' index of changed row Dim rowAddress As String ri = Target.Row rowAddress = ri &amp; ":" &amp; ri ' address of changed row if ri = 1 Then Application.EnableEvents = True Exit Sub ' don't record changes to the headers End If Range(rowAddress).Select Selection.Copy ' copy changed row Set s1 = ActiveSheet ' know where we will go back to srcAddress = s1.Name &amp; ":row" &amp; ri ' full address to be used later Set s2 = ActiveWorkbook.Sheets("summary") s2.Range("2:2").Insert ' add a row at the top of the list s2.Select ' activate sheet where we want to paste Range("A2").Select ' leftmost cell of column ActiveSheet.Paste ' paste the entire changed row ' optionally, we can add "source", "last changed" and "changed by" ' we do this if appropriately named columns exist ' slightly clumsy code to catch errors... Dim lcCol If Not IsError(Application.Match("last changed", Range("1:1"), 0)) Then lcCol = Application.Match("last changed", Range("1:1"), 0) Range("A2").Offset(0, lcCol - 1).Value = Date End If Dim cbCol If Not IsError(Application.Match("changed by", Range("1:1"), 0)) Then cbCol = Application.Match("changed by", Range("1:1"), 0) Range("A2").Offset(0, cbCol - 1).Value = UserName End If Dim srcCol If Not IsError(Application.Match("source", Range("1:1"), 0)) Then srcCol = Application.Match("source", Range("1:1"), 0) ' find earlier entry regarding this row... Columns("A:A").Offset(0, srcCol - 1).Select Dim sf As Range Set sf = Selection.Find(What:=srcAddress, After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _ xlNext, MatchCase:=False, SearchFormat:=False) If Not sf Is Nothing Then ri = sf.Row rowAddress = ri &amp; ":" &amp; ri ' address of changed row Range(rowAddress).Select Selection.Delete End If Range("A2").Offset(0, srcCol - 1).Value = srcAddress End If s1.Activate ' go back to original worksheet Application.CutCopyMode = False ' get rid of the "marching ants" oldSelection.Select ' select the previous selection "like nothing happened" ' and turn on screenupdating and events... Application.EnableEvents = True Application.ScreenUpdating = True End Sub Sub eventsOn() Application.EnableEvents = True End Sub Public Function UserName() ' note - this function only works on PC UserName = Environ$("UserName") End Function </code></pre> <p>With all that done, you can now put headers in your sheets - use the same column headers in all four sheets. In the first (summary) sheet, you can optionally add three more headers: these should not be the same as other headers you used, and be called (exactly - no extra spaces, capitalization, ...): <code>source</code>, <code>last changed</code>, <code>changed by</code>. </p> <p>If these last three column headings are not there, the behavior will be as follows:</p> <p>Every time you make a change to one of the three worksheets, the row in which the change was made will be copied into the first row of the summary sheet, below the headers. Everything else will move down one row.</p> <p>If you add the "source" column, two things will happen: the source (sheet name : row number) will be added in that column, and any previous entries for that same source (same row) will be deleted. Thus you will only see "the most recent change" for a given row.</p> <p>If you add "changed by", you will get the name of the user who made the last change; the "last changed" header will have the date of the last change.</p> <p>Let me know if you can figure it out from here - use the example spreadsheet which I linked above to guide you if you get stuck.</p>
    singulars
    1. This table or related slice is empty.
    1. PODisplay last updated row in overview page - Excel
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USFloris
    1. USFloris
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PODisplay last updated row in overview page - Excel
      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. VTAcceptedByOriginator
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COHi, not very familiar with VBA, is it possible to use some formulas in excel to do this? What im looking for is this: http://s9.postimage.org/mjkh64h4v/pic.png . Thanks
      singulars
      1. PO
      1. USSlider105
    2. COHang in there... I have created a "complete solution" for you. Can't upload it right now - but you're going to love it. Will be able to give it in about two hours. Will be worth the wait, I promise.
      singulars
      1. PO
      1. USFloris
    3. COJust looked at the format of the spreadsheet you used. It's pretty trivial to change the column headers to get what you were asking for... I did just realize that if you start editing headers, these changes will show up as an "edit to row 1". I will modify the code to trap that...
      singulars
      1. PO
      1. USFloris
 

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