Go to sheet based on ListBox selection

[jeff]

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 I’m 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, I’m 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.

I’ve never used a ListBox in a UserForm before.
Thanks
j.o.

 

[Dave Peterson]

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)
and
http://contextures.com/xlUserForm02.html

I don't think she used Listboxes. If you have trouble, post back with your
question.

 

[jeff]

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.












--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks for replying Dave.
Sorry. I guess I gave too much information. I’m sure I’m making it too
complicated. I’ll try to boil it down.
1st off, all the sheets have already been created. This doesn’t 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 I’m 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 I’m 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 it’s 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 don’t know how many will be on
this list. Also, I’m sure there’s a much more efficient way to write
it. Hope I explained it better this time.
Thanks again.
j.o.

 

[LOFE]

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

 

[Dave Peterson]

I've never been a fan of using the count into a list to find the name of
something. It scares me--especially since the names could be rearranged pretty
easily.

But you could use:

Dim myStartCell as range
dim testwks as worksheet
with worksheets("Vehicle Summary")
set myStartCell = .range("VehicleMake").cells(1) 'or "A2:A999"????
end with

set testwks = nothing
on error resume next
set testwks = worksheets(mystartcell.offset(me.listbox2.listindex,0))
on error goto 0

if testwks is nothing then
msgbox "there isn't a worksheet by the name"
else
testwks.range("A1").value = "I'm on the right sheet!"
'add the rest of the stuff to testwks!
end if

(Untested, uncompiled. Watch for typos.)

==========
I think I'd trust it more if I kept a crossreference on that "vehicle summary"
sheet. Put the name of the worksheet in column A (say) and the name of the
vehicle in column B. (Column B would be the same as what is shown in listbox2.

Then...

dim res as variant
dim testwks as worksheet
dim wksname as string
with worksheets("vehicle summary")
res = application.match(me.listbox2.value, .range("b:b"),0)
if iserror(res) then
msgbox "Design error--there isn't a match
else
wksname = .range("a:a")(res)
set testwks = nothing
on error resume next
set testwks = worksheets(wksname)
on error goto 0

if testwks is nothing then
msgbox "a different design error
else
'write the values to testwks
testwks.range("A1").value = "hi there!"
end if
end if
end with

(still untested, uncompiled.)

 

[jeff]

I've never been a fan of using the count into a list to find the name of
something.  It scares me--especially since the names could be rearranged pretty
easily.

But you could use:

Dim myStartCell as range
dim testwks as worksheet
with worksheets("Vehicle Summary")
   set myStartCell = .range("VehicleMake").cells(1) 'or "A2:A999"????
end with

set testwks = nothing
on error resume next
set testwks = worksheets(mystartcell.offset(me.listbox2.listindex,0))
on error goto 0

if testwks is nothing then
  msgbox "there isn't a worksheet by the name"
else
  testwks.range("A1").value = "I'm on the right sheet!"
  'add the rest of the stuff to testwks!
end if

(Untested, uncompiled.  Watch for typos.)

==========
I think I'd trust it more if I kept a crossreference on that "vehicle summary"
sheet.  Put the name of the worksheet in column A (say) and the name ofthe
vehicle in column B.  (Column B would be the same as what is shown in listbox2.

Then...

dim res as variant
dim testwks as worksheet
dim wksname as string
with worksheets("vehicle summary")
   res = application.match(me.listbox2.value, .range("b:b"),0)
   if iserror(res) then
      msgbox "Design error--there isn't a match
   else
      wksname = .range("a:a")(res)
      set testwks = nothing
      on error resume next
      set testwks = worksheets(wksname)
      on error goto 0

      if testwks is nothing then
         msgbox "a different design error
      else
         'write the values to testwks
         testwks.range("A1").value = "hi there!"
      end if
   end if
end with

(still untested, uncompiled.)










--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thanks for the replies. I'll test them out over the weekend. I
appreciate it.
j.o.