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]

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]

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]

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]

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