using a worksheet range to populate a combo box in excel

[Ivan Raiminius]

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

 

[Kev]

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.

 

[Ivan Raiminius]

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

 

[Kev]

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

 

[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.

 

[Ivan Raiminius]

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

 

[Kev]

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

 

[Ivan Raiminius]

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

 

[Kev]

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

 

[Ivan Raiminius]

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

 

[Kev]

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

 

[Ivan Raiminius]

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

 

[Kev]

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

 

[Ranon Goldfield]

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

 

[R1C1]

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

Regards,

Alan

 

[R1C1]

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

 

[Superman]

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.

 

[Superman]

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