Can't assign RowSource property 1

[Trudye]

The book says this should work:

Private sub obMonths_Click()
Listbox1.RowSource = "Sheet1!Months"
end sub

I tried setting Months
Set months = Range("a1:a25")
received error:Obj doesn't support property or method


I tried to assign the range a Name
Names.Add Name:="Months", RefersTo:="=Sheet3!$A$1:$A$25"
recieved error on listbox statement:Obj doesn't support property or method

I tried
ListBox1.RowSource = Range("a1:a25") 'got type mismatch

The form is on sheet#5, the list of Months is on sheet1. How do I reference the list on sheet1?

Thanks,
Trudye

 

[petrosky]

Hi there,

I just tried this and had no problems.

What is obMonths your listbox? If so, I tried that and it did not work.

The userform click event did work, as did putting the code below any command button.

Listbox1.RowSource = "Sheet1!Months"

I hope this helps.

Peter

Remember- It's nice to be important,
but it's important to be nice

 

[Trudye]

Thanks Petrosky for responding. optmonths is just the name of a option button. The form contains 3 option buttons and a listbox. The obj of the exercise is: based on the optin button you click a list (that exists on a worksheet) associated with that button will appear in the listbox.

The list of months is located on sheet1 of the active workbook.

The err msg (Obj doesn't support property or method) seems to state that the syntax is wrong as opposed to not being able to find the data.

Thanks again,
Trudye

 

[SkipVought]

Private Sub OptionButton1_Click()
   ListBox1.RowSource = [b]"Months"[/b]
End Sub

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[petrosky]

Hi Trudye,

It is a little messy but this code accomplishes what you want (I think)

Private Sub Months_Click()
Me.ListBox1.Clear
Dim cell As Range
    Dim Rng As Range
    
    With ThisWorkbook.Sheets("Sheet1")
        Set Rng = .Range("a1", .Range("a1").End(xlDown))
    End With
    
    For Each cell In Rng.Cells
        Me.ListBox1.AddItem cell.Value
    Next cell
End Sub

If you had days in the range sheet1!b1:b7, simply change the click event of the option button days to reflect this line.

Set Rng = .Range("b1", .Range("b1").End(xlDown))

Hope this helps.

Peter

Remember- It's nice to be important,
but it's important to be nice

 

[Trudye]

Thanks Skip but I can't do "Months", I have to point to " Sheet1" the active sheet is sheet5. Thanks for hanging in there with me.

Thanx Peter you code worked. It is frustrating when you are trying to learn something and the examples in the book don't work. Unfortunately this is not the first example in the book that did not work.

Thanks again,
Trudye

 

[SkipVought]

It does not matter what sheet is active as long as you do not have more than ONE range named Months

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[Trudye]

Hi Skip, I tried the command you suggested and I got the list of random numbers I created and placed on worksheet3 rather than the list of months on sheet1.

Be well,
Trudye

 

[SkipVought]

Do you have TWO ranges named Months???

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[Trudye]

I recreated the form (including the listbox) in a seperate workbook. I put the months list on wksheet1 and the random list on wrksheet2.

when I ran I got the msg: Could not set rowsource property, Invalid property value.

The only code I had in the procedure was your one line of code. I have the listbox's first option (optMonths) set to "Months".

I'm sure it's something I'm doing wrong, but I'm not going to worry about it I've move on to much tougher stuff. That was chapter 11 I'm now on chapter 15.

Thanks for all your help.

Trudye