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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Simon Lloyd - 21 May 2006 17:44 GMT

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

Signature

Simon Lloyd

Reply to this Message

WhytheQ - 21 May 2006 17:53 GMT

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

Reply to this Message

Simon Lloyd - 21 May 2006 18:12 GMT

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

Reply to this Message

Tom Ogilvy - 21 May 2006 18:26 GMT

This was answered yesterday.

Signature

Regards,
Tom Ogilvy

> Hi, I have sorted the first error message i got, it was a space missing
> where it says "Drop Down"&i it needed a space after the word down, that
[quoted text clipped - 5 lines]
> Regards,
> Simon

Reply to this Message

Simon Lloyd - 21 May 2006 19:19 GMT

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

Signature

Simon Lloyd

Reply to this Message

Tom Ogilvy - 21 May 2006 19:58 GMT

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?

Signature

Regards,
Tom Ogilvy

> 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
[quoted text clipped - 5 lines]
> Regards,
> Simon

Reply to this Message

Simon Lloyd - 21 May 2006 20:46 GMT

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

Signature

Simon Lloyd

Reply to this Message

Tom Ogilvy - 21 May 2006 21:03 GMT

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

Signature

Regards,
Tom Ogilvy

> 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
[quoted text clipped - 7 lines]
> Regards,
> Simon

Reply to this Message

Simon Lloyd - 22 May 2006 00:49 GMT

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

Signature

Simon Lloyd

Reply to this Message

Tom Ogilvy - 22 May 2006 02:06 GMT

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.

Signature

Regards,
Tom Ogilvy

> 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
[quoted text clipped - 7 lines]
> Regards,
> Simon

Reply to this Message

Simon Lloyd - 22 May 2006 12:30 GMT

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

Signature

Simon Lloyd

Reply to this Message

Tom Ogilvy - 22 May 2006 16:23 GMT

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.

Signature

Regards,
Tom Ogilvy

> 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
[quoted text clipped - 9 lines]
> Regards,
> Simon

Reply to this Message

Simon Lloyd - 22 May 2006 23:24 GMT

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

Signature

Simon Lloyd

Reply to this Message

Tom Ogilvy - 21 May 2006 18:12 GMT

Yesterday, I answered your previous posting of this question.

Signature

regards,
Tom Ogilvy

> 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
[quoted text clipped - 35 lines]
> Next
> End Sub

Reply to this Message