如何解锁和锁定Excel单元格以运行宏

问题描述

我有一个具有两个按钮的电子表格-用于从数据库检索记录，另一个用于将更改从excel上传到数据库.从数据库检索记录的宏如下.现在，在检索记录之后，我希望用户仅编辑某些列(这里是从一月到方案的列)，以便用户在更新这些单元格后可以单击更新按钮以将更改保存到数据库.但是，我不希望他们触摸其他列(通过状态的EmpID).我想要一个宏，该宏将在数据检索后锁定这些列并在单击``检索''按钮以检索记录时将其解锁.这是因为每当我单击检索记录"按钮时，我都会清除工作表.我尝试了几种方法，但没有用.多谢您的协助.

I have a spreadsheet that has two buttons - To retrieve records from database and another one to upload changes from excel to database. The macro for retrieving records from Database is as follows. Now after retrieving the records, I want the users only to edit certain columns( here the columns from January to Scenario) so that the users, after updating those cells can click on the update button to save the changes to the database. However, I don't want them to touch the other columns ( EmpID through Status).I want a macro that would lock those columns after data retrieval and unlock while retrieving the records on clicking the Retrieval button. This is because I am clearing the worksheet whenever I click on the retrieve records buttons. I tried several methods and it's not working. I would appreciate your help.

Public Sub RetrieveDBToWorkSheet()
Dim sQry As String
Dim iRows As Integer
Dim iCols As Integer
Dim SQL As String



On Error GoTo ErrHandler



'Clear worksheet
Call ClearExistingRows(4)

'Create ADODB Recordset for retrieved data
Call DBConnection.OpenDBConnection

'Create Recordset
Dim rsMY_Resources As ADODB.Recordset
Set rsMY_Resources = New ADODB.Recordset

SQL = "SELECT  EmpID, EName, CCNum, CCName, ProgramNum, ProgramName, ResTypeNum, ResName, Status, January, February, March, April, May, June, July, August, September, October, November, December, Total_Year, Year, Scenario from Actual_FTE2"

'Query the database

rsMY_Resources.Open SQL, DBConnection.oConn, adOpenStatic, adLockReadOnly
If rsMY_Resources.EOF = True Then
    MsgBox ("No record found in database")
    Exit Sub
End If

'Fill excel active sheet, starting from row# 3
iRows = 3
For iCols = 0 To rsMY_Resources.Fields.Count - 1
    ActiveSheet.Cells(iRows, iCols + 1).Value = rsMY_Resources.Fields(iCols).Name
Next
ActiveSheet.Range(ActiveSheet.Cells(iRows, 1), ActiveSheet.Cells(iRows, rsMY_Resources.Fields.Count)).Font.Bold = True

iRows = iRows + 1
ActiveSheet.Range("A" + CStr(iRows)).CopyFromRecordset rsMY_Resources

iRows = rsMY_Resources.RecordCount

'Clean up
rsMY_Resources.Close:
Set rsMY_Resources = Nothing

Call DBConnection.CloseDBConnection


MsgBox (CStr(iRows) + " records have been retrieved from the database!")


Exit Sub

ErrHandler:
MsgBox (Error)

End Sub

Public Sub ClearExistingRows(lRowStart As Long)
Dim lLastRow As Long
Dim iLastCol As Integer

 If (Not (Cells.Find("*", Range("A1"), xlFormulas, , xlByRows, xlPrevious)
 Is Nothing)) Then
    lLastRow = Cells.Find("*", Range("A1"), xlFormulas, ,
   xlByRows,xlPrevious).Row ' Find the last row with data

    If (lLastRow >= lRowStart) Then
        iLastCol = Cells.Find("*", Range("A1"), xlFormulas, , xlByColumns,
    xlPrevious).Column ' Find the last column with data
        Range(Cells(lRowStart, 1), Cells(lLastRow, iLastCol)).Select
        Selection.EntireRow.Delete
       End If
    End If
   End Sub

谢谢，赫马

推荐答案

在您之后:

MsgBox (CStr(iRows) + " records have been retrieved from the database!")

放置此代码:

Dim rngCol As Range ' Create a Range object

Set rngCol = ActiveSheet.Range("A:Z") ' Select all columns on sheet
   rngCol.Locked = False              ' Unlock all columns
Set rngCol = ActiveSheet.Range("A:I") ' Now Select columns EmpID - Status
   rngCol.Locked = True               ' Lock only those columns
   ActiveSheet.Protect                ' Protect will now only protect the Locked columns

当您进入需要重写所有内容的过程时:

When you enter the procedure where you need to rewrite everything:

Public Sub RetrieveDBToWorkSheet()

放置以下代码以解锁整个工作表:

Place this code to unlock the whole sheet:

ActiveSheet.Unprotect  ' This will unprotect the whole sheet

您必须记住要先解锁工作表上的所有列.如果您不这样做，那么保护功能将锁定您的所有列(即使您专门锁定"了您想要的范围).这是不直观的，并且使许多excel用户感到困惑.

You have to remember to unlock all the columns on the sheet first. If you don't, then the protect will lock down all of your columns (even though you specifically "locked" the range you want). This is unintuitive and has baffled many excel users.

这篇关于如何解锁和锁定Excel单元格以运行宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！