Macros to cells, formula only not data locked

M

Margo

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.
 
J

Jim Cone

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.
 
M

Margo

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'.
 
J

Jim Cone

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'.
 
M

Margo

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top