Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capture a worksheet in VBA
Hi all,
I have a huge worksheet full of sumproduct formulas which fatches links from various other files and creats summary reports.I do not want to reveal formulas I have used which will be used against me (directly or indirectly). Manually creating VBA code for the same for more then 300 formulas is quite tedius. Is there a way by which I can capture the status of the whole sheet (all formulas and links) in VBA which can be used later to creat the sheet again when required? Regards, Madiya |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capture a worksheet in VBA
Just use the same formulae in an Evaluate statement. Slow and inefficient,
but should work. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Madiya" wrote in message ups.com... Hi all, I have a huge worksheet full of sumproduct formulas which fatches links from various other files and creats summary reports.I do not want to reveal formulas I have used which will be used against me (directly or indirectly). Manually creating VBA code for the same for more then 300 formulas is quite tedius. Is there a way by which I can capture the status of the whole sheet (all formulas and links) in VBA which can be used later to creat the sheet again when required? Regards, Madiya |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capture a worksheet in VBA
Thank you for your reply.
But this essancially means that I write Evaluate statement for each of these 300+ cells which is very tedius. That's the reason I was requesting for some automated VBA code which will pick up formula in each cell and when I run the code, it will put the same formula in the same cell. Regards, Madiya Bob Phillips wrote: Just use the same formulae in an Evaluate statement. Slow and inefficient, but should work. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Madiya" wrote in message ups.com... Hi all, I have a huge worksheet full of sumproduct formulas which fatches links from various other files and creats summary reports.I do not want to reveal formulas I have used which will be used against me (directly or indirectly). Manually creating VBA code for the same for more then 300 formulas is quite tedius. Is there a way by which I can capture the status of the whole sheet (all formulas and links) in VBA which can be used later to creat the sheet again when required? Regards, Madiya |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capture a worksheet in VBA
Hi Madiya,
Why not simply hide your formulas. - Select all cells on the sheet (square that intersects row.column headers) - Format Cells Protection uncheck locked and uncheck hidden - F5 Special formulas - With your formulas selected - - Format Cells Protection check locked and check hidden - Right click sheet tab view code Properties Enable selection - Tools Protection Protect sheet curiosity, why might your formulas be used against you ! Regards, Peter T "Madiya" wrote in message ups.com... Hi all, I have a huge worksheet full of sumproduct formulas which fatches links from various other files and creats summary reports.I do not want to reveal formulas I have used which will be used against me (directly or indirectly). Manually creating VBA code for the same for more then 300 formulas is quite tedius. Is there a way by which I can capture the status of the whole sheet (all formulas and links) in VBA which can be used later to creat the sheet again when required? Regards, Madiya |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capture a worksheet in VBA
Hi Peter,
Right question.... Why not simply hide your formulas. It means that I will have to protect the sheet and after that I am not free to modify any thing at will. Each time I will have to unprotect the same which is a hinderance in working. As regards to your curiosity, at present I am enjoying the freedom for my saved time. Once it is known to others, they will surely burden me with much more additional work which may not be possible to automate(we can only automate repitative tasks ) And if I do not get any solution here in NG, I will have to write code for all 300+ cell formulas. Regards, Madiya. Peter T wrote: Hi Madiya, Why not simply hide your formulas. - Select all cells on the sheet (square that intersects row.column headers) - Format Cells Protection uncheck locked and uncheck hidden - F5 Special formulas - With your formulas selected - - Format Cells Protection check locked and check hidden - Right click sheet tab view code Properties Enable selection - Tools Protection Protect sheet curiosity, why might your formulas be used against you ! Regards, Peter T "Madiya" wrote in message ups.com... Hi all, I have a huge worksheet full of sumproduct formulas which fatches links from various other files and creats summary reports.I do not want to reveal formulas I have used which will be used against me (directly or indirectly). Manually creating VBA code for the same for more then 300 formulas is quite tedius. Is there a way by which I can capture the status of the whole sheet (all formulas and links) in VBA which can be used later to creat the sheet again when required? Regards, Madiya |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capture a worksheet in VBA
A code writer
Dim cell As Range For Each cell In ActiveSheet.UsedRange If cell.HasFormula Then Debug.Print "Range(""" & cell.Address & _ """).Value = Evaluate(""" & Replace(cell.Formula, """", """""") & """)" End If Next cell -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Madiya" wrote in message ps.com... Thank you for your reply. But this essancially means that I write Evaluate statement for each of these 300+ cells which is very tedius. That's the reason I was requesting for some automated VBA code which will pick up formula in each cell and when I run the code, it will put the same formula in the same cell. Regards, Madiya Bob Phillips wrote: Just use the same formulae in an Evaluate statement. Slow and inefficient, but should work. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Madiya" wrote in message ups.com... Hi all, I have a huge worksheet full of sumproduct formulas which fatches links from various other files and creats summary reports.I do not want to reveal formulas I have used which will be used against me (directly or indirectly). Manually creating VBA code for the same for more then 300 formulas is quite tedius. Is there a way by which I can capture the status of the whole sheet (all formulas and links) in VBA which can be used later to creat the sheet again when required? Regards, Madiya |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Capture a worksheet in VBA
How about a simple macro to protect / unprotect. If the only user on your
system is you this could run automatically on open by detecting username, and protect in the save event. Or some other quick access to the macro known only to you. Whatever way you chose to write those cell formulas in VBA is going to involve not only initial work but on-going to update for renamed links. Also you say you want to carry on modifying things which I assume means the formulas (only the formulas are protected the way I outlined). I would have though a lot more work than un-protect / protect. Once you've found a way to hide them don't be surprised if you lose yet more of your freedom from the inquisitive pestering you to know how you did it and to do similar for them <g Regards, Peter T "Madiya" wrote in message ups.com... Hi Peter, Right question.... Why not simply hide your formulas. It means that I will have to protect the sheet and after that I am not free to modify any thing at will. Each time I will have to unprotect the same which is a hinderance in working. As regards to your curiosity, at present I am enjoying the freedom for my saved time. Once it is known to others, they will surely burden me with much more additional work which may not be possible to automate(we can only automate repitative tasks ) And if I do not get any solution here in NG, I will have to write code for all 300+ cell formulas. Regards, Madiya. Peter T wrote: Hi Madiya, Why not simply hide your formulas. - Select all cells on the sheet (square that intersects row.column headers) - Format Cells Protection uncheck locked and uncheck hidden - F5 Special formulas - With your formulas selected - - Format Cells Protection check locked and check hidden - Right click sheet tab view code Properties Enable selection - Tools Protection Protect sheet curiosity, why might your formulas be used against you ! Regards, Peter T "Madiya" wrote in message ups.com... Hi all, I have a huge worksheet full of sumproduct formulas which fatches links from various other files and creats summary reports.I do not want to reveal formulas I have used which will be used against me (directly or indirectly). Manually creating VBA code for the same for more then 300 formulas is quite tedius. Is there a way by which I can capture the status of the whole sheet (all formulas and links) in VBA which can be used later to creat the sheet again when required? Regards, Madiya |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unsure how to set up worksheet to capture data | Excel Discussion (Misc queries) | |||
Excel screen capture to capture cells and row and column headings | Excel Discussion (Misc queries) | |||
Macro to capture worksheet names | Excel Worksheet Functions | |||
Can you capture worksheet double click event from an Add-In???? | Excel Programming | |||
Capture mouses X &Y position on a worksheet | Excel Programming |