Populating Combobox

[WLMPilot]

I have three userforms, each with a combobox that I need to populate with
items from the same worksheet. Also each list that populates each combobox
has the possibility to increase is number of items. The name of the
worksheet the holds all the data is "Items"

1) This is the largest and can probably use a feature I have seen where the
command scrolls up to the last empty row. I need to pull item number and
item description from "Items". Starting cell for each is A3 and B3
respectfully. Ending cell is unknown due to the possibility of adding new
items. However, I will not enter any data having to do with anything else
past the end of the list. I only need to populate with the item number but
need to be able to place the item number and corresponding item description
in the appropriate cells (already have code to place data in cell).

2) Currently, I have station numbers 1-7 in C3-C9. This may be added to if
a new EMS station is built, ie station 8 would be in C10. How do you
populate a combobox using same row. NOTE: I have data to the right of this
list that pertains to another combobox (see #3).

3) Currently I have a list of the ambulance numbers (M1, M2, M4, M7, etc) in
a list starting with M3 and listing down the sheet. I am not sure if you can
pinpoint that column and still do a command that locates the last empty cell
in that column or not. For this example, let's say M3-M20 contains each unit
number, with the possibility of adding more.

Thanks!

 

[Tom Ogilvy]

Put an initialize event in each form using this code:

Private Sub Userform_Initialize()
Dim rng1 as Range
with Worksheets("Items")
set rng1 = .Range(.Range("A3"),.Range("A3").End(xldown))
End with
Combobox1.RowSource = rng1.Address(0,0,xlA1,True)
End with

In Userform1, use the above code
In userform2, change the A3 to C3 in both instances

in Userform3, change the A3 to M3 in both instances.

In userform1 to get the description

Private Sub Combobox1_Click()
Dim rng1 as Range, cellA as Range
Dim cellB as Range
With worksheets("Items")
set rng1 = .Range(Combbox1.Rowsource)
end with
set cellA = rng1(Combobox1.ListIndex + 1)
set cellB = cell.offset(0,1)
' use cellB to get the description
end Sub

--
Regards,
Tom Ogilvy




End Sub

 

[WLMPilot]

Hey Tom,

Thanks for the info. However, I made an error for option 2. It should be
C3-I3 with station 8 becoming J3. It is the same row, not column.

Thanks!

 

[Tom Ogilvy]

You can't populate a combobox with a horizontal range using rowsource. You
will need to loop through the cells and use additem to populate the combobox.

 

[WLMPilot]

I do not know how to do that. I am new to VBA and using comboboxes.

Thanks again!

 

[Tom Ogilvy]

Private Sub Userform_Initialize()
Dim cell as Range
Combobox2.RowSource = ""
Combobox2.Clear
with worksheets("Items")
for each cell in .Range("C3",.Range("C3").End(xltoRight))
Combobox2.AddItem cell.Value
Next
End With
End Sub

 

[WLMPilot]

Thanks Tom. However, I am encountering a new problem. Prior to inserting
combobox, I was using textboxes without incident. Now that I inserted the
first set of code (for userform 1), I come up with an error that reads as:

Run-time Error '2147352573 (80020003)':
Could not find the specified object

When I debug, it highlights the indicated line below:

Sub OrderEntry()
Dim iq As New ItemQty ' Userform with combobox
iq.Show <-------HIGHLIGHTS WHEN DEBUGGED
End Sub

I have a subroutine that is activated when the user clicks a command button
"BEGIN ORDER" The code is found below. Within this routine, the routine
"OrderEntry" is called.

'BEGIN NEW ORDER
Private Sub CommandButton2_Click()
Range("C1").Value = ""
Range("F1").Value = ""
Range("C3").Value = ""
Range("D6:H8").Value = ""
Range("A13:B50").ClearContents
Range("G13:H50").Value = ""
Range("B1").Select
Dim st As New STINFO ' Userform: Obtain station# and crew names
st.Show ' Show STINFO userform
Narcotics ' Routine to get narcotic order
OrderEntry ' Routine that shows userform (gets item# and
Qty)
End Sub

As I said before, everything worked fine until I inserted the code for the
combobox.
What would cause this problem?

Thanks.

 

[Dave Peterson]

Your code worked for me in minor testing (just a blank userform named itemqty),
but you may want to try:

Option Explicit
Sub OrderEntry()
Dim iq As itemqty ' Userform with combobox
Set iq = New itemqty
iq.Show '<-------HIGHLIGHTS WHEN DEBUGGED
End Sub

Chip Pearson explains why using "dim xxx as New yyyy" is a bad idea:
http://cpearson.com/excel/variables.htm

look for:
Don't Use The New Keyword In A Dim Statement

 

[WLMPilot]

I will try it. However, being new to VBA, do I need to change anything on
how OrderEntry is called? I ask this because the code Option Explicit is
above the "Sub" routine and I was wondering how that works.

Thanks.

 

[Dave Peterson]

Option explicit will force you to declare any variables that you use. Without
that, your code may have run, but may not have done what you wanted.
(Undeclared variables could be treated as 0, "", ...)

And it'll help you find typos:
xlvalues (EX-ELL-values)
compared with
x1value (EX-One-values)

 

[Dave Peterson]

And it won't change the way you call the routine.

Ps. You'll want to change this:
Dim st As New STINFO
to
Dim st As STINFO
set st = new stinfo
also.

(But your original code worked ok for me.)

 

[WLMPilot]

I placed Option Explicit right about the code "Sub OrderEntry()". Excel
placed a line between the two lines, ie the lines that are placed between all
the different routines. What do I need to do now?

Also, I have another question. I am using the following code to pull that
last character from the username. How do I also use this code as a caption
on a userform. The last character is actually a number and I want the
caption to read "Station " and the number to follow.

Thanks for all your help.

 

[WLMPilot]

Sorry, forgot to place the code in the last reply that I use to pull the last
character from the username. Here it is:

Right(Environ("UserName"), 1)

I placed Option Explicit right about the code "Sub OrderEntry()". Excel
placed a line between the two lines, ie the lines that are placed between all
the different routines. What do I need to do now?

Also, I have another question. I am using the following code to pull that
last character from the username. How do I also use this code as a caption
on a userform. The last character is actually a number and I want the
caption to read "Station " and the number to follow.

Thanks for all your help.

 

[WLMPilot]

Dave, I got everything to work. Thanks for your help.

Les

 

[WLMPilot]

Tom,

Thanks for all your help!!! Most everything is working but I am coming up
with an error with the following code you provided that obtains the item
description. The error is a Runtime Error 424 - Object Required. I have
indicated the line that the error occurs in. NOTE: Please read below the
code for another question. The code is:

Private Sub ComboBox1_Click()
Dim rng1 As Range, cellA As Range
Dim cellB As Range
With Worksheets("Items")
Set rng1 = .Range(Combbox1.RowSource) <---HILGIHTED WHEN DEBUGGED
End With
Set cellA = rng1(ComboBox1.ListIndex + 1)
Set cellB = cell.Offset(0, 1)
' use cellB to get the description
End Sub

My question at this point (when error free) is that I assume cellB is a
variable and that I can assign (insert) it into a cell within another routine?

Thanks!!

 

[WLMPilot]

Tom,

I found the problem with the line that I indicated as highlighted when
debugged. It was a spelling error with Combobox1 (Combbox1). Once I fixed
that, I got the same runtime error and description with the following line:

Set cellB = cell.Offset(0, 1)

Thanks again and sorry for all the questions. You have been very helpful to
this beginner.

 

[Tom Ogilvy]

I was using the variable cell, but changed it to CellA to make it clearer.
Apparently I didn't change the reference in that line

Set cellA = rng1(ComboBox1.ListIndex + 1)
Set cellB = cell.Offset(0, 1)

should be

Set cellA = rng1(ComboBox1.ListIndex + 1)
Set cellB = cellA.Offset(0, 1)

This will be the cell to the right of the cell in Column A selected in the
combobox if you description and my interpretation match reality.

--
Regards,
Tom Ogilvy

 

[WLMPilot]

Everything is working great!!! THANK YOU!!!

Two more short questions:
1) I want the focus to advance from one field (combobox1) to the next field
(quantity) via hitting the ENTER key. Currently, I have to tab to next
field. How can I fix that?

2) Can I place a formula within a Commandbutton_click routine or do I have
to have a separate routine for a formula. I will be calculating the
difference between the quantity ordered and the stock level if qty ordered >
stock level.

Thanks