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

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

"FSt1" wrote:

> hi
> AFAIK there isn't a non macro way to do it.
> try this.
> Private Sub Workbook_BeforeClose(Cancel As Boolean)
> Sheets("Sheet1").Range("A4").ClearContents
> Sheets("Sheet1").Range("A6").ClearContents
> End Sub
> 
> this is workbook code. press Alt+F11 to bring up the VB editor. in the 
> project window(far left), expand your project(file). double click 
> ThisWorkbook. in the code window,(far right) click the left dropdown at the 
> top and click this workbook.
> 
> Private Sub Workbook_Open() should default in. delete it and paste the above 
> in in it place.
> 
> if you don't like the before close event, you can try the before save event 
> but this would clear the cells out each time you save the file.
> 
> adjust the ranges and sheet name to suit.
> 
> Regards
> FSt1
> 
> 
> "Gary" wrote:
> 
> > Is there a non-macro way to clear a cell's contents automatically when the 
> > spreadsheet is saved? 
> > 
> > This is one of several un-locked cells in my spreadsheet and I  want to 
> > clear 1 or 2 specific cells each time a document is closed. I have hidden 
> > notes (a price list) that are only visible via Conditional formatting color 
> > change from white to black if a password is entered in the cell I want to 
> > clear upon Saving or exiting a document to prevent the next user, possibly a 
> > customer, from seeing the notes (price list) if not authorized.
> > 
> > If I need a macro, what would you suggest?
> > 
> > Also, if I send my spread to other users, will the macro work? Must they 
> > choose "Enable Macros?" each time the document is opened?

0
Utf
11/24/2009 6:54:03 PM
excel.misc 78881 articles. 5 followers. Follow

7 Replies
779 Views

Similar Articles

[PageSpeed] 51

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?


"Gary" wrote:

> 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
> 
> "FSt1" wrote:
> 
> > hi
> > AFAIK there isn't a non macro way to do it.
> > try this.
> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > Sheets("Sheet1").Range("A4").ClearContents
> > Sheets("Sheet1").Range("A6").ClearContents
> > End Sub
> > 
> > this is workbook code. press Alt+F11 to bring up the VB editor. in the 
> > project window(far left), expand your project(file). double click 
> > ThisWorkbook. in the code window,(far right) click the left dropdown at the 
> > top and click this workbook.
> > 
> > Private Sub Workbook_Open() should default in. delete it and paste the above 
> > in in it place.
> > 
> > if you don't like the before close event, you can try the before save event 
> > but this would clear the cells out each time you save the file.
> > 
> > adjust the ranges and sheet name to suit.
> > 
> > Regards
> > FSt1
> > 
> > 
> > "Gary" wrote:
> > 
> > > Is there a non-macro way to clear a cell's contents automatically when the 
> > > spreadsheet is saved? 
> > > 
> > > This is one of several un-locked cells in my spreadsheet and I  want to 
> > > clear 1 or 2 specific cells each time a document is closed. I have hidden 
> > > notes (a price list) that are only visible via Conditional formatting color 
> > > change from white to black if a password is entered in the cell I want to 
> > > clear upon Saving or exiting a document to prevent the next user, possibly a 
> > > customer, from seeing the notes (price list) if not authorized.
> > > 
> > > If I need a macro, what would you suggest?
> > > 
> > > Also, if I send my spread to other users, will the macro work? Must they 
> > > choose "Enable Macros?" each time the document is opened?
> 
0
Utf
11/24/2009 8:18:01 PM
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" wrote:

> 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
> 
> "FSt1" wrote:
> 
> > hi
> > AFAIK there isn't a non macro way to do it.
> > try this.
> > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > Sheets("Sheet1").Range("A4").ClearContents
> > Sheets("Sheet1").Range("A6").ClearContents
> > End Sub
> > 
> > this is workbook code. press Alt+F11 to bring up the VB editor. in the 
> > project window(far left), expand your project(file). double click 
> > ThisWorkbook. in the code window,(far right) click the left dropdown at the 
> > top and click this workbook.
> > 
> > Private Sub Workbook_Open() should default in. delete it and paste the above 
> > in in it place.
> > 
> > if you don't like the before close event, you can try the before save event 
> > but this would clear the cells out each time you save the file.
> > 
> > adjust the ranges and sheet name to suit.
> > 
> > Regards
> > FSt1
> > 
> > 
> > "Gary" wrote:
> > 
> > > Is there a non-macro way to clear a cell's contents automatically when the 
> > > spreadsheet is saved? 
> > > 
> > > This is one of several un-locked cells in my spreadsheet and I  want to 
> > > clear 1 or 2 specific cells each time a document is closed. I have hidden 
> > > notes (a price list) that are only visible via Conditional formatting color 
> > > change from white to black if a password is entered in the cell I want to 
> > > clear upon Saving or exiting a document to prevent the next user, possibly a 
> > > customer, from seeing the notes (price list) if not authorized.
> > > 
> > > If I need a macro, what would you suggest?
> > > 
> > > Also, if I send my spread to other users, will the macro work? Must they 
> > > choose "Enable Macros?" each time the document is opened?
> 
0
Utf
11/24/2009 8:20:06 PM
Thanks you guys!

"FSt1" wrote:

> 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" wrote:
> 
> > 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
> > 
> > "FSt1" wrote:
> > 
> > > hi
> > > AFAIK there isn't a non macro way to do it.
> > > try this.
> > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > Sheets("Sheet1").Range("A4").ClearContents
> > > Sheets("Sheet1").Range("A6").ClearContents
> > > End Sub
> > > 
> > > this is workbook code. press Alt+F11 to bring up the VB editor. in the 
> > > project window(far left), expand your project(file). double click 
> > > ThisWorkbook. in the code window,(far right) click the left dropdown at the 
> > > top and click this workbook.
> > > 
> > > Private Sub Workbook_Open() should default in. delete it and paste the above 
> > > in in it place.
> > > 
> > > if you don't like the before close event, you can try the before save event 
> > > but this would clear the cells out each time you save the file.
> > > 
> > > adjust the ranges and sheet name to suit.
> > > 
> > > Regards
> > > FSt1
> > > 
> > > 
> > > "Gary" wrote:
> > > 
> > > > Is there a non-macro way to clear a cell's contents automatically when the 
> > > > spreadsheet is saved? 
> > > > 
> > > > This is one of several un-locked cells in my spreadsheet and I  want to 
> > > > clear 1 or 2 specific cells each time a document is closed. I have hidden 
> > > > notes (a price list) that are only visible via Conditional formatting color 
> > > > change from white to black if a password is entered in the cell I want to 
> > > > clear upon Saving or exiting a document to prevent the next user, possibly a 
> > > > customer, from seeing the notes (price list) if not authorized.
> > > > 
> > > > If I need a macro, what would you suggest?
> > > > 
> > > > Also, if I send my spread to other users, will the macro work? Must they 
> > > > choose "Enable Macros?" each time the document is opened?
> > 
0
Utf
11/24/2009 8:36:03 PM
I guess my next question is how do i re-enable macros so this one will run?

"JLatham" wrote:

> 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?
> 
> 
> "Gary" wrote:
> 
> > 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
> > 
> > "FSt1" wrote:
> > 
> > > hi
> > > AFAIK there isn't a non macro way to do it.
> > > try this.
> > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > Sheets("Sheet1").Range("A4").ClearContents
> > > Sheets("Sheet1").Range("A6").ClearContents
> > > End Sub
> > > 
> > > this is workbook code. press Alt+F11 to bring up the VB editor. in the 
> > > project window(far left), expand your project(file). double click 
> > > ThisWorkbook. in the code window,(far right) click the left dropdown at the 
> > > top and click this workbook.
> > > 
> > > Private Sub Workbook_Open() should default in. delete it and paste the above 
> > > in in it place.
> > > 
> > > if you don't like the before close event, you can try the before save event 
> > > but this would clear the cells out each time you save the file.
> > > 
> > > adjust the ranges and sheet name to suit.
> > > 
> > > Regards
> > > FSt1
> > > 
> > > 
> > > "Gary" wrote:
> > > 
> > > > Is there a non-macro way to clear a cell's contents automatically when the 
> > > > spreadsheet is saved? 
> > > > 
> > > > This is one of several un-locked cells in my spreadsheet and I  want to 
> > > > clear 1 or 2 specific cells each time a document is closed. I have hidden 
> > > > notes (a price list) that are only visible via Conditional formatting color 
> > > > change from white to black if a password is entered in the cell I want to 
> > > > clear upon Saving or exiting a document to prevent the next user, possibly a 
> > > > customer, from seeing the notes (price list) if not authorized.
> > > > 
> > > > If I need a macro, what would you suggest?
> > > > 
> > > > Also, if I send my spread to other users, will the macro work? Must they 
> > > > choose "Enable Macros?" each time the document is opened?
> > 
0
Utf
11/24/2009 8:41:03 PM
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" wrote:

> I guess my next question is how do i re-enable macros so this one will run?
> 
> "JLatham" wrote:
> 
> > 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?
> > 
> > 
> > "Gary" wrote:
> > 
> > > 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
> > > 
> > > "FSt1" wrote:
> > > 
> > > > hi
> > > > AFAIK there isn't a non macro way to do it.
> > > > try this.
> > > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > > Sheets("Sheet1").Range("A4").ClearContents
> > > > Sheets("Sheet1").Range("A6").ClearContents
> > > > End Sub
> > > > 
> > > > this is workbook code. press Alt+F11 to bring up the VB editor. in the 
> > > > project window(far left), expand your project(file). double click 
> > > > ThisWorkbook. in the code window,(far right) click the left dropdown at the 
> > > > top and click this workbook.
> > > > 
> > > > Private Sub Workbook_Open() should default in. delete it and paste the above 
> > > > in in it place.
> > > > 
> > > > if you don't like the before close event, you can try the before save event 
> > > > but this would clear the cells out each time you save the file.
> > > > 
> > > > adjust the ranges and sheet name to suit.
> > > > 
> > > > Regards
> > > > FSt1
> > > > 
> > > > 
> > > > "Gary" wrote:
> > > > 
> > > > > Is there a non-macro way to clear a cell's contents automatically when the 
> > > > > spreadsheet is saved? 
> > > > > 
> > > > > This is one of several un-locked cells in my spreadsheet and I  want to 
> > > > > clear 1 or 2 specific cells each time a document is closed. I have hidden 
> > > > > notes (a price list) that are only visible via Conditional formatting color 
> > > > > change from white to black if a password is entered in the cell I want to 
> > > > > clear upon Saving or exiting a document to prevent the next user, possibly a 
> > > > > customer, from seeing the notes (price list) if not authorized.
> > > > > 
> > > > > If I need a macro, what would you suggest?
> > > > > 
> > > > > Also, if I send my spread to other users, will the macro work? Must they 
> > > > > choose "Enable Macros?" each time the document is opened?
> > > 
0
Utf
11/24/2009 10:57:01 PM
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" wrote:

> 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" wrote:
> 
> > 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
> > 
> > "FSt1" wrote:
> > 
> > > hi
> > > AFAIK there isn't a non macro way to do it.
> > > try this.
> > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > Sheets("Sheet1").Range("A4").ClearContents
> > > Sheets("Sheet1").Range("A6").ClearContents
> > > End Sub
> > > 
> > > this is workbook code. press Alt+F11 to bring up the VB editor. in the 
> > > project window(far left), expand your project(file). double click 
> > > ThisWorkbook. in the code window,(far right) click the left dropdown at the 
> > > top and click this workbook.
> > > 
> > > Private Sub Workbook_Open() should default in. delete it and paste the above 
> > > in in it place.
> > > 
> > > if you don't like the before close event, you can try the before save event 
> > > but this would clear the cells out each time you save the file.
> > > 
> > > adjust the ranges and sheet name to suit.
> > > 
> > > Regards
> > > FSt1
> > > 
> > > 
> > > "Gary" wrote:
> > > 
> > > > Is there a non-macro way to clear a cell's contents automatically when the 
> > > > spreadsheet is saved? 
> > > > 
> > > > This is one of several un-locked cells in my spreadsheet and I  want to 
> > > > clear 1 or 2 specific cells each time a document is closed. I have hidden 
> > > > notes (a price list) that are only visible via Conditional formatting color 
> > > > change from white to black if a password is entered in the cell I want to 
> > > > clear upon Saving or exiting a document to prevent the next user, possibly a 
> > > > customer, from seeing the notes (price list) if not authorized.
> > > > 
> > > > If I need a macro, what would you suggest?
> > > > 
> > > > Also, if I send my spread to other users, will the macro work? Must they 
> > > > choose "Enable Macros?" each time the document is opened?
> > 
0
Utf
11/24/2009 11:03:02 PM
hi
delete the macro. if you don't want others to use it then it's just extra 
unnecessary baggage.

Regards
FSt1

"Gary" wrote:

> 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" wrote:
> 
> > 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" wrote:
> > 
> > > 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
> > > 
> > > "FSt1" wrote:
> > > 
> > > > hi
> > > > AFAIK there isn't a non macro way to do it.
> > > > try this.
> > > > Private Sub Workbook_BeforeClose(Cancel As Boolean)
> > > > Sheets("Sheet1").Range("A4").ClearContents
> > > > Sheets("Sheet1").Range("A6").ClearContents
> > > > End Sub
> > > > 
> > > > this is workbook code. press Alt+F11 to bring up the VB editor. in the 
> > > > project window(far left), expand your project(file). double click 
> > > > ThisWorkbook. in the code window,(far right) click the left dropdown at the 
> > > > top and click this workbook.
> > > > 
> > > > Private Sub Workbook_Open() should default in. delete it and paste the above 
> > > > in in it place.
> > > > 
> > > > if you don't like the before close event, you can try the before save event 
> > > > but this would clear the cells out each time you save the file.
> > > > 
> > > > adjust the ranges and sheet name to suit.
> > > > 
> > > > Regards
> > > > FSt1
> > > > 
> > > > 
> > > > "Gary" wrote:
> > > > 
> > > > > Is there a non-macro way to clear a cell's contents automatically when the 
> > > > > spreadsheet is saved? 
> > > > > 
> > > > > This is one of several un-locked cells in my spreadsheet and I  want to 
> > > > > clear 1 or 2 specific cells each time a document is closed. I have hidden 
> > > > > notes (a price list) that are only visible via Conditional formatting color 
> > > > > change from white to black if a password is entered in the cell I want to 
> > > > > clear upon Saving or exiting a document to prevent the next user, possibly a 
> > > > > customer, from seeing the notes (price list) if not authorized.
> > > > > 
> > > > > If I need a macro, what would you suggest?
> > > > > 
> > > > > Also, if I send my spread to other users, will the macro work? Must they 
> > > > > choose "Enable Macros?" each time the document is opened?
> > > 
0
Utf
11/25/2009 12:26:02 AM
Reply:

Similar Artilces:

Need a formula for multiple sums in a cloumn when rows vary
I am making a invoice I need a total for each truck, with a grand total at the end. The number of rows for each truck in the amount column will vary. Is it possible to have the text truck total to the left of the total? A small example below, there will be more entries under each truck and more trucks also. tr# date hrs rate amount 3 4/12/10 10 50 500 4/13/10 5 50 250 truck 3 total 750 4 4/10/10 1 50 50 truck 4 total 50 and so on ...

Help with averaging...
I have Column Q total the gross profit's in each row. So Q3=total $ in each cell of Row 3. There are 120 rows. With a total $ amount at the bottom of column Q. I need to track my average gross per unit. I found out a way to average Column Q without factoring in 0. =AVERAGE(IF(Q3:Q120<>0, Q3:Q120,"")) I just discovered today that I need it to count 0's but I don't need it to count BLANK Cells. Since each cell in Column Q has something in it, if I simply us the AVERAGE(Q3:Q120) it takes my total gross and divdes by 117 cells because they all have either a $ amo...

Outlook still hanging
I have followed the advice from my last message - I have renamed the file to outcmd.old and I have disabled messenger in the Options. The problem only occurs when I try to send/receive. If I turn off send/receive every thing is OK but as soon as I try to manually receive or on opening, Outlook freezes still. please help Peter ...

Help on Help
Using Office 2003, Excel Help doesn't work. When I click on Help, I get the Help dialog and type in a keyword or browse the contents. After the list of possible links come up, I click on one of them, and all I get is a blank greyed-out pop-up. I've tried Detect and Repair, doesn't fix it. Any ideas? ...

How to set CEdit to ReadOnly but the background color still White?
I use Visual C++ MFC 4.2. I want to set all my CEdit controls to read only but I want to keep the WHITE BACKGROUND appearance. How to do that? Thank you very much. Handle WM_CTLCOLORSTATIC. -- Ajay "Landon" <Landon@discussions.microsoft.com> wrote in message news:3C081A07-E979-42C5-B56B-8F151B440757@microsoft.com... >I use Visual C++ MFC 4.2. > > I want to set all my CEdit controls to read only but I want to keep the > WHITE BACKGROUND appearance. > > How to do that? > > Thank you very much. I've tried it and it works. But I've got 2 M...

Bill Summary Troubleshooting Help
I recently had to delete a Money file because it suddenly became corrupt. When I set back up, all my Bill Summary entries are not displayed but they are summarized on my main Money page. So, they are there. If I enter a Bill or Deposit in Bill Summary, it accepts it, I receive no error message, but nothing is diplayed in the Summary, only on the "My Money Home Page Bills & Deposit Box." Can someone please advise as to how to get my Bills & Deposits to display in the "Bill Summary" again? Thank you in advance. ...

Help: Outlook mail not loading in inbox
After clicking on send/receive... I can see the emails retrieval process in action... checking each account and listing the number of emails being retrieved. However, the emial are not displayed in any of the folders, not even the inbox. Where is my mail going... has someone highjacked my inbox? Please help... any sound advice is greatly appreciated. Micorsoft Office Outlook 2003, Windows XP iMac Lover What view do you have set on your inbox? If you use advanced find and search for items received today, do you see any items? --� Milly Staples [MVP - Outlook] Post all replies to the...

Named cells
How do I use a name of a cell rather than the R1C1 address. I have tried Range("Name") = Range(Name) = Thanks Dave, What happened when you tried Range("Name") = -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Dave Griffiths" <dave@daveg.co.uk.nospam> wrote in message news:4oOUb.2185$rj4.31303@news2.e.nsc.no... > How do I use a name of a cell rather than the R1C1 address. > > I have tried > Range("Name") = > Range(Name) = > > Th...

CRM 3 STILL NOT GOOD
Just tried CRM 3 on the demo servers provided by Microsoft. After ALL THE TIME they spent on it . . . and it's STILL NOT AS GOOD AS IT SHOULD BE! It lacks basic functionality found in earlier versions of ACT. And much is not intuitive (such as searching for accounts). I will not enjoy rolling this out to the sales reps as they will say that it's still not good. Even if you add a module like marketing, and yet neglect the basic problems with the software, you still don't have a truly good product. -- Brandon What sort of search functionality were you looking for ?. ...

Need "Out of Office Assistant " Access
I use Microsoft Office 2003 with the XP Service Pack 2 Download. Please let me know if there is any way I can set up an Out of Office Assistant. I need to know how to get and Exchange Server e-mail account for my business: Journeys far & Near, LLC for which I use this computer. I am a self-employed person operating as an independent contractor under that company name - without a complicated set-up, but with a professional grade Dell Latitude D600 computer and Windows XP Professional. I've been working with Windows since 3.0, and with PC's since the XT, although my bus...

Printing document references
Hello, We have a number of forms which we have produced here at work which require numbered references on them so each document is unique. Can this be batch driven ? EG. I have designed a printed sheet in Excel for our stock take this year. We need 200 sheets to be issued out to the guys doing the counting. Each sheet needs its own unique documents reference number on it so it can be traced. How can I automate this so I can print 200 copies of the document, and the document references increase incrementally on each separate page. EG. ABC0001, ABC0002, ABC0003... I dont want to have t...

Help adding my fiance to Money?
I know that when I initially setup Money it asked if I would like to add my fiance/wife/other... However at the time I said no. Now, I would like to add my fiance to Money and cannot figure out how to do so. Can someone point me in the right direction. Thanks in advance! In microsoft.public.money, Matt wrote: >I know that when I initially setup Money it asked if I >would like to add my fiance/wife/other... However at the >time I said no. > >Now, I would like to add my fiance to Money and cannot >figure out how to do so. Can someone point me in the >right di...

RAID 10 still best for DB's?
We run Exchange 2003 Enterprise. We have 3 Mailbox stores which had 1-2 databases in each and each maiailbox store in on a RAID 10 partition. We use to have them on RAID 5 at it was very slow sometimes, our average writes/reads to disk per second were sometimes hitting > 50 msecs. Now on RAID 10 we sometimes get the odd >30 msecs but many when backups are running. We are going to be upgrading our server soon, would RAID 10 be the best way? Plus we will eventually upgrade this new server to Exchange 2007. On Fri, 13 Apr 2007 08:00:11 +0100, "Gonzo" <no@no.com> ...

i have a list i need to copy onto another sheet one by one
I have created an invoice on an excel spreadsheet for a new business and my plan was to use a sales list on another sheet linked to the invoice , using the same fields it worked perfectly the first time using basic = sign, then i discovered that second and subsequent entries to the list would have to be set up again . can excel handle this so that I don,t need to retype after each entry. If desired, send your file to my address below. I will only look if: 1. You send a copy of this message on an inserted sheet 2. You give me the newsgroup and the subject line ...

docmd.transferspreadsheet help??
Hi Can this methods take the results of one of my report i have a report name Report_Monthly Sales Report which when someone clicks a customer and month they want, press preview button the report then show a preview of it. what i've got then is a command button so they can export to excel with formattting but everytime i press it i get an error saying cannnot find object 'Report_Monthly Sales Report' but this is the name of the report i want to send. is this possible my code below Private Sub Command20_Click() DoCmd.TransferSpreadsheet acExpo...

Merge Cells not available
I am attempting to merge cells in a spreadsheet, but the Option to merge both on the toolbar and in 'Format'-'Cell' are greyed out. My worksheet is not protected. Every Worksheet in the document acts the same. If the workbook is shared (Tools, Share Workbook) merging cells is not allowed. -- Jim Rech Excel MVP "Flash24" <Flash24@discussions.microsoft.com> wrote in message news:BB8D4738-3CD6-4575-B44E-02FA32D88B9B@microsoft.com... |I am attempting to merge cells in a spreadsheet, but the Option to merge both | on the toolbar and in 'Format'-'...

Adding scores and need average
Mr. B set this up for me, but now I need the average of these 15 combo boxes. The formula below adds up the combo boxes for me quite nicely, but now I would like the text box to return the average score of the 15 combo boxes. Thanks, Tom Me.Text144 = Val(Me.Q1) Me.Text144 = Val(Me.Text144) + Val(Me.Q2) Me.Text144 = Val(Me.Text144) + Val(Me.Q3) Me.Text144 = Val(Me.Text144) + Val(Me.Q4) Me.Text144 = Val(Me.Text144) + Val(Me.Q5) Me.Text144 = Val(Me.Text144) + Val(Me.Q6) Me.Text144 = Val(Me.Text144) + Val(Me.Q7) Me.Text144 = Val(Me.Text144) + Val(Me.Q8) Me.Text144 = Val(Me.Text144) + Val(Me.Q...

sorting an alphanumeric list
I posted this yesterday and got no responses. Is there someone who could help me? _____________________________________________ would like to sort some text which is formatted in column one as: 1' 100 1000' 2 2' and so on. Any number that does not have an "'" at the end should come before any number that does and all the 1s, 2s, 3s, etc. should be grouped together. Is this possible? The result would look like: 1 100 1000' 2 2' Boy, would I appreciate some help Joanne, If your values start in cell A1, enter this formula in cell B1: =VALUE(LEFT(A1,1)...

Routing Documents for Review
Hello! When I choose "send to" > "mail recipients for review" the document becomes an attachement to the email and I would like to have a link to the document where it is saved on our network. Please help me be able to change this default so that a link is provided instead of the attachement, or even better...how can I get prompted to choose link or attach the file in case I want to send outside of our network....? TIA for your help! Raymond Hi Raymond- Your request suggests that you are at crossed purposes. The point of "for Review" is so the re...

CArray help
Hello, I am struggling to learn the concept of template language and the usage of CArray. I have found the CArray declaration in the afxtempl.h file and I have managed to compile with no errors the apparent construction of CArray(MyStructType); But I don't really understand what next to do with it. I am having a mental block with understanding the template declaration of template<class TYPE, class ARG_TYPE> What exactly is this saying ? The constructor is just CArray( ); My compiler docs don't seem to have much on CArray except a listing of it's class members. I...

OUTLOOK HELP!!!
I cannot open links that come through an email in outlook express. when i double click a link just won't open. Everthing else seems to work fine. can anyone please help out. This newsgroup is for support of Outlook 97, 98, 2000, 2002 & 2003 from the Office family for Intel PCs. For Outlook Express (OE) support try posting in one of these newsgroups: microsoft.public.inetexplorer.ie4.outlookexpress for OE 4.x microsoft.public.windows.inetexplorer.ie5.outlookexpress for OE 5.x microsoft.public.windows.inetexplorer.ie55.outlookexpress for OE 5.5x microsoft.public.windows.inetexplorer...

Still losing pictures
I am still losing my graphics! Here is what has happened: Originally, I started to edit an existing file. Most of the pictures were already there. I added some pictures and text using the insert picture from file method. After saving and re-opening the next day, I had lost all of the old images but still had the ones that I had just added. I replaced all of those pictures using the graphics Manager by clicking on the empty picture in the Graphics Manager and selecting replace picture. After saving and re-opening the next day, now they are all gone. I am using windows metafile images. Today,...

lost all emails, computer went back to default...help
i turned on my computer one morning and it was back to factory settings. had to redo my outlook account and lost all emails. is there a way to recover them? On 13/03/2010 01:20, stephanieann wrote: > i turned on my computer one morning and it was back to factory settings. had > to redo my outlook account and lost all emails. > > is there a way to recover them? Do a search for *.pst files. On 3/12/2010 5:20 PM, stephanieann wrote: > i turned on my computer one morning and it was back to factory settings. had > to redo my outlook account and lost all emai...

excel 2003 no of cells
Hi ! DOes anyone know if the size of a worksheet in excel has increased in 2003 version compared to 2002. We have some reports that are bigger than excel but cannot find any info re the size of the new version. Hope someone can help ! Thanks in advance Carolyn Same size - 65536 rows and 256 columns. -- Jim Rech Excel MVP "Carolyn Bromley" <anon@anon> wrote in message news:uJzl9tMREHA.3300@tk2msftngp13.phx.gbl... > Hi ! > > DOes anyone know if the size of a worksheet in excel has increased in 2003 > version compared to 2002. > > We have some reports ...

Find formatted cells
Hi Is there a way in VBA to scan a range of cells and pick up the references of any cells that have a particular format applied? The range may have more than one cell with the format applied and I want to be able to use the references elsewhere in the workbook. Many thanks. Jim xl2002 added the ability to find by format. Before that you could have a macro that would search through your sheets looking for cells that match a specific format. But there are lots of things that are in the format of the cell. If you limit your search criteria (font/boldness/fill color/font color), you may eve...