Hi all,

I have quiet a few dropdown boxes on my w/s (combo boxes, 56 in all!),
i have assigned named ranges for the data in each box but i have not
done anything with linked cell (as i dont know what this does) what i
would like to do is click my command button and have all dropdown box
data copied down from the next availabe cell where i will use the
ActiveCell.Offset(x,x) to place the data where i want after
that........so i need help with the dropdown copy contents and paste to
lets say sheet3, but after contents have been pasted i would like all
the NEW data to be a named range using the text in the TextBox2 on the
w/s...........is this possible?

Hope you can help!

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

If a combobox is continually associated with a specific cell, then the value
displayed in the combobox will also display in the cell specified in the
linked cell property.

Other than that, your description is not completely clear. Also, you use
both the term dropdown and combobox. Dropdown boxes are in the forms toolbar
and are also called comboboxes. Control toolbox toolbar controls also
include a combobox control. And some call the data validation dropdown under
the list option as a Combobox. You might specify what you are using.

You say sheet3, but is sheet3 the sheet with the commandbutton and the
comboboxes. If it isn't, what is the concept of activecell you allude to
since the activecell will be on the sheet with the commandbutton. Assuming
the commandbutton, textboxes and comboboxes (all control toolbox toolbar) are
on sheet3 then:

Private Sub commandButton1_click()
Dim i as Long, s as String
for i = 1 to 56
s = activesheet.OleObjects("Combobox" & i).Object.Value
activecell.offset(i-1,0).value = s
activesheet.OleObjects("Combobox" & i).ListFillRange =
activesheet.Textbox2.Text
Next
end sub

--
Regards,
Tom Ogilvy





--
Regards,
Tom Ogilvy


"Simon Lloyd" wrote:


Hi all,

I have quiet a few dropdown boxes on my w/s (combo boxes, 56 in all!),
i have assigned named ranges for the data in each box but i have not
done anything with linked cell (as i dont know what this does) what i
would like to do is click my command button and have all dropdown box
data copied down from the next availabe cell where i will use the
ActiveCell.Offset(x,x) to place the data where i want after
that........so i need help with the dropdown copy contents and paste to
lets say sheet3, but after contents have been pasted i would like all
the NEW data to be a named range using the text in the TextBox2 on the
w/s...........is this possible?

Hope you can help!

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

Tom, thanks for the reply and my apologies for the murky
description.....the dropdown boxes were created from the forms toolbox
and the command button is on the sheet that has all those boxes, i was
describing using the Activecell.Offset because i thought copy from
dropdown box select sheet 3 Range("A1").Select,
Selection.End(xlDown).Select and then paste, the first 18 values i was
going to paste one under the other the next 18 in the column next to it
and the 3rd 18 the next column to that, the remaining 2 boxes would be
pasted to a different location. I am working on a sheet called "User
Data" and want the values of the boxes copied and pasted to a sheet
called "Stats".

Hope this is a little clearer, probably going the wrong way with trying
to capture this data in 3 seperate columns from values chosen in the
boxes!

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

Private Sub commandButton1_click()
Dim i as Long, rng as Range
Dim d as Dropdown, d1 as Dropdown
Dim j as Long, i as Long
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.Value
j = j + 1
if j 17 then
j = 0
k = k + 1
d.ListFillRange = Worksheets("Stats").Textbox2.Text
Next
With worksheets("Stats")
d = Worksheets("UserData").Dropdowns("Drop Down 55")
d1 = Worksheets("UserData").Dropdowns("Drop Down 56")
.Range("M1").Value = d.Value
.Range("M8").Value = d1.Value
d.ListFillRange = worksheets("UserData").TextBox2.Text
d1.ListFillRange = worksheets("UserData").TextBox2.Text
End With
end sub

Obviously the success of this approach depends on the names of your
dropdowns.
Adjust to suit
--
Regards,
Tom Ogilvy


"Simon Lloyd" wrote:


Tom, thanks for the reply and my apologies for the murky
description.....the dropdown boxes were created from the forms toolbox
and the command button is on the sheet that has all those boxes, i was
describing using the Activecell.Offset because i thought copy from
dropdown box select sheet 3 Range("A1").Select,
Selection.End(xlDown).Select and then paste, the first 18 values i was
going to paste one under the other the next 18 in the column next to it
and the 3rd 18 the next column to that, the remaining 2 boxes would be
pasted to a different location. I am working on a sheet called "User
Data" and want the values of the boxes copied and pasted to a sheet
called "Stats".

Hope this is a little clearer, probably going the wrong way with trying
to capture this data in 3 seperate columns from values chosen in the
boxes!

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

Thanks again Tom,

I will start incorporating this, one question?......will this add data
under the present data every time the User Data w/s is used i.e no loss
of data on Stats, probably sounds like a dumb question to you, but
eventually there will be 3,000 rows 3 column across of data with each
section of data having its own named range!, so just wanted to make
sure.

Thanks,

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

this line

set rng = Worksheets("Stats").Cells(rows.count,1).End(xlup)( 2)

will find the last used cell in column A (each time you run it) and then
offset to the first blank cell. So it should not overwrite data as long as
you have data in column A. Now that is not true for the 55/56th Drop Down
boxes for which you did not specify a location or any rules.

--
Regards,
Tom Ogilvy

"Simon Lloyd"
wrote in message
...

Thanks again Tom,

I will start incorporating this, one question?......will this add data
under the present data every time the User Data w/s is used i.e no loss
of data on Stats, probably sounds like a dumb question to you, but
eventually there will be 3,000 rows 3 column across of data with each
section of data having its own named range!, so just wanted to make
sure.

Thanks,

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

Hi Tom,
Ran your code and got the message below i have checked the names of the
Drop Down boxes and they start at 102 up to 157 so i made the changes
for box 55 and 56 and in your For i = statement, but still gives the
error below
any ideas?

Runtime 1004: Unable to get the DropDowns property of the worksheet
class

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

I think the main problem is I didn't have a space after the Down. So
"Drop Down" & i
should have been

"Drop Down " & i

I have tested this code and it worked for me:

Private Sub commandButton1_click()
Dim i As Long, rng As Range
Dim d As DropDown, d1 As DropDown
Dim j As Long
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 = Worksheets("UserData").Range( _
Worksheets("Stats").TextBox2.Text).Address(externa l:=True)
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)
d.ListFillRange = Worksheets("UserData").Range( _
Worksheets("Stats").TextBox2.Text).Address(externa l:=True)
d1.ListFillRange = Worksheets("UserData").Range( _
Worksheets("Stats").TextBox2.Text).Address(externa l:=True)
End With
End Sub

--
Regards,
Tom Ogilvy


"Simon Lloyd"
wrote in message
...

Hi Tom,
Ran your code and got the message below i have checked the names of the
Drop Down boxes and they start at 102 up to 157 so i made the changes
for box 55 and 56 and in your For i = statement, but still gives the
error below
any ideas?

Runtime 1004: Unable to get the DropDowns property of the worksheet
class

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