Protection Stops Macro From Running

Hi All,

I have a very simple request. I want to make the worksheet protected, however, when I protect the worksheet the macro will not run. Does anyone have any useful tips. Many thanks in anticipation.

Keith

You need to remove and re-apply protection within your code.

Sub CourseFinder()

   Dim DstWks As Worksheet
   Dim FirstAddress As String
   Dim SearchCell As Range
   Dim SearchData As Variant
   Dim SearchRng As Range
   Dim SrcWks As Worksheet
   
   ActiveSheet.Unprotect
   
     SearchData = InputBox("Enter the word or word* you want to find.")
        If SearchData = "" Then Exit Sub

     Set SrcWks = ActiveSheet
     Set SearchRng = SrcWks.UsedRange
' remove hilite from previous search    
SearchRng.Offset(1).Resize(SearchRng.Rows.Count).EntireRow.Interior.ColorIndex = xlNone
         
     Set SearchCell = SearchRng.Find(What:=SearchData, After:=Cells(1, 1), _
                        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, MatchCase:=False)
                        
     If Not SearchCell Is Nothing Then
        FirstAddress = SearchCell.Address
          Do
              SearchCell.EntireRow.Interior.ColorIndex = 6
              SearchCell.Cells(SearchCell.Row, "L") = 1     '<<<<< Change Column Here
            Set SearchCell = SearchRng.FindNext(SearchCell)
          Loop While Not SearchCell Is Nothing And SearchCell.Address <> FirstAddress
     End If
     
   ActiveSheet.Protect
   
End Sub

You might want to consider remove the attached file as the content looks as though it may be confidential.

You could possibly use Protect with UserInterface, see an example here

http://www.excel-it.com/vba_examples.htm

Works for me.

Run the macro and search for time.

EDIT: Confidential file removed