Results 1 to 2 of 2

If multiple comboboxes not used then stop populating cells at last combobox input

Threaded View

  1. 10-28-2013, 09:33 AM #1
    JacobBlue
    JacobBlue is offline
    Registered User
    Join Date
    08-05-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    3

    If multiple comboboxes not used then stop populating cells at last combobox input

    Hi, I am new to this and this is probably very simple but I am trying to setup a userform with multiple combo boxes and text boxes. I have the form setup and it is working fine except for one thing.

    The form has a league combobox and if you pick the league it then goes to Date (input date from calendar). Once the league has been chosen you then go to home or away teams and it will give you a drop down of teams for that league. The scores are also combo boxes with 0-9. This all works fine but if I only want to add in say 4 match results for that week the form populates my sheet with 4 scores but 12 dates.

    e.g.

    28/10/2013 Team A 1 Team B 1
    28/10/2013 Team C 1 Team D 0
    28/10/2013 Team E 3 Team F 2
    28/10/2013 Team G 0 Team H 2
    28/10/2013
    28/10/2013
    28/10/2013
    28/10/2013
    28/10/2013
    28/10/2013
    28/10/2013
    28/10/2013

    My question is how can I add in say just 4 match results and only populate the spreadsheet with those 4 scores so it stops adding in the additional 8 lines with just the dates. Code for this form is below.

    Any help would be greatly appreciated.
    -----------------------------------------------------------------
    Private Sub CmdAdd_Click()
Dim FirstBlankCell As Range
Set FirstBlankCell = Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
FirstBlankCell.Activate
    
    ActiveCell.Offset(0, 0).Value = Format(Me.Date1.Value, "short Date") 'set col B
    ActiveCell.Offset(0, 1).Value = Me.HomeTeam1.Text 'set col C
    ActiveCell.Offset(0, 2).Value = Me.HomeScore1.Text 'set col D
    ActiveCell.Offset(0, 3).Value = Me.AwayTeam1.Text 'set col E
    ActiveCell.Offset(0, 4).Value = Me.AwayScore1.Text 'set col F
    ActiveCell.Offset(1, 0).Value = Format(Me.Date1.Value, "short Date") 'set col B
    ActiveCell.Offset(1, 1).Value = Me.HomeTeam2.Text 'set col C
    ActiveCell.Offset(1, 2).Value = Me.HomeScore2.Text 'set col D
    ActiveCell.Offset(1, 3).Value = Me.AwayTeam2.Text 'set col E
    ActiveCell.Offset(1, 4).Value = Me.AwayScore2.Text 'set col F
    ActiveCell.Offset(2, 0).Value = Format(Me.Date1.Value, "short Date") 'set col B
    ActiveCell.Offset(2, 1).Value = Me.HomeTeam3.Text 'set col C
    ActiveCell.Offset(2, 2).Value = Me.HomeScore3.Text 'set col D
    ActiveCell.Offset(2, 3).Value = Me.AwayTeam3.Text 'set col E
    ActiveCell.Offset(2, 4).Value = Me.AwayScore3.Text 'set col F
    ActiveCell.Offset(3, 0).Value = Format(Me.Date1.Value, "short Date") 'set col B
    ActiveCell.Offset(3, 1).Value = Me.HomeTeam4.Text 'set col C
    ActiveCell.Offset(3, 2).Value = Me.HomeScore4.Text 'set col D
    ActiveCell.Offset(3, 3).Value = Me.AwayTeam4.Text 'set col E
    ActiveCell.Offset(3, 4).Value = Me.AwayScore4.Text 'set col F
    ActiveCell.Offset(4, 0).Value = Format(Me.Date1.Value, "short Date") 'set col B
    ActiveCell.Offset(4, 1).Value = Me.HomeTeam5.Text 'set col C
    ActiveCell.Offset(4, 2).Value = Me.HomeScore5.Text 'set col D
    ActiveCell.Offset(4, 3).Value = Me.AwayTeam5.Text 'set col E
    ActiveCell.Offset(4, 4).Value = Me.AwayScore5.Text 'set col F
    ActiveCell.Offset(5, 0).Value = Format(Me.Date1.Value, "short Date") 'set col B
    ActiveCell.Offset(5, 1).Value = Me.HomeTeam6.Text 'set col C
    ActiveCell.Offset(5, 2).Value = Me.HomeScore6.Text 'set col D
    ActiveCell.Offset(5, 3).Value = Me.AwayTeam6.Text 'set col E
    ActiveCell.Offset(5, 4).Value = Me.AwayScore6.Text 'set col F
    ActiveCell.Offset(6, 0).Value = Format(Me.Date1.Value, "short Date") 'set col B
    ActiveCell.Offset(6, 1).Value = Me.HomeTeam7.Text 'set col C
    ActiveCell.Offset(6, 2).Value = Me.HomeScore7.Text 'set col D
    ActiveCell.Offset(6, 3).Value = Me.AwayTeam7.Text 'set col E
    ActiveCell.Offset(6, 4).Value = Me.AwayScore7.Text 'set col F
    ActiveCell.Offset(7, 0).Value = Format(Me.Date1.Value, "short Date") 'set col B
    ActiveCell.Offset(7, 1).Value = Me.HomeTeam8.Text 'set col C
    ActiveCell.Offset(7, 2).Value = Me.HomeScore8.Text 'set col D
    ActiveCell.Offset(7, 3).Value = Me.AwayTeam8.Text 'set col E
    ActiveCell.Offset(7, 4).Value = Me.AwayScore8.Text 'set col F
    ActiveCell.Offset(8, 0).Value = Format(Me.Date1.Value, "short Date") 'set col B
    ActiveCell.Offset(8, 1).Value = Me.HomeTeam9.Text 'set col C
    ActiveCell.Offset(8, 2).Value = Me.HomeScore9.Text 'set col D
    ActiveCell.Offset(8, 3).Value = Me.AwayTeam9.Text 'set col E
    ActiveCell.Offset(8, 4).Value = Me.AwayScore9.Text 'set col F
    ActiveCell.Offset(9, 0).Value = Format(Me.Date1.Value, "short Date") 'set col B
    ActiveCell.Offset(9, 1).Value = Me.HomeTeam10.Text 'set col C
    ActiveCell.Offset(9, 2).Value = Me.HomeScore10.Text 'set col D
    ActiveCell.Offset(9, 3).Value = Me.AwayTeam10.Text 'set col E
    ActiveCell.Offset(9, 4).Value = Me.AwayScore10.Text 'set col F
    ActiveCell.Offset(10, 0).Value = Format(Me.Date1.Value, "short Date") 'set col B
    ActiveCell.Offset(10, 1).Value = Me.HomeTeam11.Text 'set col C
    ActiveCell.Offset(10, 2).Value = Me.HomeScore11.Text 'set col D
    ActiveCell.Offset(10, 3).Value = Me.AwayTeam11.Text 'set col E
    ActiveCell.Offset(10, 4).Value = Me.AwayScore11.Text 'set col F
    ActiveCell.Offset(11, 0).Value = Format(Me.Date1.Value, "short Date") 'set col B
    ActiveCell.Offset(11, 1).Value = Me.HomeTeam12.Text 'set col C
    ActiveCell.Offset(11, 2).Value = Me.HomeScore12.Text 'set col D
    ActiveCell.Offset(11, 3).Value = Me.AwayTeam12.Text 'set col E
    ActiveCell.Offset(11, 4).Value = Me.AwayScore12.Text 'set col F
    
    'Clear down the values ready for the next record entry...
    Me.LeagueName.Value = Empty
    Me.HomeTeam1.Value = Empty
    Me.HomeScore1.Value = Empty
    Me.HomeTeam2.Value = Empty
    Me.HomeScore2.Value = Empty
    Me.HomeTeam3.Value = Empty
    Me.HomeScore3.Value = Empty
    Me.HomeTeam4.Value = Empty
    Me.HomeScore4.Value = Empty
    Me.HomeTeam5.Value = Empty
    Me.HomeScore5.Value = Empty
    Me.HomeTeam6.Value = Empty
    Me.HomeScore6.Value = Empty
    Me.HomeTeam7.Value = Empty
    Me.HomeScore7.Value = Empty
    Me.HomeTeam8.Value = Empty
    Me.HomeScore8.Value = Empty
    Me.HomeTeam9.Value = Empty
    Me.HomeScore9.Value = Empty
    Me.HomeTeam10.Value = Empty
    Me.HomeScore10.Value = Empty
    Me.HomeTeam11.Value = Empty
    Me.HomeScore11.Value = Empty
    Me.HomeTeam12.Value = Empty
    Me.HomeScore12.Value = Empty
    Me.AwayTeam1.Value = Empty
    Me.AwayScore1.Value = Empty
    Me.AwayTeam2.Value = Empty
    Me.AwayScore2.Value = Empty
    Me.AwayTeam3.Value = Empty
    Me.AwayScore3.Value = Empty
    Me.AwayTeam4.Value = Empty
    Me.AwayScore4.Value = Empty
    Me.AwayTeam5.Value = Empty
    Me.AwayScore5.Value = Empty
    Me.AwayTeam6.Value = Empty
    Me.AwayScore6.Value = Empty
    Me.AwayTeam7.Value = Empty
    Me.AwayScore7.Value = Empty
    Me.AwayTeam8.Value = Empty
    Me.AwayScore8.Value = Empty
    Me.AwayTeam9.Value = Empty
    Me.AwayScore9.Value = Empty
    Me.AwayTeam10.Value = Empty
    Me.AwayScore10.Value = Empty
    Me.AwayTeam11.Value = Empty
    Me.AwayScore11.Value = Empty
    Me.AwayTeam12.Value = Empty
    Me.AwayScore12.Value = Empty

    Me.LeagueName.SetFocus 'positions the cursor for next record entry

End Sub
    Private Sub cmdClose_Click()
    'close the form (itself)
    Unload Me

End Sub
Private Sub LeagueName_Change()
Me.HomeTeam1.Value = ""
Me.HomeTeam1.RowSource = Me.LeagueName.Value
Me.HomeTeam2.Value = ""
Me.HomeTeam2.RowSource = Me.LeagueName.Value
Me.HomeTeam3.Value = ""
Me.HomeTeam3.RowSource = Me.LeagueName.Value
Me.HomeTeam4.Value = ""
Me.HomeTeam4.RowSource = Me.LeagueName.Value
Me.HomeTeam5.Value = ""
Me.HomeTeam5.RowSource = Me.LeagueName.Value
Me.HomeTeam6.Value = ""
Me.HomeTeam6.RowSource = Me.LeagueName.Value
Me.HomeTeam7.Value = ""
Me.HomeTeam7.RowSource = Me.LeagueName.Value
Me.HomeTeam8.Value = ""
Me.HomeTeam8.RowSource = Me.LeagueName.Value
Me.HomeTeam9.Value = ""
Me.HomeTeam9.RowSource = Me.LeagueName.Value
Me.HomeTeam10.Value = ""
Me.HomeTeam10.RowSource = Me.LeagueName.Value
Me.HomeTeam11.Value = ""
Me.HomeTeam11.RowSource = Me.LeagueName.Value
Me.HomeTeam12.Value = ""
Me.HomeTeam12.RowSource = Me.LeagueName.Value
Me.AwayTeam1.Value = ""
Me.AwayTeam1.RowSource = Me.LeagueName.Value
Me.AwayTeam2.Value = ""
Me.AwayTeam2.RowSource = Me.LeagueName.Value
Me.AwayTeam3.Value = ""
Me.AwayTeam3.RowSource = Me.LeagueName.Value
Me.AwayTeam4.Value = ""
Me.AwayTeam4.RowSource = Me.LeagueName.Value
Me.AwayTeam5.Value = ""
Me.AwayTeam5.RowSource = Me.LeagueName.Value
Me.AwayTeam6.Value = ""
Me.AwayTeam6.RowSource = Me.LeagueName.Value
Me.AwayTeam7.Value = ""
Me.AwayTeam7.RowSource = Me.LeagueName.Value
Me.AwayTeam8.Value = ""
Me.AwayTeam8.RowSource = Me.LeagueName.Value
Me.AwayTeam9.Value = ""
Me.AwayTeam9.RowSource = Me.LeagueName.Value
Me.AwayTeam10.Value = ""
Me.AwayTeam10.RowSource = Me.LeagueName.Value
Me.AwayTeam11.Value = ""
Me.AwayTeam11.RowSource = Me.LeagueName.Value
Me.AwayTeam12.Value = ""
Me.AwayTeam12.RowSource = Me.LeagueName.Value

End Sub
    Private Sub UserForm_Click()

End Sub
    Last edited by Fotis1991; 10-28-2013 at 09:41 AM. Reason: Pls use code tags around your codes as per forum rules.
    Reply With QuoteRegister To Reply
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Print out combobox value to cell (for multiple comboboxes)
    By hemal89 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-04-2013, 08:56 AM
  2. Populating multiple forms from combobox choice and updating!
    By luargee in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-18-2012, 02:49 PM
  3. Populating comboboxes in a userform
    By metametrics in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-22-2011, 09:34 AM
  4. Excel 2007 : Populating multiple cells based on single cell input
    By UNCTEP in forum Excel General
    Replies: 2
    Last Post: 09-24-2010, 08:35 AM
  5. Populating multiple combobox with same data
    By jberr in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-28-2010, 06:19 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1