Add Items from inactive sheet to ActiveX ComboBox

[terrarium]

Hey Chandoo!


I'm trying to populate a activex box with dates that i've listed down a column on a sheet called "Data". This is my first time writing a form in ActiveX, though I've coded some macros.


What is wrong with what I've written? This returns Application-defined or object-defined error.


Thanks for your help, and have an EXCEL-lent holiday.


Private Sub UserForm_Activate()


Dim i As Integer


For i = 1 To Range(Sheets("Data").Range("A1"), Sheets("Data").Range("A1").End(xlDown)).Count


ComboBox1.AddItem Sheets("Data").Range("A1").Offset(i - 1, 0).Value


Next i


End Sub

 

[Luke M]

I don't believe that's the right event callout. Make sure your sub's header is correct (easiest way is to double click on the combo box, it should show you the change event code for your combo box. Code will look something like this:

[pre]

Private Sub ComboBox1_GotFocus()
Dim i as Integer
i = Range("A1").End(xlDown).Row
ComboBox1.ListFillRange = Range("A1", Cells(i, "A")).Address
End Sub

[/pre]

 

[memons@memon.com]

Hello One & All,


I m trying to bring corresponding value (Date) of Max Value of price of a Security (share).

Lets me put it this way. there are two columns. first with the Date and second with the Price. I m trying to get the date when the price of the security was Max. I write offset function in one of the field as =OFFSET(MAX(B2:B21),0,-1), consider that I m interested only in (B2:B21). It doesn't work. Could you please help.


Many Thanks!


Regards,

 

[Luke M]

Memons,

Please don't hijack threads. In the future, please start a new thread with your question.


Your formula is:

=INDEX(A2:A21,MATCH(MAX(B2:B21),B2:B21,0))

if dates are in a2:a21 and prices are in b2:b21.

 

[terrarium]

Thanks Luke. But how can i reference space on a different sheet?

 

[Luke M]

Add some worksheet objects.

[pre]

Private Sub ComboBox1_GotFocus()
Dim i as Integer
i = Worksheet("My Sheet").Range("A1").End(xlDown).Row
ComboBox1.ListFillRange = Worksheet("My Sheet").Range("A1", Cells(i, "A")).Address
End Sub

[/pre]

 

[memons@memon.com]

Sorry and Thanks Luke. I tried to start new thread but could not. I was under the impression that I m starting new threat. Sorry again.


Thanks for such a quick and nice help.


Regards,

Fayyaz Memon