Thread: VBA to prompted user for employee number.

Hello,
I really need some help on this. So, basically what I am trying to do is to create an inventory control sheet. I have many buttons on a spreadsheet (IN and OUT, all in column A) that when IN is pressed I want IN to apear in column D and the row coresponding to the button that was pushed. I also want the user to be prompted with an input box to enter the employee number and then have it placed in E and the coresponding row for that button. Any help on this would be greatly apriciated.

Hi Absent
Assign this code to your Command Button

Option Explicit
Sub test()
    Dim r As Range
    Dim eNumber As String

    Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
    eNumber = Application.InputBox _
            (Prompt:="Please enter your Employee Number.", _
            Title:="Employee Number", Type:=2)
    If eNumber = "" Then
        MsgBox "Please Enter Your Employee Number"
        Exit Sub
    Else
        Range(Cells(r.Row, 5), Cells(r.Row, 5)).Value = eNumber
    End If
    Range(Cells(r.Row, 4), Cells(r.Row, 4)).Value = ActiveSheet.Buttons(Application.Caller).Characters.Text
End Sub

Hi absent
First, you MUST use code tags around any code you post to a Thread. Click on Forum Rules...it describes how to do this.
Please post a dummy file so I can see what your worksheet looks like...are both buttons on the same row? In the same cell?

Regarding this

also the code that you wrote writes "Button 6" in column D instead of in or out depending on which button is clicked

The code is picking up the Text of the Button that's clicked. I just assumed the Buttons were labeled "IN" and "OUT".

So, show me what you have...perhaps I can help.

Hi absent
No apologies necessary...don't want you to get hammered by a Moderator.
I really can't do much with a picture. Send me a copy of your workbook (with existing code). Eliminate (or change) proprietary information. If you're not willing to do so, I'll need to do it myself and I prefer to not guess.

Hi absent

This code is in the attached for each Command Button in the worksheet and appears to do as you require. To make it easier for you to copy the Command Buttons to additional rows, click on the Office Button, click on Excel Options, click on Customize, in the Drop Down "Commands Not in Ribbon" select "Select Multiple Objects". This will allow you to copy the Command Buttons to additional rows (of course, you MUST DELETE the pre-existing Command Buttons before you copy the new ones). The same code applies to ALL command buttons. Let me know how you make out.

Option Explicit
Sub test()
    Dim r As Range
    Dim eNumber As String

    Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell

    Select Case ActiveSheet.Buttons(Application.Caller).Characters.Text
    Case "OUT"
        eNumber = Application.InputBox _
                (Prompt:="Please enter your Employee Number.", _
                Title:="Employee Number", Type:=2)
        If eNumber = "" Then
            MsgBox "Please Enter Your Employee Number"
            Exit Sub
        Else
            Range(Cells(r.Row, 5), Cells(r.Row, 5)).Value = eNumber
            Range(Cells(r.Row, 4), Cells(r.Row, 4)).Value = ActiveSheet.Buttons(Application.Caller).Characters.Text
        End If
    Case "IN"
        Range(Cells(r.Row, 4), Cells(r.Row, 4)).Value = ActiveSheet.Buttons(Application.Caller).Characters.Text
        Range(Cells(r.Row, 5), Cells(r.Row, 5)).Value = ""
    End Select
End Sub

Hi Absent
Try this code...replace the underlined portion with your question

Option Explicit
Sub test()
    Dim r As Range
    Dim eNumber As String
    Dim Answer As String

    Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell

    Select Case ActiveSheet.Buttons(Application.Caller).Characters.Text
    Case "OUT"
        eNumber = Application.InputBox _
                (Prompt:="Please enter your Employee Number.", _
                Title:="Employee Number", Type:=2)
        If eNumber = "" Then
            MsgBox "Please Enter Your Employee Number"
            Exit Sub
        Else
            Range(Cells(r.Row, 5), Cells(r.Row, 5)).Value = eNumber
            Range(Cells(r.Row, 4), Cells(r.Row, 4)).Value = ActiveSheet.Buttons(Application.Caller).Characters.Text
        End If
    Case "IN"
        Answer = MsgBox("Type your question here", vbYesNo, "Proceed?")
        If Answer = vbYes Then
            Range(Cells(r.Row, 4), Cells(r.Row, 4)).Value = ActiveSheet.Buttons(Application.Caller).Characters.Text
            Range(Cells(r.Row, 5), Cells(r.Row, 5)).Value = ""
        Else
            Exit Sub
        End If
    End Select
End Sub

Hi Absent

Try this

Option Explicit
Sub test()
    Dim r As Range
    Dim eNumber As String
    Dim Answer As String

    Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell

    Select Case ActiveSheet.Buttons(Application.Caller).Characters.Text
    Case "OUT"
TryAgain:
        eNumber = Application.InputBox _
                (Prompt:="Please enter your Employee Number.", _
                Title:="Employee Number", Type:=2)
        If eNumber = "False" Then Exit Sub
        If eNumber = "" Then
            MsgBox "Please Enter Your Employee Number"
            GoTo TryAgain
        Else
            Range(Cells(r.Row, 5), Cells(r.Row, 5)).Value = eNumber
            Range(Cells(r.Row, 4), Cells(r.Row, 4)).Value = ActiveSheet.Buttons(Application.Caller).Characters.Text
        End If
    Case "IN"
        Answer = MsgBox("Type your question here", vbYesNo, "Proceed?")
        If Answer = vbYes Then
            Range(Cells(r.Row, 4), Cells(r.Row, 4)).Value = ActiveSheet.Buttons(Application.Caller).Characters.Text
            Range(Cells(r.Row, 5), Cells(r.Row, 5)).Value = ""
        Else
            Exit Sub
        End If
    End Select
End Sub

Hi Absent
Happy I could help.