CODEWITHSUNDEEP
excelvba
Anthony
Anthony

Reputation: 552

How do I protect only cells with text in using VBA?

I know how to protect a sheet using the following:-

Sub Protect()

    Worksheets("Sheet1").Cells.Locked = False
    Worksheets("Sheet1").Range("B1:b10").Locked = True
    Worksheets("Sheet1").Protect Password:="erty", UserInterfaceOnly:=True


End Sub

I only want to protect cells that have text in. My problem is that the number of columns varies. How do I count the columns and only protect cells with text in them?

Upvotes: 0

Views: 493

Answers (2)

Xabier
Xabier

Reputation: 7735

The following would achieve what you are expecting:

Sub protectit()
Dim ws As Worksheet: Set ws = Sheets("Sheet1")
'declare and set the worksheet you are working with, amend as required
Dim c As Range
ws.Cells.Locked = False 'unlock all the cells in the given Sheet
For Each c In ws.UsedRange 'for each cell in UsedRange
    If c.Value <> "" Then c.Locked = True 'if cell is not empty then locked = True
Next
ws.protect Password:="erty", UserInterfaceOnly:=True 'protect the populated cells
End Sub

Upvotes: 1

iamanigeeit
iamanigeeit

Reputation: 834

You can just check all the cells and lock them if there's text:

Dim Cell As Range
For Each Cell in Sheet1.UsedRange
    If Cell.Value <> "" Then Cell.Locked = True
Next Cell

Upvotes: 1

Related Questions