Combobox list not displaying correctly when clicked on the Arrow

N

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
688
Hello
my combobox has 2 column counts and its items are added
When i am clicking on the same it shows the values of 1st item. i dont understand why ?
and not changing the value of idx as shown below
infact clicking on each item should display in combobox text

Code: 
Private Sub ComboBox1_Click()

      Dim idx As Long
      idx = ComboBox1.ListIndex

          If idx <> -1 Then
              ComboBox1.Text = Me.ComboBox1.List(idx, 0) & "     " & Me.ComboBox1.List(idx, 1)
          End If
End Sub
NimishK
 
Last edited:
Sort by date Sort by votes
Try this code by itself !!

Code: 
Private [COLOR="Navy"]Sub[/COLOR] ComboBox1_KeyDown(ByVal KeyCode [COLOR="Navy"]As[/COLOR] MSForms.ReturnInteger, ByVal Shift [COLOR="Navy"]As[/COLOR] Integer)
Static idx [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] KeyCode
    Case 38 '[COLOR="Green"][B]up arrow key[/B][/COLOR]
        idx = idx - IIf(idx = 0, 0, 1)
        KeyCode = 0
    Case 40 '[COLOR="Green"][B]down arrow key[/B][/COLOR]
        idx = idx + IIf(idx < ComboBox1.ListCount, 1, 0)
        KeyCode = 0
    [COLOR="Navy"]End[/COLOR] Select
[COLOR="Navy"]If[/COLOR] idx > 0 [COLOR="Navy"]Then[/COLOR]
    ComboBox1.Text = Me.ComboBox1.List(idx - 1, 0) & "     " & Me.ComboBox1.List(idx - 1, 1)
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Thanks
FYI Some of the changes i did inorder to have smoother combobox functioning
Observations with below changes in code
Observations
1. Clicking on combobox Arrow DropDown shows
from DropDown Selecting item for eg DataA5 DataB5 -----> Bascially clicked on this item
Now when Pressing UpArrow Key on selected DataA5 Data B5 the Record jumps of to DataA3 DataB3 surprising instead of going on DataA4 DataB4
and then Pressing down Arrow on DataA3 DataB3 respectively shows DataA4 DataB4 and so on

2. from DropDown Selecting item for eg DataA5 DataB5 -----> Bascially clicked on this item
Pressing Twice down Arrow respectively shows DataA6 DataB6 and this is not on one go

These are all the sub-routines on userform
Code: 
Option Explicit
Public idx As Long
Private Sub ComboBox1_Click()
'''Dim idx As Long
      idx = ComboBox1.ListIndex
          If idx <> -1 Then
              ComboBox1.Text = Me.ComboBox1.List(idx, 0) & "     " & Me.ComboBox1.List(idx, 1)
          End If
End Sub

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
'''Static idx As Long
Select Case KeyCode
    Case 38 'up arrow key
        idx = idx - IIf(idx = 0, 0, 1)
        KeyCode = 0
    Case 40 'down arrow key
        idx = idx + IIf(idx < ComboBox1.ListCount, 1, 0)
        KeyCode = 0
    End Select
If idx > 0 Then
    ComboBox1.Text = Me.ComboBox1.List(idx - 1, 0) & "     " & Me.ComboBox1.List(idx - 1, 1)
End If
End Sub

Private Sub UserForm_Initialize()
'''Dim idx As Long
idx = 0 
ComboBox1.Text = Me.ComboBox1.List(idx, 0) & "     " & Me.ComboBox1.List(idx, 1)
End Sub
*500-260519-816AM*
 
Last edited:
Upvote 0
Try this:-
Its a bit closer !!
Code: 
Option Explicit
Public idx [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Private [COLOR="Navy"]Sub[/COLOR] ComboBox1_Click()
   idx = ComboBox1.ListIndex
      [COLOR="Navy"]If[/COLOR] idx <> -1 [COLOR="Navy"]Then[/COLOR]
              ComboBox1.Text = Me.ComboBox1.List(idx, 0) & "     " & Me.ComboBox1.List(idx, 1)
          [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] ComboBox1_KeyDown(ByVal KeyCode [COLOR="Navy"]As[/COLOR] MSForms.ReturnInteger, ByVal Shift [COLOR="Navy"]As[/COLOR] Integer)
[COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] KeyCode
   Case 38 '[COLOR="Green"][B]up arrow key[/B][/COLOR]
        idx = idx - IIf(idx = 0, 0, 1)
        KeyCode = 0
    Case 40 '[COLOR="Green"][B]down arrow key[/B][/COLOR]
        idx = idx + IIf(idx < ComboBox1.ListCount, 1, 0)
        KeyCode = 0
    [COLOR="Navy"]End[/COLOR] Select
[COLOR="Navy"]If[/COLOR] idx > -1 And idx < ComboBox1.ListCount [COLOR="Navy"]Then[/COLOR]
ComboBox1.Text = Me.ComboBox1.List(idx, 0) & "     " & Me.ComboBox1.List(idx, 1)
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] UserForm_Initialize()
[COLOR="Navy"]Dim[/COLOR] idx [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
idx = 0
ComboBox1.Text = Me.ComboBox1.List(idx, 0) & "     " & Me.ComboBox1.List(idx, 1)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Very Sorry for late response: Just tried your code it works perfectly. Thank you so much:)

Last but not the least
If i want to include Combobox change_event for updating values in Textbox
i added column C with DataC1 to DataC10 and changed its row source to A1:C1 and also added one Textbox
1. How can ComboBox_change Event be as Equivalent ComboBox_Click Event
i.e clicking with down arrow key on combobox my textbox to be updated with value of Colum3
eg clicking on DataA4 DataB4 so textbox = DataC4 and so on

ok whenever i saved your file with moifications also with different file name i got below msg. How will below msg help ?
Be Careful ! Parts of your documents may include personal information that can't be removed by document inspector
Click to expand...

Thanks
 
Last edited:
Upvote 0
Try this:-
Code: 
Option Explicit
Public idx [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] ComboBox1_Change()
idx = IIf(idx = ComboBox1.ListCount, 1, idx)
TextBox1.Value = Me.ComboBox1.List(idx, 2)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] ComboBox1_Click()
   idx = ComboBox1.ListIndex
      [COLOR="Navy"]If[/COLOR] idx <> -1 [COLOR="Navy"]Then[/COLOR]
              ComboBox1.Text = Me.ComboBox1.List(idx, 0) & "     " & Me.ComboBox1.List(idx, 1)
          [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] ComboBox1_KeyDown(ByVal KeyCode [COLOR="Navy"]As[/COLOR] MSForms.ReturnInteger, ByVal Shift [COLOR="Navy"]As[/COLOR] Integer)
[COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] KeyCode
   Case 38 '[COLOR="Green"][B]up arrow key[/B][/COLOR]
        idx = idx - IIf(idx = 0, 0, 1)
        KeyCode = 0
    Case 40 '[COLOR="Green"][B]down arrow key[/B][/COLOR]
        idx = idx + IIf(idx < ComboBox1.ListCount, 1, 0)
        KeyCode = 0
    [COLOR="Navy"]End[/COLOR] Select
[COLOR="Navy"]If[/COLOR] idx > -1 And idx < ComboBox1.ListCount [COLOR="Navy"]Then[/COLOR]
ComboBox1.Text = Me.ComboBox1.List(idx, 0) & "     " & Me.ComboBox1.List(idx, 1)
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Private [COLOR="Navy"]Sub[/COLOR] UserForm_Initialize()
[COLOR="Navy"]Dim[/COLOR] idx [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
idx = 0
ComboBox1.Text = Me.ComboBox1.List(idx, 0) & "     " & Me.ComboBox1.List(idx, 1)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]




Ref:-
Be Careful ! Parts of your documents may include personal information that can't be removed by document inspector
Click to expand...
Try:-
Go to File in the upper left hand corner, then Options > Trust Center > Trust Center Settings > Privacy Options > Document Inspector and un-check the check box that says "Document Properties and Personal Information", then hit OK. Hope that helps.

Regards Mick
 
Last edited:
Upvote 0
It works Perfect:beerchug:
Thanks MickG . for spending your valuable time for right and perfect solution and making me learn many more things.
Thank you so much
NimishK
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
offset Combobox.ListIndex
Replies
1
Views
152
RoryA
RoryA
J
  • Question Question
Userform inconsistent responses
Replies
1
Views
194
Candyman8019
C
S
  • Solved
The value of combobox1 is not shown on combobox2 when changing
Replies
2
Views
242
sofas
S
M
  • Question Question
ComboBox List selection to trigger code
Replies
1
Views
459
MichaelRSnow
M
I
  • Question Question
Receive Automation error when running code & values added to sheet twice
2
Replies
13
Views
430
NoSparks
N

Forum statistics

Threads
1,223,909
Messages
6,175,310
Members
452,634
Latest member
cpostell

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