 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / March 2008"For" help (I think?)
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? 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 > Good morning all- > [quoted text clipped - 19 lines] > > How far am I off the beaten track here? 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. > assuming you have 51 UserForms. > [quoted text clipped - 30 lines] > > > > How far am I off the beaten track here? 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 > 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 [quoted text clipped - 34 lines] > > > > > > How far am I off the beaten track here? 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! > Yep, can't use a Name that way. Here is one way to do it: > [quoted text clipped - 53 lines] > > > > > > > > How far am I off the beaten track here? 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. > 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 [quoted text clipped - 72 lines] > > > > > > > > > > How far am I off the beaten track here? 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. > 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 [quoted text clipped - 80 lines] > > > > > > > > > > > > How far am I off the beaten track here? 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:) > 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. [quoted text clipped - 83 lines] > > > > > > > > > > > > > > How far am I off the beaten track here? 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 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. > 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 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.  SignatureJim at Eagle > 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 [quoted text clipped - 72 lines] > > > > > > > > > > How far am I off the beaten track here? Check Chip Pearson's site: http://www.cpearson.com/excel/ShowAnyForm.htm > Good morning all- > [quoted text clipped - 19 lines] > > How far am I off the beaten track here? SignatureDave Peterson |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.