"For" help (I think?)

[64-bit Newbie]

Good morning all-

I have a quick question for you concerning a selecting an item out of a
listbox, allowing another userform pop up. In listbox1, I have 51 line
items, with a SelectButton and a CancelButton. What would be a good code for
the following:

If listindex 1 is selected, then show userform1...
If listindex 2 is selected, then show userform2...
If listindex 51 is selected, then show userform51

I'm thinking something along the lines of:

Private Sub SelectButton_Click()
Dim I as Integer
For I = 1 to 51
if listbox1.selected(I) then
userform(I).open
end if
next I
End Sub

How far am I off the beaten track here?

 

[JLGWhiz]

assuming you have 51 UserForms.

Private Sub SelectButton_Click()
Dim I as Long
For I = 0 to 50
If Listbox1.Selected(I) = True then
Userform(I + 1).Show
End If
Next I
End Sub

 

[64-bit Newbie]

I got a "Sub or function not defined". I've created 3 userforms (userform1
thru userform3) and tested this argument for the first 3 line items selected
in the listbox and got the above error.

 

[Dave Peterson]

Check Chip Pearson's site:
http://www.cpearson.com/excel/ShowAnyForm.htm

 

[JLGWhiz]

Yep, can't use a Name that way. Here is one way to do it:

Private Sub CommandButton1_Click()
Dim I As Long
UF = Array(UserForm1, UserForm2, UserForm3)
For I = 0 To 2
If ListBox1.Selected(I) Then
UF(I).Show
End If
Next I
End Sub

By putting the UserForms in an array, you can call them up by their array
index. The problem is, it makes for a pretty long array with fifty one
UserForms. I'm wondering why so many forms and wouldn't an alternative
method of doing whatever it is you are trying to do be a better approach.

I

 

[64-bit Newbie]

Here's what I'm trying to accomplish for my law enforcement department. I
have 51 items in my listbox, representing all 50 states and DC. If a user
selects one of the states, I'm trying to get its corresponding userform to
open depicting the county breakdown for that state (in law enforcement it's
called Area of Responsibility). So I've started with Alabama as the first
line item, where the county breakdown is in the first and foremost userform
entitled Userform1, etc...

I think my other option, albeit time committed, is to create a master
userform with 51 optionbuttons. And, with the click of an optionbutton,
simply show its corresponding userform.

Then, the next part of the project is to list the counties on those
userforms. By selecting a county, a msgbox will appear depicting area of
responsibility with phone numbers and faxes off of numbers entered in a
worksheet.

That's the jist of what I'm trying to accomplish. Thanks for your assistance!

 

[JLGWhiz]

If you have already created the 51 UserForms then suggesting the use of
MultiForm would be a little late in coming. The alternatives to the array
method are to either create an If...Then...ElseIf algorithm statement for 51
criteria of ListBox1.Selected(i) or a Select Case statement that uses 51
cases to Show the individual UserForms. There probably is an easier way, but
I can't think of it at the moment. Maybe someone smarter than me can come up
with a way to shorten the code a little.

 

[JLGWhiz]

I said MultiForm, should be MultiPage. You might want to check that out in
the help files. It is probably better than 51 individual UserForms.

 

[Jefgorbach]

Havent done much work with userforms but it sounds like you already
have the county information stored for each state, so can you pass
that filename to the form for it to load?

ie: select state
case state1 : load state1.csv to country, phone

 

[64-bit Newbie]

Actually haven't gotten that far yet. Like everything else in VBA, I had to
think in terms of getting from point A (being the US) down to a specific
county. However, I do see what you are saying, and I thank you for your
kindness as well.

 

[64-bit Newbie]

The Multipage is definitely the way to go in this case. I've been toying
around with that principle, and it appears that will be the easier route.
Fortunately, I haven't created only but 3 userforms thus far, and will be
putting a hault to that

 

[Jim at Eagle]

What you’re trying to do is quite simple as long as the problem is described
properly. Do you want to choose from Listbox1 the name of state? Then base on
Listbox1.value Listbox2 is populated with the Area of Responsibility within
that state and then when the Area of Responsibility is selected from Listbox2
then the point of contact is displayed.
Listbox's are nice to control and protect data within your spreadsheet. I
would probably start with a region first then to a state. Too many selections
in one listbox is not generally desirable.