Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
data from combo box in userform to spreasheet???? URGENT!!
Look at the attached spreadsheet (scalled down, unfinished, just look a
the worksheets, Classes and Fieldtrips, and the button in fieldtrips) The purpose of the two worksheets in question is to allow a schoo geography teacher to input if there is a fieldtrip this year (wateve year it is, 2003 to 2008). The classes worksheet holds the data, th fieldtrips worksheet stores the summary. In the fieldtrips tab, the user would click the button on the rite This takes them to a userform. They select which year the fieldtrip i and click 1 of two buttons, Yes or No. Yes takes them to the Classe worksheet and tells them to input appropriate data. No again takes the to the Classes worksheet and formats the appropriate column, statin that there is no fieldtrip in that year. Before they are taken there the macro puts a Yes or No in cell C2 and the year that is selecte into B2. That worked fine. But to make it easier I have filled out th years, ready for the Yes or No to be placed. However, I can't work ou how to get the year (whatever year the user selects) into th appropriate cell. Can I use check boxes, option buttons etc.? Below is the code for the Yes button: Private Sub CommandButton1_Click() UserForm.Hide Range("C3").Select ActiveCell.FormulaR1C1 = "Yes" Sheets("Classes").Select MsgBox "Please now enter pupil attendance on the fieldtrip under th selected year", vbOKOnly + vbExclamation, "Important!" Unload UserForm End Sub Below is the code for the No button: Private Sub CommandButton2_Click() UserForm.Hide Range("C3").Select ActiveCell.FormulaR1C1 = "No" Sheets("Classes").Select Load UserForm3 UserForm3.Show Unload UserForm3 Unload UserForm End Sub Don't worry about the UserForm3, that is another story So.......any ideas of how can I solve this problem. Thx to any people who reply Chri Attachment filename: geo system3 (min).xls Download attachment: http://www.excelforum.com/attachment.php?postid=39437 -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
data from combo box in userform to spreasheet???? URGENT!!
Private Sub CommandButton1_Click()
UserForm.Hide Range("C3").Value = "Yes" Sheets("Classes").Activate MsgBox "Please now enter pupil attendance on the fieldtrip under the selected year", vbOKOnly + vbExclamation, "Important!" Unload me End Sub Below is the code for the No button: Private Sub CommandButton2_Click() me.hide Range("C3").Value = "No" Sheets("Classes").Activate UserForm3.Show Unload me End Sub Don't attach workbooks - some browsers - like mine -won't allow attachments through. I'm guessing that your userform3 allows the user to select a year? then places a No next to the relevent year on the sheet. Seems like a long winded way to do this. However. Range name the years as say AllYears. Add a listbox control (lstYears) to the userform then set a listbox's RowSource property as AllYears Next, range name the first year of the list of years on the sheet as FirstYear. Add a button (btnOK) to the userform, and add this code. Private Sub btnAccept_Click() Dim Index As Long Dim rYear As Range Set rYear = ThisWorkbook.Names("FirstYear").RefersToRange With lstYears For Index = 0 To .ListCount - 1 If .Selected(Index) Then rYear.Offset(Index, 1).Value = "Yes" Else rYear.Offset(Index, 1).Value = "No" End If Next End With Unload Me End Sub -- Patrick Molloy Microsoft Excel MVP ---------------------------------- "KrisB_bacon" wrote in message ... Look at the attached spreadsheet (scalled down, unfinished, just look at the worksheets, Classes and Fieldtrips, and the button in fieldtrips). The purpose of the two worksheets in question is to allow a school geography teacher to input if there is a fieldtrip this year (watever year it is, 2003 to 2008). The classes worksheet holds the data, the fieldtrips worksheet stores the summary. In the fieldtrips tab, the user would click the button on the rite. This takes them to a userform. They select which year the fieldtrip is and click 1 of two buttons, Yes or No. Yes takes them to the Classes worksheet and tells them to input appropriate data. No again takes them to the Classes worksheet and formats the appropriate column, stating that there is no fieldtrip in that year. Before they are taken there, the macro puts a Yes or No in cell C2 and the year that is selected into B2. That worked fine. But to make it easier I have filled out the years, ready for the Yes or No to be placed. However, I can't work out how to get the year (whatever year the user selects) into the appropriate cell. Can I use check boxes, option buttons etc.? Below is the code for the Yes button: Private Sub CommandButton1_Click() UserForm.Hide Range("C3").Select ActiveCell.FormulaR1C1 = "Yes" Sheets("Classes").Select MsgBox "Please now enter pupil attendance on the fieldtrip under the selected year", vbOKOnly + vbExclamation, "Important!" Unload UserForm End Sub Below is the code for the No button: Private Sub CommandButton2_Click() UserForm.Hide Range("C3").Select ActiveCell.FormulaR1C1 = "No" Sheets("Classes").Select Load UserForm3 UserForm3.Show Unload UserForm3 Unload UserForm End Sub Don't worry about the UserForm3, that is another story So.......any ideas of how can I solve this problem. Thx to any people who reply Chris Attachment filename: geo system3 (min).xls Download attachment: http://www.excelforum.com/attachment.php?postid=394370 --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
data from combo box in userform to spreasheet???? URGENT!!
Thx a lot for the help. This works fabulously except 1 thing.
Although I did want the userform to change the appropriate cell with Yes or No, I did not want the already changed cells to change. So, for example, if the user has filled in that 2003 doesn't have a field trip e.g. on Wednesday, and is now trying to enter that 2004 does have a field trip e.g. on Friday, he doesn't want the 2003 "No" to be deleted. After a cell has Yes or No in it, I don't want it to change, can u help? I hope you can solve my problem again, your first solution was otherwise excellent!! Here's the edited buttons Private Sub CommandButton2_Click() Me.Hide Range("C3").Value = "No" Sheets("Classes").Activate UserForm3.Show Unload Me End Sub Private Sub CommandButton3_Click() Dim Index As Long Dim rYear As Range Set rYear = ThisWorkbook.Names("FirstYear").RefersToRange With lstYear For Index = 0 To .ListCount - 1 If .Selected(Index) Then rYear.Offset(Index, 1).Value = "Yes" Else rYear.Offset(Index, 1).Value = "No" End If Next UserForm.Hide Range("C3").Value = "Yes" Sheets("Classes").Activate MsgBox "Please now enter pupil attendance on the fieldtrip under the selected year", vbOKOnly + vbExclamation, "Important!" End With Unload Me End Sub Thx again --- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
data from combo box in userform to spreasheet???? URGENT!!
For Index = 0 To .ListCount - 1 IF rYear.Offset(Index, 1).Value = "" THEN If .Selected(Index) Then rYear.Offset(Index, 1).Value = "Yes" Else rYear.Offset(Index, 1).Value = "No" End If END IF Next -- Patrick Molloy Microsoft Excel MVP ---------------------------------- "Patrick Molloy" wrote in message ... Private Sub CommandButton1_Click() UserForm.Hide Range("C3").Value = "Yes" Sheets("Classes").Activate MsgBox "Please now enter pupil attendance on the fieldtrip under the selected year", vbOKOnly + vbExclamation, "Important!" Unload me End Sub Below is the code for the No button: Private Sub CommandButton2_Click() me.hide Range("C3").Value = "No" Sheets("Classes").Activate UserForm3.Show Unload me End Sub Don't attach workbooks - some browsers - like mine -won't allow attachments through. I'm guessing that your userform3 allows the user to select a year? then places a No next to the relevent year on the sheet. Seems like a long winded way to do this. However. Range name the years as say AllYears. Add a listbox control (lstYears) to the userform then set a listbox's RowSource property as AllYears Next, range name the first year of the list of years on the sheet as FirstYear. Add a button (btnOK) to the userform, and add this code. Private Sub btnAccept_Click() Dim Index As Long Dim rYear As Range Set rYear = ThisWorkbook.Names("FirstYear").RefersToRange With lstYears For Index = 0 To .ListCount - 1 If .Selected(Index) Then rYear.Offset(Index, 1).Value = "Yes" Else rYear.Offset(Index, 1).Value = "No" End If Next End With Unload Me End Sub -- Patrick Molloy Microsoft Excel MVP ---------------------------------- "KrisB_bacon" wrote in message ... Look at the attached spreadsheet (scalled down, unfinished, just look at the worksheets, Classes and Fieldtrips, and the button in fieldtrips). The purpose of the two worksheets in question is to allow a school geography teacher to input if there is a fieldtrip this year (watever year it is, 2003 to 2008). The classes worksheet holds the data, the fieldtrips worksheet stores the summary. In the fieldtrips tab, the user would click the button on the rite. This takes them to a userform. They select which year the fieldtrip is and click 1 of two buttons, Yes or No. Yes takes them to the Classes worksheet and tells them to input appropriate data. No again takes them to the Classes worksheet and formats the appropriate column, stating that there is no fieldtrip in that year. Before they are taken there, the macro puts a Yes or No in cell C2 and the year that is selected into B2. That worked fine. But to make it easier I have filled out the years, ready for the Yes or No to be placed. However, I can't work out how to get the year (whatever year the user selects) into the appropriate cell. Can I use check boxes, option buttons etc.? Below is the code for the Yes button: Private Sub CommandButton1_Click() UserForm.Hide Range("C3").Select ActiveCell.FormulaR1C1 = "Yes" Sheets("Classes").Select MsgBox "Please now enter pupil attendance on the fieldtrip under the selected year", vbOKOnly + vbExclamation, "Important!" Unload UserForm End Sub Below is the code for the No button: Private Sub CommandButton2_Click() UserForm.Hide Range("C3").Select ActiveCell.FormulaR1C1 = "No" Sheets("Classes").Select Load UserForm3 UserForm3.Show Unload UserForm3 Unload UserForm End Sub Don't worry about the UserForm3, that is another story So.......any ideas of how can I solve this problem. Thx to any people who reply Chris Attachment filename: geo system3 (min).xls Download attachment: http://www.excelforum.com/attachment.php?postid=394370 --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare data from one column/spreasheet to another? | Excel Discussion (Misc queries) | |||
NEED URGENT HELP WITH COMBO BOX!!!! | Excel Discussion (Misc queries) | |||
How can I run a spreasheet over range of input data ? | Excel Discussion (Misc queries) | |||
Populating Sheet Data From A UserForm Combo Box Enabled For Multi-Choices | Excel Programming | |||
Combo Box in userform | Excel Programming |