Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data entry form-select sheet
Hi, i found an old post with this link from Jacob Skaria:
http://www.contextures.com/xlUserForm01.html I like this example. i have one more question. I need an button to select the sheet i need the data to go. Today i need to select sheet "monday" from the button, and the data to go to this sheet. Tomorrow i will select "tuesday" sheet and the data to go to this sheet. I will add an button, but i need an code. Can this be done? Thanks! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data entry form-select sheet
Add a combobox to the userform.
Then add this procedure (or merge it into your existing userform_initialize event): Private Sub UserForm_Initialize() Dim iCtr As Long For iCtr = 1 To 7 Me.ComboBox1.AddItem Format(DateSerial(2009, 6, iCtr), "dddd") Next iCtr End Sub I used 1 to 7 to get Monday, Tuesday, ..., Sunday. You may not want the weekend. Change the 7 to 5. And I looked at a calendar to see that June 1, 2009 was a Monday. If you want to start on a different day, choose a month that starts on that day. Then in the cmdAdd_Click event, change this lines: Set ws = Worksheets("PartsData") to: Set ws = Nothing On Error Resume Next Set ws = Worksheets(Me.ComboBox1.Value) On Error GoTo 0 If ws Is Nothing Then MsgBox "Please select a day!" Exit Sub End If If you've changed the name of the combobox, then you'll have to use that name. puiuluipui wrote: Hi, i found an old post with this link from Jacob Skaria: http://www.contextures.com/xlUserForm01.html I like this example. i have one more question. I need an button to select the sheet i need the data to go. Today i need to select sheet "monday" from the button, and the data to go to this sheet. Tomorrow i will select "tuesday" sheet and the data to go to this sheet. I will add an button, but i need an code. Can this be done? Thanks! -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data entry form-select sheet
It's working very well!!! I have one more question, can this code be made to
select a sheet that is not a day? Let's say it's a name. "John" sheet "Mary" sheet "Jim" sheet...... Can this be done? Thanks!!! "Dave Peterson" a scris: Add a combobox to the userform. Then add this procedure (or merge it into your existing userform_initialize event): Private Sub UserForm_Initialize() Dim iCtr As Long For iCtr = 1 To 7 Me.ComboBox1.AddItem Format(DateSerial(2009, 6, iCtr), "dddd") Next iCtr End Sub I used 1 to 7 to get Monday, Tuesday, ..., Sunday. You may not want the weekend. Change the 7 to 5. And I looked at a calendar to see that June 1, 2009 was a Monday. If you want to start on a different day, choose a month that starts on that day. Then in the cmdAdd_Click event, change this lines: Set ws = Worksheets("PartsData") to: Set ws = Nothing On Error Resume Next Set ws = Worksheets(Me.ComboBox1.Value) On Error GoTo 0 If ws Is Nothing Then MsgBox "Please select a day!" Exit Sub End If If you've changed the name of the combobox, then you'll have to use that name. puiuluipui wrote: Hi, i found an old post with this link from Jacob Skaria: http://www.contextures.com/xlUserForm01.html I like this example. i have one more question. I need an button to select the sheet i need the data to go. Today i need to select sheet "monday" from the button, and the data to go to this sheet. Tomorrow i will select "tuesday" sheet and the data to go to this sheet. I will add an button, but i need an code. Can this be done? Thanks! -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data entry form-select sheet
Yep.
Just add all the items you want. Private Sub UserForm_Initialize() with me.ComboBox1 .AddItem "Monday" .additem "John" .additem "Mary" .additem "anything you want here" end with End Sub If you wanted all the worksheet names in the activeworkbook: Private Sub UserForm_Initialize() dim wks as worksheet for each wks in activeworkbook.worksheets me.ComboBox1.additem wks.name next wks End Sub (Both untested, uncompiled. Watch for typos.) puiuluipui wrote: It's working very well!!! I have one more question, can this code be made to select a sheet that is not a day? Let's say it's a name. "John" sheet "Mary" sheet "Jim" sheet...... Can this be done? Thanks!!! "Dave Peterson" a scris: Add a combobox to the userform. Then add this procedure (or merge it into your existing userform_initialize event): Private Sub UserForm_Initialize() Dim iCtr As Long For iCtr = 1 To 7 Me.ComboBox1.AddItem Format(DateSerial(2009, 6, iCtr), "dddd") Next iCtr End Sub I used 1 to 7 to get Monday, Tuesday, ..., Sunday. You may not want the weekend. Change the 7 to 5. And I looked at a calendar to see that June 1, 2009 was a Monday. If you want to start on a different day, choose a month that starts on that day. Then in the cmdAdd_Click event, change this lines: Set ws = Worksheets("PartsData") to: Set ws = Nothing On Error Resume Next Set ws = Worksheets(Me.ComboBox1.Value) On Error GoTo 0 If ws Is Nothing Then MsgBox "Please select a day!" Exit Sub End If If you've changed the name of the combobox, then you'll have to use that name. puiuluipui wrote: Hi, i found an old post with this link from Jacob Skaria: http://www.contextures.com/xlUserForm01.html I like this example. i have one more question. I need an button to select the sheet i need the data to go. Today i need to select sheet "monday" from the button, and the data to go to this sheet. Tomorrow i will select "tuesday" sheet and the data to go to this sheet. I will add an button, but i need an code. Can this be done? Thanks! -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Data entry form-select sheet
It's working very well.
Thanks allot! "Dave Peterson" wrote: Yep. Just add all the items you want. Private Sub UserForm_Initialize() with me.ComboBox1 .AddItem "Monday" .additem "John" .additem "Mary" .additem "anything you want here" end with End Sub If you wanted all the worksheet names in the activeworkbook: Private Sub UserForm_Initialize() dim wks as worksheet for each wks in activeworkbook.worksheets me.ComboBox1.additem wks.name next wks End Sub (Both untested, uncompiled. Watch for typos.) puiuluipui wrote: It's working very well!!! I have one more question, can this code be made to select a sheet that is not a day? Let's say it's a name. "John" sheet "Mary" sheet "Jim" sheet...... Can this be done? Thanks!!! "Dave Peterson" a scris: Add a combobox to the userform. Then add this procedure (or merge it into your existing userform_initialize event): Private Sub UserForm_Initialize() Dim iCtr As Long For iCtr = 1 To 7 Me.ComboBox1.AddItem Format(DateSerial(2009, 6, iCtr), "dddd") Next iCtr End Sub I used 1 to 7 to get Monday, Tuesday, ..., Sunday. You may not want the weekend. Change the 7 to 5. And I looked at a calendar to see that June 1, 2009 was a Monday. If you want to start on a different day, choose a month that starts on that day. Then in the cmdAdd_Click event, change this lines: Set ws = Worksheets("PartsData") to: Set ws = Nothing On Error Resume Next Set ws = Worksheets(Me.ComboBox1.Value) On Error GoTo 0 If ws Is Nothing Then MsgBox "Please select a day!" Exit Sub End If If you've changed the name of the combobox, then you'll have to use that name. puiuluipui wrote: Hi, i found an old post with this link from Jacob Skaria: http://www.contextures.com/xlUserForm01.html I like this example. i have one more question. I need an button to select the sheet i need the data to go. Today i need to select sheet "monday" from the button, and the data to go to this sheet. Tomorrow i will select "tuesday" sheet and the data to go to this sheet. I will add an button, but i need an code. Can this be done? Thanks! -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Entry Form | Excel Discussion (Misc queries) | |||
data entry form | Excel Discussion (Misc queries) | |||
Form for data entry | Excel Discussion (Misc queries) | |||
Can I create an entry form that will feed a data sheet | Excel Worksheet Functions | |||
data entry form | Excel Discussion (Misc queries) |