Hi All,

I have created a small application using VBA on Excel 2002. Every month
I upload data into the data workbook, on a new column, from input files
using the macros which are stored in a different workbook.

I am facing some problems recently. I have no idea how but some data is
either getting deleted or changed. Is there any way I can protect my
data/macro workbook or may be lock cells.

What I was thinking is may be after every monthly upload I lock the
column that had been populated. But my worry is that if I have to, may
be in future, update some data then what should I do. Right now I am
taking copies of my workbook(s) every month but sooner or later I will
have space problems. This application will be run for the next 5 years
or so.

So can someone suggest me the best possible way for protection.

I have recently started working on Excel VBA (my background being AS400
before). Therefore I would also request someone to direct/suggest me to
any tutorials or books on the net as I want to fully understand what I
can do using VBA on Excel.

I think I have asked a lot here but would sincerely appreciate if
someone comes back to me asap on this.

Thanks & Regards,
Prasad

Prasad,

I always recommend the below reference. What it does is when the workbook
opens
it protects all worksheets such that only cells with Protection = Unlocked
can be selected.
It also allows protected cells to be changed through code.

So manually or throught code you can protect individual cells or groups of
cells. You may have to
unprotect/protect the worksheet through code.

Let me know if you need any further help...

==========================
Select UnProtected Cells ONLY
This will prevent users from clicking on protected cells on all
worksheets. Therefore, the warning message will not appear. The code
must be enterred in the ThisWorkbook module.

Note that the EnableSelection property must be reset each time the
workbook is opened as it defaults to xlNoRestrictions. The
worksheet(s)
must first be unprotected to set the EnableSelection property and then
must be protected for it to take effect.

Private Sub Workbook_Open()
Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
WS.Unprotect 'Password:="wxyz"
WS.EnableSelection = xlUnlockedCells
WS.Protect 'Password:="wxyz", UserInterfaceOnly:=True
Next
End Sub

Regards,
Greg Wilson 5/3/03

Further to Steve's comment, I was running xl97 back then (2003). I now
have
xl2000 and I note that you don't have to unprotect anymore before
changing
the EnableSelection property. You still have to reset the
EnableSelection
property to xlUnlockedCells each time the wb is opened. You should
experiment
with whatever version you are running.
Greg Wilson 7/25/05

--
steveB

Remove "AYN" from email to respond
wrote in message
oups.com...
Hi All,

I have created a small application using VBA on Excel 2002. Every month
I upload data into the data workbook, on a new column, from input files
using the macros which are stored in a different workbook.

I am facing some problems recently. I have no idea how but some data is
either getting deleted or changed. Is there any way I can protect my
data/macro workbook or may be lock cells.

What I was thinking is may be after every monthly upload I lock the
column that had been populated. But my worry is that if I have to, may
be in future, update some data then what should I do. Right now I am
taking copies of my workbook(s) every month but sooner or later I will
have space problems. This application will be run for the next 5 years
or so.

So can someone suggest me the best possible way for protection.

I have recently started working on Excel VBA (my background being AS400
before). Therefore I would also request someone to direct/suggest me to
any tutorials or books on the net as I want to fully understand what I
can do using VBA on Excel.

I think I have asked a lot here but would sincerely appreciate if
someone comes back to me asap on this.

Thanks & Regards,
Prasad