UserForm labels to display text dependant upon Cell Values

chippymark

Board Regular
Joined
Aug 2, 2007
Messages
50
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..
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

yytsunamiyy

Well-known Member
Joined
Mar 17, 2008
Messages
963
You could simply loop through Column C to build the string for the label like this:

Code:
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

Board Regular
Joined
Aug 2, 2007
Messages
50
Thanks for the quick response, Looks exactly what I was looking for, I will try it out shortly and let you know.

Cheers :eek:)
 

chippymark

Board Regular
Joined
Aug 2, 2007
Messages
50
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:

Code:
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

Board Regular
Joined
Aug 2, 2007
Messages
50
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

Board Regular
Joined
Aug 2, 2007
Messages
50
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,407
Messages
5,571,924
Members
412,426
Latest member
DeficientOptimism
Top