VBA USERform to a specific employee.

M

maxxmadd

New Member
Joined
Nov 21, 2017
Messages
5
Greetings all,

I have the following code:

iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

I have three inputs Agent name, QA score, and Date. How would I change the above code to automatically enter the QA score and Date data under each Agents name/column instead of the first free space on the sheet

My spreadsheet is set up similar to the following.




[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Agent one[/TD]
[TD][/TD]
[TD]Agent 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]XX.XX%[/TD]
[TD]Average[/TD]
[TD]XX.XX%[/TD]
[TD]Average[/TD]
[/TR]
[TR]
[TD]Score[/TD]
[TD]Date[/TD]
[TD]Score[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]95%[/TD]
[TD]11/21[/TD]
[TD]100%[/TD]
[TD]11/20[/TD]
[/TR]
</tbody>[/TABLE]








My entire code for the submission button is following:

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("QA Form")


'find
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1


'check for a agent name
If Trim(Me.AGT.Value) = "" Then
Me.AGT.SetFocus
MsgBox "Missing Agent name"
Exit Sub
End If


With ws


.Cells(iRow, 1).Value = Me.AGT.Value
.Cells(iRow, 2).Value = Me.SCR.Value
.Cells(iRow, 3).Value = Me.DTE.Value


End With


'clear the data
Me.AGT.Value = ""
Me.SCR.Value = ""
Me.DTE.Value = ""
Me.AGT.SetFocus
End Sub
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
Hi & welcome to the board.
How about this
Code: 
Private Sub CommandButton1_Click()
    Dim iRow As Long
    Dim ws As Worksheet
    Dim Col As Long
    Set ws = Worksheets("QA Form")
    
    
    'find
    iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    
    
    'check for a agent name
    If Trim(Me.agt.Value) = "" Then
    Me.agt.SetFocus
    MsgBox "Missing Agent name"
    Exit Sub
    End If
    
    Select Case Me.agt.Value
        Case "[COLOR=#ff0000]Agent one[/COLOR]"
            Col = [COLOR=#0000ff]1[/COLOR]
        Case "[COLOR=#ff0000]Agent 2[/COLOR]"
            Col = [COLOR=#0000ff]4[/COLOR]
        Case "[COLOR=#ff0000]Agent 3[/COLOR]"
            Col = [COLOR=#0000ff]6[/COLOR]
    End Select
    
    With ws.Cells(iRow, Col)
        .Value = Me.agt.Value
        .Offset(, 1).Value = Me.SCR.Value
        .Offset(, 2).Value = Me.DTE.Value
    End With
    
    
    'clear the data
    Me.agt.Value = ""
    Me.SCR.Value = ""
    Me.DTE.Value = ""
    Me.agt.SetFocus
End Sub
Change the col values in blue to match the column number for each agent.
The agent names in red must match exactly the name in AGT
 
Upvote 0
Thank you for the reply; It seems to have resolved the sorting issue, however it does not post in the first available location in the column. The sheet looks like: [TABLE="width: 500"]
<tbody>[TR]
[TD]agent1[/TD]
[TD][/TD]
[TD][/TD]
[TD]agent2[/TD]
[TD][/TD]
[TD][/TD]
[TD]agent3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]avg[/TD]
[TD][/TD]
[TD][/TD]
[TD]avg[/TD]
[TD][/TD]
[TD][/TD]
[TD]avg[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]QA Score[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]DATE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]QA Score[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]DATE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD]QA Score[/TD]
[TD][TABLE="width: 75"]
<tbody>[TR]
[TD="class: xl65, width: 75"]DATE[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]100[/TD]
[TD] 11/23/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD]11/23/2017[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[TD]11/23/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


I have changed my AGT field to a Combobox.

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim Col As Long
Set ws = Worksheets("QA Form")


'find
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1


'check for a agent name
If Trim(Me.AGT.Value) = "" Then
Me.AGT.SetFocus
MsgBox "Missing Agent name"
Exit Sub
End If

Select Case Me.AGT.Value
Case "agent1"
Col = 1
Case "agent2"
Col = 4
Case "agent3"
Col = 7
End Select

With ws.Cells(iRow, Col)
.Value = Me.AGT.Value
.Offset(, 1).Value = Me.SCR.Value
.Offset(, 2).Value = Me.DTE.Value
End With


'clear the data
Me.AGT.Value = ""
Me.SCR.Value = ""
Me.DTE.Value = ""
Me.AGT.SetFocus
End Sub
 
Upvote 0
OK try this
Code: 
Private Sub CommandButton1_Click()

    Dim iRow As Long
    Dim ws As Worksheet
    Dim Col As Long
    Set ws = Worksheets("QA Form")
    
    'check for a agent name
    If Trim(Me.AGT.Value) = "" Then
        Me.AGT.SetFocus
        MsgBox "Missing Agent name"
        Exit Sub
    End If
    
    Select Case Me.AGT.Value
        Case "agent1"
            Col = 1
        Case "agent2"
            Col = 4
        Case "agent3"
            Col = 7
    End Select
    
   [COLOR=#0000ff] iRow = ws.Cells(Rows.Count, Col).End(xlUp).Offset(1).Row[/COLOR]
    
    With ws.Cells(iRow, Col)
        .Value = Me.AGT.Value
        .Offset(, 1).Value = Me.SCR.Value
        .Offset(, 2).Value = Me.DTE.Value
    End With
    
    'clear the data
    Me.AGT.Value = ""
    Me.SCR.Value = ""
    Me.DTE.Value = ""
    Me.AGT.SetFocus
End Sub
I've changed the way iRow is calculated, do it now looks at Agents name column to find the next row
 
Upvote 0
Fluff said:
OK try this
Code: 
Private Sub CommandButton1_Click()

    Dim iRow As Long
    Dim ws As Worksheet
    Dim Col As Long
    Set ws = Worksheets("QA Form")
    
    'check for a agent name
    If Trim(Me.AGT.Value) = "" Then
        Me.AGT.SetFocus
        MsgBox "Missing Agent name"
        Exit Sub
    End If
    
    Select Case Me.AGT.Value
        Case "agent1"
            Col = 1
        Case "agent2"
            Col = 4
        Case "agent3"
            Col = 7
    End Select
    
   [COLOR=#0000ff] iRow = ws.Cells(Rows.Count, Col).End(xlUp).Offset(1).Row[/COLOR]
    
    With ws.Cells(iRow, Col)
        .Value = Me.AGT.Value
        .Offset(, 1).Value = Me.SCR.Value
        .Offset(, 2).Value = Me.DTE.Value
    End With
    
    'clear the data
    Me.AGT.Value = ""
    Me.SCR.Value = ""
    Me.DTE.Value = ""
    Me.AGT.SetFocus
End Sub
I've changed the way iRow is calculated, do it now looks at Agents name column to find the next row
Click to expand...

Thank you, I receive a Runtime Error 1004 “Application-defined or Object-defined error” when Selecting Range

Debug directs to the line - With ws.Cells(iRow, Col)



[h=1][/h]
 
Upvote 0
What are the values of irow & col when you get that error?
 
Upvote 0
The simplest way is
Code: 
    iRow = ws.Cells(Rows.Count, Col).End(xlUp).Offset(1).Row
   [COLOR=#0000ff] MsgBox irow[/COLOR]
    With ws.Cells(iRow, Col)
You'll now get a msgbox telling you the value of irow
 
Last edited:
Upvote 0
Fluff said:
The simplest way is
Code: 
    iRow = ws.Cells(Rows.Count, Col).End(xlUp).Offset(1).Row
   [COLOR=#0000ff] MsgBox irow[/COLOR]
    With ws.Cells(iRow, Col)
You'll now get a msgbox telling you the value of irow
Click to expand...

Prompted - complie error End select without select case

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim Col As Long
Set ws = Worksheets("QA Form")

'check for a agent name
If Trim(Me.AGT.Value) = "" Then
Me.AGT.SetFocus
MsgBox "Missing Agent name"
Exit Sub
End If



Select Case Me.AGT.Value
Case "agent1"
Col = 1
Case "agent2"
Col = 4
Case "agent3"
Col = 7

iRow = ws.Cells(Rows.Count, Col).End(xlUp).Offset(1).Row
MsgBox iRow
With ws.Cells(iRow, Col)

End Select

With ws.Cells(iRow, Col)
.Value = Me.AGT.Value
.Offset(, 1).Value = Me.SCR.Value
.Offset(, 2).Value = Me.DTE.Value
End With


'clear the data
Me.AGT.Value = ""
Me.SCR.Value = ""
Me.DTE.Value = ""
Me.AGT.SetFocus
End Sub
 
Upvote 0
You've got the End Select line in the wrong place.
Code: 
Private Sub CommandButton1_Click()

    Dim iRow As Long
    Dim ws As Worksheet
    Dim Col As Long
    Set ws = Worksheets("QA Form")
    
    'check for a agent name
    If Trim(Me.AGT.Value) = "" Then
        Me.AGT.SetFocus
        MsgBox "Missing Agent name"
        Exit Sub
    End If
    
    Select Case Me.AGT.Value
        Case "agent1"
            Col = 1
        Case "agent2"
            Col = 4
        Case "agent3"
            Col = 7
    End Select
    
    iRow = ws.Cells(Rows.Count, Col).End(xlUp).Offset(1).Row
    MsgBox iRow
    
    With ws.Cells(iRow, Col)
        .Value = Me.AGT.Value
        .Offset(, 1).Value = Me.SCR.Value
        .Offset(, 2).Value = Me.DTE.Value
    End With
    
    'clear the data
    Me.AGT.Value = ""
    Me.SCR.Value = ""
    Me.DTE.Value = ""
    Me.AGT.SetFocus
End Sub
Also when posting code could you please use code tags. click the # icon in the reply window & paste the code between the tags
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
User Form not Loading
Replies
5
Views
161
StevieMP
S
E
  • Question Question
Generate unique code based on user form entry and put in table
2
Replies
18
Views
488
Micron
M
S
  • Solved
Move row based on cell color
Replies
3
Views
332
jolivanes
jolivanes
R
  • Question Question
User form textbox to determine specific worksheet location for input data
Replies
1
Views
460
Skyybot
Skyybot
M
  • Question Question
Add in VBA Code to check zip code against pre-determined list
Replies
0
Views
405
mikefisher
M

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

Share this page

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
Back
Top