Still need help - Clear a cell's contents when document is closed

[Gary]

Here's my last dialogue. I'm still unable to clear a cell upon Close

Thanks FSt1 !
I'm a novice and need further clarification.

The subroutine did not run as I received the Macro Disabled message. I
clicked OK, and next time I opened the workbook, I didn't get the Macro
Warning, but the subroutine apparently didn't run as the chosen cells were
not blank.

I don't want people who use my workbook to have to deal with macro warnings.
Is this something that must be set on each user's computer, or can I set it
in my workbook so the subroutine automatically runs on everyone's computer?

Do I need to get a Certificate?

Here's my code. Are the () and the " " necessary?
I'm working in Sheet 8 and
want Cells A124 and A125 to clear upon Close.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Sheet8").Range("A124").ClearContents
Sheets("Sheet8").Range("A125").ClearContents
End Sub

Here's the subroutine I deleted. Is this OK to delete? What does it do?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

Thanks!
Gary

 

[JLatham]

The reason the cells still contained information was, as you've no doubt
figured out, because macros were disabled, so the code couldn't run to clear
the cells.

You don't want people to have to deal with macro warnings, well, it's hard
to get around that little thing. You could tell your users to set Macro
Security to LOW which is pretty much "off" and very unsafe for them to use.
They can set it to MEDIUM which will give a warning, offering them the
opportunity to use macros or not - the lowest setting that I recommend - but
always requires an extra click to get a workbook containing macros to open.
If you had (and they had) Excel 2007, you could declare a trusted location on
their system/network where any workbooks stored would open without any
warnings.

Should I get a certificate: probably not unless you have pretty deep
pockets. They're expensive. You can 'self certify' and ask your users to
add you to their trusted publishers. This may be the easy way.

Your application cannot control their macro security.

As for the Private Sub Workbook_BeforeClose(Cancel As Boolean) code, yes,
all of the parenthesis and quotation marks are absolutely required for it to
run as it is now written.

As for the _SelectionChange() routine, and you deleting it - that was just
fine. No harm done at all. That's the default routine 'stub' that is
created when you start a worksheet event code section.

BTW: your Workbook_BeforeClose() code should be in the ThisWorkbook code
area, not in one of the worksheet's code areas.

I'm with Gary - there simply is no non-VBA/Macro way of clearing a cell when
you close a workbook. Kind of would be cool if there was something like
conditional formatting that is dependent upon the worksheet/workbook events
for this kind of thing... Excel 2020 perhaps?

 

[FSt1]

hi
i just ran the macro on a test file and it worked file. you may need to set
your macro security to low.
2003 on the menu bar>tools>macro>security
2007 office button>excel options>popular>show developers tab on ribbon

as to your other users, you will have to set their security to low also. you
can't do that with the file so far as i know.

oh! and i left out a line on the macro....
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Sheet1").Range("A24").ClearContents
Sheets("Sheet1").Range("A25").ClearContents
ActiveWorkbook.Close True
End Sub

this will prevent you from having to save the file again after the macro
runs. it will save the file again but it will be automatic.

regards
FSt1

 

[Gary]

Thanks you guys!

hi
i just ran the macro on a test file and it worked file. you may need to set
your macro security to low.
2003 on the menu bar>tools>macro>security
2007 office button>excel options>popular>show developers tab on ribbon

as to your other users, you will have to set their security to low also. you
can't do that with the file so far as i know.

oh! and i left out a line on the macro....
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("Sheet1").Range("A24").ClearContents
Sheets("Sheet1").Range("A25").ClearContents
ActiveWorkbook.Close True
End Sub

this will prevent you from having to save the file again after the macro
runs. it will save the file again but it will be automatic.

regards
FSt1

 

[Gary]

I guess my next question is how do i re-enable macros so this one will run?

 

[JLatham]

Gary,
Changes to Excel's macro security level do not take place immediately.
After you make a change, you have to close Excel and then reopen it for the
change you made to take effect.

 

[Gary]

If I have a macro for my use, but don't want others, who get my workbook, to
deal with the macro warning, is there a way to disable the macro before I
send the file or do I need to delete the macro and then send the file?

 

[FSt1]

hi
delete the macro. if you don't want others to use it then it's just extra
unnecessary baggage.

Regards
FSt1