VBA UserForm CheckBoxes Import

[msquared99]

I have a UserForm with some CheckBoxes and TextBoxes. If the CheckBox is checked my macro assigns a value such as "Cat" to a new workbook then saves the workbook in cells D15:D18. I can import the data back into the UserForm in the TextBoxes. My problem is how do I recheck the CheckBoxes?


I may have none to all 4 CheckBoxes checked. Here is how the values are assigned.


If cbCat.Value = True Then .Cells(25, 4).End(xlUp).Offset(1).Value = "Cat"

If cbDog.Value = True Then .Cells(25, 4).End(xlUp).Offset(1).Value = "Dog"

If cbRabbit.Value = True Then .Cells(25, 4).End(xlUp).Offset(1).Value = "Rabbit"

If cbDeer.Value = True Then .Cells(25, 4).End(xlUp).Offset(1).Value = "Deer"


So, when I import the saved workbook how do I get the macro to look at cells D15:18 and say, If "Cat" or any other selection is in cells D15:D18 check the CheckBox in the UserForm on the import?


Thanks,


Mike

 

[Debraj]

Hi Mike,


Please try the Below code in UserForm_Initialize

[pre]

Private Sub UserForm_Initialize()
With WorksheetFunction
If .CountIf(Range("D15:D18"), "Cat") > 0 Then cbCat.Value = True
If .CountIf(Range("D15:D18"), "Dog") > 0 Then cbDog.Value = True
If .CountIf(Range("D15:D18"), "Rabbit") > 0 Then cbRabbit.Value = True
If .CountIf(Range("D15:D18"), "Deer") > 0 Then cbDeer.Value = True
End With
End Sub

[/pre]
Regards,

Deb

 

[mercatog]

Hi Mike, Deb


Another way to make Roy's code (interesting in case of many checkboxes and the way how are named these checkboxes)

[pre]

Private Sub UserForm_Initialize()
Dim Tb, Elem

Tb = Array("Cat", "Dog", "Rabbit", "Deer")
For Each Elem In Tb
Me.Controls("cb" & Elem).Value = Application.CountIf(Sheet1.Range("D15:D18"), Elem)
Next Elem
End Sub[/pre]

Adapte the CodeName of your Sheet (here Sheet1)! CodeName and not Name, otherwise in place of [code]Sheet1

, make Worksheets("TheNameOfYourWorksheet&#34[/code]

Regards

 

[msquared99]

Thanks Deb & mercatog!


I had Deb's code but did not have the "> 0"


So close but so far away.


Again, many thanks!