Function/Sub to change cell properties

ExcelMonkey · 04-16-2006, 03:40 PM

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

Greg Wilson · 04-16-2006, 04:00 PM

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
>

ExcelMonkey · 04-16-2006, 04:00 PM

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
> >

ExcelMonkey · 04-16-2006, 04:10 PM

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
> >

Bob Phillips · 04-16-2006, 04:35 PM

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> > >

ExcelMonkey · 04-16-2006, 05:00 PM

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> > > >
>
>
>

ExcelMonkey · 04-16-2006, 05:10 PM

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> > > >
>
>
>

Chip Pearson · 04-17-2006, 09:10 AM

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" <[email protected]> wrote in
message
news:[email protected]...
>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
>