Protect Rows depending on Values in Particular Columns

  • Thread starter Thread starter singh352
  • Start date Start date
S

singh352

Hello All,
I posted the following in excel.programming... no reply so may be I was
in the wrong group and hence posting it here again.

I am using Excel 2003 and have a problem:

I have a worksheet where I maintain my Annual Maintenance Records
(AMC).
The details are as follows:

Worksheet Name: AMC 2005
Range Name: Database (Dynamic)
Col G: Cell Values = (Data Validation to allow only) Closed or Blank
Col AA: Cell Values = (Data Validation to allow only) Invoice, Cash,
Check.

I wish to have a macro solution where it should search for Col G and
Col AA values...if both are filled with Closed and Cash/Invoice/Check
respectively...then that particular row should be protected .. what I
mean not editable.... where both Col G = Closed and Col AA is either of
the three values viz.. Invoice, Cash or Check

Any help would be appreciated.

TIA

Ashish Kumar
 
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As
Boolean)
Dim myRange as Range
Dim myRowCounter as Range

Set myRange = Range("Database")
Worksheets("AMC 2005").Unprotect
For Each myRowCounter in myRange
With myRowCounter
If .Column = 1 Then
If not .Locked then
if .Offset(0,6).Value = "Closed" then
if (.Offset(0,26).Value = "Invoice") Or _
(.Offset(0,26).Value = "Cash") Or _
(.Offset(0,26).Value = "Check") Then

EntireRow.Locked = True

EndIf
EndIF
EndIf
End If
End with
Next
worksheets("AMC 2005").Protect
End Sub
 
Thanks for your reply. But I don't get the desired result.

May be I was not clear with my original post. I will try to make it
more clear.

Worksheet Name: AMC 2005
Sheet Name: Combined
Range Name: Database (Dynamic) - currently $A$2:$AB$218
Col G: Cell Values = (Data Validation to allow only) Closed or Blank
Col AA: Cell Values = (Data Validation to allow only) Invoice, Cash,
Check.

I already have other data filled in on my sheet... My requirement is to
have a solution where in when I input the values in Column G and Column
AA as mentioned above then those rows should only be Locked.. or not
editable. Others should be editable.

May be something like an OnEntry type of macro.

Hope I am clear now.

Thanks for your time and help

Ashish Kumar
 
Back
Top