Selecting cell in Beforesave Event

Question

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)

 Sub Refresh_Pivot()
 '
 ' Refresh_Pivot Macro

   ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
   ActiveWorkbook.Save

 End Sub

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?

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.

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 <> "" 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

You need to add Sheet2.Activate before you run the for loop. — Siddharth Rout, Dec 13 '13 at 17:11

score 1 · Accepted Answer · edited May 23 '17 at 11:56

.Select should be avoided. INTERESTING READ

I also want to know why are you trying to select that cell? What is the purpose. If you want to interact with it, then you can do that without selecting it. For example

If cell.Value <> "" 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
    With cell.Offset(0, 1)
        '
        ''~~> Do something
        '
    End With
    Exit For
End If

Having said that if you still want to select that cell then you need to be on that sheet. There are two ways now. One is like I mentioned in the comment above.

Add Sheet2.Activate just before For Each cell In RAll in the Workbook_BeforeSave event or do that in the button's click event.

Sub Refresh_Pivot()
   ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
   Sheet2.Activate
   ActiveWorkbook.Save
End Sub

Another point. You might want to pass Cancel = True before the Exit For to disable the save?

Selecting cell in Beforesave Event

1 Answers1