data from combo box in userform to spreasheet???? URGENT!!

[KrisB_bacon]

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

 

[Patrick Molloy]

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

 

[KrisB_bacon]

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

 

[Patrick Molloy]

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
----------------------------------

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