Data on UserForm to different sheet based on day of week chosen?

[JennLee]

I desperately need to utilize one UserForm to send data to different
worksheets based on the choice of DAY of WEEK which will be chosen from a
combo box.

Please someone tell me it can be done.

I have one sheet named EntryForm and subsequent worksheets named Monday,
Tuesday, etc. While staff are entering data for day's activity, they will
choose Day of week and the following data entered should go to Monday
worksheet if Monday is chosen, etc. My program works fine for data going to
Sheet1 in my current set up but I need another similar program to place data
on separate worksheets based on choice of Day of Week.

Currently I have the following:
Private Sub CommandButton2_click()
Dim LastRow As Object

Set Lastow = Sheet2.Range("a65536").End(x1Up)

LastRow.Offset (1, 0).Value = TextBox1.Text
LastRow.Offset (1, 1).Value = Text Box2.Text
LastRow.Offset (1, 2).Value = ComboBox3.Text

etc, etc,

then at last 'LastRow"
MsgBox "One record written to POC Activity Log"

response = MsgBox ("Do you want to enter another record?", vbYesNo)

Records are written to sheet2.
But I need to add a text field named Day of Week and as I already said
(twice!) need the data to go to sheet Monday, Tuesday, etc. Clear as mud??!!

PLEASE HELP!!

 

[Martin Fishlock]

Hi:

You appear to be using a worksheet as the 'userform' and not a dialog
userform.

So you either use data validation and a list or a combo box or a set of 7
option buttons to get the day.

Then when you click the ok you get the day as put it into variable say wsname.

You then reference the workshhet as in

with worksheets(wsname)

code in here

end with

or a nicer option is to use the object so

dim ws as worksheet
set ws= worksheets(wsname)
Dim LastRow As Object

Set Lastow = ws.Range("a65536").End(x1Up)

LastRow.Offset (1, 0).Value = TextBox1.Text
LastRow.Offset (1, 1).Value = Text Box2.Text
LastRow.Offset (1, 2).Value = ComboBox3.Text
'....

 

[Martin Fishlock]

Hi:

You appear to be using a worksheet as the 'userform' and not a dialog
userform.

So you either use data validation and a list or a combo box or a set of 7
option buttons to get the day.

Then when you click the ok you get the day as put it into variable say wsname.

You then reference the workshhet as in

with worksheets(wsname)

code in here

end with

or a nicer option is to use the object so

dim ws as worksheet
set ws= worksheets(wsname)
Dim LastRow As Object

Set Lastow = ws.Range("a65536").End(x1Up)

LastRow.Offset (1, 0).Value = TextBox1.Text
LastRow.Offset (1, 1).Value = Text Box2.Text
LastRow.Offset (1, 2).Value = ComboBox3.Text
'....

 

[JennLee]

Thanks so much for your help! This was kind of the line of thinking I had
but couldn't make it work. Thanks again!