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.




Agent oneAgent 2
XX.XX%AverageXX.XX%Average
ScoreDateScoreDate
95%11/21100%11/20

<tbody>
</tbody>








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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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:
agent1agent2agent3
avgavgavg
QA Score
DATE

<tbody>
</tbody>
QA Score
DATE

<tbody>
</tbody>
QA Score
DATE

<tbody>
</tbody>
100 11/23/2017
10011/23/2017
10011/23/2017

<tbody>
</tbody>


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

R
  • Question
User form textbox to determine specific worksheet location for input data
Replies
1
Views
272
Skyybot
Skyybot
M
  • Question
Add in VBA Code to check zip code against pre-determined list
Replies
0
Views
148
mikefisher
M
A
  • Question
Trying to add New names to a user list table with a user form. In the current format it does not show up on the table, what do I need to do?
Replies
1
Views
270
alleneure
A
K
  • Solved
VBA not finding next empty row due to formula
Replies
8
Views
909
Alex Blakenburg
A
M
  • Solved
userform VB problem
Replies
5
Views
311
Mattlake
M

Forum statistics

Threads
1,214,786
Messages
6,121,546
Members
449,038
Latest member
Guest1337

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