Check Box and Validation of a cell

  • Thread starter Thread starter leerem
  • Start date Start date
L

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
 
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
 
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)
 
Back
Top