Userform data entry tool - 'get next work'

[LiamMik]

Hello everyone,

Firstly, apologies if this has already been asked on here - I've had a little search around but I can't seem to find exactly what I'm looking for. I really just need an excel guru to point me in the direction of what I need to know then I can go away and find it.

So, little project at work running at the moment that requires a data entry exercise to be carried out. The end goal I'd like is a list of numbers down column A and then 'yes' or 'no' down column B (see below).

End Goal

A________B

12121____Yes
12122____No
12123____Yes
12124____Yes


I'd like to create a userform/tool for the colleague doing the data entry exercise to make it cleaner and ideally for the colleague NOT to be able to see all the background database data (therefore connection will be required to another sheet).

The journey steps for the colleague would be as follows:

• Colleague opens userform/tool, selecting 'get next work' which looks at the database and finds the next number in cell A (1000's of lines of data has been dumped in column A) that doesn't have a corresponding Yes/No next to it in cell B which would be A3 (12123) as shown below.

A________B
12121____Yes
12122____No
12123____
12124____

• Colleague then selects either yes/no on the userform, and again would hit 'get next work'. This would then 1. add their entry to B3 and 2. reset the form, pulling in A4's number (the next number) for the process to be done again.

Like I mentioned, ideally I'd like the data to be sent from the spreadsheet with the userform to a database elsewhere on our server. I also have the database connecting to another tool where management can get a live view of percentage splits of Yes/No (this has already been built).

I understand this is probably complex and am a novice when it comes to VBA coding but I am a quick learner and if anyone can give me the basics of how this would work, pointing me in the direction of what I need, I'd really appreciate it. Additionally, someone may tell me what I'm trying to achieve isn't possible with VBA which would also be beneficial to know if that's the case!

Cheers in advance!

 

[AFPathfinder]

OK, for selecting the next number, think of it as looking for the next blank in column B instead of the next index in column A:

iNum = Range("A" & Cells(Rows.Count, 2).End(xlUp).Row + 1).Value

With that bit of code, you'll want to make sure the previous Yes/No has been entered before that executes. I'd tie the Yes/No action to entering the value in column B. Here is a bit of code I use to lookup the reference value:

    On Error Resume Next
    Set x = Columns(1).Find(What:=Me.Label3.Caption, After:=Cells(1, 1), LookIn:=xlValues, LookAt:=xlPart, _
        SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False)
    On Error GoTo 0

    x.Offset(, 1).Value = "Yes"

 

[LiamMik]

 

[LiamMik]

Thanks for the reply.

I'm slowly starting to pick this up now, so I've set the following for pulling in the value into the text box (is there a way I can lock the text box so no one can enter anything else in it?).

Private Sub UserForm_Initialize()iNum = Range("A" & Cells(Rows.Count, 2).End(xlUp).Row + 1).Value
TextBox1.Text = iNum
End Sub

I now need the code to do the following:

When CommandButton1_Click() ...

• Code checks if either OptionButton1 or OptionButton2 have been filled in, if none are selected then error message saying nothing has been selected and code doesn't move any further forward.
• If OptionButton1 (Yes) or OptionButton1 (No) are selected, code then writes that (Yes or No) to the next blank cell in B column.
• Then, repeat - pulls in next blank to Text.Box1 and we go again.

Thanks in advanced guys, I'm learning as I go here and I really appreciate the help!

 

[Fluff]

How about

Option Explicit
[COLOR=#ff0000]Dim NxtB As Long
[/COLOR]

Private Sub CommandButton1_Click()

    If OptionButton1 = False And OptionButton2 = False Then
        MsgBox "No options selected"
        Exit Sub
    Else
        If OptionButton1 = True Then
            Range("B" & NxtB).Value = "Yes"
        Else
            Range("B" & NxtB).Value = "No"
        End If
    NxtB = Range("B" & Rows.Count).End(xlUp).Offset(1).Row
    OptionButton1 = False
    OptionButton2 = False
    TextBox1 = Range("A" & NxtB)
    End If

End Sub

Private Sub UserForm_Initialize()

    NxtB = Range("B" & Rows.Count).End(xlUp).Offset(1).Row
    TextBox1.Text = Range("A" & NxtB)
    TextBox1.Enabled = False

End Sub

Note the line in red has to go at the top of the module

 

[AFPathfinder]

To keep users from making changes to the values, I typically use a label control instead of a textbox, but the Enabled property of a textbox is handy if you need to limit a user under certain circumstances.

If you're new to userforms, you'll want to make sure you have a UserForm_QueryClose event to allow or prohibit users from using the red X to close the form (and any form Unload event).

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    'Run when user clicks the red X button or the 'Cancel' button
    
    Unload Me
End Sub

I find the difficult part of userforms to be finding all the ways a user might try using your form and controlling the unexpected (I guess that's the same for all VBA).

 

[LiamMik]

How about

Option Explicit
[COLOR=#ff0000]Dim NxtB As Long
[/COLOR]

Private Sub CommandButton1_Click()

    If OptionButton1 = False And OptionButton2 = False Then
        MsgBox "No options selected"
        Exit Sub
    Else
        If OptionButton1 = True Then
            Range("B" & NxtB).Value = "Yes"
        Else
            Range("B" & NxtB).Value = "No"
        End If
    NxtB = Range("B" & Rows.Count).End(xlUp).Offset(1).Row
    OptionButton1 = False
    OptionButton2 = False
    TextBox1 = Range("A" & NxtB)
    End If

End Sub

Private Sub UserForm_Initialize()

    NxtB = Range("B" & Rows.Count).End(xlUp).Offset(1).Row
    TextBox1.Text = Range("A" & NxtB)
    TextBox1.Enabled = False

End Sub

Note the line in red has to go at the top of the module

It works perfect! Exactly what I needed .. if only I knew how you did it *head scratch* Thank you so much!!!

 

[LiamMik]

To keep users from making changes to the values, I typically use a label control instead of a textbox, but the Enabled property of a textbox is handy if you need to limit a user under certain circumstances.

If you're new to userforms, you'll want to make sure you have a UserForm_QueryClose event to allow or prohibit users from using the red X to close the form (and any form Unload event).

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    'Run when user clicks the red X button or the 'Cancel' button
    
    Unload Me
End Sub

I find the difficult part of userforms to be finding all the ways a user might try using your form and controlling the unexpected (I guess that's the same for all VBA).

Might work actually - can I copy and paste a label? I notice when TextBox1.Enabled = False, they can't edit the text (which is good) but it'd be handy if they could copy and paste whats in the box.

 

[LiamMik]

Managed to work out how to get it to auto copy

   With New MSForms.DataObject
        .SetText TextBox1.Text
        .PutInClipboard
    End With

 

[Fluff]

AFAIK you cannot copy & paste a label. But we can change the Textbox to locked

Private Sub UserForm_Initialize()

    NxtB = Range("B" & Rows.Count).End(xlUp).Offset(1).Row
    TextBox1.Text = Range("A" & NxtB)
    [COLOR=#0000ff]TextBox1.Locked = True[/COLOR]

End Sub

This means you can copy the contents (using the keyboard) but not change it.