VBA Protected sheet prevents macro from replacing text but cells are not protected-CodePudding

So I have this macro:

Application.Goto (ActiveWorkbook.Sheets("Fetch").Range("C33"))

Range("C33").Value = text
Range("B3").Value = text

Cells.Replace What:="ÿþ", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

The excel sheet is protected, but the two cells C33 and B3 are not since i want text to be replaced in those two cells.

When i run the above macro with the sheet unprotected, it works flawlessly. It runs together with a script that import a string of text to Excel.

When i protect the sheet, the macro does not replace the text but leave it unchanged.

Basically i just want the macro to run, target the text in those two cells, replace the specified part with nothing, and leave the rest, even when the sheet is protected.

CodePudding user response：

Assuming that:

Cells C33 and B3 are on 'Fetch' and are not locked, and
The sheet 'Fetch' is protected

The following would work:

With ActiveWorkbook.Sheets("Fetch")

    Union(.Range("C33"), .Range("B3")).Replace What:="ÿþ", Replacement:="", LookAt:=xlPart, SearchOrder _
            :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
            
End With

There is no need to remove the sheet protection if those two cells are unlocked.

Alternatively, you could perform the replacement one cell at a time using:

With ActiveWorkbook.Sheets("Fetch")

    .Range("C33").Value = Replace(.Range("C33").Value, "ÿþ", "")
    .Range("B3").Value = Replace(.Range("B3").Value, "ÿþ", "")
            
End With

Finally, now we have a clearer understanding of what is going on and how the text gets there in the first place, the following would probably be the simplest method:

Sub ParentPath_Import_To_Excel()

    Dim myFile As String
    Dim text As String
    Dim textline As String

    myFile = "H:\Temp\PublicFolder-powershell\ParentPath-FETCH\fullpath_X.txt"
      
    Open myFile For Input As #1
        Do Until EOF(1)
            Line Input #1, textline
            text = text & textline
        Loop
    Close #1

    With ActiveWorkbook.Sheets("Fetch")
        .Range("C33").Value = Replace(text, "ÿþ", "")
        .Range("B3").Value = .Range("C33").Value
        .Range("B3").Activate
    End With
        
End Sub

If the above fails and errors when writing to the cells, then you have not unlocked the two cells that you're trying to write to. You can either unlock the cells once, within the formatting menu (being the best solution) or you could unlock the entire sheet and relock it during runtime - but that is very much a sledgehammer to crack a nut kind of solution.

CodePudding user response：

I decided to go with the unprotect method, since the union suggestion above, did not work when the sheet was protected.

So i ended up with the following:

Sub ParentPath_Import_To_Excel()

    Dim myFile As String
    Dim text As String
    Dim textline As String

myFile = "H:\Temp\PublicFolder-powershell\ParentPath-FETCH\fullpath_X.txt"

Application.Goto (ActiveWorkbook.Sheets("Fetch").Range("C33"))

Open myFile For Input As #1
Do Until EOF(1)
    Line Input #1, textline
     text = text & textline
Loop

Close #1

Application.Goto (ActiveWorkbook.Sheets("Fetch").Range("C33"))

Range("C33").Value = text 'writes text to cell C33
Range("B3").Value = text 'writes text to cell B3

ActiveWorkbook.Sheets("Fetch").Unprotect  ' This removes the protection of the worksheet, so the search for the BOM-chars can be done, and changed.

Cells.Replace What:="ÿþ", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

ActiveWorkbook.Sheets("Fetch").Protect ' This protects the worksheet again

End Sub