Ivan-(selecting rows)

[Todd_M]

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

--------------------------------------------------------------------------------

Ivan F Moala
MrExcel MVP

Joined: Feb 11, 2002
Posts: 120
From: Auckland, New Zealad
Posted: 2002-03-09 23:39
--------------------------------------------------------------------------------
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





--------------------------------------------------------------------------------

Todd_M
Board Regular

Joined: Feb 25, 2002
Posts: 22 Posted: 2002-03-10 10:49
--------------------------------------------------------------------------------
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??

[Ivan F Moala]

Sorry Todd

Just saw the post......probably easier
if you emailed me the book....I can also
check out an alternative IF ness....


Ivan