Help with combobox full with dates

[ColdGeorge]

Hi all
I have a userform with a combobox populated with following code

Private Sub UserForm_Initialize()
Dim rango, celda As Range
Set rango = Worksheets("actividad").Range("R75:R105")
For Each celda In rango
ComboBox1.AddItem celda.Value
Next celda
End Sub

Range R75:R105 is full with dates (dd/mm/yyyy), what I want to do is that when I click on a date of the combobox, for instance 01/01/2021 and when I hit the button Go. Select the cell having same date (01/01/2021). Any help will be appreciated.

ColdGeorge

 

[Fluff]

How about

Private Sub UserForm_Initialize()
   Me.ComboBox1.list = Sheets("actividad").Range("R75:R105").Value
End Sub

and in your button code

   Range("R" & Me.ComboBox1.ListIndex + 75).Select

 

[ColdGeorge]

Hi Fluff

It did work, thanks again.

ColdGeorge

 

[Fluff]

You're welcome & thanks for the feedback.

 

[ColdGeorge]

Hi all

Now, I'm requesting your help because my comboBox's range has changed to R91:R121 and, I think this piece of code: "Range("R" & Me.ComboBox1.ListIndex + 75).Select" has to be updated and, to tell the truth, I don't know how. Any help will be appreciated.

ColdGeorge

 

[Fluff]

Just change the 75 to 91

 

[ColdGeorge]

Hi Fluff

I already did. And, again, it works. Now, please, help me to understand how a comboBox works in order to fix this situation myself. Thanks in advance.

ColdGeorge

 

[Fluff]

The listindex property of the combo shows which item has been selected & it starts at 0 for the 1st item in the list, then 1 for the 2nd item etc.
So by adding the start row of the range to the listindex we get the relevant cell.

 

[ColdGeorge]

Hi Fluff

I really thank you for your clear explanation. Now I got it!

ColdGeorge

 

[Fluff]

Glad to help & thanks for the feedback.