Userform question about IF w/outputs on multiple lines

**Sibrulotte** · 03-22-2012, 01:00 PM

I'm trying to get a form going for our tennis rankings.

Check the file, my code for checking if a player played and keep his score, and opponants score is long and flawed:

Private Sub CommandButton1_Click()

Dim emptyRow As Long

Sheets("Résultats").Activate
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

If Pointsform.Boxandre1.Value = True Then
    Cells(emptyRow, 1).Value = Calendar1.Value
    Cells(emptyRow, 2).Value = "André"
    Cells(emptyRow, 3).Value = Score1.Value
    Cells(emptyRow, 4).Value = Score2.Value
    
    Else
        
    End If

If Pointsform.boxsimon1.Value = True Then
    Cells(emptyRow, 1).Value = Calendar1.Value
    Cells(emptyRow, 2).Value = "Simon"
    Cells(emptyRow, 3).Value = Score1.Value
    Cells(emptyRow, 4).Value = Score2.Value
    
    Else
        
    End If


If Pointsform.boxpascal1.Value = True Then
    Cells(emptyRow, 1).Value = Calendar1.Value
    Cells(emptyRow, 2).Value = "Pascal"
    Cells(emptyRow, 3).Value = Score1.Value
    Cells(emptyRow, 4).Value = Score2.Value
    
    Else
        
    End If

If Pointsform.boxsylvain1.Value = True Then
    Cells(emptyRow, 1).Value = Calendar1.Value
    Cells(emptyRow, 2).Value = "Sylvain"
    Cells(emptyRow, 3).Value = Score1.Value
    Cells(emptyRow, 4).Value = Score2.Value
    
    Else
        
    End If

If Pointsform.boxthierry1.Value = True Then
    Cells(emptyRow, 1).Value = Calendar1.Value
    Cells(emptyRow, 2).Value = "Thierry"
    Cells(emptyRow, 3).Value = Score1.Value
    Cells(emptyRow, 4).Value = Score2.Value
    
    Else
        
    End If
    
If Pointsform.boxyve1.Value = True Then
    Cells(emptyRow, 1).Value = Calendar1.Value
    Cells(emptyRow, 2).Value = "Yvé"
    Cells(emptyRow, 3).Value = Score1.Value
    Cells(emptyRow, 4).Value = Score2.Value
    
    Else
        
    End If

If Pointsform.Boxandre2.Value = True Then
    Cells(emptyRow, 1).Value = Calendar1.Value
    Cells(emptyRow, 2).Value = "André"
    Cells(emptyRow, 3).Value = Score2.Value
    Cells(emptyRow, 4).Value = Score1.Value
    
    Else
        
    End If


If Pointsform.boxsimon2.Value = True Then
    Cells(emptyRow, 1).Value = Calendar1.Value
    Cells(emptyRow, 2).Value = "Simon"
    Cells(emptyRow, 3).Value = Score2.Value
    Cells(emptyRow, 4).Value = Score1.Value
    
    Else
        
    End If

If Pointsform.boxpascal2.Value = True Then
    Cells(emptyRow, 1).Value = Calendar1.Value
    Cells(emptyRow, 2).Value = "Pascal"
    Cells(emptyRow, 3).Value = Score2.Value
    Cells(emptyRow, 4).Value = Score1.Value
    
    Else
        
    End If

If Pointsform.boxsylvain2.Value = True Then
    Cells(emptyRow, 1).Value = Calendar1.Value
    Cells(emptyRow, 2).Value = "Sylvain"
    Cells(emptyRow, 3).Value = Score2.Value
    Cells(emptyRow, 4).Value = Score1.Value
    
    Else
        
    End If
    
If Pointsform.boxthierry2.Value = True Then
    Cells(emptyRow, 1).Value = Calendar1.Value
    Cells(emptyRow, 2).Value = "Thierry"
    Cells(emptyRow, 3).Value = Score2.Value
    Cells(emptyRow, 4).Value = Score1.Value
    
    Else
        
    End If

If Pointsform.boxyve2.Value = True Then
    Cells(emptyRow, 1).Value = Calendar1.Value
    Cells(emptyRow, 2).Value = "Yvé"
    Cells(emptyRow, 3).Value = Score2.Value
    Cells(emptyRow, 4).Value = Score1.Value
    
    Else
        
    End If

Score1.Value = ""
Score2.Value = ""

Boxandre1.Value = False
boxsimon1.Value = False
boxpascal1.Value = False
boxsylvain1.Value = False
boxthierry1.Value = False
boxyve1.Value = False
Boxandre2.Value = False
boxsimon2.Value = False
boxpascal2.Value = False
boxsylvain2.Value = False
boxthierry2.Value = False
boxyve2.Value = False

Unload Me


End Sub

Only the last guy ticked gets his name on the "Résultats" sheets.
can someone help me simplify?

**Special-K** · 03-22-2012, 01:11 PM

Not a solution to the problem but can't you shorten this to:


Cells(emptyRow, 1).Value = Calendar1.Value
Cells(emptyRow, 3).Value = Score1.Value
Cells(emptyRow, 4).Value = Score2.Value


If Pointsform.Boxandre1.Value = True Then
    Cells(emptyRow, 2).Value = "André"
    
    Else
        
    End If

If Pointsform.boxsimon1.Value = True Then
    Cells(emptyRow, 2).Value = "Simon"
    
    Else
        
    End If


If Pointsform.boxpascal1.Value = True Then
    Cells(emptyRow, 2).Value = "Pascal"
    
    Else
        
    End If

If Pointsform.boxsylvain1.Value = True Then
    Cells(emptyRow, 2).Value = "Sylvain"
    
    Else
        
    End If

If Pointsform.boxthierry1.Value = True Then
    Cells(emptyRow, 2).Value = "Thierry"
    
    Else
        
    End If
    
If Pointsform.boxyve1.Value = True Then
    Cells(emptyRow, 2).Value = "Yvé"
    
    Else
        
    End If

If Pointsform.Boxandre2.Value = True Then
    Cells(emptyRow, 2).Value = "André"
    
    Else
        
    End If

If Pointsform.boxsimon2.Value = True Then
    Cells(emptyRow, 2).Value = "Simon"
    
    Else
        
    End If

If Pointsform.boxpascal2.Value = True Then
    Cells(emptyRow, 2).Value = "Pascal"
    
    Else
        
    End If

If Pointsform.boxsylvain2.Value = True Then
    Cells(emptyRow, 2).Value = "Sylvain"
    
    Else
        
    End If
    
If Pointsform.boxthierry2.Value = True Then
    Cells(emptyRow, 2).Value = "Thierry"
    
    Else
        
    End If

If Pointsform.boxyve2.Value = True Then
    Cells(emptyRow, 2).Value = "Yvé"
    
    Else
        
    End If

**Special-K** · 03-22-2012, 01:16 PM

Does this work?

Cells(emptyRow, 1).Value = Calendar1.Value
Cells(emptyRow, 3).Value = Score1.Value
Cells(emptyRow, 4).Value = Score2.Value


If Pointsform.Boxandre1.Value = True Then
    Cells(emptyRow, 2).Value = "André"
    
    Else
        
If Pointsform.boxsimon1.Value = True Then
    Cells(emptyRow, 2).Value = "Simon"
    
    Else
        
If Pointsform.boxpascal1.Value = True Then
    Cells(emptyRow, 2).Value = "Pascal"
    
    Else
        
If Pointsform.boxsylvain1.Value = True Then
    Cells(emptyRow, 2).Value = "Sylvain"
    
    Else
        
If Pointsform.boxthierry1.Value = True Then
    Cells(emptyRow, 2).Value = "Thierry"
    
    Else

If Pointsform.boxyve1.Value = True Then
    Cells(emptyRow, 2).Value = "Yvé"
    
    Else
        
If Pointsform.Boxandre2.Value = True Then
    Cells(emptyRow, 2).Value = "André"
    
    Else
        
If Pointsform.boxsimon2.Value = True Then
    Cells(emptyRow, 2).Value = "Simon"
    
    Else
        
If Pointsform.boxpascal2.Value = True Then
    Cells(emptyRow, 2).Value = "Pascal"
    
    Else
        
If Pointsform.boxsylvain2.Value = True Then
    Cells(emptyRow, 2).Value = "Sylvain"
    
    Else
        
If Pointsform.boxthierry2.Value = True Then
    Cells(emptyRow, 2).Value = "Thierry"
    
    Else
        
If Pointsform.boxyve2.Value = True Then
    Cells(emptyRow, 2).Value = "Yvé"
    
    Else

End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If

**Sibrulotte** · 03-22-2012, 01:41 PM

Only the first guy checked comes out on the list.
All players that we're checked need to get listed.

**Sibrulotte** · 03-22-2012, 02:53 PM

Any takers before it gets thrown to page 2?

**Leith Ross** · 03-22-2012, 03:20 PM

Hello Sibrulotte ,

What type of control is "Pointsform.Boxandre1" ?

**Sibrulotte** · 03-22-2012, 03:28 PM

It's a box that states if a player was present.
I list all possible players (I could use 2 comboboxs instead of ticking who was present).

So team 1 was comprised of : check box of players present
team 2 was comprised of: check box of player present (boxandre2, and so forth)

In sheets "résultats" I'll have points for and points against ("score2" if player was in team 1, or "Score1" if player was in team 2).

**Leith Ross** · 03-22-2012, 03:35 PM

Hello Sibrulotte,

Try this out and let me know if you need any changes.


Private Sub CommandButton1_Click()

    Dim Ctrl As Object
    Dim emptyRow As Long
    Dim Rng As Range
    Dim RngEnd As Range
    Dim Wks As Worksheet

        Set Wks = Sheets("Résultats")
        Set Rng = Wks.Range("A1")
        
            Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
            If RngEnd.Row < Rng.Row Then Exit Sub Else Set Rng = RngEnd.Offset(1, 0).Resize(1, 4)
            
            Rng.Cells(1, 1).Value = Calendar1.Value
            Rng.Cells(1, 3).Value = Score1.Value
            Rng.Cells(1, 4).Value = Score2.Value
            
              For Each Ctrl In Me.Controls
                If TypeName(Ctrl) = "CheckBox" Then
                   Rng.Cells(1, 2) = Mid(Ctrl.Name, 4, Len(Ctrl.Name) - 4)
                   Set Rng = Rng.Offset(1, 0)
                   Ctrl.Value = False
                End If
              Next Ctrl

        Score1.Value = ""
        Score2.Value = ""

        Unload Me

End Sub

**Sibrulotte** · 03-22-2012, 03:53 PM

Good look on this:

    Rng.Cells(1, 2) = Mid(Ctrl.Name, 4, Len(Ctrl.Name) - 4)

to get names without typing.

But the whole coding as suggested returns on 1 row:
the calendar date, the first name on the list, score1, score2
And then all the following rows have somebody's name in colomne B.

So not a success right now.

**Leith Ross** · 03-22-2012, 03:57 PM

Hello Sibrulotte,

That's why is nice to have the workbook whenever possible. Try this change. This should be more what you are looking for.


Private Sub CommandButton1_Click()

    Dim Ctrl As Object
    Dim emptyRow As Long
    Dim Rng As Range
    Dim RngEnd As Range
    Dim Wks As Worksheet

        Set Wks = Sheets("Résultats")
        Set Rng = Wks.Range("A1")
        
            Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
            If RngEnd.Row < Rng.Row Then Exit Sub Else Set Rng = RngEnd.Offset(1, 0).Resize(1, 4)
            
              For Each Ctrl In Me.Controls
                If TypeName(Ctrl) = "CheckBox" Then
                    Rng.Cells(1, 1).Value = Calendar1.Value
                    Rng.Cells(1, 2) = Mid(Ctrl.Name, 3, Len(Ctrl.Name) - 4)
                    Rng.Cells(1, 3).Value = Score1.Value
                    Rng.Cells(1, 4).Value = Score2.Value
                    Set Rng = Rng.Offset(1, 0)
                    Ctrl.Value = False
                End If
              Next Ctrl

        Score1.Value = ""
        Score2.Value = ""

        Unload Me

End Sub

**Sibrulotte** · 03-22-2012, 04:22 PM

We're getting there.
look at the first post, it contains the xlsm file.

Last post you did brought back:
calendar1, "xandr", Score1, Score2
calendar1, "xsimo", Score1, Score2

And so forth for all checkboxes.

I'm only strggling with empty rows here. I've tried with comboboxes instead and it's the same thing:

My last if statement looks for an empty row, but it's as if the if statement before had not populated the last empty row, so the whole sub sees only 1 emptyrow, the first one that was available when the first if statement started.

**Leith Ross** · 03-22-2012, 05:59 PM

Hello Sibrulotte,

I made some changes to the UserForm. I removed the Labels and added the names to each CheckBox. Here is the current code for the button. The attached workbook has all the changes made.

Private Sub CommandButton1_Click()

    Dim Ctrl As Object
    Dim Rng As Range
    Dim RngEnd As Range
    Dim Wks As Worksheet

        Set Wks = Sheets("Résultats")
        Set Rng = Wks.Range("A2")
        
            Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
            If RngEnd.Row < Rng.Row Then Exit Sub Else Set Rng = RngEnd.Offset(1, 0).Resize(1, 4)
            
            Addx = Rng.Address
            
              For Each Ctrl In Me.Controls
                If TypeName(Ctrl) = "CheckBox" Then
                    If Ctrl.Value = True Then
                        Rng.Cells(1, 1).Value = Calendar1.Value
                        Rng.Cells(1, 2) = Ctrl.Caption
                        Rng.Cells(1, 3).Value = Score1.Value
                        Rng.Cells(1, 4).Value = Score2.Value
                        Set Rng = Rng.Offset(1, 0)
                        Ctrl.Value = False
                    End If
                End If
              Next Ctrl

        Score1.Value = ""
        Score2.Value = ""

        Unload Me

End Sub

**Sibrulotte** · 03-23-2012, 08:43 AM

Wow that does great. I just need to add an if to set team2's "opponent" score, or the score agains to be score1, and their core to be score2.

Now team 2 has the same score as team1.

Thanks a bun. I'll try to drill through to understand the logic behind the coding. very usefull

**Leith Ross** · 03-23-2012, 11:28 AM

Hello Sibrulotte,

I noticed that yesterday but didn't say anything. It is easier to handle the problems one at a time. We still have to make the the players name selection exclusive. Do you want me to work on that while you work on the scores or should I do both?

**Sibrulotte** · 03-23-2012, 11:39 AM

I tried to add an if Name (Ctrl) = "*1"
to check for theam 1.
that doesn't work it seems.

Dim Ctrl As Object
    Dim Rng As Range
    Dim RngEnd As Range
    Dim Wks As Worksheet

        Set Wks = Sheets("Résultats")
        Set Rng = Wks.Range("A1")
        
            Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
            If RngEnd.Row < Rng.Row Then Exit Sub Else Set Rng = RngEnd.Offset(1, 0).Resize(1, 4)
            
            Addx = Rng.Address
            
              For Each Ctrl In Me.Controls
                If TypeName(Ctrl) = "CheckBox" Then
                   If Name(Ctrl) = "*1" Then
                    If Ctrl.Value = True Then
                        Rng.Cells(1, 1).Value = Calendar1.Value
                        Rng.Cells(1, 2) = Ctrl.Caption
                        Rng.Cells(1, 3).Value = Score1.Value
                        Rng.Cells(1, 4).Value = Score2.Value
                        Set Rng = Rng.Offset(1, 0)
                        Ctrl.Value = False
                        End If
                    End If
                End If
                
              Next Ctrl
              
              

        Score1.Value = ""
        Score2.Value = ""

        Unload Me

It said number of arguments incorrect or invalid property.
(it's in french, I'm not 100% sure how to translate)

**Sibrulotte** · 03-23-2012, 11:44 AM

Everytime I come on here, I get the most amazing help. Remember, this is tennis scores. I'm on work hours, and I shouldn't even be doing this.

it's good knowledge to apply afterwards, but it's still tennis scores...

**Sibrulotte** · 03-23-2012, 02:47 PM

I'm still stuck. I don'T know what object to use to check for the checkbox name having a 1 or a 2 in it.

**Pichingualas** · 03-23-2012, 03:12 PM

Do you mean the name that the checkbox displays on your userform? If so, try with:

If Checkbox1.Caption Like "*1" Then
    'What you want it to do in this case
ElseIf Checkbox1.Caption Like "*2" Then
    'What you want it to do in this other case
End If

If you have more than one checkbox change the 1 (in red) accordingly. I hope that works.

**Leith Ross** · 03-23-2012, 03:57 PM

Hello Sibrulotte,

I ditched the Check Boxes and replaced them all with 2 List Boxes. These are linked to a new sheet "Joueurs" with a dynamic Named Range "Nom". This shortens the code and allows you flexibility when adding or removing names. Here is the User Form code for the new version of the workbook.


Private Sub cancel_Click()
    Unload Me
End Sub

Private Sub CommandButton1_Click()

    Dim Ctrl As Object
    Dim Rng As Range
    Dim RngEnd As Range
    Dim Wks As Worksheet

        Set Wks = Sheets("Résultats")
        Set Rng = Wks.Range("A2")
        
            Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp)
            If RngEnd.Row < Rng.Row Then Exit Sub Else Set Rng = RngEnd.Offset(1, 0).Resize(1, 4)
            
            Rng.Cells(1, 1).Value = Calendar1.Value
            Rng.Cells(1, 2) = ListBox1.Value
            Rng.Cells(1, 3).Value = Score1.Value
            Rng.Cells(1, 4).Value = ""
            
            Set Rng = Rng.Offset(1, 0)
            Rng.Cells(1, 1).Value = Calendar1.Value
            Rng.Cells(1, 2) = ListBox2.Value
            Rng.Cells(1, 3).Value = ""
            Rng.Cells(1, 4).Value = Score2.Value

        Score1.Value = ""
        Score2.Value = ""

        Unload Me

End Sub

Private Sub ListBox1_Click()
    Dim I As Long
    
        I = ListBox2.ListIndex
        If ListBox2.ListIndex > -1 Then
            If I = ListBox1.ListIndex Then ListBox2.Selected(I) = False
        End If
End Sub

Private Sub ListBox2_Click()
    Dim I As Long
    
        I = ListBox1.ListIndex
        If ListBox1.ListIndex > -1 Then
            If I = ListBox2.ListIndex Then ListBox1.Selected(I) = False
        End If
End Sub

**Sibrulotte** · 03-26-2012, 11:22 AM

Only one player can partake in the score, we play doubles

Ah, I'll probably just keep the version where I select lastrow, and do +1, +2, +3 to combobox, even if this leaves blanks, I'll live with it, we almost never play singles anyways.

Userform question about IF w/outputs on multiple lines

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Userform question about IF w/outputs on multiple lines

Re: We play tennis- I keep score (Userform question about IF)

Re: We play tennis- I keep score (Userform question about IF)

Re: We play tennis- I keep score (Userform question about IF)

Re: We play tennis- I keep score (Userform question about IF)

Re: We play tennis- I keep score (Userform question about IF)

Re: We play tennis- I keep score (Userform question about IF)

Re: We play tennis- I keep score (Userform question about IF)

Re: We play tennis- I keep score (Userform question about IF)

Re: We play tennis- I keep score (Userform question about IF)

Re: We play tennis- I keep score (Userform question about IF)

Re: We play tennis- I keep score (Userform question about IF)

Re: Userform question about IF

Re: Userform question about IF

Re: Userform question about IF

Re: Userform question about IF

Re: Userform question about IF w/outputs on multiple lines

Re: Userform question about IF w/outputs on multiple lines

Re: Userform question about IF w/outputs on multiple lines

Re: Userform question about IF w/outputs on multiple lines

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions