Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macros to cells, formula only not data locked
My workbook consists of two worksheets, several columns are governed by a
formulas referencing both data columns in one, the other, or both worksheets. Some of these columns require various sorts after the data has been entered, something done on a daily basis. Now, I would prefer to protect those cells that contain formulas and show function results, but then the user cannot perform a sort when they want to. If I leave the column unlocked so the user can right-click a column to sort the function formula might inadvertently be over written. So, the question: Can I either: protect only the function formula in a cell but leave the value free to be accessed, or, since the user is not macro savy, embed a button into a cell that will have the necessary macro assigned to it, several would be required per sheet. And is creating a button in a cell for the macros even possible? I've tried Excel MS Help, and read through previous asked questions here on the board, but haven't found an answer yet that makes sense to me. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macros to cells, formula only not data locked
A button can be added to a sheet that would unlock the sheet, sort the data and protect the sheet again, just by clicking the button. It could be set up to sort selected data or only sort a predetermined area. Some vba code, without the unlock feature, and used for a single column is shown here... http://www.contextures.on.ca/xlSort02.html It won't make much sense to a beginner -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) .. "Margo" wrote in message My workbook consists of two worksheets, several columns are governed by a formulas referencing both data columns in one, the other, or both worksheets. Some of these columns require various sorts after the data has been entered, something done on a daily basis. Now, I would prefer to protect those cells that contain formulas and show function results, but then the user cannot perform a sort when they want to. If I leave the column unlocked so the user can right-click a column to sort the function formula might inadvertently be over written. So, the question: Can I either: protect only the function formula in a cell but leave the value free to be accessed, or, since the user is not macro savy, embed a button into a cell that will have the necessary macro assigned to it, several would be required per sheet. And is creating a button in a cell for the macros even possible? I've tried Excel MS Help, and read through previous asked questions here on the board, but haven't found an answer yet that makes sense to me. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macros to cells, formula only not data locked
Thanks Jim. I can understand the creation of what I need done by the sort
and/or macro, but ... "a button can be added to a sheet" Therein lies my problem - *how* do I add a button to a worksheet? I'm working in Excel 2007, creating a spreadsheet for a user using Excel 2003, so adding the Macro button to the Quick Launch bar is not an option. What I'd really like to do is assign each required macro to it's own cell in the required worksheet, sort of a 'Click Here'. "Jim Cone" wrote: A button can be added to a sheet that would unlock the sheet, sort the data and protect the sheet again, just by clicking the button. It could be set up to sort selected data or only sort a predetermined area. Some vba code, without the unlock feature, and used for a single column is shown here... http://www.contextures.on.ca/xlSort02.html It won't make much sense to a beginner -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) .. "Margo" wrote in message My workbook consists of two worksheets, several columns are governed by a formulas referencing both data columns in one, the other, or both worksheets. Some of these columns require various sorts after the data has been entered, something done on a daily basis. Now, I would prefer to protect those cells that contain formulas and show function results, but then the user cannot perform a sort when they want to. If I leave the column unlocked so the user can right-click a column to sort the function formula might inadvertently be over written. So, the question: Can I either: protect only the function formula in a cell but leave the value free to be accessed, or, since the user is not macro savy, embed a button into a cell that will have the necessary macro assigned to it, several would be required per sheet. And is creating a button in a cell for the macros even possible? I've tried Excel MS Help, and read through previous asked questions here on the board, but haven't found an answer yet that makes sense to me. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macros to cells, formula only not data locked
The SetupOneTime sub in the link I posted adds a transparent rectangle to a cell that when clicked runs the sort routine. That might work for you. As I don't do XL2007, I can't be of further help. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Margo" wrote in message Thanks Jim. I can understand the creation of what I need done by the sort and/or macro, but ... "a button can be added to a sheet" Therein lies my problem - *how* do I add a button to a worksheet? I'm working in Excel 2007, creating a spreadsheet for a user using Excel 2003, so adding the Macro button to the Quick Launch bar is not an option. What I'd really like to do is assign each required macro to it's own cell in the required worksheet, sort of a 'Click Here'. "Jim Cone" wrote: A button can be added to a sheet that would unlock the sheet, sort the data and protect the sheet again, just by clicking the button. It could be set up to sort selected data or only sort a predetermined area. Some vba code, without the unlock feature, and used for a single column is shown here... http://www.contextures.on.ca/xlSort02.html It won't make much sense to a beginner -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) .. "Margo" wrote in message My workbook consists of two worksheets, several columns are governed by a formulas referencing both data columns in one, the other, or both worksheets. Some of these columns require various sorts after the data has been entered, something done on a daily basis. Now, I would prefer to protect those cells that contain formulas and show function results, but then the user cannot perform a sort when they want to. If I leave the column unlocked so the user can right-click a column to sort the function formula might inadvertently be over written. So, the question: Can I either: protect only the function formula in a cell but leave the value free to be accessed, or, since the user is not macro savy, embed a button into a cell that will have the necessary macro assigned to it, several would be required per sheet. And is creating a button in a cell for the macros even possible? I've tried Excel MS Help, and read through previous asked questions here on the board, but haven't found an answer yet that makes sense to me. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Macros to cells, formula only not data locked
Thank you again for your time. I'm going to give that SetUpOneTime sub a go,
see if I can figure it out ... I'm nothing if not stubborn. "Jim Cone" wrote: The SetupOneTime sub in the link I posted adds a transparent rectangle to a cell that when clicked runs the sort routine. That might work for you. As I don't do XL2007, I can't be of further help. -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Margo" wrote in message Thanks Jim. I can understand the creation of what I need done by the sort and/or macro, but ... "a button can be added to a sheet" Therein lies my problem - *how* do I add a button to a worksheet? I'm working in Excel 2007, creating a spreadsheet for a user using Excel 2003, so adding the Macro button to the Quick Launch bar is not an option. What I'd really like to do is assign each required macro to it's own cell in the required worksheet, sort of a 'Click Here'. "Jim Cone" wrote: A button can be added to a sheet that would unlock the sheet, sort the data and protect the sheet again, just by clicking the button. It could be set up to sort selected data or only sort a predetermined area. Some vba code, without the unlock feature, and used for a single column is shown here... http://www.contextures.on.ca/xlSort02.html It won't make much sense to a beginner -- Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) .. "Margo" wrote in message My workbook consists of two worksheets, several columns are governed by a formulas referencing both data columns in one, the other, or both worksheets. Some of these columns require various sorts after the data has been entered, something done on a daily basis. Now, I would prefer to protect those cells that contain formulas and show function results, but then the user cannot perform a sort when they want to. If I leave the column unlocked so the user can right-click a column to sort the function formula might inadvertently be over written. So, the question: Can I either: protect only the function formula in a cell but leave the value free to be accessed, or, since the user is not macro savy, embed a button into a cell that will have the necessary macro assigned to it, several would be required per sheet. And is creating a button in a cell for the macros even possible? I've tried Excel MS Help, and read through previous asked questions here on the board, but haven't found an answer yet that makes sense to me. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros to protect worksheet and prevent locked cell selection | Excel Discussion (Misc queries) | |||
ALLOWING ONLY DATA INSERTION IN LOCKED CELLS | Excel Discussion (Misc queries) | |||
paste locked cells and unlocked cells in protected sheet | Excel Worksheet Functions | |||
locked cells | Excel Worksheet Functions | |||
Put comments on a locked spreadsheet even though cells not locked | Excel Worksheet Functions |