using a worksheet range to populate a combo box in excel

[Kev]

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!

 

[Ivan Raiminius]

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

 

[Kev]

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

 

[Bob Phillips]

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]

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

 

[Kev]

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

 

[Ivan Raiminius]

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

 

[Kev]

Thanks again,

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

Could there be a problem somewhere else?

Thanks,

Kev

 

[Ivan Raiminius]

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).address '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

 

[Kev]

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

 

[Ivan Raiminius]

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

 

[Kev]

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

 

[Ivan Raiminius]

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

 

[Kev]

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

 

[Ivan Raiminius]

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

 

[Kev]

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

 

[Ivan Raiminius]

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

 

[Kev]

Morning Ivan,

Thanks again!

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

Any ideas?

Kev

 

[Ivan Raiminius]

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).Address).Value

Regards,
Ivan

 

[Kev]

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.