CODEWITHSUNDEEP
excelvba
czchlong
czchlong

Reputation: 2594

Performing named cell additions in EXCEL VBA

I have a cell that is named DATA_FIELD_NAME and I would like to use it in the following way:

Private Sub LockCells(iNumberOfDataColumns As Long)
  ActiveSheet.Unprotect
  ActiveSheet.Cells.Locked = False
  ActiveSheet.Range("DATA_FIELD_NAME:DATA_FIELD_NAME+iNumberOfDataColumns").Locked = True
  ActiveSheet.Protect Contents:=True
End Sub

Essentially I would like to lock the range of cells starting from the DATA_FIELD_NAME cell horizontally to DATA_FIELD_NAME + n.

However this doesn't work. Could someone please tell me the correct syntax or an alternate method?

Upvotes: 0

Views: 114

Answers (2)

Daniel
Daniel

Reputation: 13132

I'd try this:

ActiveSheet.Range("DATA_FIELD_NAME").Resize(1, iNumberOfDataColumns).Locked = True

Here is a reference on Range.Resize Basically it changes the amount of cells you are dealing with based upon the current range. In what I gave you, it changes to 1 row, and iNumberOfDataColumns columns.

Upvotes: 2

iDevlop
iDevlop

Reputation: 25272

I just wrote this in SO, not tested in XL, so it might contain typos, but this should work: 

With ActiveSheet
   Range(.Range("DATA_FIELD_NAME"), .Range("DATA_FIELD_NAME").Offset(0,iNumberOfDataColumns)
End With

The idea is to combine Offset(rows, cols) with to use the Range(range1, range2) syntax.
Alternatively, you could be a dynamic range name as explained here.

Upvotes: 1

Related Questions