Set ComboBox RowSource to Excel Spreadsheet UserForm Control

    Hello,



    I am trying to limit the amount of data is in hidden sheets in a workbook. I would like to transition some of that data to a userform containing the Excel Spreadsheet Control, and use that data as a RowSource for various ComboBoxes and ListBoxes. However I am unable to link the source data on the spreadsheet control to the comboboxes via the properties menu or through VBA code.


    Below is an example of what I am trying to do.


    Code
    Public Sub rwsrc()


Recall.ComboBox1.RowSource = RecallForm.Recall.Sheets("sheet1").Range("a1:a8")
End Sub


    Please let me know if this is even possible.


    Thanks

    Re: Set ComboBox RowSource to Excel Spreadsheet UserForm Control


    Try This..


    This code Populates the ComboBox with Data that is listed in A1 to A8 in Sheet 1

    Code
    Private Sub UserForm_Initialize()
With ComboBox1
.List = Sheets(1).Range("A1:A8").Value
End With
End Sub

    Re: Set ComboBox RowSource to Excel Spreadsheet UserForm Control


    Welcome to the forum. :)


    I'm afraid the short answer is no, you can't use a range on a spreadsheet control as the rowsource for another control on the form. You can however transfer the data from a spreadsheet control to other controls using VBA.


    Also, I assume you are aware that the spreadsheet control was discontinued from Office 2007 onwards so most people won't have it.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Re: Set ComboBox RowSource to Excel Spreadsheet UserForm Control


    Hi Rory,


    Thanks for the insight. I did try Dan's code and it worked in the sense that it didn't cause an error, but the combobox is still not showing any values in the list.
    Do you by chance know if there is a newer version of the spreadsheet control, or an alternative / comparable control?

    Re: Set ComboBox RowSource to Excel Spreadsheet UserForm Control


    No, there isn't an updated version. To load your data you would need something like this:


    Code
    Public Sub rwsrc()      
    Recall.ComboBox1.List = RecallForm.Recall.Sheets("sheet1").Range("a1:a8").Value
End Sub

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login