Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel - Create Combo Code (2000)

    Hi everybody:

    I've created an excel spreadsheet with 3,000 rows of data. I need to provide it to a colleague, who will fill values into two empty columns. I want to give him a combo box that will provide a range of values for each column. The combo needs to be linked to the cell where it appears on the spreadsheet, so after he makes his selections, I can download the data for each record.

    My problem is that I can't seem to get Excel VBA to create a combo for each cell in the column. I tried recording a macro, but it didn't record any of the properties I need (listfill range, linked cell). It doesn't let me create a reference to the newly created combo box so I can change its properties, nor does it allow me to include those properties as arguments in the ActiveSheet.OLEObjects.Add(ClassType:="Forms.Combo Box.1") method.

    Here is the macro that Excel recorded:

    ' comp2 Macro
    ' Macro recorded 3/17/2003 by Kriss
    '
    Range("L4").Select
    ActiveSheet.OLEObjects.Add(ClassType:="Forms.Combo Box.1", Link:=False, _
    DisplayAsIcon:=False, Left:=423, Top:=235.5, Width:=75, Height:=18.75 _
    ).Select
    Range("L5").Select
    ActiveSheet.OLEObjects.Add(ClassType:="Forms.Combo Box.1", Link:=False, _
    DisplayAsIcon:=False, Left:=423, Top:=311.25, Width:=75, Height:= _
    17.25).Select
    ActiveWindow.SmallScroll Down:=3
    Range("L6").Select
    ActiveSheet.OLEObjects.Add(ClassType:="Forms.Combo Box.1", Link:=False, _
    DisplayAsIcon:=False, Left:=422.25, Top:=411.75, Width:=76.5, Height _
    :=16.5).Select

    As you can see, the listfillrange and linked cell properties I set didn't get recorded. The only thing the macro changed as it looped down each cell in the column was the "top" value. So, I tried setting a reference to the newly-created combo box to set its properties, but don't know how to do it.

    Set cb = activeworksheet.OLEObjects.Add(ClassType:="Forms.C omboBox.1", Link:=False, DisplayAsIcon:=False, Left:=423, Top:=235.5, Width:=75, Height:=18.75 ) gets an "object required" error message.

    Leaving out the set statement (the way the macro did) gets first "cannot enter break mode at this time" and then an "object variable or with block variable not set" error message.

    I can't seem to please it!

    Here is my code attempt:

    sub createcombo()
    Dim cb As OLEObject, rngCell As Range, shtFMA As Worksheet
    Set shtFMA = Worksheets("FMA")
    Dim Counter As Integer
    For Counter = 7 To 10 'for testing; we'll need 6,000 of these later
    Set rngCell = Sheet1.Cells(Counter, 12)
    With rngCell
    .Select
    Set cb = shtFMA.OLEObjects.Add(ClassType:="Forms.ComboBox.1 ", Link:=False, _
    DisplayAsIcon:=False, Left:=423, Top:=235.5, Width:=75, Height:=18.75 _
    ).Select
    End With

    cb.LinkedCell = rngCell.Address
    cb.ListFillRange = "R3:R43"
    Next Counter
    end sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel - Create Combo Code (2000)

    Have you tried DATA VALIDATION. This would be much easier to create a column of validations for 2 columns with lists than comboboxes.

    If the LIST in all the cells in col1 is the same and a separate list for column 2, then create the data validation for the first row and copy it down the columns.

    Steve

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel - Create Combo Code (2000)

    Thank you, but that wasn't the approach I wanted, because in some cases, a new value might be added.
    I figured out the code, but after adding 6,000 combo boxes, Excel ran out of memory, so I did the job using an Access form.

    Sub makecombos()
    On Error Resume Next
    'Code to create a combo box in each cell of column F
    'with row height set to 100 for 3085 rows on sheet

    Dim cb As OLEObject, rngCell As Range, shtFMA As Worksheet, lngTop As Single
    Set shtFMA = Worksheets("FMA")
    Dim Counter As Integer
    lngTop = 297695.25 'Set this to top of the first combo you used macro recorder to create;
    'subsequently, the last combo after which you want to add more
    For Counter = 3001 To 3085 'This was used to create the last 85 combos
    lngTop = lngTop + 99 'for row height of 100
    shtFMA.Cells(Counter, 11).Select
    shtFMA.OLEObjects.Add(ClassType:="Forms.ComboBox.1 ", Link:=False, _
    DisplayAsIcon:=False, Left:=373.5, Top:=lngTop, Width:=75, Height:=18.75).Select

    Next Counter

    Set shtFMA = Nothing

    End Sub

    Sub fixcombo() 'Set listfill and linkedcell properties for combo boxes on sheet
    '
    Dim shtFMA As Worksheet
    Dim combo As OLEObject
    Set shtFMA = Worksheets("Sheet4")


    For Each combo In shtFMA.OLEObjects
    If InStr(1, combo.Name, "Combo") = 1 Then 'Make sure it's a combo box
    With combo
    .ListFillRange = "R3:R40" 'Put your range here to fill the combo selections
    .LinkedCell = "K" & CStr(combo.Index) 'Use the index property to link the combos to each row number
    End With
    End If
    Next

    End Sub

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel - Create Combo Code (2000)

    You could also just user a userform in VB instead of access. Have the userform put the entries in the proper cells.

    Steve

Posting Permissions

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