Check Box and Validation of a cell

[leerem]

Hi All,
Once again, I ask for your assistance. I have a worksheet with with a
simple check box. What I need to do is set up a cell and set its validation
as a list dependant of the value of the checkbox. Eg if the
checkbox.value=false then the validation of Cell B6 will be a list with the
source being =NameCheck, else if Checkbox.value=true then Cell B6 will be a
list with the source being =AgencyNameCheck.

If this were to be on a userform I'd use the rowsource method, but on a
worksheet I believe the easiest way is to set it up a cell useing the
validation method. The problem I'm having is getting the code correct.

I'd appreciate your assistance with this

Regards Lee

 

[leerem]

Many Thanks, But I've solved it as follows unless there's another more
efficeint approach.

Private Sub Agency1_Click()

If Agency1.Value = True Then
With Range("B6")
.ClearContents
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=agencynamecheck"
.IgnoreBlank = True
.InCellDropdown = True
End With
End With
Else
With Range("B6")
.ClearContents
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop,
Operator:= _
xlBetween, Formula1:="=namecheck"
.IgnoreBlank = True
.InCellDropdown = True
End With
End With
End If

End Sub


Many Thanks
rgds Lee

 

[Dave Peterson]

I'd use a linked cell for that checkbox. Then you can use that cell in your
data|validation rules.

Say your linkedcell is X99,
then you could select your cell
Data|Validation (xl2003 menus)
Allow: List
Source: =if(x99=true,AgencyNameCheck,NameCheck)