Help with form variable user option

[HDfatboy03]

Hello

I am creating a time sheet workbook. I've figured out how to have the punches go to a table and a general journal, based on Start, go to break, back from break, go to lunch and back from lunch and go home.

What I am wanting to do ... is have a seperate work sheet for each employee. I work for a small company with only 7 employees. So based on the entry for txtName (I want txtName to be the variable the controls ws2) ... I want the input to go to the appropriate worksheet for that employee base on their name (ex Pam, Bob, Brad ...etc).

This is what I have so far. X2 is where I am having problems.

S T A R T C O D E

Private Sub cmdADD5_Click()

Dim iRow As Long
Dim x As Long
x = CLng(Me.txtLoc.Text)

Dim x2 As Long
x2 = CLng(Me.txtName.Text)

Dim ws As Worksheet
Dim ws2 As Worksheet

Set ws = Worksheets("GeneralJournal")
Set ws2 = Worksheets("x2")

'find first empty row in database - THIS WORKS
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'copy the DATA to databases - THIS WORKS
ws.Cells(iRow, 1).Value = txtDate.Value
ws2.Cells(x, "D").Value = Me.txtDate.Value
ws.Cells(iRow, 2).Value = txtName.Value
ws2.Cells(2, "B").Value = Me.txtName.Value
ws.Cells(iRow, 3).Value = txtTime.Value
ws2.Cells(x, "G").Value = Me.txtTime.Value
ws.Cells(iRow, 4).Value = cboPunch.Value
ws2.Cells(x, "F").Value = Me.cboPunch.Value
'ws2.Cells(x, "C").Value = Me.txtLoc.Value

'clear the data - THIS WORKS
txtName.Value = ""
txtTime.Value = ""
cboPunch.Value = ""
txtDay.Value = ""

End Sub

E N D C O D E

 

[doofusboy]

Have you tried something like:

Dim x2 As [COLOR=red][B]String[/B][/COLOR]
[COLOR=red][B]x2 = Me.txtName.Text
[/B][/COLOR]
Dim ws As Worksheet
Dim ws2 As Worksheet

Set ws = Worksheets("GeneralJournal")
Set ws2 = Sheets([COLOR=red][B]x2[/B][/COLOR])

 

[HDfatboy03]

thanks so much ... it worked ... you are the BEST. You saved me alot of time

HDfatboy03