none
How to set range in Excel UserForm With ComboBoxes

    Question

  • Hi, i found a tutorial on Userform with comboboxes on contextures site : http://www.contextures.com/xluserform02.html

    This tutorial is designed to "find first empty row in database"

    How can I make it to fill a specific range lets say A2:E10 then it stops?

    I am new to VBA. Thank you for your help

    Thursday, November 10, 2011 4:56 AM

All replies

  • The website you provided is loading the combo boxes using the code below.  Additem is inserting a new row into the combobox and inserting the cPart which is the index pointer from the "FOR" statement.

    For Each cPart In ws.Range("PartIDList")
      With Me.cboPart
        .AddItem cPart.Value
        .List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
      End With
    Next cPart

    For Each cLoc In ws.Range("LocationList")
      With Me.cboLocation
        .AddItem cLoc.Value
      End With
    Next cLoc

     

    "PartIDList" and are named ranges in on the worksheet.  Yo can load a specific range likt this

    Set ws = sheets("sheet1")

    For Each cPart In ws.Range("A2:E10")

        'then put the rest of the for loop in here

    Or This

    For Each cPart In Sheets("Sheet1").Range("A2:E10")

        'then put the rest of the for loop in here


     


    jdweng
    Thursday, November 10, 2011 10:06 AM
  • Hi, jdweng thank you for your reply but I am still having some difficulties. I am a beginner in this VBA stuff. It still functions pretty much as before.

    Could you please edit the whole code on the site and set it to the specific range ("A2:E10) for me to see how it is done?

    Thank you for your help


    adonteng
    Thursday, November 10, 2011 8:13 PM
  • the part ID's are in column A in the example and the location is in column E.  I commented out the original lines of code and replaced with the changes.  The webpage instruction under the heading "Create Dynamic Named Ranges" sets up named ranges.  Instead of using the named range I'm using specific ranges on the worksheet.

     

    Private Sub UserForm_Initialize()
    Dim cPart As Range
    Dim cLoc As Range
    Dim ws As Worksheet
    Set ws = Worksheets("LookupLists")
    
    'For Each cPart In ws.Range("PartIDList")
    For Each cPart In ws.Range("A2:A10")
      With Me.cboPart
        .AddItem cPart.Value
        .List(.ListCount - 1, 1) = cPart.Offset(0, 1).Value
      End With
    Next cPart
    
    'For Each cLoc In ws.Range("LocationList")
    For Each cLoc In ws.Range("E2:E10")
      With Me.cboLocation
        .AddItem cLoc.Value
      End With
    Next cLoc
    
    Me.txtDate.Value = Format(Date, "Medium Date")
    Me.txtQty.Value = 1
    Me.cboPart.SetFocus
    
    End Sub
    
    

     


    jdweng
    Thursday, November 10, 2011 9:50 PM
  • thanks for the explanation, i would like to send u file for you check a problem for me. is it possible to get email address or i have to paste it on this forum?
    adonteng
    Thursday, November 10, 2011 10:56 PM
  • joel dot warburg at itt dot com
    jdweng
    Friday, November 11, 2011 5:27 AM