Using TextBox1.Value to reference a specific worksheet

[TheDadliestDad]

Hello!,

I've been trying to wrap my brain around this for about 2 days now and decided to finally make an account to get some help. I'm trying to utilize a textbox entry on a userform to reference a specific worksheet for a time clock system. This is what I have so far for when the user actually clocks in...

Private Sub clockInOut_Button_Click()

    'Sheet = employeeID.Value
    'Set ws = ThisWorkbook.Sheets(employeeID.Value)
    c = ThisWorkbook.Worksheets("K1001").ActiveCell.Column
    'c = 2

    If c = 3 Then
        Sheets(CStr(employeeID.Value)).ActiveCell = Format(Now, "mm-dd-yyyy,h:mm:ss AM/PM")
        Sheets(CStr(employeeID.Value)).ActiveCell.Offset(1, -1).Select
    Else
        Sheets(employeeID.Value).ActiveCell = Format(Now, "mm-dd-yyyy,h:mm:ss AM/PM")
        Sheets(employeeID.Value).ActiveCell.Offset(0, 3) = PWC.Value
        Sheets(CStr(employeeID.Value)).ActiveCell.Offset(0, 4) = Description.Value
        Sheets(CStr(employeeID.Value)).ActiveCell.Offset(0, 5) = salesOrderNum.Value
        Sheets(CStr(employeeID.Value)).ActiveCell.Offset(0, 1).Select
    End If

    Call resetForm

End Sub

I have the employee ID #'s as the names of separate worksheets so each employee's time clock info is on their own respective worksheet for ease of use for the supervisor.

I need to find a way to reference the employee ID that the user types into the userform to the actual worksheet I want to "point" to. As you can see above, I tried ThisWorkbook.Worksheets("K1001").ActiveCell.Column, but that gives me an error code: 438. I've also tried the method I outlined in the If statement because I originally thought that it was because the .Value I'm giving to the Sheets() was not a string for som. I added the CStr() to try and solve this, but no dice. From what I understand it really does not like me using the textbox value object in the way I'm using it, but I can't figure out a workaround. I'm really hoping there is a way to do this as it would make my life ten times easier! I would be more than happy to go into more explanation if I didn't give enough. Thank you in advance for any help!

 

[Micron]

The error message always helps because there's thousands of them. Simply, ActiveCell is a property of the Application object, not a sheet.
Not real clear to me what the setup is, but if your data (empl name or id) is in the same cell on every sheet, then perhaps first activate the sheet
Sheets("K1001").Activate
something = Application.ActiveCell to get its value, or add .Cells.Address to get its location (for example).

I'm reading this ThisWorkbook.Worksheets("K1001").ActiveCell.Column as the location of the employee id/name so that's the basis for my suggestion. However, it doesn't seem all that efficient because what guarantee do you have that the active cell is the correct one?

 

[TheDadliestDad]

The error message always helps because there's thousands of them. Simply, ActiveCell is a property of the Application object, not a sheet.
Not real clear to me what the setup is, but if your data (empl name or id) is in the same cell on every sheet, then perhaps first activate the sheet
Sheets("K1001").Activate
something = Application.ActiveCell to get its value, or add .Cells.Address to get its location (for example).

I'm reading this ThisWorkbook.Worksheets("K1001").ActiveCell.Column as the location of the employee id/name so that's the basis for my suggestion. However, it doesn't seem all that efficient because what guarantee do you have that the active cell is the correct one?

So if I can explain a bit further, how I have my code set up/want my code to work is as follows:

- I have a line by line sheet for each employee that when they first clock in for a specific task, excel will record the current time and date using Now() and Format() in the B (2) column.
- The code then performs ActiveCell.Offset(#,#) to move across the row and take the information from my other textboxes such as work center, description, job #.
- After I input that data from the text boxes, I make the current active cell the cell directly to the right of the Time-In cell where the Now information was recorded previously, this is the Time-Out cell.
- When the employee clocks in again, the If else statement that I have will detect if the column that the active cell is at is the third column, which is the Time-Out cell.
- If the active cell is there, then the employee did not yet clock out of their current job. The code inputs the current time and date using the same functions as before, and then moves the active cell to the next line at the Time-In column.

I have it set up as such so that I can traverse down the rows and keep a living record for each individual employee, rather than them being all on the same sheet to complicate things further. It just becomes a necessity to ensure that the active cell on each worksheet is the correct active cell after you go into one to either check a particular clock in/out etc., which I deemed not too big of a deal.

So the issue I was running into is that, as you stated, I can't do what I'm trying to do when trying to travel to a specific excel sheet. Does Application object in this case not mean the same thing as a worksheet? From your explanation it appears that "Activating" a sheet would potentially allow me to do what I need to do, but I'm slightly confused at the context of "Application". Actually that's besides the point, haha. I appreciate your insight and I tried the .Activate and Application.ActiveCell method and it seems to have worked! Thank you!

 

[Micron]

Can't recall now what 438 error message is but based on what I looked up, I went here (where I always go for property, method, etc. related issues).

Office Visual Basic for Applications (VBA) reference

This reference is for experienced Office users who want to learn about VBA and who want some insight into how programming can help them to customize Office.

docs.microsoft.com

I either start browsing if I know where to start, or enter a search term. I was apparent that Worksheet object has no ActiveCell property, so I had to search to see that it belongs to the Application object. You can solve a lot of issues if you know where to look or how to search.