Help with form variable user option

HDfatboy03

Board Regular
Joined
May 23, 2010
Messages
62
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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

doofusboy

Well-known Member
Joined
Oct 14, 2003
Messages
1,325
Have you tried something like:

Code:
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])
 
Upvote 0

Forum statistics

Threads
1,195,838
Messages
6,011,886
Members
441,651
Latest member
drewe2000

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top