Solved: How to make Userform input data into a specific (hidden) sheet?

[Jodeyarce]

Hi everyone,

I'm pretty sure this is a very simple question that will make me sound stupid asking :( Does anyone know how to program your userform to input data keyed in the userform into a specific(hidden) sheet? I'm totally new to VBA so I'm at my wit's end here. Here's some background info:

I've started doing a time in/time out sheet for a production line for employees to clock in & out during the day. The following are my parameters:

Column A: User ID (A2 as the heading)
Column B: Name (B2 as the heading)
Column C onwards : Dates (2 cells merged each, i.e. 1/10/2011 is in merged C1 & D1 and so on and so forth.) Under each date heading would have a time in & time out,i.e. C2 is Time in & D2 is Time Out.

I've created a userform which allows me to key in a user ID(TextBox1) and press Ok (CommandButton1) reflect the name (TextBox2). Afterwhich, the employee would just simply press Login(CommandButton4) or Logout(CommandButton5) to time in or out respectively. Each time he/she logs in or out the respective time would be reflected in TextBox3 & TextBox4 respectively. I've made it such that the data will automatically be inputted to the current date:

Private Sub CommandButton3_Click()
Unload Me
UserForm1.Show
End Sub
Private Sub UserForm_Activate()
Me.TextBox1.SetFocus
End Sub
Private Sub CommandButton1_Click()
If Me.TextBox1.Value = "9876" Then Unload Me: Exit Sub

If TextBox2.Value = "" Then
Set fCell = Range("A:A").Find(TextBox1, lookat:=xlWhole)

If fCell Is Nothing Then
MsgBox ("The User Id '" & TextBox1.Value & "' was not found."), vbInformation, "No Match Found..."
Me.TextBox1.Value = ""
Me.TextBox1.SetFocus
Else
Me.TextBox2.Value = fCell(1, 2).Value
End If
End If
End Sub
Private Sub CommandButton4_Click()
If Me.TextBox2.Value = fCell(1, 2).Value Then
dcol = Rows(1).Find(Date, lookat:=xlWhole).Column
If Cells(fCell.Row, dcol) = "" Then
Cells(fCell.Row, dcol) = Format(Time, "hh:mm")
Me.TextBox3.Text = Format(Cells(fCell.Row, dcol).Value, "hh:mm")
End If
End If
End Sub
Private Sub CommandButton5_Click()
If Me.TextBox2.Value = fCell(1, 2).Value Then
dcol = Rows(1).Find(Date, lookat:=xlWhole).Column
If Cells(fCell.Row, dcol).Offset(, 1) = "" Then
Cells(fCell.Row, dcol).Offset(, 1) = Format(Time, "hh:mm")
Me.TextBox4.Value = Format(Cells(fCell.Row, dcol).Offset(, 1).Value, "hh:mm")
End If
End If
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Cancel = True
MsgBox "Key in Master Id to Close"
End If
End Sub


with this as my only module


Public fCell As Range

Public Sub ClearTxtBx()
With UserForm1
.TextBox1.Value = ""
.TextBox2.Value = ""
.TextBox1.SetFocus
End With
End Sub


I have also created another Button on the worksheet which would allow me to call up my Userform. All these work perfectly fine now, however now I have to change them such that the Button must be in a different sheet. And if i use that button to call up the Userform, it will still input changes into the sheet with all the employee information (which i also have to hide as well).

I'm afraid that I'm TOTALLY NEW to VBA and these codes written above were copied and pasted from many different places before it worked.

PS: If any of you think that the code above isn't very good feel free to comment:)

[mikerickson]

You are using unqualified references. This line of code refers to column A of the ActiveSheet.Set fCell = Range("A:A").Find(TextBox1, lookat:=xlWhole)
Qualifying references allows one to specify the workbook and worksheet of the range being referred to.
Set fCell = Workbooks("Workbook1.xlsm").Sheets("Sheet1").Range("A:A").Find(TextBox1, lookat:=xlWhole)
It is considered good practice to always qualify one's references, the first line would be writtenSet fCell = ActiveSheet.Range("A:A").Find(TextBox1, lookat:=xlWhole)
Note the existance of the keywords ThisWorkbook and ActiveWorkbook.

For your need, just qualify the appropriate code line to the name of the hidden worksheet.

[Jodeyarce]

Hi mikerickson,

Thank you so much for your help! Helped loads. Glad that I've learnt something new today:)