How to repeat my VBA code in the Next row, instead of writing the statement over and over.

Dear All
Please find the attached excel file. I was creating a GL Code Generator and I want to lock the cell range B5:I5 as soon as I select "Authorised" in column I5 and the same should repeat in the next row as well. I found a VBA code that helped me a lot but, I have to write the same code over and over to achieve the results. below I have pasted the VBA code that I am using. Please help.... thnx

Hi Asim ,

See the uploaded file ; I have retained your code ; if you are sure the revised code is working the way you want it to , you can delete the lines of code which are not required.

As it stands , the code will allow the user to select Authorised from the dropdown only once , while the cell is unlocked ; the moment Authorised is selected , the range from columns B through I in that row are locked , thus preventing the user from changing the selection in column I in that row.

If you want that it should be possible to change the selection , even after Authorised has been selected , then change the following line of code :

Target.Offset(, -7).Resize(1, 8).Locked = (Target = "Authorised")

Change the 8 to 7.

Narayan

Dear Narayan
Your code works perfectly, fine the way I wanted. Thank You so much.
Please explain me what did you do.
What is the meaning of following:

>> Target.Offset(, -7)
>> .Resize(1, 8)
>> .Locked = (Target = "Authorised")

How is the code going to the next row itself. ??

Regards
Asim

Hi Asim ,

Certain event procedures , such as the Worksheet_Change or the Worksheet_SelectionChange event procedures , have a parameter called Target ; this always refers to the cell / range of cells which triggered the event.

Thus , if you have a Worksheet_Change procedure , and you enter data in cell J7 , the procedure runs , and Target refers to J7. Similarly , if you have a dropdown in B17 , and you make a selection from the dropdown list , the procedure will run , and Target will refer to B17.

This allows you to decide :

1. Whether you need to do anything ; if Target is referring to a cell which is not relevant , you do nothing ; this is done by the statement :

If Not (Application.Intersect(Target , Range_which_is_relevant) Is Nothing) Then Exit Sub

where the Intersect method returns the intersection of two or more ranges ; if the ranges have no cell in common , it returns the object Nothing.

For example , suppose Target refers to B17 , and the Range_which_is_relevant is B6:H11 ; in this case , there is no cell in common between these two , and the above Intersect method returns Nothing.

Now , if the Range_which_is_relevant refers to B6:H21 , then the intersection of the two is the cell B17 itself , and the Intersect method will return this range.

2. If you decide to do something , then the range reference Target can be used along with its properties :

Target.Row - this will give the row number of the cell / range which triggered the event

Target.Column - the column number

Target.Value - the contents of the cell if it is a single cell

Target.Cells(1,1).Value - the contents of the top left hand corner cell of a multi-cell range

You can use all the properties and methods of a Range object.
...........................................................................................................

Now , to go on to : Target.Offset(, -7)

Since we know that Target refers to a cell / range , the above construct refers to a cell / range which is offset from the Target reference by 0 rows and -7 columns ; thus , if the procedure was triggered because of a change in cell I13 , the above will now refer to the cell B13 , in row 13 , and column B , since the row offset is 0 , and the column offset is 7 columns to the left of column I.
...........................................................................................................

Coming to : .Resize(1, 8)

The Resize method has two parameters , the first one being the rownumber , and the second being the columnnumber.

Together , these two change the size of the range reference to what ever you want. For example , suppose we use the cell B13 as the reference for the Resize method , then :

Range("B13").Resize(1,8) will return a range reference B13:I13 - the 1 stands for 1 row , and the 8 stands for 8 columns.

Range("B13").Resize(8,1) will return a range reference B13:B20 - the 8 stands for 8 rows , and the 1 stands for 1 column.

Range("B13").Resize(3,2) will return a range reference B13:C15 - the 3 stands for 3 rows , and the 2 stands for 2 columns.
...........................................................................................................

Lastly , .Locked = (Target = "Authorised")

Since a Range object has a property called Locked , which can be set to True or False , we use the result of the expression (Target = "Authorised") ; if Target is blank , then the result of this expression will be False , if Target has been selected to Authorised , then the result of this expression will be True. This result is assigned to the Locked property.
...........................................................................................................

To sum up , the entire procedure is run when any change is done by the user to any cell in the worksheet ; when the change is detected to have been done in column I , then we do what is required , else the procedure exits without doing anything.

When the change in column I is detected , if the change is selecting Authorised , then that particular row , from column B through column I , is locked ; if the change is a clearing of the cell in column I , so that it is not Authorised , then that particular row , from column B through column I , is unlocked.

Narayan