Protect worksheet after operation

Question

I am trying to protect my worksheet called DATA after the rows were imported and inserted from database. But unfortunately it's not getn protected. I also need the column J to be locked and hidden so that the user cannot view the formula

Here is my code :

           Option Explicit

Sub GetDataFromADO()

        'Input query'
        'Dim dbQuery As String
        'dbQuery = InputBox("Enter Query")

    Dim pword As String
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

   ActiveSheet.Unprotect Password:="star"

    pword = InputBox("Please enter the password", "Password Required")

    If pword <> "Audit2016" Then
         MsgBox "You are not given access to view this!", vbCritical + vbOKOnly
         Exit Sub
    Else

        Sheets("Data").Visible = True
        On Error Resume Next

        MsgBox "Check Worksheet DATA to view the results "
        '   Your code here
    End If

        'Declare variables'
        Dim objMyConn As ADODB.Connection
        Dim objMyCmd As ADODB.Command
        Dim objMyRecordset As ADODB.Recordset
        Dim iCols As Integer
        Dim tbl As ListObject

        Set objMyConn = New ADODB.Connection
        Set objMyCmd = New ADODB.Command
        Set objMyRecordset = New ADODB.Recordset

        'Open Connection'
        objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=;Initial Catalog=;Trusted_connection=Yes;Integrated Security=;"
        objMyConn.Open

        'Dim sSqL As String
        'Set objMyCmd.ActiveConnection = objMyConn
        'sSqL = "Select AccName,Underwriter,Auditor, UT_Score, Underwriter_Score from AUTUARIAL.dbo.AUDIT_CHECKLIST"
        'objMyConn.Execute sSqL

        'Set and Excecute SQL Command'
        Dim u As String
        Set objMyCmd.ActiveConnection = objMyConn
        objMyCmd.CommandText = "SELECT FileID, AccName, Underwriter, Auditor,UT_Score,Underwriter_Score from Checklist"
        'objMyCmd.CommandText = "SELECT  CHECKLIST.FileID, CHECKLIST.AccName, CHECKLIST.Underwriter, CHECKLIST.Auditor, CHECKLIST.UT_Score, CHECKLIST.Underwriter_Score   "


        objMyCmd.CommandType = adCmdText
        objMyCmd.Execute


        'Open Recordset'
        Set objMyRecordset.Source = objMyCmd
        objMyRecordset.Open
        For iCols = 0 To objMyRecordset.Fields.Count - 1
        Worksheets("Data").Cells(1, iCols + 1).Value = objMyRecordset.Fields(iCols).name
        Next

        'Copy Data to Excel'
        Worksheets("Data").Range("A2").CopyFromRecordset objMyRecordset

        'MsgBox "Report Generated Succesfully. View the results in worksheet called DATA !", vbOKOnly
        ActiveSheet.Protect Password:="star"

        Application.ScreenUpdating = True
        Application.DisplayAlerts = True



        End Sub

Answer 1

Are Worksheets("Data") and ActiveSheet are same? You are manipulating the sheet called Data and protecting ActiveSheet.

Worksheets("Data").Protect will ensure that its protected and not some random active sheet.