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" <madiya122@yahoo.co.uk> wrote in message
news:1155298155.946255.146560@75g2000cwc.googlegroups.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
>

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" <madiya122@yahoo.co.uk> wrote in message
> news:1155298155.946255.146560@75g2000cwc.googlegroups.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
> >

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" <madiya122@yahoo.co.uk> wrote in message
news:1155305171.119895.25520@i42g2000cwa.googlegroups.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" <madiya122@yahoo.co.uk> wrote in message
> > news:1155298155.946255.146560@75g2000cwc.googlegroups.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
> > >
>