Hi All,

I have designed a jobsheet in excel and am using a user form to pick
parts from a list located on a different worksheet.

I have a quantity box on there with a spinbutton and when a control
button is pressed the parts and quantities are updated on the next
available empty line on my job sheet. I can manually type into the
combo box and all works great, but I cannot get the box to pick up any
parts from the worksheet.

The parts list will be modified frequently by others so I need to add
all items untill it comes across a blank line, or perhaps a flag at the
end of the list would be better???

Also, it would be handy if the pick list could jump to a section by
pressing the first letter key on the keyboard, can a combo box do that?

Heres what I have that doesnt work!

Private Sub cboPartsused_Click()


ActiveWorkbook.Sheets("temp parts").Activate
Range("A2").Select

Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
AddItem.ActiveCell.Value
End If
Loop Until IsEmpty(ActiveCell) = True


End Sub


Now just to make things more complicated!

I have wrote macros that enable the job sheet to be closed without
prompting to be saved and before it closes, a new sheet is opened and
the values copied and pasted into it so the macros are not copied. Then
the filename contains the job number and date time stamp so the
filenames are not duplicated, and the files are saved into another
folder on the desktop.

This can be printed "on site" and given to the customer.

I would like all the info to go to another almost duplicate worksheet
except that on this one, the contents of 2 more columns (prices etc)
are placed in the jobsheet for invoicing purposes, but I dont want the
prices to appear in the combo box, only the colum A containing part
descriptions.

Can anyone out there help?

I am totally new to this VBA programming. The last time I programmed
anything was 15 years ago - basic and 6502!! I'm slowly getting back
into programming!

Thanks in advance!

Hi Kev,

for filling combobox with values you have in excel range (a2 .. last
populated row in column a) use something like this:

Private Sub cboPartsused_Click()
dim rng as range
set rng = range("a2")
userform1.combobox1.list=range(rng.address,rng.End (xlDown).Address).value
End Sub

Regards,
Ivan

Thanks for you Reply Ivan,

Would you be so kind as to explain how this works, a little so I might
adapt it into my sub?

(I'm easilly confused at the moment but working on it)

Thanks,

Kev

Hi Kev,

userform1 'name of the userform
combobox1 'name of the combobox
list 'property of the combobox, returns or sets the list entries of a
ListBox or ComboBox

rng 'object which is set to range("a2") - this is the cell which
contains first entry, that should be placed in the combobox (change to
suit your needs)

range(rng.address,rng.End(xlDown).Address).value 'rng.End(xlDown) -
finds the last cell below rng before empty cell, this is used to
construct range from a2 till the last non-empty cell bellow a2,
value(s) from this range are used to fill the combobox1

Please let me know if something is not clear.

Regards,
Ivan

Thank you both for you help.

I understand how it works, I dont know what I am doing wrong but I
cannot get it to work!!

Heres what I have there right now
Private Sub cboPartsused_Click()

'ActiveWorkbook.Sheets("temp parts").Activate
'AddItem.Range = Cells("a2:a12")
'ActiveWorkbook.Sheets("temp parts").Activate
'Range("A2").Select

'Do
'If IsEmpty(ActiveCell) = False Then
'ActiveCell.Offset(1, 0).Select
'AddItem.ActiveCell.Value
'End If
'Loop Until IsEmpty(ActiveCell) = True

'Dim rng As Range
'Set rng = Range("a2")
'cboPartsused.List = Range(rng.ActiveWorkbook.Sheets("temp parts"),
rng.End(xlDown).ActiveWorkbook.Sheets("temp parts")).Value

Dim rng As Range
Set rng = Range("a2")
Me.cboPartsused.List = Range(rng.Sheets("temp parts"),
rng.End(xlDown).Sheets("temp parts")).Value


End Sub

I have tried various methods and have ' them out and will obviously
dump them once this is de-bugged.

Have I got the address wrong?

Thank you.

Kev

Hi Kev,

if "temp parts" is name of sheet on which you have the list and a2 is
cell with first item of the list, then:

Dim rng As Range
Set rng = worksheets("temp parts").Range("a2")
Me.cboPartsUsed.List = Range(rng.Address,
rng.End(xlDown).Address).Value

Regards,
Ivan

I think it should be

Private Sub cboPartsused_Click()
Dim rng As Range
Set rng = Range("a2")
Me.cboPartsUsed.List = Range(rng.Address, rng.End(xlDown).Address).Value

End Sub

all it does it build a range from A2 down to the ;last filled cell
(rng.End(xlDown).Address) and then loads those values into the list.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Ivan Raiminius" wrote in message
oups.com...
Hi Kev,

for filling combobox with values you have in excel range (a2 .. last
populated row in column a) use something like this:

Private Sub cboPartsused_Click()
dim rng as range
set rng = range("a2")
userform1.combobox1.list=range(rng.address,rng.End (xlDown).Address).value
End Sub

Regards,
Ivan