View original (French)

VBA: issue filling textbox based on a combobox

Solved
clemsz -  
 clemsz -
Hello everyone,

I am a beginner in VBA and I'm turning to you hoping to get some help.

So I have a little problem that I think will seem quite simple to you but is personally complicating my work a lot...

Let me explain:
I have a userform that contains a combobox1 and a textbox1.
In my combobox1 are dates (of type 01/01/2014... etc) that correspond to the payment date of the loan installments.

I would like my textbox1 to display the value of the remaining capital (column J) based on the selected date.

I entered this formula:

Private Sub ComboBox1_Change()

Dim range As Range
'Sheets("Tableau Seul").Select
Set range = Sheets("Tableau Seul").Range("K14:K100")
range.Select

e = range.Row
TextBox1 = Sheets("Tableau Seul").Range("J" & e).Value

End Sub

But the problem is that the value appearing in the textbox remains the same (the one from my cell J14), even when I change the date... I think the error comes from my last 2 lines with the "e" but I can't figure out where it goes wrong...

I can eventually send my Excel file if I haven't been clear enough!

So if someone has a solution or a correction to propose, I would be very grateful!

Thank you in advance,
Best regards

1 réponse

Réponse 1 / 1
f894009 Posted messages 17417 Registration date   Status Membre Last intervention   1 717
 
Hello,

If the dates and remaining capital values are logically stacked: 

Private Sub ComboBox1_Change()
 Dim range As Range
 Set range = Sheets("Tableau Seul").Range("J14:J100")
 TextBox1 = range(1 + ComboBox1.ListIndex, 1)
End Sub
4
clemsz
 
That's perfect, thank you very much, you're saving me!

quick and efficient response ;)

wish you all the best!
0