If you want to write code with code,
See Chip Pearson's site on programming in the VBE

http://www.cpearson.com/excel/vbe.htm

to add a combobox from the control toolbox toolbar

Sub ABC()
Dim cbox As MSForms.ComboBox
Dim OleObj As OLEObject
With ActiveSheet.Range("B9")
Set OleObj = ActiveSheet.OLEObjects.Add( _
ClassType:="Forms.ComboBox.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=.Left, _
Top:=.Top, _
Width:=.Width, _
Height:=.Height)
OleObj.ListFillRange = "'" & ActiveSheet.Name _
& "'!A1:A20"
Set cbox = OleObj.Object
End With

End Sub

--
Regards,
Tom Ogilvy

"Nigel" wrote in message
...
Ok I am using code to copy the data from one sheet into another sheet on a
new workbook, I was looking for some code that that I could add to my
current
code that would automatically create a combo box on the new worksheet
based
on the following information

The rows for the combo box are in A1 to A 50 and the code behind the
combo box is

NewSheet = Range("B1")
Sheets(NewSheet).Select


"Tom Ogilvy" wrote:

It isn't clear what you are doing. Do you want to set up a new workbook
using code - this activity will be part of your application. Or are you
just creating a new workbook manually and want to know how to copy the
combobox and the code associated with the combobox.

You can copy a combobox by selecting it and doing edit=copy, then
select
the new sheet and do edit=Paste

for code, you can go to the code, highlight it, and do Ctrl+C. Go to
the
new module/location and click in the module, then do Ctrl+V

If it is more complex than that, then provide some details. For
instance,
the Control Toolbox Toolbar has a combobox, as does the Forms toolbar
and
you can get a dropdown in a cell with the list option of Data
validation.
The first two can have code associated with them, but in each case, the
location of the code would be different.

So the more information you provide, the better people can provide
assistance.

--
Regards,
Tom Ogilvy


"Nigel" wrote in message
...
OK I think I have the issue, I am moving\Copying the sheet to a new
workbook,
If I just copy the data and paste into the new sheet it is much
smaller,

My dilema now is I need to have the combo box created on the new shet
and
the code added behind the scene

The rows for the combo box are in A1 to A 50 and the code behind the
combon
box is

NewSheet = Range("B1")
Sheets(NewSheet).Select

How would I add the combo box and the code to the new sheet

thanks

"Tom Ogilvy" wrote:

On the sheet, do edit=Goto=special and select Last Cell. Do you
end
up
where you expected or are you way past where your data ends.

If you ended up well past where your data ends, then see Debra
Dalgleish's
site for some hints:
http://www.contextures.com/xlfaqApp.html#Unused

--
Regards,
Tom Ogilvy


"Nigel" wrote in message
...
I did some investigation and it is a new worksheet I added that is
causing
the size increase, strangely tho the worksheets has 1 list box and
1
column
of data which is only 55 names
I Exported the offending sheet to a new workbook and deleted
everything
from
it, all the code everything

this one sheet is increasing the size of the workbook by over 3.6
MB

Anyone got any ideas

Thanks

"Vacation's Over" wrote:

lines of code are small

what may happen is that in developing those final lines some
garbage
may
collect.
try a code cleaner

http://www.appspro.com/Utilities/CodeCleaner.htm



"Nigel" wrote:

I added some code to a worksheet, a macro and a list box and
it
increased the
size of the worksheet quite substantially, is there anyway to
prevent
just a
few lines of code increasing spreadsheet size