UserForm labels to display text dependant upon Cell Values

[chippymark]

Hi, I will try and simplify my question.. I have a userform from which a user selects different option buttons etc which will then decipher a route for production.

However I am struggling to show the user the selected route within the userform without showing all the information.

Let say the calculations show

Cell B2 = Cut Material C2 = Yes
Cell B3 = Rough Material C3 = No
Cell B4 = Stand 24 hrs C4 = Yes
Cell B5 = Send to Sub Con C5 = No
Cell B6 = Inspect C6 = Yes
Etc......

I Would like is the UserForm to only display the text in C if the B contains Yes

so in the above example i'd like the UserFom to show

Cut Material
Stand 24hrs
Inspect

Would this be done as Labels or text boxes.

Any help would be appreciated (please note I am relatively new to UserForms)

Thanks
Mark..

 

[yytsunamiyy]

You could simply loop through Column C to build the string for the label like this:

Sub foo()
Dim lbltext As String
Dim i As Integer
[COLOR=green]'Loop through your cells in Colum C[/COLOR]
For i = 2 To 6
[COLOR=green]   'If C contains a "YES" add the value in B and a linebreak to the labeltext[/COLOR]
    If Cells(i, "C").Value = "YES" Then lbltext = lbltext & Cells(i, "B").Value & vbCr
Next i
[COLOR=green]'If at least one line with "Yes" was found set the label text, Otherwise give error message[/COLOR]
If lbltxt <> "" Then
    userform.label1.Text = lbltext
Else
    MsgBox "Please select at least one value"
End If
End Sub

 

[chippymark]

Thanks for the quick response, Looks exactly what I was looking for, I will try it out shortly and let you know.

Cheers )

 

[chippymark]

Hi again,
I have assigned this code to a Button so when the user clicks it adds the text in 'B' to the Label field.
However even though I have several 'YES' results in column 'C' I always get the MsgBox "Please select at least one value"

ANy Ideas what I am doin wrong, I have duplicated your code.

You could simply loop through Column C to build the string for the label like this:

Sub foo()
Dim lbltext As String
Dim i As Integer
[COLOR=green]'Loop through your cells in Colum C[/COLOR]
For i = 2 To 6
[COLOR=green]   'If C contains a "YES" add the value in B and a linebreak to the labeltext[/COLOR]
    If Cells(i, "C").Value = "YES" Then lbltext = lbltext & Cells(i, "B").Value & vbCr
Next i
[COLOR=green]'If at least one line with "Yes" was found set the label text, Otherwise give error message[/COLOR]
If lbltxt <> "" Then
    userform.label1.Text = lbltext
Else
    MsgBox "Please select at least one value"
End If
End Sub

 

[chippymark]

Ok... My first stupid fault was not changing the Userform Name accordingly. But now It stops at the .text in the following line and says Compile error..

Can anyone please help !

UserForm1.Label1.Text = lbltext

 

[chippymark]

SORTED !!! Yeah... Obviously I need to tweak and incorperate other code but here is the code that Worked

Private Sub CommandButton1_Click()
Dim i As Integer
Label1.Caption = ""
'Loop through your cells in Colum C
For i = 1 To 11
'If C contains a "YES" add the value in B and a linebreak to the label1 caption
If Range("C" & i).Value = "YES" Then Label1.Caption = Label1.Caption & Range("B" & i) & vbCr
Next i
'If no Results then display error message
If Label1.Caption = "" Then
MsgBox "No Routing Available"
End If
End Sub