create drop down in column and lock other cells

[Halley yenn]

Hey Folks,

I have a excel with some columns data where my requirement is to create dropdown in column c where the list should contain 1 to 5 in the drop down and the data from column D till column end where the data is present must be locked.

Can someone help me to acheive this via macro code.

Regards,
Halley

 

[ThanP]

Hi Halley yenn,

You can try this below, but it will need some tweak for your needs.
Note: I have assumed :
1) you have a list like Col I & J, 2) you want only the cell C1 unlocked and 3) it is on change, when an user selects a number from the drop down

Module

Dim LastRow, IndexValue As Integer
Dim rng, cell As Range

Sub ReturnList()
    Sheets("sheet1").Unprotect 'Take out the protection to overwrite cells
    Set rng = Range("i1:i21") 'I supposed you have a list somewhere
    Range("D1:D100").ClearContents 'I guess you want  to clear-out the data everytime
    LastRow = Cells(Rows.Count, "D").End(xlUp).Row 'Last row, to display list

    IndexValue = Range("C1").Value 'Store the chosen value in a variable
    
    'This part, I suppose you have a list you want to return, like a vloopup
    For Each cell In rng
        If cell.Value = IndexValue Then
            Cells(LastRow, "D").Value = cell.Offset(0, 1).Value
            LastRow = Cells(Rows.Count, "D").End(xlUp).Row + 1
        End If
    Next cell
    
   Sheets("Sheet1").Range("C1").Locked = False 'Range NOT protected
   Sheets("Sheet1").Protect 'possibility to add Password:= "xxxx"
End Sub

Worksheet

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C1")) Is Nothing Then
        Call ReturnList
    End If
End Sub

I didn't include the pwd for protection, you will be able to unprotect it any time with

ThanP

 

[Halley yenn]

Hi Halley yenn,

You can try this below, but it will need some tweak for your needs.
Note: I have assumed :
1) you have a list like Col I & J, 2) you want only the cell C1 unlocked and 3) it is on change, when an user selects a number from the drop down

View attachment 59194

Module

Dim LastRow, IndexValue As Integer
Dim rng, cell As Range

Sub ReturnList()
    Sheets("sheet1").Unprotect 'Take out the protection to overwrite cells
    Set rng = Range("i1:i21") 'I supposed you have a list somewhere
    Range("D1:D100").ClearContents 'I guess you want  to clear-out the data everytime
    LastRow = Cells(Rows.Count, "D").End(xlUp).Row 'Last row, to display list

    IndexValue = Range("C1").Value 'Store the chosen value in a variable
   
    'This part, I suppose you have a list you want to return, like a vloopup
    For Each cell In rng
        If cell.Value = IndexValue Then
            Cells(LastRow, "D").Value = cell.Offset(0, 1).Value
            LastRow = Cells(Rows.Count, "D").End(xlUp).Row + 1
        End If
    Next cell
   
   Sheets("Sheet1").Range("C1").Locked = False 'Range NOT protected
   Sheets("Sheet1").Protect 'possibility to add Password:= "xxxx"
End Sub

Worksheet

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C1")) Is Nothing Then
        Call ReturnList
    End If
End Sub

I didn't include the pwd for protection, you will be able to unprotect it any time with

View attachment 59195


ThanP

Hey Than,
I have the validation list in other worksheet in column F from f2,how to call it here ??

Thanks,
H

 

[ThanP]

Change this line :
Set rng = Range("i1:i21") 'I supposed you have a list somewhere
to

      Set rng = Sheets("Sheet2").Range("F2:F21") 'Replace Sheet2 for your WS name and F21 to your last row

 

[Halley yenn]

Hi ThanP,

This is working well for dropdown, for locking the columns let me make it clear .

I have 10k excels in a folder where I have to open each excel and Lock Columns : Col C , Col D, Col E, Col F, Col G, Col H, Col I , Col J , Col K, Col L, Col M, Col N and apply password say "GRUG" and make Col A and Col B editable or Unlocked.

The no of rows in each column will be around 20k rows and I recieve 10k files on weekly basis.

Any macro to open each excel and format accordingly will be appreciated.

Thanks
H