title: How to unlock and lock Excel cells for running a macro
body: <p>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. </p>
  
  <pre><code>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 &gt;= 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
  </code></pre>
  
  <p>Thanks,
  Hema</p>