Results 1 to 6 of 6

cannot change userform combobox range error 457

Threaded View

cfinch100 cannot change userform... 01-08-2014, 04:22 PM
Solus Rankin Re: cannot change userform... 01-08-2014, 04:26 PM
cfinch100 Re: cannot change userform... 01-08-2014, 04:34 PM
Solus Rankin Re: cannot change userform... 01-08-2014, 04:41 PM
cfinch100 Re: cannot change userform... 01-08-2014, 04:46 PM
cfinch100 Re: cannot change userform... 01-08-2014, 05:22 PM
  1. #1
    Forum Contributor
    Join Date
    10-01-2012
    Location
    england
    MS-Off Ver
    Excel 2016 pro
    Posts
    772

    cannot change userform combobox range error 457

    hi
    a userform has this code

    Option Explicit
    Private Sub ComboBox1_Change()
        Dim idx As Long
        Dim rsource As Long
        Dim rowsource As Long
        Dim i As Long
        Dim rw As Long
        Dim arrIDS As Variant
        Dim arrFilteredIDS As Variant
        idx = ComboBox1.ListIndex
        If idx <> -1 Then
            ListBox1.Clear
            With Sheets("repairs")
                With .Range("a2", .Range("a" & Rows.Count).End(xlUp))'changed to("f2", .Range("f"   ect
                    arrIDS = Evaluate("TRANSPOSE(repairs!" & .Address & "&""-""&ROW(" & .Address & "))")
                End With
            End With
            arrFilteredIDS = Filter(arrIDS, ComboBox1.Value, True)
            For i = LBound(arrFilteredIDS) To UBound(arrFilteredIDS)
                rw = Replace(arrFilteredIDS(i), ComboBox1.Value & "-", "")
                With ListBox1
                    .AddItem ComboBox1.Value
                  '  .List(.ListCount - 1, 1) = Sheets("repairs").Range("a" & rw).Value
                    .List(.ListCount - 1, 2) = Sheets("repairs").Range("b" & rw).Value
                    .List(.ListCount - 1, 3) = Sheets("repairs").Range("c" & rw).Value
                    .List(.ListCount - 1, 4) = Sheets("repairs").Range("i" & rw).Value
                    .List(.ListCount - 1, 5) = Sheets("repairs").Range("j" & rw).Value
                    .List(.ListCount - 1, 6) = Sheets("repairs").Range("f" & rw).Value
                    .List(.ListCount - 1, 7) = Sheets("repairs").Range("l" & rw).Value
                    .List(.ListCount - 1, 8) = Sheets("repairs").Range("n" & rw).Value
                    
                    Dim Rownum As Long
                    .ColumnWidths = "1.0 in;0;3.0in;1.0 in;1.5 in;1.5 in;1.5 in"
                End With
            Next i
        End If
    End Sub
    Private Sub TextBox1_Change()
    'Me.TextBox1.Value = ListBox6.ListCount
    End Sub
    Private Sub TextBox2_Change()
    End Sub
    
    Private Sub CommandButton1_Click()
    Unload Me
    End Sub
    
    Private Sub CommandButton2_Click()
    Unload Me
    End Sub
    
    Private Sub ListBox1_Click()
    
    End Sub
    
    Private Sub UserForm_Initialize()
    
     Call RemoveCaption(Me)
    
        With Application
            Width = .Width
            Height = .Height
        End With
    
    
    
    
    
    
        Dim dicIDs As Object
        Dim rngIDs As Range
        Dim cl As Range
       
        With Sheets("repairs")
            Set rngIDs = .Range("a2", .Range("a" & Rows.Count).End(xlUp))  'changed to ("f2", .Range("f"   ect
        End With
        Set dicIDs = CreateObject("Scripting.Dictionary")
        For Each cl In rngIDs.Cells
            If Not dicIDs.exists(cl.Text) Then
                dicIDs.Add cl.Value, cl.Text
            End If
        Next cl
        ComboBox1.List = dicIDs.keys
    End Sub
    this works fine if range is a2 but if I change it to f2 it has error457 (this key is already associated with an element of this collection)can anyone tell me why
    it will not work please
    cheers colin
    Last edited by cfinch100; 01-08-2014 at 05:19 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Change Userform Label based on ComboBox Selection
    By rbyrd023 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-02-2014, 09:25 AM
  2. Validating userform combobox selection with vba and getting an error
    By drew.j.harrison in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-22-2013, 11:27 AM
  3. [SOLVED] Change Labels in Userform based on selection in ComboBox
    By johnw993 in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 01-03-2013, 10:52 PM
  4. [SOLVED] UserForm ComboBox Change Event Question
    By tja1964 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-10-2012, 07:21 PM
  5. Rowsource property error in combobox userform
    By asha3010 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-15-2010, 08:06 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1