Databases
StackOverflow2013
Postsdbo
POSelecting cell in Beforesave Event
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSelecting cell in Beforesave Event
    primarykey
    data
    text
    <p>see codes below. I have the 'beforesave' code in the Workbook module and it works fine when I'm in the active sheet. However from the table I use on sheet 2 I also have a pivot table on sheet 1. To refresh my pivot I use an inserted button with an attached macro (this is in the module section)</p> <pre><code> Sub Refresh_Pivot() ' ' Refresh_Pivot Macro ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh ActiveWorkbook.Save End Sub </code></pre> <p>On Activate.Workbook.Save its starts to act on my other code (which is in the workbook module), I want this to happen as a pivot table with missing data is not a good tool. However on using this it defaults with an error and highlights the cell.Offset(0, 1).Select - How can I prevent this?</p> <p>Ideally I want the user to select OK on the msgbox and then the screen page changes to Sheet 2 and highlights the offending cell.</p> <pre><code>Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) Dim esave As Range Dim psave As Range Dim jsave As Range Dim RAll As Range Dim cell As Range Set esave = Sheet2.Range("Table1[Estimated Claim (USD)]") Set psave = Sheet2.Range("Table1[Provisional Claim (USD)]") Set jsave = Sheet2.Range("Table1[Agreed Claim (USD)]") Set RAll = Union(esave, psave, jsave) For Each cell In RAll If cell.Value &lt;&gt; "" And cell.Offset(0, 1).Value = "" Then Dim missdata missdata = MsgBox("Missing Data - Enter the Date for WorkBook to Save", vbOKOnly, "Missing Data") Cancel = True cell.Offset(0, 1).Select Exit For End If Next cell End Sub </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USJimQ
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COYou need to add Sheet2.Activate before you run the for loop.
      singulars
      1. POSelecting cell in Beforesave Event
      1. USSiddharth Rout
 

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