Creating an input form

M

mckenny16

--------------------------------------------------------------------------------

Hi to all,

Excelforums newbie here, needing some help with a job I've been given.
I'm usually a Access person, but Excel seems the better option with
this. I need to create workbook that will show the jobs numbers/names
with which employee has worked on it, which date and how much time has
been spent on it. Basically, each sheet will have on job on it, with
the dates of the year (yeah, he want every working date of the year on
one sheet) along the y axis and employee names along the x axis,
therefore having the amount of time spent on this project in the
corresponding field.

However, after building the input form on Access, the boss wants
something similar, but I'll need to do it in Excel. So, I need to
create a input form that will have combo boxes (I can do these) for
employee names, job number, and date, with an input box for time spent.
I need the time spent to be magically placed into the exact field I need
it to be. I've been told that I need to Use job number in the form to
select the sheet and the date in the form to select the date

Any help on this would be really appricated.

Matt
 
D

Dave Peterson

You could put a combobox on your form that shows all the worksheets.

Option Explicit
Private Sub CommandButton1_Click()

Dim testWks As Worksheet
Dim DestCell As Range

If Me.ComboBox1.Value = "" Then
MsgBox "no sheet"
Exit Sub
End If

Set testWks = Nothing
On Error Resume Next
Set testWks = ThisWorkbook.Worksheets(Me.ComboBox1.Value)
On Error GoTo 0

If testWks Is Nothing Then
MsgBox "something bad happened!"
Exit Sub
Else
With testWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
DestCell.Offset(0, 3).Value = Me.TextBox1.Value
End With
End If

End Sub
Private Sub UserForm_Initialize()
Dim wks As Worksheet

For Each wks In ThisWorkbook.Worksheets
Select Case LCase(wks.Name)
Case Is = "skip1", "skip2"
'do nothing
Case Else
Me.ComboBox1.AddItem wks.Name
End Select
Next wks

End Sub

You may want to get some tips from the way John Walkenbach did his enhanced
data|form. You can find it at:
http://j-walk.com/ss/dataform/index.htm

The code is available if you want to see the secrets (and modify it for your
use!).
 
Top