Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Ivan-(selecting rows)

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    117
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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??


  2. #2
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sorry Todd

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


    Ivan

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •