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

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

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

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 now works fine, the code works fine BUT.......the values of the Drop Down boxes are not being copied but the row nubers that the value refers to is being copied over......i need the values from the boxes copying....any ideas for this?

Regards,
Simon

Yesterday, I answered your previous posting of this question.

--
regards,
Tom Ogilvy

"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=544076
>

This was answered yesterday.

--
Regards,
Tom Ogilvy

"Simon Lloyd" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
> now works fine, the code works fine BUT.......the values of the Drop
> Down boxes are not being copied but the row nubers that the value
> refers to is being copied over......i need the values from the boxes
> copying....any ideas for this?
>
> Regards,
> Simon
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=544076
>

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

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]...
>
> 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
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=544076
>

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

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]...
>
> 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
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=544076
>

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

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]...
>
> 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
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile:
http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=544076
>

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

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.

--
Regards,
Tom Ogilvy





"Simon Lloyd" wrote:

>
> 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
>
>
> --
> Simon Lloyd
> ------------------------------------------------------------------------
> Simon Lloyd's Profile: http://www.excelforum.com/member.php...fo&userid=6708
> View this thread: http://www.excelforum.com/showthread...hreadid=544076
>
>

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