checkbox code needed

[Woodi2]

I have 3 checkboxes on a userform. The Userform is opened if anycell in
range C4:C100 is selected. Checkbox 1 if true = "Mech", Checkbox 2 if true =
"Elect" and Checkbox 3 if true = "Inst/FET".
I need some code so that if for example Checkbox 1 and 2 are true, then
Activecell.offset (0,9) is returned with Mech & Elect.
Similiarly, if Checkbox 1, 2 and 3 are true it returns Mech & Elect &
Inst/FET.
I need this to work in all combinations. Is it possible?

 

[Tom Hutchins]

Try this in your userform code (you may have to change the object names):

Private Sub UserForm_Terminate()
Dim OutStr As String
If Me.CheckBox1.Value = True Then
OutStr$ = "Mech & "
End If
If Me.CheckBox2.Value = True Then
OutStr$ = OutStr$ & "Elect & "
End If
If Me.CheckBox3.Value = True Then
OutStr$ = OutStr$ & "Inst/FET & "
End If
ActiveCell.Offset(0, 9).Value = Left(OutStr$, Len(OutStr$) - 3)
End Sub

Hope this helps,

Hutch

 

[Gary Keramidas]

tom:

your code works fine except if somebody closes the form without making a
selection. not sure this will happen, but in my experience it somebody will
close the form without selecting an item.

so, just to add to your code, i think this would work:

Private Sub UserForm_Terminate()
Dim OutStr As String
If Me.CheckBox1.Value = True Then
OutStr$ = "Mech & "
End If
If Me.CheckBox2.Value = True Then
OutStr$ = OutStr$ & "Elect & "
End If
If Me.CheckBox3.Value = True Then
OutStr$ = OutStr$ & "Inst/FET & "
End If
If Me.CheckBox1 = False And Me.CheckBox2 = False And Me.CheckBox3 =
False Then
End
Else
ActiveCell.Offset(0, 9).Value = Left(OutStr$, Len(OutStr$) - 3)
End If
End Sub

 

[Gary Keramidas]

i forgot a line before the end statement

ActiveCell.Offset(0, 9).Value = ""
End

 

[Tom Hutchins]

Good point. Instead of checking all three checkbox values, I might just check
the length of OutStr$:

If Len(OutStr$) > 0 Then
ActiveCell.Offset(0, 9).Value = Left(OutStr$, Len(OutStr$) - 3)
Else
MsgBox "No selection was made"
End If

Hutch

 

[Woodi2]

Thanks Tom. That worked perfectly. Thanks to yourself as well Gary however
Toms code did the trick as I have code that checks the values within the
checkboxes elswhere.
Much Appreciated.