vba macro in unload or exit area of Excel

[wengang]

Hey guys. I want to write a VBA macro in Excel that basically checks if a certain cell has data in it. If so, the program can't close. Also, if possible, if that cell has data in it, the program can't save.

Possible?
Thanks

[mlewis]

Why would you do that? seems to me it would force Excel to be open 24/7... Is that what you're after?

Besides, I don't know if it is possible to prevent excel from closing from VBA, without some nasty hacks.

[KWell]

Take a look at this code
In the example, the program can't close and it can't save if it's data in cells(1,1) of the worksheet 'Sheet1'.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 ' The program can't close if Cancel=True
 Cancel = True
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 ' Check your cell here
 If Worksheets("Sheet1").Cells(1,1)<>"" then
    Cancel = True
 End If
End Sub

[wengang]

KWell
that's along the lines of what i'm looking for. but it didn't work. my program saved and closed with data in cell 1,1 (I assume that's A1) of worksheet Sheet1.

Any ideas?

[KWell]

Sorry, i forget to tell you an important thing. You must put the code in the 'ThisWorkbook' Microsoft Excel Objects

[wengang]

Well, I'm still a bit hazy about this. I pasted the two into module 1 of the book i'm working on. But when I click Macros on the file menu, they dont show up in any list, all workbooks, this workbook, or any other. Then if I open the VB editor, they're there in Module 1 with my other macros.

Do you have any idea why?

[KWell]

The macros i made must be in ThisWorkbook because they are event Word
- Microsoft Excel Objects
| |- Sheet1(Sheet1)
| |- Sheet2(Sheet2)
| |- Sheet3(Sheet3)
| |- ...
| |- ThisWorkBook <-- Here
- Modules
|-Module1
|-...

For the macros show in the macro menu, only public sub (without Public or Private, a sub is public) are visible.

[wengang]

What I mean to say is that I don't know how to put them in the THIS WORKBOOK section. I have never seen any option for this. If I choose Tools>Macros>... I get Module 1.


If I choose to create a macro, and select save in "THis workbook" the name of this sub always returns "NOT A VALID NAME"

how do i select the THIS WORKBOOK for saving those subs?

Thanks

[wengang]

KWELL,
SO SORRY.

Geeez. My project explorer window was closed.

Everything works just fine.


Really thanks so much. ANd thanks for your patience.