Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2005
    Posts
    1

    Unanswered: Pasting Problem in Macro...

    First post on this forum. Hello, all...

    Here's the deal: I have to clear out my ComboBox list before running my macro, and it's messing up my results. If I do not clear out the ComboBox list, everything works fine, but then the list has repeated information in it. Here's the code, and the WB is attached:

    Code:
    Public Sub Finish_Click()
         
        Application.ScreenUpdating = False
         
        Sheets("Invoice").Range("B1:B2").Copy
         
        Sheets("Database").Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial _
        Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
         
        Range("C8:F8").Copy
         
        Sheets("Database").Range("A65536").End(xlUp).Offset(0, 2).PasteSpecial _
        Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
         
        Application.ScreenUpdating = True
         
    End Sub
    Private Sub Worksheet_Activate()
         
        Application.ScreenUpdating = False
         
        Dim cbCVals
        Dim i As Integer
         
        cbCVals = Array("Axle", "Wheel")
         
        cbComponents.Clear
        cbComponents2.Clear
         
        For i = 0 To 1
            cbComponents.AddItem cbCVals(i)
            cbComponents2.AddItem cbCVals(i)
        Next i
        
        cbComponents.ListIndex = 0
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
         
    End Sub
    How do I modify this to 1) keep unique entries in the ComboBox list and 2) prevent the list from clearing out entirely? I think the code modification will be simple, but I'm simply not getting it.

    Thanks for any assistance, folks!

    Bubbis Thedog

  2. #2
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Hi and Welcome,

    im guessing at what your after just now,
    i have added a routine to pick unique values from your list to fill your combo,
    but im probably getting the wrong end of the stick

    Code:
    Private Sub Worksheet_Activate()
         
        Application.ScreenUpdating = False
         
        Dim cbCVals() As String
        Dim i As Integer
        Dim rngLastcell As Range
        Dim rngFind As Range
        
        'set up the lastcell
        Set rngLastcell = Worksheets("Database").Cells.Find("*", , , , , xlPrevious)
        
        'clear the 2 comboboxes
        cbComponents.Clear
        cbComponents2.Clear
         
         
        'exit if no data in sheet
        If rngLastcell Is Nothing Then Exit Sub
        
        'Fill array from values from the database sheet
        'ive added this loop to show how to get unique values from a list
        'into an array
        With Worksheets("Database")
            For i = 2 To rngLastcell.Row
                If i = 2 Then
                    If IsEmpty(.Cells(i, 1)) Then Exit Sub
                    ReDim cbCVals(i - 1)
                    cbCVals(i - 1) = .Cells(i, 1).Value
                Else
                    'check to see if value already present
                    Set rngFind = .Range(.Cells(2, 1), .Cells(i - 1, 1)).Find(.Cells(i, 1).Value)
                    If rngFind Is Nothing Then
                        'if not add a new value to the array
                        ReDim Preserve cbCVals(UBound(cbCVals) + 1)
                        cbCVals(UBound(cbCVals)) = .Cells(i, 1).Value
                    End If
                End If
            Next i
        End With
        
        'using the array fill the comboboxes
        For i = LBound(cbCVals) + 1 To UBound(cbCVals) '+1 on lbound as started at 1
            cbComponents.AddItem cbCVals(i)
            cbComponents2.AddItem cbCVals(i)
        Next i
        
        'set the first value of the first combobox
        cbComponents.ListIndex = 0
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
         
        Set rngLastcell = Nothing
        Set rngFind = Nothing
    End Sub
    if you need to keep your combovalues in i would suggest filling your array with wach value of your combo then adding a routine to the combo fill to search if there exists a value in your array or a value in the list previous

    just a few thoughts

    Dave

Posting Permissions

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