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

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

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

Thanks again,

Tried this. Again, just get a blank combo box.

Could there be a problem somewhere else?

Thanks,

Kev

Hi Kev,

set breakboint in your code at line

Me.cboPartsUsed.List = Range(rng.Address,
rng.End(xlDown).Address).Value

and into immediate window (when the code stops), check for the proper
address and values:

?Range(rng.Address,rng.End(xlDown).Address).addres s 'should give you
proper address
?for each cell in Range(rng.Address,rng.End(xlDown).Address) :
debug.print cell.value : next cell 'should print the list of values

If these two checks give you proper address and list of values, then
proceed one step (by pressing F8) and check the combobox for values.

?for i = 0 to Me.cboPartsUsed.List.count - 1 : debug.print
Me.cboPartsUsed.List(0,i) : next i

Let me know the results.

Regards,
Ivan

I have tried this and nothing is different!

I have inserted a breakpoint by clicking debug then toggle breakpoint.
I run the code and nothing has changed.

Any ideas for this novice?

Thanks Ivan,

Kev

Hi Kev,

1) did you set breakpoint at the correct row?
2) what is the result from immediate window after executing the checks?

Regards,
Ivan

Morning Ivan,

I dont understand the procedure or where to find the window etc
.. I placed my cursor at the begining of the line you stated then
pressed debug etc.

I then used the run command. The user form came up as normal and run as
normal. The combo box displays a blank box underneath when pressed, but
nothing else happens.

Cheers

Kev

Hi Kev,

in VBA window you should see window called immediate, otherwise go to
view immediate window.

When the code stops at the breakpoint (the application will bring you
to vbe window), you should see the line of code with yellow background
and should be able to execute commands from previous post in immediate
window (just copy and paste them, you need to press enter at each row
of code to execute the row in immediate window).

You need to click on your userform at "cboPartsused" to fire event
"Private Sub cboPartsused_Click" - the breakpoint is inside this event
procedure.

Regards,
Ivan

Sory Ivan,

The code doesnt seem to be stopping at the breakpoint. The combo box
stays on the screen with the cursor flashing. I can click the drop down
arrow to the right and a blank box appears, but this is endless. There
is nothing in the immediate window which I have now found.

I have tried to execute your commands, but get an error message -
object required which leads me to believe that no values are set!

I know it's me thats doing something wrong and I will learn from
this!!!

Cheers,

Kev

Hi Kev,

nothing to be sorry about.

Seems to me like Private Sub cboPartsused_Click never runs.

replace _Click with _DropButtonClick to see what happens (it should run
when you click on dropdown button on the combobox).

Regards,
Ivan

Thanks Ivan,

Thats made a difference. I now get a selection to choose from, however,
its not the values from sheet 4 "temp parts, its from sheet 2 "job
sheet" the same sheet as the user form appears.

I have deleted the click sub, double clicked the object and a new sub
has appeared for click. I have copied and pasted the code back but it
doesnt work. Hey ho, I can live with clicking the drop down arrow
anyway!

If I could get the data from temp parts I would be chuffed!

Thanks again

Kev

Hi Kev,

change:
Set rng = Range("a2")
to be:
Set rng = worksheets("temp parts").Range("a2")

You don't have to use "Private Sub cboPartsused__DropButtonClick", you
can use for example: "Private Sub UserForm_Initialize()" or "Private
Sub ComboBox1_Enter()" or other event (depends on when you need the
event to fire). Or you don't have to use events for filling the
combobox at all. In the procedure building and showing the userform,
before you show the userform, you can paste the code:
Dim rng As Range
Set rng = worksheets("temp parts").Range("a2")
Me.cboPartsUsed.List = Range(rng.Address,
rng.End(xlDown).Address).Value
Me.show
Just replace "Me" with the userform name (I don't know it's name).

Regards,
Ivan

Morning Ivan,

Thanks again!

I allready have this line (Set rng = worksheets("temp
parts").Range("a2") ) in my sub!

Any ideas?

Kev

Morning Kev,

(so you have the same time as me in Prague),

now I can see it, change also:
Me.cboPartsUsed.List = Range(rng.Address,rng.End(xlDown).Address).Value

to be:
Me.cboPartsUsed.List =
rng.parent.Range(rng.Address,rng.End(xlDown).Addre ss).Value

Regards,
Ivan

Ivan,

Your a star!

At last, it works a treat.

I can now develop the rest of the program, thank you so much.

I might contact you for more help as it goes along, or would it be best
to re-post? (Have you had enough?)

I'm not well travelled myself, I'm not sure about the time in prague?
I'm in the UK.

Take care for now,

Kev.

Hi Kev,

you have one hour less.

Of course you can contact me, if you need more help. But for you is
better to re-post, because also others might answer your questions.

Or use e-mail address from my profile (preferably send me a link to
your new post).

Regards,
Ivan

Hi again Ivan,

Now that you have had 2 days off, could I pick your brains again
please?

I decided to come straight to you after all, you know the history etc.

What I need to do now, is for the combo box to only display column A of
Sheet (Temp parts) as it does perfectly at the moment, onto another
sheet (Customer copy). When an item from temp parts is selected, the
first available line on Customer copy is selected, and the value of the
combo box to be written in it.

This I have achieved with a command button and code shown below. It
works fine. Now I need the associated columns A, B, C & D to be placed
into another Excel sheet in the same woorbook (called Financal copy).

The other columns contain part numbers, costs etc, associated to the
part description from column A.

Hope this makes sense!

Here's the code I have written for the ADD button I have set up. It
also writes a quantity box from my user form, resets the quantity to 1
and the blanks the combo box.

Private Sub cmdAdd_Click()

ActiveWorkbook.Sheets("Financial copy").Activate
Range("A23").Select

Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = cboPartsused.Value
ActiveCell.Offset(0, 2) = txtQuantity.Value
txtQuantity.Value = "1"
cboPartsused.Value = ""
Range("A1").Select
End Sub


Here goes.......................!

Thank you.

Kev.

Hi Kev,

first, we will change your procedure a little:

Private Sub cmdAdd_Click()

'ActiveWorkbook.Sheets("Financial copy").Activate
with Sheets("Financial copy").Range("A23").End(xlDown)
..Offset(1,0).Value=cboPartsused.Value
..Offset(1, 2).value = txtQuantity.Value
end with
txtQuantity.Value = "1"
cboPartsused.Value = ""
' Range("A1").Select
End Sub

I am not sure with range("A23"), it should be the first cell of the
column you are finding the last cell to fill in cboPartsused.Value (I
hope it is clear. Probably it should be A1 or A2 - the header cell).

Also I am not sure if you need to select (Range("A1").Select) at the
end.

Why do you leave one empty column between cboPartsused.Value and
txtQuantity.Value?

Where in worksheet Customer Copy you need to add values from just
filled row, columns A,B,C,D?

Regards,
Ivan

Hi Ivan,

I am starting at cell A23 because above that is the rest of a jobsheet.
Address job number work description etc.

I left colum B out for cosmetic reasons but have changed my mind, I
will now use B for the quantity.

I have 2 almost identical worksheets.

1 is called customer copy and all I want on that one is the part
description and quantity. The part description is obtained from
Worksheet Temp parts via the combo box you helped me with earlier.

On the temp parts sheet, the next column is the Part number, then in C
is the trade price. In column D is the list price.

I need this extra information about the part picked by the combo box to
appear in the columns on the sheet called financial copy.

Hope this is clear (only I know whats in my head!!)

Thanks

Kev

Also, the add button code searches from a23 downwards for the next
empty cell. then places the value there. Each time a part is selected,
the add button will effectively place the parts in a list going
downwards.

Hi Kev,

if I understood you correctly, the procedure should be:

Private Sub cmdAdd_Click()

'ActiveWorkbook.Sheets("Financial copy").Activate
with Sheets("Financial copy").Range("A23").End(xlDown)
..Offset(1,0).Value=cboPartsused.Value
..Offset(1, 2).value = txtQuantity.Value
worksheets("customer
copy").range("a1").End(xlDown).Offset(1,0).Resize( 1,4).value =
..offset(1,0).resize(1,4).value 'added this row to copy result from
combobox
end with
txtQuantity.Value = "1" 'consider using number 1 instead of string
"1"
cboPartsused.Value = ""
' Range("A1").Select
End Sub

Please let me know if I missed something.

Regards,
Ivan

Thanks Ivan,

I'll give it a try over the weekend.

Have a nice one yourself and thanks for all your time up to now!

Kev

Hi Kev,

thanks, nice weekend to you too.

I should be in the office on Sunday, so if you will need something, let
me know.

Regards,
Ivan

Ivan,

I tried this and get a runtime error!

Im not sure if its because the command lines get carrage returns on
here and I might not be putting them together correctly. I dont think I
explained myself properly anyway. I'll try again!

My workbook has 3 sheets labelled customer copy, financial copy and
temp parts.

Temp parts has 4 columns. The headers are Part description, Part
number, Trade price and list price.

Customer copy has a parts used section starting from A23 downwards, and
I only want the part description and quantity values displayed here as
this will be printed out on site and given to the customer.

Customer copy has a graphic icon to run a user form where the combo box
picks off the part descriptions and places them in the next available
empty cell when a button named add is pressed. As there will almost
certainly be more than one part used on each job, everytime the add
button is pressed it needs to select the next available cell in column
A from row 23 downwards.

The last sheet, finacial copy is exactly the same as customer copy, but
in the parts used section, I not only need column A from temp parts
placed from row 23 onwards, but also the data from column B, C & D as
well to tell us what that part costed us, what the customer has been
charged, and the part number.

I think that explains what i need a little better?

Dont work too hard at the weekend mate!

Cheers for now,

Kev

Hi Kev,

the runtime error is because I supposed, that there is at least one
non-blank cell below A23. Now I added check for it. I hope I understood
correctly locations for all the data.

Private Sub cmdAdd_Click()
Dim rng1 As Range
Dim rng2 As Range
Dim i As Long
'ActiveWorkbook.Sheets("Financial copy").Activate
Set rng1 = Sheets("Financial copy").Range("A23").End(xlDown)
If rng1.Row = Application.Rows.Count Then
Set rng1 = Sheets("Financial copy").Range("A23")
End If
Set rng2 = Worksheets("customer copy").Range("a1").End(xlDown)
If rng2.Row = Application.Rows.Count Then
Set rng2 = Worksheets("customer copy").Range("a1")
End If
With rng1
For i = 0 To cboPartsused.ColumnCount - 1
..Offset(1, i).Value = cboPartsused.List(cboPartsused.ListIndex, i)
Next i
..Offset(1, i).Value = txtQuantity.Value
rng2.Offset(1, 0).Resize(1, 2).Value = .Offset(1, 0).Resize(1, 2).Value

'added this row to copy result from ComboBox
rng2.Offset(1, 2).Value = txtQuantity.Value
End With
txtQuantity.Value = 1 'consider using number 1 instead of string
"1"
cboPartsused.listindex=-1
' Range("A1").Select
End Sub

Now I suppose that you will change your combobox to contain 4 columns
of data from Temp Parts (Part description, Part number, Trade price and
list price). If will get lost in achieving this, please post the code
that you use for initializing the combobox.

Regards,
Ivan

Thanks Ivan,

I can see you have put some thought into the code your supplying me and
I appreciate your help.

I'm not sure how to initialize the combobox.

This is the only code I have!

Private Sub UserForm_Initialize()

txtQuantity.Value = "1"
cboPartsused.Value = ""
cboPartsused.SetFocus
spnButton1.Min = "1"

End Sub

I would appreciate your help here!

Nearly done now (I hope)!

Thank you,

Kev

Hi Kev,

sorry for replying so late, too busy now.

in code we did together:

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

replace "Set rng = worksheets("temp parts").Range("a2")" with "Set rng
= worksheets("temp parts").Range("a2..d2")" probably - "a2..d2" is the
location of first data in "temp parts" sheet.

Set columncount in properties of combobox cbopartsused to 4. Then your
combobox will show four columns of data. If you don't want some of them
to display, set accordingly columnwidths in properties of cbopartsused.

Regards,
Ivan

Ivan,

Don't know if you will get this, I used the reply button as usual, but
have been directed elsewhere!

Dont worry about taking time over this, I dont expect an immediate
response, seeing as though I'm getting free consultancy here - I'm
gratefull for whatever information I get, and I respect the fact that
you must be doing something full time elsewhere.


Okay, it nearly works apart from one or two things.

If I leave the combo box blank, I get, "runtime error 381, could not
get the list property, invalid property array index".

The cell a24 was selected as the first cell. I have corrected this
changing the reference in the code to a22. Also the data on finantial
copy needs to go from a23 onwards, so I changed the reference here from
a1 to a22 as well.

The code works on the financial copy twice only. What happens here is
cell a23 gets the first part, cell a24 gets the next, from then on cell
a24 updates instead of moving onto cell a25.

There is nothing happening in the financial copy at all!

Just a minor bug I know, but I cant get my head round it.

Thanks again,

Kev

I was just wondering if you might be able to help with a similar issue
as this one here. I know you helped this other person fill in a
combobox using a cell range from a worksheet from A1-the last line in
the "A" column, but for some reason if I change rng.End(xlDown).Address
to rng.End(xlToRight).Address because I am trying to fill the combobox
with items from left to right instead of down the same column, it's only
giving me the first item and not the rest of them. I am still using
rng.Parent.Range to output the list, should it be something different if
I am going across columns? instead of down the same row?

Thank you for any help you can offer.

Ranon

*** Sent via Developersdex http://www.developersdex.com ***

The combobox defaults to 1 column. Is the column count in the combobox
properties to the number of correct number of columns?

Regards,

Alan


"Ranon Goldfield" wrote in message
...
I was just wondering if you might be able to help with a similar issue
as this one here. I know you helped this other person fill in a
combobox using a cell range from a worksheet from A1-the last line in
the "A" column, but for some reason if I change rng.End(xlDown).Address
to rng.End(xlToRight).Address because I am trying to fill the combobox
with items from left to right instead of down the same column, it's only
giving me the first item and not the rest of them. I am still using
rng.Parent.Range to output the list, should it be something different if
I am going across columns? instead of down the same row?

Thank you for any help you can offer.

Ranon

*** Sent via Developersdex http://www.developersdex.com ***

I think I had one too many, lol, I meant to say,

The combobox defaults to 1 column. Is the column count, in the combobox
properties, set to the correct number of columns?

Regards,

Alan

"Ranon Goldfield" wrote in message
...
I was just wondering if you might be able to help with a similar issue
as this one here. I know you helped this other person fill in a
combobox using a cell range from a worksheet from A1-the last line in
the "A" column, but for some reason if I change rng.End(xlDown).Address
to rng.End(xlToRight).Address because I am trying to fill the combobox
with items from left to right instead of down the same column, it's only
giving me the first item and not the rest of them. I am still using
rng.Parent.Range to output the list, should it be something different if
I am going across columns? instead of down the same row?

Thank you for any help you can offer.

Ranon

*** Sent via Developersdex http://www.developersdex.com ***

Well I don't think the column count has anything to do with the problem.
Maybe I did not explain the question correctly.

I have a worksheet with information going vertical in each column, I
also have information going horizontal across several rows.

I have successfully populated the comboboxes using information
vertically down a column with the assistance of this forum. However if
I take the exact same coding and just change xldown to xltoright for
some reason I can only populate the combobox with only the first row and
it does not populate the rest of the rows into the combobox, but if I
change that same code back to xldown everything works perfectly again.
So I believe it's something in my vb code not something physically wrong
with the combobox.

*** Sent via Developersdex http://www.developersdex.com ***

I have since solved the issue. Thanks for all your help.

*** Sent via Developersdex http://www.developersdex.com ***