如何锁定特定列，但是部分列可由用户修改？ （VBA）

So I am creating a worksheet to allow the user to enter data by using a button to create a new line in the table. I only want specific cells in the table and column to be modifiable.

所以我创建了一个工作表，允许用户使用按钮在表格中创建新行来输入数据。我只希望表和列中的特定单元格可以修改。

I have used a similar function to the one below before, however now it is not working.

我之前使用过类似的功能，但是现在它没有用。

In this case I want the range from B7 to the end of the table to be modifiable by the user and the rest of the column to be locked.

在这种情况下，我希望用户可以修改从B7到表格末尾的范围，以及要锁定的列的其余部分。

Function ProtectionOn()

Sequencer.Select
Cells.Locked = True
Range("B7", Range("B1048576").End(xlUp)).Locked = False
ActiveSheet.Protect Password:="test"

End Function

The easiest way I have found this to work is to use xlup from the end of the spreadsheet. Any help to fix my function would be greatly appreciated! Thanks!

我发现这个最简单的方法是使用电子表格末尾的xlup。任何帮助修复我的功能将不胜感激！谢谢！

2 个解决方案

#1

Instead Function, use sub.

相反功能，使用sub。

Sub ProtectionOn()

Sequencer.Select
Cells.Locked = True
Range("B7", Range("B1048576").End(xlUp)).Locked = False
ActiveSheet.Protect Password:="test"

End Sub

If you need sub precedure, try like this.

如果您需要sub precedure，请尝试这样做。

Main procedure.

主要程序。

Sub test()
    ProtectionOn Sheets("Sequencer")
End Sub

Sub ProtectionOn(Ws As Worksheet)
    With Ws
        .Unprotect Password:="test" '<~~ add code
        .Cells.Locked = True
        .Range("B7", .Range("B1048576").End(xlUp)).Locked = False
        .Protect Password:="test"
    End With
End Sub

#2

We suppose that we have the table1 in sheet Sequencer please Create:

我们假设我们在表单序列器中有table1请创建：

Sub ProtectionOn(sh As Worksheet)

Dim i As Long

With sh

i = .Range("Table1[#All]").Rows.Count

.Cells.Locked = True
.Range("b7", .Range("Table1[#All]").End(xlDown)).Locked = False
.Protect Password:="test"

End With

End Sub




  'and call it from:


Sub call_protect()

    ProtectionOn Sheets("Sequencer")



End Sub