As long as it is a Single Select list, you can use something like:
MyValue = ListBox2.ListIndex
You can then assume that if the Index is 1 (the ListIndex starts at 0) then
the user has selected vehicle 2 (VNum = MyValue + 1, VSheet = "V_" & VNum,
Sheets(VSheet).Select or Sheets("V_" & MyValue + 1).Select or even
Sheets("V_" & ListBox2.ListIndex + 1).Select).
Or you can loop through the vehicle list the number of times shown through
ListIndex (remembering ListIndex 0 = 1, ListIndex 1 = 2 etc) to get the sheet
name (which I think you are saying is stored with the vehicle list on
creation - if not it probably should be).
"jeff" wrote:
On Aug 7, 3:25 pm, Dave Peterson wrote:
I'm not sure what you're doing, but maybe something like this in your ok button
procedure.
Dim V2Wks as worksheet
set v2wks = worksheets.add 'new sheet in the activeworkbook
with v2wks
on error resume next
.name = me.listbox2.value
if err.number < 0 then
msgbox "Name not changed!"
err.clear
end if
on error goto 0
.range("a1").value = me.textbox1.value 'some value from the userform?
.range("A2").value = me.textbox2.value 'as much as you need???
End with
==========
Debra Dalgleish has some nice notes about userforms:http://contextures.com/xlUserForm01.html
(video: http://contextures.com/xlVideos05.html#UserForm01)
andhttp://contextures.com/xlUserForm02.html
I don't think she used Listboxes. If you have trouble, post back with your
question.
jeff wrote:
UserForm2 allows user to enter maintenance records for a vehicle.
There is potentially more than 1 vehicle.
The user will select a vehicle shown in ListBox2. The list of vehicles
comes from range €śVehicleMake€ť in sheet Vehicle Summary. Also, each
vehicle on that same list has its own range name. V__1, V__2, etc thru
V__10. Each of these range names contain no data until the user Adds
another vehicle, which is done via another UserForm prior to using
this UserForm. When a new vehicle is added, a sheet is created for the
new vehicle added. Sheet V #1 for the 1st vehicle added, sheet V #2
for the 2nd vehicle added, etc.
The user fills out the rest of UserForm2, then clicks OK.
What I need is for the data in UserForm2 to be entered in the correct
sheet based on the selection of the vehicle in ListBox2.
The problem Im having is to decide how to direct the macro to look
for the correct sheet to put the data in.
Example, if the 1st vehicle is selected on the list in ListBox2, then
the data should go to sheet V #1. When the macro looks at which
vehicle was selected, Im not sure if my coding should reference the
whole range of vehicles €śVehicleMake€ť, or if it should reference the
range name of the 1st vehicle on the list, V__1.
Ive never used a ListBox in a UserForm before.
Thanks
j.o.
--
Dave Peterson- Hide quoted text -
- Show quoted text -
Thanks for replying Dave.
Sorry. I guess I gave too much information. Im sure Im making it too
complicated. Ill try to boil it down.
1st off, all the sheets have already been created. This doesnt have
anything to do with creating any sheets. I need to know how to program
this so it knows which sheet to go to (so that I can paste the data
from this userform. This will be based on what the user selects in
ListBox2. I have no idea what will be in this listbox. Nor will I know
how many will be on the list. The user will add these prior to using
the userform Im working on now. When the user adds a new vehicle (in
a userform prior to this one), a sheet is created to hold data for
that vehicle. The first one he adds results in a sheet named V_1
being created. It also puts that vehicle name (example: Chevy) on a
list in a sheet already named Vehicle Summary. ALSO, that name is the
1st on the list. And, the 1st position on that list has a range name
V#1. IF/When a 2nd vehicle is added, it creates another sheet, this
one named V_2, and enters it on the next open position on the list in
the 2nd position, which is named V#2. This could happen any number of
times, based on the user.
All that is done. Now I come to the part I need some help with.
The user opens up the userform Im working on now. He has to pick a
vehicle from the list in the ListBox. It shows the list that was
already created above. It shows each vehicle in the order he added
them. If he selects the 1st one on the list. How do I write it so that
the data entered on this userform goes to the correct sheet? For
example, if he selected the 1st one, then its suppose to go to the
sheet named V_1.
BASICALLY, if the user selects the 1st one on the list, go to V#1, if
he selects the 2nd, go to V#2, etc. Maybe I could write a series of If/
Then statements, but like I said, I dont know how many will be on
this list. Also, Im sure theres a much more efficient way to write
it. Hope I explained it better this time.
Thanks again.
j.o.