Saving combobx enteries

[CR]

Hello, I am fairly new to VBA, using Excel 2000.
I run a football pool for 50 people and have used Excel with success for
many years to keep track of it. Next year someone else who is not as
familiar with Excel will start running it.
I am trying to make it easier on her by making userforms for her to enter
the info on.

I have a userform that has combobx "weeks" that has a List Array that lets
the user choose one of 17 weeks. There are 17 other combobxs have a
rowsource called 'Team Names'!A1:A32 the user can choose from the rowsource
list to fill these boxes and then push a command btn to enter the
information into a range on a worksheet that is picked depending which week
is chosen. The next time that week is picked the boxes populate from that
range. Than all works fine.

The problem I'm having is getting the data to enter in the proper range when
the "weeks" combobx is changed from week 1 to week 2 before the command btn
is pushed. With no code the team selections are just lost. I have tried to
code the change event with the same code as the command btn but it doesn't
fire until after the change resulting in week 1 data being entered into week
2's range.

I have tried using the beforeupdate event and click event thinking it would
work but they both do nothing. I guess I don't know exactly what they are
supposed to do. :~(
Is there a way to get this done?

Help, please.
Thank you
CR

 

[JLGWhiz]

I don't see the code.

 

[CR]

I don't see the code.

I have not done much VBA and hope to tighten the code up with ranges and
loops once I get it working the way I want.

My basic question is: ComboBox33 value is WK1. If the user changes the
combobx value to anyone of 16 other WKs without first hitting the "enter'
command btn to write the new data to the sheet, the entries are lost and
have to be reselected. How can I make the data write to the sheet in the
proper weeks range if they don't hit the enter button first?

Here is a short version to the code that works with the Enter command
button. The same basic thing works with the combobx change event but it
seems to put the data into the weeks range that the combobx changes to, not
the range for the WK before it changes.

This code is already in the change event , when I try to add the other calls
it will not work the way I need it to.

Thanks for the reply
CR

Sub ComboBox33_Change()
ComboBox1.SetFocus
'changes week reference number
Sheets("Schedule").Range("B20").Select
ActiveCell.Value = ComboBox33.Value
If frmschedule.ComboBox33.Value = "WK1" Then
Call RdWeek1left
Call RdWeek1Right
Else
If frmschedule.ComboBox33.Value = "WK2" Then
Call RdWeek2Left
Call RdWeek2Right
End If
End If
End Sub

'Fills the combobx's with the weeks schedule if it is already entered on
sheet (Column B and C for week 1)

Sub RdWeek1left()
Range("B3").Select
Call ReadWeeksLeft
Range("C3").Select
Call ReadWeeksRight
End Sub

Sub RdWeek2Left()
Range("G3").Select
Call ReadWeeksLeft
Range("H3").Select
Call ReadWeeksRight
End Sub

Sub ReadWeeksLeft()
'enters previously entered schedule in form Left side
ActiveWorkbook.Sheets("Schedule").Activate
ComboBox1.Value = ActiveCell.Value
ComboBox3.Value = ActiveCell.Offset(1, 0)
ComboBox5.Value = ActiveCell.Offset(2, 0)
ComboBox7.Value = ActiveCell.Offset(3, 0)
ComboBox9.Value = ActiveCell.Offset(4, 0)
ComboBox11.Value = ActiveCell.Offset(5, 0)
ComboBox13.Value = ActiveCell.Offset(6, 0)
ComboBox15.Value = ActiveCell.Offset(7, 0)
ComboBox17.Value = ActiveCell.Offset(8, 0)
ComboBox19.Value = ActiveCell.Offset(9, 0)
ComboBox21.Value = ActiveCell.Offset(10, 0)
ComboBox23.Value = ActiveCell.Offset(11, 0)
ComboBox25.Value = ActiveCell.Offset(12, 0)
ComboBox27.Value = ActiveCell.Offset(13, 0)
ComboBox29.Value = ActiveCell.Offset(14, 0)
ComboBox31.Value = ActiveCell.Offset(15, 0)


End Sub

'enters previously entered schedule in form right side
ActiveWorkbook.Sheets("Schedule").Activate
ComboBox2.Value = ActiveCell.Value
ComboBox4.Value = ActiveCell.Offset(1, 0)
ComboBox6.Value = ActiveCell.Offset(2, 0)
ComboBox8.Value = ActiveCell.Offset(3, 0)
ComboBox10.Value = ActiveCell.Offset(4, 0)
ComboBox12.Value = ActiveCell.Offset(5, 0)
ComboBox14.Value = ActiveCell.Offset(6, 0)
ComboBox16.Value = ActiveCell.Offset(7, 0)
ComboBox18.Value = ActiveCell.Offset(8, 0)
ComboBox20.Value = ActiveCell.Offset(9, 0)
ComboBox22.Value = ActiveCell.Offset(10, 0)
ComboBox24.Value = ActiveCell.Offset(11, 0)
ComboBox26.Value = ActiveCell.Offset(12, 0)
ComboBox28.Value = ActiveCell.Offset(13, 0)
ComboBox30.Value = ActiveCell.Offset(14, 0)
ComboBox32.Value = ActiveCell.Offset(15, 0)

End Sub

'This is for the entry command button:

Private Sub cmdEnter_1_Click()
'Call the change sub to enter new schedule in correct week
ActiveWorkbook.Sheets("Schedule").Activate
Sheets("Schedule").Range("B20").Select
ActiveCell.Value = ComboBox33.Value
Call Change

End Sub

Sub Change()
If frmschedule.ComboBox33.Value = WK1 Then
Call Week1left
Else
If frmschedule.ComboBox33.Value = WK2 Then
Call Week2Left
End If
End If
End Sub

Sub Week1left()
Range("B3").Select
Call EnterWeeksLeft
End Sub
Sub Week1Right()
Range("C3").Select
Call EnterWeeksRight
End Sub
Sub Week2Left()
Range("G3").Select
Call EnterWeeksLeft
End Sub
Sub Week2Right()
Range("H3").Select
Call EnterWeeksRight
End Sub

Sub EnterWeeksLeft()
'Enters values from comboboxes to schedule sheet
'WEEK 1
ActiveWorkbook.Sheets("Schedule").Activate
ActiveCell.Value = ComboBox1.Value
ActiveCell.Offset(1, 0) = ComboBox3.Value
ActiveCell.Offset(2, 0) = ComboBox5.Value
ActiveCell.Offset(3, 0) = ComboBox7.Value
ActiveCell.Offset(4, 0) = ComboBox9.Value
ActiveCell.Offset(5, 0) = ComboBox11.Value
ActiveCell.Offset(6, 0) = ComboBox13.Value
ActiveCell.Offset(7, 0) = ComboBox15.Value
ActiveCell.Offset(8, 0) = ComboBox17.Value
ActiveCell.Offset(9, 0) = ComboBox19.Value
ActiveCell.Offset(10, 0) = ComboBox21.Value
ActiveCell.Offset(11, 0) = ComboBox23.Value
ActiveCell.Offset(12, 0) = ComboBox25.Value
ActiveCell.Offset(13, 0) = ComboBox27.Value
ActiveCell.Offset(14, 0) = ComboBox29.Value
ActiveCell.Offset(15, 0) = ComboBox31.Value
Call ChangeRight
End Sub

Sub EnterWeeksRight()
ActiveWorkbook.Sheets("Schedule").Activate
ActiveCell.Value = ComboBox2.Value
ActiveCell.Offset(1, 0) = ComboBox4.Value
ActiveCell.Offset(2, 0) = ComboBox6.Value
ActiveCell.Offset(3, 0) = ComboBox8.Value
ActiveCell.Offset(4, 0) = ComboBox10.Value
ActiveCell.Offset(5, 0) = ComboBox12.Value
ActiveCell.Offset(6, 0) = ComboBox14.Value
ActiveCell.Offset(7, 0) = ComboBox16.Value
ActiveCell.Offset(8, 0) = ComboBox18.Value
ActiveCell.Offset(9, 0) = ComboBox20.Value
ActiveCell.Offset(10, 0) = ComboBox22.Value
ActiveCell.Offset(11, 0) = ComboBox24.Value
ActiveCell.Offset(12, 0) = ComboBox26.Value
ActiveCell.Offset(13, 0) = ComboBox28.Value
ActiveCell.Offset(14, 0) = ComboBox30.Value
ActiveCell.Offset(15, 0) = ComboBox32.Value
End Sub

Sub ChangeRight()
If frmschedule.ComboBox33.Value = WK1 Then
Call Week1Right
Else
If frmschedule.ComboBox33.Value = WK2 Then
Call Week2Right
End If
End If
End Sub

 

[CR]

Thank you anyway, I solved the problem using the DropButtonClick Event. Now,
I at least know what that one does.

CR