PDA

View Full Version : Error help BeforeClose



Emoncada
02-14-2008, 09:59 AM
I have a simple code that for some reason it gives me an error once in a while.

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)

FrmTotals.Show

End Sub

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
02-14-2008, 10:01 AM
My temporary fix is copy the data and paste it into the original template spreadsheet and save it. Which always works fine.

mdmackillop
02-14-2008, 02:57 PM
Try

Private Sub Workbook_BeforeClose(Cancel As Boolean)
FrmTotals.Show
Cancel = True
End Sub

Emoncada
02-15-2008, 06:24 AM
will do thanks mdmack

Emoncada
02-15-2008, 08:09 AM
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
02-15-2008, 08:19 AM
Remove the line
Cancel = True

Emoncada
02-15-2008, 08:31 AM
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.







Option Explicit Private Sub Workbook_BeforeClose(Cancel As Boolean) FrmTotals.Show End Sub




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
02-15-2008, 10:25 AM
What is the code behind the form Close button?

Emoncada
02-15-2008, 10:48 AM
This is in the ThisWorkbook
Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)

FrmTotals.Show

End Sub

This behind the FrmTotals Form
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

hope that helps.

mdmackillop
02-15-2008, 11:08 AM
Is frmTotals in the workbook that is closing, in Personal.xls or elsewhere?

Emoncada
02-15-2008, 11:38 AM
Yes the FrmTotals is attached with the spreadsheet if that's what you mean. It's not an external spreadsheet with the form.

mdmackillop
02-15-2008, 12:53 PM
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
02-15-2008, 01:23 PM
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.