Copying contents of w/s drop down boxes to another sheet and creating a named range?

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

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

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

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" <Simon.Lloyd.282e2n_1148058604.6557@excelforum-nospam.com>
wrote in message
news:Simon.Lloyd.282e2n_1148058604.6557@excelforum-nospam.com...
>
> 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

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(external:=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(external:=True)
d1.ListFillRange = Worksheets("UserData").Range( _
Worksheets("Stats").TextBox2.Text).Address(external:=True)
End With
End Sub

--
Regards,
Tom Ogilvy


"Simon Lloyd" <Simon.Lloyd.282g5m_1148061302.5306@excelforum-nospam.com>
wrote in message
news:Simon.Lloyd.282g5m_1148061302.5306@excelforum-nospam.com...
>
> 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
>