Data entry form-select sheet

[puiuluipui]

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]

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]

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:

 

[Dave Peterson]

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]

It's working very well.
Thanks allot!

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