PDA

View Full Version : Listbox Values from VBA, not from worksheet



canselmi
06-12-2011, 11:02 AM
Hi all,

I have a quick and hopefully straightforward question.

I have 3 listboxes on a userform that I want a user to pick a number from 1 to 512, then the total of all three would make visible the number of rows in sheet 1. But, I don't want to refer to another worksheet for the values to pick from.

I found this code, but I do not want to create 512 entries:


Private Sub form1_Activate()

listBox1.AddItem "Item1"
listBox1.AddItem "Item2"
listBox1.AddItem "Item3" 'etc

End Sub


How can I do this without refering to another sheet?

ntrauger
06-12-2011, 11:55 AM
Try a loop. Something along the lines of:Private Sub form1_Activate()
Dim i As Long
For i = 1 to 512
listBox1.AddItem "Item" & i
Next
End Sub

canselmi
06-12-2011, 12:54 PM
I'm very sorry, I meant to say combobox, not listbox.

Would it be the same, though? And if I have 3 combo boxes, would it look like this?


Private Sub UserForm2_Activate()
Dim i As Long
For i = 1 to 512
ComboBox1.AddItem "Item" & i
ComboBox2.AddItem "Item" & i
ComboBox3.AddItem "Item" & i
Next
End Sub

ntrauger
06-12-2011, 01:16 PM
You've got the idea.

canselmi
06-12-2011, 01:19 PM
How do I take the total of the three values selected and apply it to my worksheet to make that total = visible rows?

canselmi
06-12-2011, 01:29 PM
I've attached my spreadsheet to show you what I'm trying to do:

ntrauger
06-12-2011, 01:52 PM
Remove the 2 from "Private Sub UserForm2_Activate()" and that part will work for you. Secondly, replace the existing code in that sub with this:Dim i As Long
For i = 1 To 512
ComboBox1.AddItem i
ComboBox2.AddItem i
ComboBox3.AddItem i
Next
Thirdly, I'm sure you have already decided at what point you will want to show these rows, so put the following code in that position:Dim sumOfCombos As Long
sumOfCombos = CInt(ComboBox1) + CInt(ComboBox2) + CInt(ComboBox3)
Sheet1.Rows(2).Resize(sumOfCombos).Hidden = False

canselmi
06-12-2011, 06:04 PM
Nate,
1. How do I update Textbox1 with the total of all 3 comboboxes?
2. How do I keep the values of all 3 comboboxes if I advance to UserForm3 and then return back to UserForm2?
3. When the Workbook opens, I want to show UserForm1 and Hide All but rows 1 and 2 and columns A, B, and C. I'm confused where to place the code. Do I place it in "My Workbook" or in a Class Module?
Part of my code looks like this, currently in Module1:

Option Explicit
Private Sub Workbook_Open()
Columns("D:XFD").Select
Selection.EntireColumn.Hidden = True
Rows("3:1048576").Select
Selection.EntireRow.Hidden = True

ntrauger
06-13-2011, 04:18 PM
Attaching the workbook with said changes:

1. Use the Change() event of each combobox to update the textbox.
2. More than one way to skin a cat...you can use public variables to store the values of the comboboxes as long as the code is running. Update the variables each time the comboboxes are changed (Change() event again) and refer to them whenever the userform is loaded. If the user is allowed to run your code more than once (i.e. a button is available that will show userform1 again), you will need to use the End statement at some terminal point to clear the variables.
3. Your code is correct, but in the wrong place. Move to the "ThisWorkbook" module. Also, try to not use the .Select method where possible.