Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data on UserForm to different sheet based on day of week chosen?
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!! -- Jennifer Lee IS Coordinator/App Support |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data on UserForm to different sheet based on day of week chosen?
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 '.... -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "JennLee" wrote: 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!! -- Jennifer Lee IS Coordinator/App Support |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data on UserForm to different sheet based on day of week chosen?
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 '.... -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "JennLee" wrote: 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!! -- Jennifer Lee IS Coordinator/App Support |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data on UserForm to different sheet based on day of week chose
Thanks so much for your help! This was kind of the line of thinking I had
but couldn't make it work. Thanks again! -- Jennifer Lee IS Coordinator/App Support "Martin Fishlock" wrote: 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 '.... -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "JennLee" wrote: 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!! -- Jennifer Lee IS Coordinator/App Support |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter rows on sheet 2 when a certain option is chosen on sheet 1 | Excel Worksheet Functions | |||
Auto-filtering based on a value chosen in a combo box? | Excel Discussion (Misc queries) | |||
Creating a graph based on a chosen group of data | Charts and Charting in Excel | |||
Select sheet based on userform date input | Excel Programming | |||
How do I generate a new sheet based on date/week/month? | Excel Worksheet Functions |