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..
 

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
 

Forum statistics

Threads
1,081,695
Messages
5,360,679
Members
400,592
Latest member
katekoz

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top