Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 2 of 2

Thread: ListBox: transfer selected values to range and delete deselected values from range

  1. #1
    Join Date
    16th June 2017
    Posts
    1

    ListBox: transfer selected values to range and delete deselected values from range

    Hello,

    I would like to select and deselect values from a listbox (located on "Table"), and those values to be transferred to a range of cells located on a different worksheet (B7 to below) called "Criteria". Currently, my code works well when I select values, but if I unselect them, the values still appear on the range.

    Basically, I would like range(B7 to below) to always show what is currently being selected in the listbox.

    How would you change this code to reflect that?

    Thanks a lot

    VB:
    Private Sub ListBox1_Click() 
        Dim a, i&, r&, c& 
         
         
         
         
         
        With ListBox1 
            a = .List 
            For r = 0 To UBound(a) 
                If .Selected(r) Then 
                    For c = 0 To UBound(a, 2) 
                        a(i, c) = .List(r, c) 
                    Next 
                    i = i + 1 
                End If 
            Next 
        End With 
        If i Then 
             ' Copy selected rows to destination
            Worksheets("Criteria").Range("B7").Resize(i, UBound(a, 2) + 1) = a 
             
             
        Else 
            MsgBox "Items are not selected", vbExclamation 
        End If 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    10th January 2007
    Location
    Borneo
    Posts
    3,182

    Re: ListBox: transfer selected values to range and delete deselected values from rang

    Maybe this
    VB:
    Private Sub ListBox1_Click() 
        Dim a, b, i&, r&, c& 
         
        With ListBox1 
            a = .List 
            Sheets("Criteria").Range("B7").Resize(.ListCount, UBound(a, 2) + 1).ClearContents 
            For r = 0 To UBound(a) 
                If .Selected(r) Then 
                    i = i + 1: Redim Preserve b(1 To UBound(a, 2) + 1, 1 To i) 
                    For c = 0 To UBound(a, 2) 
                        b(c + 1, i) = .List(r, c) 
                    Next 
                End If 
            Next 
        End With 
        If i > 0 Then 
             ' Copy selected rows to destination
            Sheets("Criteria").Range("B7").Resize(UBound(b, 2), i) = Application.Transpose(b) 
        Else 
            MsgBox "Items are not selected", vbExclamation 
        End If 
         
    End Sub 
    
    
    We now have a reputation system in place. It can be found on the 'Star' icon on the bottom left hand side of the post

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Replies: 0
    Last Post: May 12th, 2009, 16:32
  2. Transfer Range Values To Another Sheet
    By sparcosso in forum Excel General
    Replies: 4
    Last Post: March 14th, 2009, 19:20
  3. Unique Values From Selected Range
    By Mavyak in forum Excel General
    Replies: 10
    Last Post: January 16th, 2008, 00:12
  4. Transfer Values of 1 Range To Another Workbook
    By jwpfin6 in forum Excel General
    Replies: 1
    Last Post: October 19th, 2007, 03:56
  5. Replies: 4
    Last Post: August 10th, 2007, 17:12

Bookmarks

Posting Permissions

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