Selecting rows from a listbox?

Todd_M

Board Regular
Joined
Feb 24, 2002
Messages
117
Hi-
I have a spread sheet("balance sheet")that has a list of numbers in column A from 1 to 9000. Part of the project has a userform that has the following code:

Private Sub CommandButton3_Click()
Sheets("BALANCE SHEET").Select
Rows("4:9141").Select
Selection.EntireRow.Hidden = True
For Each C In Worksheets("BALANCE SHEET").Range("a1:a10000") ' Adjust your range if necessary
If C.Value = ActiveWorkbook.CustomDocumentProperties("list").Value Then
C.Rows.Select
Selection.EntireRow.Hidden = False
End If
Next C
ActiveWindow.SmallScroll Down:=-1
Me.Hide
End Sub


Private Sub deletebutton_click()
If ListBox2.ListIndex = -1 Then Exit Sub
ListBox2.RemoveItem ListBox2.ListIndex
End Sub
Private Sub addbutton_click()
Dim i As Integer
For i = 0 To ListBox2.ListCount - 1
If ListBox1.Value = ListBox2.List(i) Then
Beep
Exit Sub
End If
Next i
ListBox2.AddItem ListBox1.Value
End Sub


Private Sub ListBox2_Click()
ActiveWorkbook.CustomDocumentProperties("list").Value = ListBox2.AddItem

End Sub


Ok, The user chooses an item from listbox1(Using the rowsource prop. from "balance sheet"), then clicks the add button so that his choice appears in listbox2. This works. Next upon the user clicking the "OK" commandbutton3_click(), the sheet"balance sheet" is selected(shown) and hides 9,137 rows, then shows the one row that was selected from listbox2 using the Custom Document Properties. This doesnt work?? I know its the code- but Im not sure of three things: Is this the right way to go about this task, If it is then how do you show the value in the custom documents properties so that the code can get the value and last, Im going to let the user choose more than one item to be put into listbox2, therefor more than one row will have to be unhidden upon clicking commandbutton3. Can anyone sugest something? Thankyou
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
last command should be

Private Sub ListBox2_Click()
ActiveWorkbook.CustomDocumentProperties("list").Value = ListBox2.Value
End Sub


Ivan

Also

Private Sub CommandButton3_Click()
Sheets("BALANCE SHEET").Select
Rows("4:9141").EntireRow.Hidden = True
v = ActiveWorkbook.CustomDocumentProperties("list").Value
Application.ScreenUpdating = False

For Each c In Worksheets("BALANCE SHEET").Range("a4:a9141") ' Adjust your range if necessary
If c.Value = v Then
c.Rows.EntireRow.Hidden = False
End If
Next c
Application.ScreenUpdating = True
Me.Hide
End Sub
 
Upvote 0
Ivan- Your code does work, however I have two problems with it.
1) It only allows me to select 1 value in list box2. Even if I change the list box multi property to multi, then theres no value showing at all! I really need the user to be able to select a number of items from listbox1 to be put in listbox2. One way to get around this problem is to use more than one listbox to show multiple values from listbox1.I would just add more CustomDocumentProperties for each new list box. This does work ,but not what I had in mind. But the biggest problem im having is by using the add commandbutton to list the selection of listbox1 to listbox2 is that it doesnt reset the value, I have to actually click on the selection in listbox2 for it to work. Any suggestions??
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top