Error help BeforeClose

[Emoncada]

I have a simple code that for some reason it gives me an error once in a while.

[VBA]Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)

FrmTotals.Show

End Sub[/VBA]

Now I have this on a spreadsheet that users input data and when closed the form comes up and gives then totals. Now I don't know if it's something the user is doing because it doesn't occur to all users.

It gives the user a RunTime error '13' and End Button.

When I unlock the vb and close I get the RunTime Error when I click Debug It highlights

FrmTotals.Show

And when i put the mouse over it it says

FrmTotals = <Object variable or With block variable not set>

Any Ideas?

[Emoncada]

My temporary fix is copy the data and paste it into the original template spreadsheet and save it. Which always works fine.

[mdmackillop]

Try
[VBA]
Private Sub Workbook_BeforeClose(Cancel As Boolean)
FrmTotals.Show
Cancel = True
End Sub

[/VBA]

[Emoncada]

will do thanks mdmack

[Emoncada]

mdmack that shows the form but doesn't close the workbook after form closes.

The form is a totals form with a close button the saves the activeworkbook, then closes the form.

I need for it to close the workbook after form closes.

[Bob Phillips]

Remove the line
Cancel = True

[Emoncada]

Yeah that's what i ended up doing. Can you see what you think xld

I have a simple code that for some reason it gives me an error once in a while.

[vba]


Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) FrmTotals.Show End Sub [/vba]

Now I have this on a spreadsheet that users input data and when closed the form comes up and gives then totals. Now I don't know if it's something the user is doing because it doesn't occur to all users.

It gives the user a RunTime error '13' and End Button.

When I unlock the vb and close I get the RunTime Error when I click Debug It highlights

FrmTotals.Show

And when i put the mouse over it it says

FrmTotals = <Object variable or With block variable not set>

Any Ideas?

[mdmackillop]

What is the code behind the form Close button?

[Emoncada]

This is in the ThisWorkbook
[VBA]Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)

FrmTotals.Show

End Sub[/VBA]

This behind the FrmTotals Form
[VBA]Private Sub CmdClose_Click()
ActiveWorkbook.Save
Unload FrmTotals
End Sub
Private Sub UserForm_Initialize()
FrmTotals.LblTotalDepot.Caption = Range("$R$1")
FrmTotals.LblTotalVam.Caption = Range("$S$1")
FrmTotals.LblTotalMortgage.Caption = Range("$T$1")
FrmTotals.LblTotalServiceReq.Caption = Range("$U$1")
FrmTotals.LblGrandTotal.Caption = Range("$V$1")

End Sub[/VBA]

hope that helps.

[mdmackillop]

Is frmTotals in the workbook that is closing, in Personal.xls or elsewhere?

[Emoncada]

Yes the FrmTotals is attached with the spreadsheet if that's what you mean. It's not an external spreadsheet with the form.

[mdmackillop]

I don't see a problem with your code, but I don't see its purpose.
Why load a form just to save the workbook?
The data to populate the form is not sheet specific. Does this matter? Could this cause the error?

[Emoncada]

The Form just shows the user Total Orders by group. I have the Activeworkbook.save just to make sure it's saved before closing. Unless there is a better way you can recommend to show the user the totals and close the workbook.