Disable Cell and take out Data Validation

[ExcelMonkey]

I have a routine which enables/disables cells based values in other cells.
This is done by callin the enable/disable routines below. Each routine has a
line of code that says:

..Validation.InCellDropdown = False

However this is only relevant if the cell in question has data validation.
I want to wrap of IF logic around this to check to see if the cell has data
validation. I have routine for checking if a cell has data validation (True
False) but it takes a range variable as an argument. I cannot seem to
intigrate it into these two subs.

Does any one know how I would check for this given the code below?

Thanks

'***************************************
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Range("Cell1") = "Static" Then
DisableCell Range("Cell2")
EnableCell Range("Cell3")
Else
EnableCell Range("Cell2")
DisableCell Range("Cell3")
End If
End Sub
'***********************************
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
'***********************************
Sub EnableCell(ParamArray rng())
Dim i As Long
For i = LBound(rng) To UBound(rng)
With rng(i)
.Interior.Pattern = xlSolid
.Locked = False
.FormulaHidden = True
.Validation.InCellDropdown = True
End With
Next i
End Sub

[Bob Phillips]

'-----------------------------------------------------------------
Public Function HasValidation(r As Range) As Boolean
'-----------------------------------------------------------------
Dim i
Dim ma As Range
On Error Resume Next
HasValidation = True
i = r.Validation.Type
If Err.Number <> 0 Then
HasValidation = False
Exit Function
End If
Set ma = r.MergeArea
If ma.Cells(1, 1).Address <> r.Address Then
HasValidation = False
End If
End Function


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
> I have a routine which enables/disables cells based values in other cells.
> This is done by callin the enable/disable routines below. Each routine
has a
> line of code that says:
>
> .Validation.InCellDropdown = False
>
> However this is only relevant if the cell in question has data validation.
> I want to wrap of IF logic around this to check to see if the cell has
data
> validation. I have routine for checking if a cell has data validation
(True
> False) but it takes a range variable as an argument. I cannot seem to
> intigrate it into these two subs.
>
> Does any one know how I would check for this given the code below?
>
> Thanks
>
> '***************************************
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> If Range("Cell1") = "Static" Then
> DisableCell Range("Cell2")
> EnableCell Range("Cell3")
> Else
> EnableCell Range("Cell2")
> DisableCell Range("Cell3")
> End If
> End Sub
> '***********************************
> 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
> '***********************************
> Sub EnableCell(ParamArray rng())
> Dim i As Long
> For i = LBound(rng) To UBound(rng)
> With rng(i)
> .Interior.Pattern = xlSolid
> .Locked = False
> .FormulaHidden = True
> .Validation.InCellDropdown = True
> End With
> Next i
> End Sub
>
>

[ExcelMonkey]

Sorry Bob. Maybe I was not clear. When I try to use a function like the one
you provided, I get a ByRef error. This is the code I have in the worksheet
module. Is the problem that your funciton takes a range variable but I am
using a ParamArray rng() in the Enable function?

Thanks

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Range("SenDebtIntDataType") = "Static" Then
EnableCell Range("SenDebtProfileList")
End If
End Sub

Sub EnableCell(ParamArray rng())
Dim i As Long
For i = LBound(rng) To UBound(rng)
With rng(i)
.Interior.Pattern = xlSolid
.Locked = False
.FormulaHidden = True
If HasValidation(rng) = True Then
.Validation.InCellDropdown = True
End If
End With
Next i
End Sub
Private Function HasValidation(r As Range) As Boolean
'-----------------------------------------------------------------
Dim i
Dim ma As Range
On Error Resume Next
HasValidation = True
i = r.Validation.Type
If Err.Number <> 0 Then
HasValidation = False
Exit Function
End If
Set ma = r.MergeArea
If ma.Cells(1, 1).Address <> r.Address Then
HasValidation = False
End If
End Function

"Bob Phillips" wrote:

> '-----------------------------------------------------------------
> Public Function HasValidation(r As Range) As Boolean
> '-----------------------------------------------------------------
> Dim i
> Dim ma As Range
> On Error Resume Next
> HasValidation = True
> i = r.Validation.Type
> If Err.Number <> 0 Then
> HasValidation = False
> Exit Function
> End If
> Set ma = r.MergeArea
> If ma.Cells(1, 1).Address <> r.Address Then
> HasValidation = False
> End If
> End Function
>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "ExcelMonkey" <[email protected]> wrote in message
> news:[email protected]...
> > I have a routine which enables/disables cells based values in other cells.
> > This is done by callin the enable/disable routines below. Each routine
> has a
> > line of code that says:
> >
> > .Validation.InCellDropdown = False
> >
> > However this is only relevant if the cell in question has data validation.
> > I want to wrap of IF logic around this to check to see if the cell has
> data
> > validation. I have routine for checking if a cell has data validation
> (True
> > False) but it takes a range variable as an argument. I cannot seem to
> > intigrate it into these two subs.
> >
> > Does any one know how I would check for this given the code below?
> >
> > Thanks
> >
> > '***************************************
> > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> > If Range("Cell1") = "Static" Then
> > DisableCell Range("Cell2")
> > EnableCell Range("Cell3")
> > Else
> > EnableCell Range("Cell2")
> > DisableCell Range("Cell3")
> > End If
> > End Sub
> > '***********************************
> > 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
> > '***********************************
> > Sub EnableCell(ParamArray rng())
> > Dim i As Long
> > For i = LBound(rng) To UBound(rng)
> > With rng(i)
> > .Interior.Pattern = xlSolid
> > .Locked = False
> > .FormulaHidden = True
> > .Validation.InCellDropdown = True
> > End With
> > Next i
> > End Sub
> >
> >
>
>
>

[Bob Phillips]

The ByRef error is because you re trying to pass a variant (your Paramarray)
to a function ex-expecting a range. It can be cured, as shown below, but why
are you using ParamArray?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("SenDebtIntDataType")) Is Nothing Then
If Not Range("SenDebtIntDataType") = "Static" Then
EnableCell Range("SenDebtProfileList")
End If
End If
End Sub

Sub EnableCell(ParamArray rng())
Dim i As Long
For i = LBound(rng) To UBound(rng)
With rng(i)
.Interior.Pattern = xlSolid
.Locked = False
.FormulaHidden = True
If HasValidation(rng(i)) = True Then
If .Validation.Type = 3 Then
.Validation.InCellDropdown = True
End If
End If
End With
Next i
End Sub


'-----------------------------------------------------------------
Private Function HasValidation(r) As Boolean
'-----------------------------------------------------------------
Dim i
Dim ma As Range
On Error Resume Next
HasValidation = True
i = r.Validation.Type
If Err.Number <> 0 Then
HasValidation = False
Exit Function
End If
Set ma = r.MergeArea
If ma.Cells(1, 1).Address <> r.Address Then
HasValidation = False
End If
End Function



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"ExcelMonkey" <[email protected]> wrote in message
news:[email protected]...
> Sorry Bob. Maybe I was not clear. When I try to use a function like the
one
> you provided, I get a ByRef error. This is the code I have in the
worksheet
> module. Is the problem that your funciton takes a range variable but I am
> using a ParamArray rng() in the Enable function?
>
> Thanks
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> If Not Range("SenDebtIntDataType") = "Static" Then
> EnableCell Range("SenDebtProfileList")
> End If
> End Sub
>
> Sub EnableCell(ParamArray rng())
> Dim i As Long
> For i = LBound(rng) To UBound(rng)
> With rng(i)
> .Interior.Pattern = xlSolid
> .Locked = False
> .FormulaHidden = True
> If HasValidation(rng) = True Then
> .Validation.InCellDropdown = True
> End If
> End With
> Next i
> End Sub
> Private Function HasValidation(r As Range) As Boolean
> '-----------------------------------------------------------------
> Dim i
> Dim ma As Range
> On Error Resume Next
> HasValidation = True
> i = r.Validation.Type
> If Err.Number <> 0 Then
> HasValidation = False
> Exit Function
> End If
> Set ma = r.MergeArea
> If ma.Cells(1, 1).Address <> r.Address Then
> HasValidation = False
> End If
> End Function
>
> "Bob Phillips" wrote:
>
> > '-----------------------------------------------------------------
> > Public Function HasValidation(r As Range) As Boolean
> > '-----------------------------------------------------------------
> > Dim i
> > Dim ma As Range
> > On Error Resume Next
> > HasValidation = True
> > i = r.Validation.Type
> > If Err.Number <> 0 Then
> > HasValidation = False
> > Exit Function
> > End If
> > Set ma = r.MergeArea
> > If ma.Cells(1, 1).Address <> r.Address Then
> > HasValidation = False
> > End If
> > End Function
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "ExcelMonkey" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have a routine which enables/disables cells based values in other
cells.
> > > This is done by callin the enable/disable routines below. Each
routine
> > has a
> > > line of code that says:
> > >
> > > .Validation.InCellDropdown = False
> > >
> > > However this is only relevant if the cell in question has data
validation.
> > > I want to wrap of IF logic around this to check to see if the cell has
> > data
> > > validation. I have routine for checking if a cell has data validation
> > (True
> > > False) but it takes a range variable as an argument. I cannot seem to
> > > intigrate it into these two subs.
> > >
> > > Does any one know how I would check for this given the code below?
> > >
> > > Thanks
> > >
> > > '***************************************
> > > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> > > If Range("Cell1") = "Static" Then
> > > DisableCell Range("Cell2")
> > > EnableCell Range("Cell3")
> > > Else
> > > EnableCell Range("Cell2")
> > > DisableCell Range("Cell3")
> > > End If
> > > End Sub
> > > '***********************************
> > > 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
> > > '***********************************
> > > Sub EnableCell(ParamArray rng())
> > > Dim i As Long
> > > For i = LBound(rng) To UBound(rng)
> > > With rng(i)
> > > .Interior.Pattern = xlSolid
> > > .Locked = False
> > > .FormulaHidden = True
> > > .Validation.InCellDropdown = True
> > > End With
> > > Next i
> > > End Sub
> > >
> > >
> >
> >
> >

[ExcelMonkey]

So Bob I am using the "ParamArray rng()" as I do not know how to make this
code work any other way. I tried changing the "ParamArray rng()" to "rng()"
in both the Enable and Disable routines. But when I do this I get errors in
my change event (i.e. Type Mismatch array or user defined type expected.)
What I am hearing you say is that I can use your funciton which takes a range
variable, but I have to stop using the "ParamArray rng()". How to I change
the "ParamArray rng() to accomodate your functions range varible requirement
while allowing the rest of the code to work?

Thanks


'***************************************
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Range("Cell1") = "Static" Then
DisableCell Range("Cell2")
EnableCell Range("Cell3")
Else
EnableCell Range("Cell2")
DisableCell Range("Cell3")
End If
End Sub
'***********************************
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
'***********************************
Sub EnableCell(ParamArray rng())
Dim i As Long
For i = LBound(rng) To UBound(rng)
With rng(i)
.Interior.Pattern = xlSolid
.Locked = False
.FormulaHidden = True
' .Validation.InCellDropdown = True
End With
Next i
End Sub


"ExcelMonkey" wrote:

> I have a routine which enables/disables cells based values in other cells.
> This is done by callin the enable/disable routines below. Each routine has a
> line of code that says:
>
> .Validation.InCellDropdown = False
>
> However this is only relevant if the cell in question has data validation.
> I want to wrap of IF logic around this to check to see if the cell has data
> validation. I have routine for checking if a cell has data validation (True
> False) but it takes a range variable as an argument. I cannot seem to
> intigrate it into these two subs.
>
> Does any one know how I would check for this given the code below?
>
> Thanks
>
> '***************************************
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> If Range("Cell1") = "Static" Then
> DisableCell Range("Cell2")
> EnableCell Range("Cell3")
> Else
> EnableCell Range("Cell2")
> DisableCell Range("Cell3")
> End If
> End Sub
> '***********************************
> 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
> '***********************************
> Sub EnableCell(ParamArray rng())
> Dim i As Long
> For i = LBound(rng) To UBound(rng)
> With rng(i)
> .Interior.Pattern = xlSolid
> .Locked = False
> .FormulaHidden = True
> .Validation.InCellDropdown = True
> End With
> Next i
> End Sub
>
>

[ExcelMonkey]

Sorry. I figured it out as seen below. I can now use your function which
takes the range object. Thanks for your help.

Sub DisableCell(rng As Range) 'changed from ParamArray rng() to rng As Range
Dim i As Long

With rng
.Interior.Pattern = xlGray50
.Locked = True
.FormulaHidden = False
If HasValidation(rng) = True Then
.Validation.InCellDropdown = False
End If
End With

End Sub

"ExcelMonkey" wrote:

> So Bob I am using the "ParamArray rng()" as I do not know how to make this
> code work any other way. I tried changing the "ParamArray rng()" to "rng()"
> in both the Enable and Disable routines. But when I do this I get errors in
> my change event (i.e. Type Mismatch array or user defined type expected.)
> What I am hearing you say is that I can use your funciton which takes a range
> variable, but I have to stop using the "ParamArray rng()". How to I change
> the "ParamArray rng() to accomodate your functions range varible requirement
> while allowing the rest of the code to work?
>
> Thanks
>
>
> '***************************************
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> If Range("Cell1") = "Static" Then
> DisableCell Range("Cell2")
> EnableCell Range("Cell3")
> Else
> EnableCell Range("Cell2")
> DisableCell Range("Cell3")
> End If
> End Sub
> '***********************************
> 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
> '***********************************
> Sub EnableCell(ParamArray rng())
> Dim i As Long
> For i = LBound(rng) To UBound(rng)
> With rng(i)
> .Interior.Pattern = xlSolid
> .Locked = False
> .FormulaHidden = True
> ' .Validation.InCellDropdown = True
> End With
> Next i
> End Sub
>
>
> "ExcelMonkey" wrote:
>
> > I have a routine which enables/disables cells based values in other cells.
> > This is done by callin the enable/disable routines below. Each routine has a
> > line of code that says:
> >
> > .Validation.InCellDropdown = False
> >
> > However this is only relevant if the cell in question has data validation.
> > I want to wrap of IF logic around this to check to see if the cell has data
> > validation. I have routine for checking if a cell has data validation (True
> > False) but it takes a range variable as an argument. I cannot seem to
> > intigrate it into these two subs.
> >
> > Does any one know how I would check for this given the code below?
> >
> > Thanks
> >
> > '***************************************
> > Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> > If Range("Cell1") = "Static" Then
> > DisableCell Range("Cell2")
> > EnableCell Range("Cell3")
> > Else
> > EnableCell Range("Cell2")
> > DisableCell Range("Cell3")
> > End If
> > End Sub
> > '***********************************
> > 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
> > '***********************************
> > Sub EnableCell(ParamArray rng())
> > Dim i As Long
> > For i = LBound(rng) To UBound(rng)
> > With rng(i)
> > .Interior.Pattern = xlSolid
> > .Locked = False
> > .FormulaHidden = True
> > .Validation.InCellDropdown = True
> > End With
> > Next i
> > End Sub
> >
> >