create string from checkboxes in userform

[JD]

Hello,

I currently have a userform in excel that has several checkboxes for the
user to select. The checkboxes then apply a string value if the user
selects the checkbox (ie. If chkbox.value = -1 then strname = "something"
else strname = "" ). The values are then placed into the active cell in
which the form was called.

What I want to do is create a string, separated by a delimiter, and place
the entire string into the active cell as the value. Currently, I have the
string put together, but it inserts delimiters even if there is no value.

For example, If I have four checkboxes, and only 2 are selected, then the
string value looks like ,,value,value .....What I would like to accomplish
is to put together the string of only checkboxes that are selected, and not
have values inserted into the string for blank values, or checkboxes that
have not been selected.

Any help is most appreciated.

Thanks

 

[Greg Maxey]

Maybe something like this:
Private Sub CommandButton1_Click()
Dim pStr1 As String
Dim pStr2 As String
Dim pStr3 As String
Dim pStr4 As String
Dim pStrFinal As String
If Me.CheckBox1.Value = -1 Then
pStr1 = "Apples,"
Else
pStr1 = ""
End If
If Me.CheckBox2.Value = -1 Then
pStr2 = "Peaches,"
Else
pStr2 = ""
End If
If Me.CheckBox3.Value = -1 Then
pStr3 = "Pears,"
Else
pStr3 = ""
End If
If Me.CheckBox4.Value = -1 Then
pStr4 = "Plums,"
Else
pStr4 = ""
End If
pStrFinal = pStr1 + pStr2 + pStr3 + pStr4
pStrFinal = Left(pStrFinal, Len(pStrFinal) - 1)
MsgBox pStrFinal
Unload Me

End Sub

 

[JD]

Excellent...Thank you Greg for the assistance. It worked well.

This may be a long shot, but I figured I would ask.....Is it possible to
read the string value of the excel cell, and have the appropriate checkboxes
selected as True when the userform opens?

This seems rather tricky to me, and it is not something I need to have done,
but is just a usability thought.

For example, if a cell value is Apples,Peaches,Plums.....is there a way to
have the checkboxes for these values marked as True when the form opens?

Once again, thanks for all of your help.

 

[Rob]

or

Private Sub CommandButton1_Click()
Dim pStr As String

If Me.CheckBox1.Value = -1 Then pStr = pStr & "Apples, "
If Me.CheckBox2.Value = -1 Then pStr = pStr & "Peaches, "
If Me.CheckBox3.Value = -1 Then pStr = pStr & "Pears, "
If Me.CheckBox4.Value = -1 Then pStr = pStr & "Plums, "
If pStr <> "" Then pStr = Left(pStr, Len(pStr) - 2)
MsgBox pStr
Unload Me

End Sub

 

[Greg Maxey]

JD,

I am not that familiar with Excel, but if you could capture the cell
value in a Select Case Statement contained in the USERFORM initialize
event then something like this might work:

Select Case Excel.Cell.Value
Case "Apples"
Me.Checkbox1.Value = True
Case "Apples,Pears"
Me. ....


It would take some work.

 

[Greg Maxey]

Yes that looks much better.

 

[Karl E. Peterson]

or

Private Sub CommandButton1_Click()
Dim pStr As String

If Me.CheckBox1.Value = -1 Then pStr = pStr & "Apples, "
If Me.CheckBox2.Value = -1 Then pStr = pStr & "Peaches, "
If Me.CheckBox3.Value = -1 Then pStr = pStr & "Pears, "
If Me.CheckBox4.Value = -1 Then pStr = pStr & "Plums, "
If pStr <> "" Then pStr = Left(pStr, Len(pStr) - 2)
MsgBox pStr
Unload Me

End Sub

or

If Me.CheckBox1.Value Then pStr = pStr & String$(Sgn(Len(pStr)), ",") & "Apples"
If Me.CheckBox1.Value Then pStr = pStr & String$(Sgn(Len(pStr)), ",") &
"Peaches"
If Me.CheckBox1.Value Then pStr = pStr & String$(Sgn(Len(pStr)), ",") & "Pears"
If Me.CheckBox1.Value Then pStr = pStr & String$(Sgn(Len(pStr)), ",") & "Plums"

or

If Me.CheckBox1.Value Then pStr = pStr & Iif(Len(pStr), ",", "") & "Apples"
If Me.CheckBox1.Value Then pStr = pStr & Iif(Len(pStr), ",", "") & "Peaches"
If Me.CheckBox1.Value Then pStr = pStr & Iif(Len(pStr), ",", "") & "Pears"
If Me.CheckBox1.Value Then pStr = pStr & Iif(Len(pStr), ",", "") & "Plums"

optionally followed by

pStr = Replace$(pStr, ",", ", ")

 

[Greg Maxey]

Thanks Karl,

Never used or seen IIF used before. You might want to change your
Checkbox numbers ;-)

 

[Karl E. Peterson]

Thanks Karl,

Never used or seen IIF used before.

Used to be (in the pre-COM, VB3 and earlier era) it was a function to avoid, as it
was held in a non-standard DLL. Old habits die hard, as you can see by my first
alternate suggestion, and it's rarely used. Another issue with Iif is that it will
evaluate all parameters, regardless of how the first parameter evaluates. IOW, say
you have a "test" like this:

Iif(myDriveBlank("d:"), FormatDrive("d:"), CopyFiles("d:"))

It will actually call both FormatDrive and CopyFiles, no matter what's returned by
the initial test. Simple proof:

Public Sub Main()
Dim x As Boolean
x = IIf(x, IifTrue(), IifFalse())
End Sub

Private Function IifFalse() As Boolean
Debug.Print "False"
IifFalse = False
End Function

Private Function IifTrue() As Boolean
Debug.Print "True"
IifTrue = True
End Function

You might want to change your Checkbox numbers ;-)

So much for cut/paste examples! <g>

 

[Greg Maxey]

Karl,

Nice to know stuff. Thanks.

Used to be (in the pre-COM, VB3 and earlier era) it was a function to avoid, as it
was held in a non-standard DLL. Old habits die hard, as you can see by my first
alternate suggestion, and it's rarely used. Another issue with Iif is that it will
evaluate all parameters, regardless of how the first parameter evaluates. IOW, say
you have a "test" like this:

Iif(myDriveBlank("d:"), FormatDrive("d:"), CopyFiles("d:"))

It will actually call both FormatDrive and CopyFiles, no matter what's returned by
the initial test. Simple proof:

Public Sub Main()
Dim x As Boolean
x = IIf(x, IifTrue(), IifFalse())
End Sub

Private Function IifFalse() As Boolean
Debug.Print "False"
IifFalse = False
End Function

Private Function IifTrue() As Boolean
Debug.Print "True"
IifTrue = True
End Function



So much for cut/paste examples! <g>