Solved: UserForm with ComboBoxes and TextBoxes

[msquared99]

OK, here is the deal. I have a UserForm, I have several ComboBoxes and CheckBoxes on it. If the user chooses "None" in the ComboBox(s), and leaves a CheckBox(s) unchecked I want to populate just one TextBox with that data.
I know how to populate the TextBox with just one piece of data but with multiple data in one TextBox...

I think I'm over thinking it.

chbx means CheckBox
cobx means ComboBox

chbxOne = False (COBRA)
chbxTwo = False (Debit Card)
chbxThree = True (FLEX)

cobxOne = None (401k)
cobxTwo = Termed (EMS)
cobxThree = None (TLM)

From the above TextBox1 would look like this in order:

COBRA, FLEX, 401k, TLM

Once again thanks for your time and help.

Mike

[Tinbendr]

You just concatenate them together.

If Not ChbxOne Then Textbox1.value = "Cobra"
if ChBxThree then Textbox1.value = Textbox1.value & ", FLEX"
If cmbxOne.ListIndex = -1 then Textbox1.value = Textbox1.value & ", 401K"
If cmbxTHree.ListIndex = -1 then Textbox1.value = Textbox1.value & ", TLM"

[mikerickson]

Is this a typo in the test data?

chbxOne = False (COBRA)
chbxTwo = False (Debit Card)
chbxThree = True (FLEX)

cobxOne = None (401k)
cobxTwo = Termed (EMS)
cobxThree = None (TLM)

From the above TextBox1 would look like this in order:

COBRA, FLEX, 401k, TLM
If it is a typo and the desired result is "COBRA, Debit Card, 401k, TLM", you could use code like this in the userform's code module

Private Sub UserForm_Initialize()

Dim arrControls As Variant, matchingTags As Variant
Dim i As Long

arrControls = Array(ComboBox1, ComboBox2, ComboBox3, CheckBox1, CheckBox2, CheckBox3)
matchingTags = Array("401k", "EMS", "TLM", "COBRA", "Debit Card", "FLEX")

For i = LBound(matchingTags) To UBound(matchingTags)
With arrControls(i)
.Tag = matchingTags(i)
'If TypeName(.Object) = "IMdcCheckBox" Then .Caption = .Tag
End With
Next i
End Sub

Private Sub CommandButton1_Click()
Dim oneBox As Variant
Dim Delimiter As String: Delimiter = ", "
Dim resString As String

For Each oneBox In For Each oneBox In Array(CheckBox1, CheckBox2, CheckBox3, ComboBox1, ComboBox2, ComboBox3)
Select Case TypeName(oneBox)
Case "ComboBox"
With oneBox
If .ListIndex = -1 Or LCase(.Text) = "none" Then
resString = resString & Delimiter & .Tag
End If
End With
Case "CheckBox"
With oneBox
If Not (.Value) Then resString = resString & Delimiter & .Tag
End With

End Select
Next oneBox
resString = Mid(resString, Len(Delimiter) + 1)
TextBox1.Text = resString
End SubAlternatly, the .Tags could be set at design time rather than in the Initialize event.
For checkboxes, one could use the .Caption in addition to .Tag.

[msquared99]

Hi mikerickson,

I used your code but get a "Complie Error: Variable Not Defined".

I'm in Excel 2003.

I had to change the line the:

For Each oneBox In For Each oneBox In Array

to

For Each oneBox In Array

Could be the issue as well.

Excel 2003 did not like it.

The compile error is on the line:

TextBox1.Text = resString

I tried to change it to oneBox but it did not like that either.

Here is the code that I have:

Private Sub cmdTalking_click()
Dim oneBox As Variant
Dim Delimiter As String: Delimiter = ", "
Dim resString As String

'Fill ComboBox and CheckBox data based on selection, if ComboBox = None and CheckBox = False
For Each oneBox In Array(CbxPayroll, CbxCOBRA, CbxFSA, CbxEMS, CbxTLM, CbxBenexx, cbTime, cbCashCards, cbESS, cbHartford, cbSEC125, cbATEST, cbHRSC, cb401k, cbFSADebit, cbRPS)
Select Case TypeName(oneBox)
Case "ComboBox"
With oneBox
If .ListIndex = -1 Or LCase(.Text) = "none" Then
resString = resString & Delimiter & .Tag
End If
End With
Case "CheckBox"
With oneBox
If Not (.Value) Then resString = resString & Delimiter & .Tag
End With

End Select
Next oneBox
resString = Mid(resString, Len(Delimiter) + 1)
TextBox1.Text = resString
End Sub

[mikerickson]

Do you have a control named TextBox1?

[msquared99]

No.

I've attached the spreadsheet I'm working with.

I'm trying to learn this stuff as I go along. Some I get pretty fast, others well..

Thanks for your help...

[Bob Phillips]

Do you have a control called OneBox?

[msquared99]

I Fixed it! All I needed to do was change TextBox1 to the destination I wanted the data to do into. I changed TextBox1 to tbSelling.

The only thing now is I get several commas.

Man I cannot believe that was that easy.

Like I said I'm learing on the fly.

[Bob Phillips]

Mike told you that over an hour and a half ago.

[msquared99]

Maybe I spoke too soon, it is not pulling the ComboBox selection if the ComboBox = None.

[Bob Phillips]

Then you need to test the CB value for None and act appropriately.

[mikerickson]

A series of commas and the combo box not responding when None is selected suggest that you might have some trailing spaces.

[msquared99]

Hi Mike,

I'm wondering why the ComboBox answer will pull into tbSelling? I spent a good part of the day trying to solve but kept running into issues.

Here is how I populated the ComboBoxes in the UserForm Initialize event:
With CbxPayroll
.AddItem "Won"
.AddItem "Termed"
.AddItem "Lost Opportunity"
.AddItem "None"
End With

Then the code you gave me is in the same event:

Dim arrControls As Variant, matchingTags As Variant
Dim i As Long

arrControls = Array(CbxPayroll, CbxCOBRA, CbxFSA, CbxEMS, CbxTLM, CbxBenexx, cbTime, cbCashCards, cbESS, cbHartford, cbSEC125, cbATEST, cbHRSC, cb401k, cbFSADebit, cbRPS)
matchingTags = Array("COBRA", "FSA", "EMS", "Time Import", "Cash Cards", "ESS", "Hartford", "SEC125", "ATEST Client", "HRSC", "401k", "FLEX Debit Card", "RPS Client")

For i = LBound(matchingTags) To UBound(matchingTags)
With arrControls(i)
.Tag = matchingTags(i)
'If TypeName(.Object) = "IMdcCheckBox" Then .Caption = .Tag
End With
Next i
'End New Code 8-13

End Sub

I then added this code which is what you gave me previously:

Private Sub cmdTalking_click()

Dim oneBox As Variant
Dim Delimiter As String: Delimiter = ", "
Dim resString As String
'Dim myList As String

'myList = "None" 'Added this

'Fill ComboBox and CheckBox data based on selection, ComboBox = None then populate tbSelling and if CheckBox = False populate tbSelling
For Each oneBox In Array(CbxPayroll, CbxCOBRA, CbxFSA, CbxEMS, CbxTLM, CbxBenexx, cbTime, cbCashCards, cbESS, cbHartford, cbSEC125, cbATEST, cbHRSC, cb401k, cbFSADebit, cbRPS)
Select Case TypeName(oneBox)
Case "ComboBox"
With oneBox
'.List = myList 'Added this
If .ListIndex = -1 Or LCase(.Text) = "None" Then
resString = resString & Delimiter & .Tag
End If
End With
Case "CheckBox"
With oneBox
If Not (.Value) Then resString = resString & Delimiter & .Tag
End With

End Select
Next oneBox
resString = Mid(resString, Len(Delimiter) + 1)
tbSelling.Text = resString
End Sub

Thanks,

Mike

[msquared99]

I am thinking I need to add this to the cmdTalking section:

Dim x As Long

x = Forms(frmSales).Controls(oneBox).ListIndex

Am I getting close?

Mike

[Bob Phillips]

You have CbxPayroll in the controls array, so that is why it is being loaded in.

[msquared99]

I meant to say, I wonder why the ComboBox data IS NOT pulling into tbSelling.

[Bob Phillips]

Post your latest workbook, there have been too many changes/suggestions to keep track.

[msquared99]

Ok, I've attached it.

If you would not mind tell me about the change so I can learn.

This is the first UserForm I've ever built, so its been a real learning process.

Thanks for all the help!

Mike

[Bob Phillips]

There is something very odd going on, or I am missing something obvious, but when the code processes the tag returned for CBxpayroll is COBRA, for CbxCobra it is FSA and so on.

[msquared99]

Atleast you are seeing something. If I choose "None" for COBRA, FSA, EMS, TLM and Benexx I see nothing. That is under "Core Services" on the Pre-Work page.

If I leave any of the checkboxes unchecked under "Ancillary Services", those do show up in the textbox tbSelling (Services to talk about) on the Talking Points page.

On the comboboxes I did go in to the properties and add the name to the tag.

[Bob Phillips]

I am not sure what you expect to see if you select None, but I did notice that you had the following code

If .ListIndex = -1 Or LCase(.Text) = "None" Then

which will never pass as even if you select the None option, LCase(.Text) will be "none" and you are comparing against "None". Cannot pass.

[msquared99]

xld, you gave me a clue. I changed the code, if the ComboBox = "None" it places the Tag of the ComboBox in tbSelling but it is the Tag below the ComboBox.

Payroll = Won
COBRA = None
FSA = Won

It places FSA in tbSelling instead of COBRA. Why?

Here is the code:
Private Sub cmdTalking_click()

Dim oneBox As Variant
Dim Delimiter As String: Delimiter = ", "
Dim resString As String

'Fill ComboBox and CheckBox data based on selection, ComboBox = None then populate tbSelling and if cbFSADebit = False populate tbSelling
For Each oneBox In Array(CbxPayroll, CbxCOBRA, CbxFSA, CbxEMS, CbxTLM, CbxBenexx)
Select Case TypeName(oneBox)
Case "ComboBox"
With oneBox
If oneBox.Value = "None" Then resString = resString & Delimiter & .Tag
End With

End Select

Next oneBox
resString = Mid(resString, Len(Delimiter) - 1)
tbSelling.Text = resString
'If cbFSADebit = False Then resString = resString & Delimiter & "Debit Card"

End Sub

[msquared99]

Much JOY! I have finally solved it! It may not be pretty but it does what I need it to do.

Thanks all for your help!

How do I mark this thread solved?

Code:
Private Sub cmdTalking_click()

Dim Delimiter As String: Delimiter = ", "
Dim resString As String

'Fill ComboBox and CheckBox data based on selection, ComboBox = None then populate tbSelling and if CheckBox = False populate tbSelling
'CbxCOBRA , CbxFSA, CbxEMS, CbxTLM, CbxBenexx, cbFSADebit
Select Case CbxCOBRA.Value
Case "None"
resString = resString & Delimiter & "COBRA"
End Select

Select Case CbxFSA.Value
Case "None"
resString = resString & Delimiter & "FSA"
End Select

Select Case CbxEMS.Value
Case "None"
resString = resString & Delimiter & "EMS"
End Select

Select Case CbxTLM.Value
Case "None"
resString = resString & Delimiter & "TLM"
End Select

Select Case CbxBenexx.Value
Case "None"
resString = resString & Delimiter & "Benexx"
End Select

Select Case cbFSADebit.Value
Case "False"
resString = resString & Delimiter & "Debit Card"
End Select

resString = Mid(resString, Len(Delimiter) + 1)
tbSelling.Text = resString
End Sub

[Bob Phillips]

That was exactly what I was getting and I have no idea why (my guess it is something about using an array of controls, but why that should be is beyond me).

I see you cracked t with a hammer :)

[msquared99]

One of the things I used was the immediate window. Then started changing the values from "None" to "Won" etc. But the real clue was when I selected nothing of Payroll, COBRA ect and the tbSelling populated.

I found a great website that explained listindex, case, etc, then read your posts and BAM!

Anyway, thanks for everyones help!

Mike