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)




WLMPilot wrote:

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" wrote:

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 wrote:

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.

"Tom Ogilvy" wrote:

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" wrote:

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!

--

Dave Peterson


--

Dave Peterson

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.
"Dave Peterson" wrote:

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)




WLMPilot wrote:

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" wrote:

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 wrote:

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.

"Tom Ogilvy" wrote:

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" wrote:

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!

--

Dave Peterson


--

Dave Peterson

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)

"WLMPilot" wrote:

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.
"Dave Peterson" wrote:

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)




WLMPilot wrote:

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" wrote:

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 wrote:

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.

"Tom Ogilvy" wrote:

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" wrote:

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!

--

Dave Peterson


--

Dave Peterson