create string from checkboxes in userform

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JD - 21 Mar 2007 13:03 GMT

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

Reply to this Message

Greg Maxey - 21 Mar 2007 13:33 GMT

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

> Hello,
>
[quoted text clipped - 17 lines]
>
> Thanks

Reply to this Message

JD - 21 Mar 2007 13:54 GMT

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.

> Maybe something like this:
> Private Sub CommandButton1_Click()
[quoted text clipped - 54 lines]
>>
>> Thanks

Reply to this Message

Greg Maxey - 21 Mar 2007 14:14 GMT

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.

> Excellent...Thank you Greg for the assistance. It worked well.
>
[quoted text clipped - 70 lines]
>
> - Show quoted text -

Reply to this Message

Rob - 21 Mar 2007 14:05 GMT

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

Reply to this Message

Greg Maxey - 21 Mar 2007 14:16 GMT

Yes that looks much better.

> or
>
[quoted text clipped - 10 lines]
>
> End Sub

Reply to this Message

Karl E. Peterson - 21 Mar 2007 18:01 GMT

> or
>
[quoted text clipped - 10 lines]
>
> 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, ",", ", ")

Signature

.NET: It's About Trust!
http://vfred.mvps.org

Reply to this Message

Greg Maxey - 21 Mar 2007 18:18 GMT

Thanks Karl,

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

> > or
>
[quoted text clipped - 33 lines]
> .NET: It's About Trust!
> http://vfred.mvps.org

Reply to this Message

Karl E. Peterson - 21 Mar 2007 18:45 GMT

> 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

<shudder>

> You might want to change your Checkbox numbers ;-)

So much for cut/paste examples! <g>

Signature

.NET: It's About Trust!
http://vfred.mvps.org

Reply to this Message

Greg Maxey - 21 Mar 2007 19:12 GMT

Karl,

Nice to know stuff. Thanks.

> > Thanks Karl,
>
[quoted text clipped - 34 lines]
> .NET: It's About Trust!
> http://vfred.mvps.org

Reply to this Message