Function/Sub to change cell properties

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a code snippet that I am using to change the properties of a cell:

With Range("FirstSlaveCell")
.Interior.Pattern = xlGray50
.Locked = True
.FormulaHidden = False
.Validation.InCellDropdown = False
End With

I want to replace it with a function/sub that looks like the one below but I
am getting a Run Time Error 424 Object Required. What am I doing wrong?

DisableCell (Range("FirstSlaveCell"))

Sub DisableCell(rng As Range)
With rng
.Interior.Pattern = xlGray50
.Locked = True
.FormulaHidden = False
.Validation.InCellDropdown = False
End With
End Sub
 
Assuming FirstSlaveCell actually has Data Validation applied, try:

Sub TestDisableCell()
DisableCell Range("FirstSlaveCell")
End Sub

Sub DisableCell(rng As Range)
With rng
.Interior.Pattern = xlGray50
.Locked = True
.FormulaHidden = False
.Validation.InCellDropdown = False
End With
End Sub

Regards,
Greg
 
Ok. Now suppose I wanted to do this to more than one range. Normally I
would have:

With Range("FirstSlaveCell", "SecondCell")
.Interior.Pattern = xlGray50
.Locked = True
.FormulaHidden = False
.Validation.InCellDropdown = False
End With

How would I pass more than one range to the same sub? And what if I did not
know in advance how may ranges I wanted to pass to the sub?

DisableCell Range("FirstSlaveCell")

Sub DisableCell(rng As Range)
With rng
.Interior.Pattern = xlGray50
.Locked = True
.FormulaHidden = False
.Validation.InCellDropdown = False
End With
End Sub
 
DisableCell Range("FirstSlaveCell"), Range("SecondCell")

Sub DisableCell(ParamArray rng())
Dim i As Long
For i = LBound(rng) To UBound(rng)
With rng(i)
.Interior.Pattern = xlGray50
.Locked = True
.FormulaHidden = False
.Validation.InCellDropdown = False
End With
Next i
End Sub


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
One quick questions Bob. Firstly, I am doing this in a Worksheet_Change
event (see full code below). When I try to run it, I get a Compile Error
"Wrong number of arguments or invalid property assignment." It highlights
the first line of code. Why is this happening?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)'>>FAILS HERE
DisableCell Range("FirstCell"), Range("SecondCell")
End Sub

Sub DisableCell(ParamArray rng())
Dim i As Long
For i = LBound(rng) To UBound(rng)
With rng
.Interior.Pattern = xlGray50
.Locked = True
.FormulaHidden = False
.Validation.InCellDropdown = False
End With
Next i
End Sub
 
Sorry Bob, ignore last reply. My error.


Bob Phillips said:
DisableCell Range("FirstSlaveCell"), Range("SecondCell")

Sub DisableCell(ParamArray rng())
Dim i As Long
For i = LBound(rng) To UBound(rng)
With rng(i)
.Interior.Pattern = xlGray50
.Locked = True
.FormulaHidden = False
.Validation.InCellDropdown = False
End With
Next i
End Sub


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
Click to expand...
 
The problem is the parentheses around the argument to
DisableCell:

DisableCell (Range("FirstSlaveCell"))
should be
DisableCell Range("FirstSlaveCell")

With the parens, VBA evaluates the expression and passes the
result to DisableCell. The code

DisableCell (Range("FirstSlaveCell"))
is really equivalent to
DisableCell (Range("FirstSlaveCell").Value)

You shouldn't use parens to pass argument to a Sub procedure.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



message
news:[email protected]...
 
Back
Top