ExcelBanter - Function/Sub to change cell properties

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Function/Sub to change cell properties (https://www.excelbanter.com/excel-programming/358999-function-sub-change-cell-properties.html)

Function/Sub to change cell properties

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

Function/Sub to change cell properties

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

"ExcelMonkey" wrote:

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

Function/Sub to change cell properties

Must have been the double brackets that casued the problems. Thanks.

"Greg Wilson" wrote:

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

"ExcelMonkey" wrote:

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

Function/Sub to change cell properties

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

"Greg Wilson" wrote:

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

"ExcelMonkey" wrote:

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

Function/Sub to change cell properties

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)

"ExcelMonkey" wrote in message
...
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

"Greg Wilson" wrote:

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

"ExcelMonkey" wrote:

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

Function/Sub to change cell properties

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

"Bob Phillips" wrote:

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)

"ExcelMonkey" wrote in message
...
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

"Greg Wilson" wrote:

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

"ExcelMonkey" wrote:

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

Function/Sub to change cell properties

Sorry Bob, ignore last reply. My error.

"Bob Phillips" wrote:

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)

"ExcelMonkey" wrote in message
...
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

"Greg Wilson" wrote:

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

"ExcelMonkey" wrote:

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

Function/Sub to change cell properties

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

"ExcelMonkey" wrote in
message
...
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