If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Clear a cell's contents when document is saved
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? |
#2
|
|||
|
|||
Clear a cell's contents when document is saved
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? |
#3
|
|||
|
|||
Clear a cell's contents when document is saved
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? |
Thread Tools | |
Display Modes | |
|
|