Unable to get the dropdowns property of the worksheet class???

[Simon Lloyd]

Hi all, i as kindly given the code below by Tom Ogilvy but when i try to
run it i get Runtime 1004: Unable to get the dropdowns property of the
worksheet class, any ideas what is causing this?, the line in blue is
where the code stops and gives the error.

Regards,
Simon.

Sub coursetrans()
Dim i As Long, rng As Range
Dim d As DropDown, d1 As DropDown
Dim j As Long ', i As Long
Set rng = Worksheets("Collected Data").Cells(Rows.Count,
1).End(xlUp)(2)
j = 0
k = 0
For i = 102 To 155
Set d = Worksheets("User Entry").DropDowns("Drop Down" & i)
rng.Offset(j, k).Value = d.Value

j = j + 1
If j > 17 Then
j = 0
k = k + 1

d.ListFillRange = Worksheets("Collected Data").TextBox2.Text

With Worksheets("Collected Data")
d = Worksheets("User Entry").DropDowns("Drop Down 154")
d1 = Worksheets("User Entry").DropDowns("Drop Down 155")
..Range("M1").Value = d.Value
..Range("M8").Value = d1.Value
d.ListFillRange = Worksheets("User Entry").TextBox2.Text
d1.ListFillRange = Worksheets("User Entry").TextBox2.Text

End With
End If
Next
End Sub

 

[WhytheQ]

i can't see any blue!
looking at the error message it looks like the code might be looking
for a dropdown which doesn't exist on the worksheet?

J

 

[Tom Ogilvy]

Yesterday, I answered your previous posting of this question.

--
regards,
Tom Ogilvy

"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...

 

[Simon Lloyd]

Hi, I have sorted the first error message i got, it was a space missin
where it says "Drop Down"&i it needed a space after the word down, tha
now works fine, the code works fine BUT.......the values of the Dro
Down boxes are not being copied but the row nubers that the valu
refers to is being copied over......i need the values from the boxe
copying....any ideas for this?

Regards,
Simo

 

[Tom Ogilvy]

This was answered yesterday.

--
Regards,
Tom Ogilvy

"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...

 

[Simon Lloyd]

Sorry Tom this may seem a pain to you......but the values copied over
are the row numbers not the Drop Down values....and when it copies the
first 18 it changes the last drop down boxes format to have the input
range named the same as TextBox2 it also does this with the last
box..........what i really needed to happen was all the new data in the
three coulmns to be the name range.

Regards,
Simon

 

[Tom Ogilvy]

I fixed the Value vice index in my post yesterday.



for the source of the dropdowns, you mean you want each to load with the 54
items copied to the 3 column by 18 row area just created?

--
Regards,
Tom Ogilvy




"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...

 

[Simon Lloyd]

Yes Tom, I want all 54 items to be in the range named by the text in
textbox2, they do need to be put in to the three columns as you have
already managed.....the placing was perfect, it was just the values
that it loaded and the range naming that didnt work as i imagined, but
i agree that it was my ineptitude at explaining that caused this.

Any further help you feel you could impart would be greatly
appreciated

Regards,
Simon

 

[Tom Ogilvy]

Private Sub commandButton1_click()
Dim i As Long, rng As Range
Dim d As DropDown, d1 As DropDown
Dim j As Long, v As Variant
Set rng = Worksheets("Stats").Cells(Rows.Count, 1).End(xlUp)(2)
j = 0
k = 0
For i = 1 To 54
Set d = Worksheets("UserData").DropDowns("Drop Down " & i)
rng.Offset(j, k).Value = d.List(d.Value)
j = j + 1
If j > 17 Then
j = 0
k = k + 1
End If
d.ListFillRange = ""
Next
ReDim v(1 To 54)
i = 0
For Each cell In rng.Resize(18, 3)
i = i + 1
v(i) = cell.Value
Next
For i = 1 To 54
Set d = Worksheets("UserData").DropDowns("Drop Down " & i)
d.List = v
Next
With Worksheets("Stats")
Set d = Worksheets("UserData").DropDowns("Drop Down 55")
Set d1 = Worksheets("UserData").DropDowns("Drop Down 56")
.Range("M1").Value = d.List(d.Value)
.Range("M8").Value = d1.List(d1.Value)
End With
End Sub

--
Regards,
Tom Ogilvy

"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...

 

[Simon Lloyd]

Thanks Tom worked a treat.....Well once anyway!, it seems that when the
code is run it removes all the input ranges from all the boxes? then
during the code execution it halts at this line d.List = v but the Drop
Down box contents were transferred to the required area, i think the
code halted because there were no longer any values in the boxes!

Could you give this one last look please........if it works fine for
you then it must be something i have done.....for which i apologise!

Regards,
Simon

 

[Tom Ogilvy]

It worked over and over for me as long as there was a value selected in each
dropdown box.

Certainly no error on the line you cite.

It does remove the input ranges from all the boxes because you said you
wanted to use the 18 row by 3 column range just written as the new source
for the dropdown boxes. You can only use a single column as the
ListFillRange source, so, since you don't have a single column, instead I
put those values in a 1 dimensional array and assign it to the boxes - so
they do have a list, but it isn't tied to a range.

--
Regards,
Tom Ogilvy

"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...

 

[Simon Lloyd]

Tom, your last post cleared some things up for me.......the array that
you set up is the range i wanted to be named by the text in TextBox2, i
ideally wanted the input range that i set up for the boxes to remain
intact, but i suppose i could add some code to repopulate the input
ranges back to what they were. The error message i get at the line i
mentioned is Runtime 1004: Unable to get the List properties of the
DropDown Class, this appears after it has cleared the range from the
Drop Down boxes 1 to 54.

Tom i really appreciate your patience with me.

Regards,
Simon

 

[Guest]

If you want the input range to remain intact, then remove the line of code
that clears it and the code that builds the array and assigns it to the
boxes. Since you previously said you wanted the dropdown boxes to have a
source range of the range named in textbox2 and you then said you wanted the
name in textbox2 to refer to the just written data and since using an
offsheet named range doesn't seem to work for listfillrange, then I used the
date written directly. Again, if that is not what you want, remove the code
that does that.

If you want to create a named range out of the just written data, then add a
line that does

rng.Resize(18,3).Name = "Smurf"

or
rng.Resize(18,3).Name = Worksheets("Stats").Textbox2.Value

or whatever does what you want.

 

[Simon Lloyd]

Thanks for your time and trouble Tom, i have understood what you have
described and will do that when im back at work!

Again thanks for your patience.

Regards,

Simon