Combo box problem

rjc4

Well-known Member
Joined
Nov 6, 2004
Messages
502
Hi All,
I've struck a problem with my combo box not inserting the correct data.
Can anyone see where the code may be failing, please.

My Combobox4 has a fill range of $U$26:$Y$120.
I wish to select one of the entries from the drop down and have the Code letter and Number inserted in G11 and H11 respectively.

My headings in U26:Y26 are:
Code Letter, No, Event City, Time, Competitors

This is a sample of the data in the cells.
Lines 26 to 30.
BR,1,Brazil,1.00,6
MA,1,Madrid,1.30,9
RO,1,Rome,2.15,7
PA,2,Paris,3.05,9
LO,2,London,4.15,8
etc.

This is the code for the combo box:-
Private Sub ComboBox4_Change()
With Me.ComboBox4 'inserts Code letter and number in from combobox.
If .ListIndex > -1 Then
Me.Cells(11, "G") = .Value 'target cells and indexed on col 2
Me.Cells(11, "H") = Me.Cells(.ListIndex + 26, "v") '26 is Row, v is column.
End If
End With
End Sub

The problem is, that when I select say PA 2 from my samples above, the PA is inserted in G11 ok but the number reverts to 1 not 2. It seems that the macro is extracting from the first number cell on row 26, which in my example contains 1. It must be something to do with duplicates of the 1 but I don't know if this is so.

Could anyone help with a fix. Sorry to be so long winded with the description.

Thankyou in advance,
Cheers,
RC
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi
try
Code:
Private Sub ComboBox4_Change()
With Me.ComboBox4         'inserts Code letter and number in from combobox.
    If .ListIndex > -1 Then           
        Me.Cells(11, "G") = .Value    'target cells and indexed on col 2
        Me.Cells(11, "H") = .List(.ListIndex , 1)         '26 is Row, v is column.
    End If
End With
End Sub
 

rjc4

Well-known Member
Joined
Nov 6, 2004
Messages
502
Thanks jindon,
Tried that but no change. Still gets wrong number in H11.
Cheers, :(
RC



Hi
try
Code:
Private Sub ComboBox4_Change()
With Me.ComboBox4         'inserts Code letter and number in from combobox.
    If .ListIndex > -1 Then           
        Me.Cells(11, "G") = .Value    'target cells and indexed on col 2
        Me.Cells(11, "H") = .List(.ListIndex , 1)         '26 is Row, v is column.
    End If
End With
End Sub
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
rjc4

Are you sure that the number is wrong?
try
Code:
Private Sub ComboBox4_Change()
With Me.ComboBox4         'inserts Code letter and number in from combobox.
    If .ListIndex > -1 Then           
        Me.Cells(11, "G") = .Value    'target cells and indexed on col 2
        Me.Cells(11, "H") = Val(.List(.ListIndex , 1))
    End If
End With
End Sub
 

rjc4

Well-known Member
Joined
Nov 6, 2004
Messages
502

ADVERTISEMENT

Hi again, jindon.
I've also got this code in my sheet as well.

Private Sub Worksheet_Activate()
With Me.ComboBox4
.ListIndex = -1
End With
End Sub

Is there a clue there. Also, when I click a selection from the drop down, I can see the correct data is loaded into the cells but as soon as I run another macro, the number changes to an incorrect one. I can't see any reference to G11 or H11 in my subsequent macro to cause this.

Having said that, I've just checked the drop down selections as I'm typing this and would you believe it is all working fine using my original code. The only thing I've done is copy the bit of code above into this typing box and it is still highlighted in the module. Do you have any idea what would cause this. I'll bet if I close my sheet and reopen it, it will fail again. It always does. So why is it suddenly working now. Beats me!



Cheers,
RC

Thanks jindon,
Tried that but no change. Still gets wrong number in H11.
Cheers, :(
RC



Hi
try
Code:
Private Sub ComboBox4_Change()
With Me.ComboBox4         'inserts Code letter and number in from combobox.
    If .ListIndex > -1 Then           
        Me.Cells(11, "G") = .Value    'target cells and indexed on col 2
        Me.Cells(11, "H") = .List(.ListIndex , 1)         '26 is Row, v is column.
    End If
End With
End Sub
 

rjc4

Well-known Member
Joined
Nov 6, 2004
Messages
502
Thanks jindon,
Using your code below, it now seems to be working ok. But also my original code is working ok as well. This has happened before, that is, it all works ok as expected and the next day I'll continue to use the sheet and it plays up again. What I'll do now is try again tomorrow using your latest change and see if that small change has introduced stability.

I'll let you know what happens,
Thanks again,
Cheers,
RC


rjc4

Are you sure that the number is wrong?
try
Code:
Private Sub ComboBox4_Change()
With Me.ComboBox4         'inserts Code letter and number in from combobox.
    If .ListIndex > -1 Then           
        Me.Cells(11, "G") = .Value    'target cells and indexed on col 2
        Me.Cells(11, "H") = Val(.List(.ListIndex , 1))
    End If
End With
End Sub
 

rjc4

Well-known Member
Joined
Nov 6, 2004
Messages
502
Hi jindon,
Below is the code you gave me yesterday. It has seemingly solved my problem for now. Thank you very much.
Cheers,
RC :biggrin:


rjc4

Are you sure that the number is wrong?
try
Code:
Private Sub ComboBox4_Change()
With Me.ComboBox4         'inserts Code letter and number in from combobox.
    If .ListIndex > -1 Then           
        Me.Cells(11, "G") = .Value    'target cells and indexed on col 2
        Me.Cells(11, "H") = Val(.List(.ListIndex , 1))
    End If
End With
End Sub
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Hi again, jindon.
I've also got this code in my sheet as well.

Private Sub Worksheet_Activate()
With Me.ComboBox4
.ListIndex = -1
End With
End Sub

Is there a clue there. Also, when I click a selection from the drop down, I can see the correct data is loaded into the cells but as soon as I run another macro, the number changes to an incorrect one. I can't see any reference to G11 or H11 in my subsequent macro to cause this.

Having said that, I've just checked the drop down selections as I'm typing this and would you believe it is all working fine using my original code. The only thing I've done is copy the bit of code above into this typing box and it is still highlighted in the module. Do you have any idea what would cause this. I'll bet if I close my sheet and reopen it, it will fail again. It always does. So why is it suddenly working now. Beats me!



Cheers,
RC

Thanks jindon,
Tried that but no change. Still gets wrong number in H11.
Cheers, :(
RC



Hi
try
Code:
Private Sub ComboBox4_Change()
With Me.ComboBox4         'inserts Code letter and number in from combobox.
    If .ListIndex > -1 Then           
        Me.Cells(11, "G") = .Value    'target cells and indexed on col 2
        Me.Cells(11, "H") = .List(.ListIndex , 1)         '26 is Row, v is column.
    End If
End With
End Sub

Hi
RC
As your data source was set by Property box in your 1st post, it should not be changed, unless the other code changes its setting.
 

Forum statistics

Threads
1,136,706
Messages
5,677,309
Members
419,685
Latest member
hennLow

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
Top