Copy and Set Focus

User avatar
adam
SilverLounger
Posts: 2347
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: 78235
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?
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
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: 78235
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
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
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: 78235
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
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
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: 2347
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: 78235
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
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
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: 78235
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
Best wishes,
Hans

User avatar
adam
SilverLounger
Posts: 2347
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: 78235
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?
Best wishes,
Hans

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

Re: Copy and Set Focus

Post by adam »

Yeah.
Best Regards,
Adam

User avatar
HansV
Administrator
Posts: 78235
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.
Best wishes,
Hans

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

Re: Copy and Set Focus

Post by adam »

I've found out why the code was not working. It's because I have the following lines of code. when I remove these lines of code from the txtNotes_Change event the keydown code works. How could I make the keydown code to work even with the txtNotes Change event code?

Code: Select all

Private Sub txtNotes_Change()
    If txtNotes.TextLength > 65 Then
        MsgBox "You've reached the charater limit", vbCritical, "mydata"
        txtNotes.Text = Left(txtNotes.Text, 65)
    End If
    Me.txtMessageCharacters.Value = Me.txtNotes.TextLength
    Me.txtAvailableCharacters = Me.txtMessageLimit - Me.txtMessageCharacters
End Sub
Best Regards,
Adam

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

Re: Copy and Set Focus

Post by HansV »

Could you attach a sample workbook again?
Best wishes,
Hans

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

Re: Copy and Set Focus

Post by adam »

Here is how I have modified the code.

Original Code

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
Modified one

Code: Select all

Private Sub txtNotes_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then
        CallAdd
        KeyCode = 0
    End If
End Sub
The original code does enter data with keyboard enter and tab button. The modified one enters data only with the enter button.

I wanted only the keyboard enter button to enter data to listbox and it works fine now.
Best Regards,
Adam

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

Re: Copy and Set Focus

Post by adam »

In the meantime, how could I use the keycode to run two codes.

Let's say for example if textbox1 is empty run CallAdd macro.
if textbox1 has a value run CallData macro.

Any help would be appreciated.
Best Regards,
Adam

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

Re: Copy and Set Focus

Post by HansV »

Code: Select all

Private Sub txtNotes_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then
        If Me.TextBox1 = "" Then
            CallAdd
        Else
            CallData
        End If
        KeyCode = 0
    End If
End Sub
Best wishes,
Hans