Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
"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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
"For" help (I think?)
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" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
"For" help (I think?)
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. "JLGWhiz" wrote: 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" wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
"For" help (I think?)
Check Chip Pearson's site:
http://www.cpearson.com/excel/ShowAnyForm.htm 64-bit Newbie wrote: 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? -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
"For" help (I think?)
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" wrote: 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. "JLGWhiz" wrote: 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" wrote: 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
"For" help (I think?)
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" wrote: 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" wrote: 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. "JLGWhiz" wrote: 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" wrote: 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
"For" help (I think?)
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. "64-bit Newbie" wrote: 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" wrote: 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" wrote: 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. "JLGWhiz" wrote: 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" wrote: 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? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
"For" help (I think?)
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. "JLGWhiz" wrote: 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. "64-bit Newbie" wrote: 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" wrote: 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" wrote: 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. "JLGWhiz" wrote: 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" wrote: 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? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
"For" help (I think?)
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
"For" help (I think?)
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. " wrote: 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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
"For" help (I think?)
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:) "JLGWhiz" wrote: 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. "JLGWhiz" wrote: 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. "64-bit Newbie" wrote: 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" wrote: 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" wrote: 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. "JLGWhiz" wrote: 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" wrote: 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? |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
"For" help (I think?)
What youre 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. -- Jim at Eagle "64-bit Newbie" wrote: 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" wrote: 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" wrote: 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. "JLGWhiz" wrote: 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" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
HELP on "left","right","find","len","substitute" functions | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
If changed array formula reduce ""\""\""\ - signs to #Missing, will it make ... | Excel Programming |