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

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 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

--
Simon Lloy
-----------------------------------------------------------------------
Simon Lloyd's Profile: http://www.excelforum.com/member.php...nfo&userid=670
View this thread: http://www.excelforum.com/showthread.php?threadid=54407

This was answered yesterday.

--
Regards,
Tom Ogilvy

"Simon Lloyd"
wrote in message
...

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


--
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

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"
wrote in message
...

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

Yesterday, I answered your previous posting of this question.

--
regards,
Tom Ogilvy

"Simon Lloyd"
wrote in message
...

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