Copy and Set Focus

User avatar
adam
SilverLounger
Posts: 2306
Joined: 23 Feb 2010, 12:07

Copy and Set Focus

Post by adam »

I'm trying to make the following code to copy the selected value from combo box, clear the combo box after I press enter and set focus on combo box.

Code: Select all

Private Sub ComboBox1_AfterUpdate()
Dim s As String
    s = Me.ComboBox1.Value
    If Me.TextBox1 = "" Then
        Me.TextBox1 = s
    Else
        Me.TextBox1 = Me.TextBox1 & ", " & s
    End If
    If Me.ComboBox1.Value = "" Then
    Me.ComboBox1.SetFocus
    End If
End Sub
Any help would be kindly appreciated.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 74177
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copy and Set Focus

Post by HansV »

Is this a combo box on a worksheet? Or on a userform?
Regards,
Hans

User avatar
adam
SilverLounger
Posts: 2306
Joined: 23 Feb 2010, 12:07

Re: Copy and Set Focus

Post by adam »

A combobox in a userform.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 74177
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copy and Set Focus

Post by HansV »

I'd set the Style property of the combo box to 2 - fmStyleDropdownList, and use the Change event instead of the AfterUpdate event:

Code: Select all

Private Sub ComboBox1_Click()
    Dim s As String
    s = Me.ComboBox1.Value
    If s <> "" Then
        If Me.TextBox1 = "" Then
            Me.TextBox1 = s
        Else
            Me.TextBox1 = Me.TextBox1 & ", " & s
        End If
        Me.ComboBox1.Value = ""
    End If
End Sub
Regards,
Hans

User avatar
adam
SilverLounger
Posts: 2306
Joined: 23 Feb 2010, 12:07

Re: Copy and Set Focus

Post by adam »

I've attached the workbook for your reference with the code you have provided.

I'm using AfterUpdate event because I want to give the user the choice to type the text that is not in the combo box list.

The code works if I press enter two times. That means the user types the text that's not in the combo box list and press enter two times so that the text gets copied into the textbox and then the Textbox1 in on setfocus.

Is there any possibility I could make the textbox1 as setfocus when the user press the enter button only once.
You do not have the required permissions to view the files attached to this post.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 74177
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copy and Set Focus

Post by HansV »

If you don't need the user to be able to edit the text in TextBox1, create an Enter event procedure for the text box:

Code: Select all

Private Sub TextBox1_Enter()
    Me.ComboBox1.SetFocus
End Sub
Regards,
Hans

User avatar
adam
SilverLounger
Posts: 2306
Joined: 23 Feb 2010, 12:07

Re: Copy and Set Focus

Post by adam »

Thanks a lot for the help Hans.
Best Regards,
Adam

User avatar
adam
SilverLounger
Posts: 2306
Joined: 23 Feb 2010, 12:07

Re: Copy and Set Focus

Post by adam »

In the attached workbook I need the txtNotes to be set focus after the user enters values to listbox. but my code arrangement is not doing so in the txtNotes AfterUpdate event. But the

Code: Select all

Private Sub cmdAddNote_Click()
Call CallAdd
Me.txtNotes.SetFocus
End Sub
does what I want. How could I achieve this with txtNotes AfterUpdate event
You do not have the required permissions to view the files attached to this post.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 74177
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copy and Set Focus

Post by HansV »

How about adding

Code: Select all

Private Sub txtNotes_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Or KeyCode = vbKeyTab Then
        CallAdd
        KeyCode = 0
    End If
End Sub
Regards,
Hans

User avatar
adam
SilverLounger
Posts: 2306
Joined: 23 Feb 2010, 12:07

Re: Copy and Set Focus

Post by adam »

It works great. However when i click the tab to move to another field it keeps on giving me the message "please enter notes". is there an alternative for this?
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 74177
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copy and Set Focus

Post by HansV »

Remove or comment out

Code: Select all

Private Sub txtNotes_AfterUpdate()
Call CallAdd
Me.txtNotes.SetFocus
End Sub
Regards,
Hans

User avatar
adam
SilverLounger
Posts: 2306
Joined: 23 Feb 2010, 12:07

Re: Copy and Set Focus

Post by adam »

Even after commenting out, it doesn't make a difference. Instead, it adds empty rows to the listbox when the tab button is clicked.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 74177
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copy and Set Focus

Post by HansV »

By "tab button", do you mean the tab key on the keyboard?
Regards,
Hans

User avatar
adam
SilverLounger
Posts: 2306
Joined: 23 Feb 2010, 12:07

Re: Copy and Set Focus

Post by adam »

Yeah.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 74177
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copy and Set Focus

Post by HansV »

That's strange. See the attached version.

te.xlsm
You do not have the required permissions to view the files attached to this post.
Regards,
Hans