| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Saving combobx enteries
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving combobx enteries
I don't see the code.
"CR" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving combobx enteries
"JLGWhiz" wrote in message ... I don't see the code. "CR" wrote: 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 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Saving combobx enteries
Thank you anyway, I solved the problem using the DropButtonClick Event. Now,
I at least know what that one does. CR "CR" wrote in message ... "JLGWhiz" wrote in message ... I don't see the code. "CR" wrote: 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 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Duplicate enteries | Excel Discussion (Misc queries) | |||
| Counting Enteries | New Users to Excel | |||
| filtering duplicate enteries | Excel Discussion (Misc queries) | |||
| Displaying enteries of a certain type | Excel Programming | |||
| combobx help! | Excel Programming | |||
![CR[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Hybrid Mode
