Visual Basic Question

[Fire_Chief]

I am trying to learn visual Basic and i want to create a data base control ( on a form) that will get information from am excel spreadsheet. The books I have bought are very specific about getting info from an access data base but nothing about excel. Is this possible and if so is there anyone in here that might be able to help me?

Marty

D1A@aol.com

 

[anvil19]

Hi Marty

Yes there are a number of ways to get data into a control in Excel. The easier way is to use the controls' RowSource property. Something like this :

Add a ComboBox control onto a userform and add this to the forms Activate Event...

Private Sub UserForm_Activate()
    Me.ComboBox1.RowSource = "Sheet1!Dates"
    Me.ComboBox1.Text = "Start!"
End Sub

This code adds the data from a range called "Dates". If you have a different range of values for each use of the form or if you have no named ranges at all then just substitute the "Sheet1!Dates" value for "A5:G5" for example.

The

Me.ComboBox1.Text = "Start!"

bit is just to add a first line to your list of values, but will fall away when you pick the first value from the list.


If the range of data is different each time then it may be best to add a dynamic reference to the range of values you are wanting to use, like so :


Select the cells you wish to use and run this macro...

Private Sub LoadCombo1()
    Dim c
    Dim i As String
    
    i = ActiveWindow.RangeSelection.Address
    
    For Each c In Range(i).Cells
    Me.ComboBox1.AddItem c.Value
    Next c
    Me.ComboBox1.Text = "Start!"
End Sub

Try That.

anvil19

 

[Fire_Chief]

Thank you for your response.

I understand how to do it when working with excel modules however I am trying to get it to work when using a data control in "VISUAL BASIC" not VBA. Can you help me with this?

Marty

D1A@aol.com