Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

Excel Form List Box Populate With Dynamic Range Issue

All,

I have an excel file with a sheet called bookings and a form call frmBookings.

The form is used to capture bookings for people and the resultant records are added to the "bookings" sheet.

I have already set up a named range for the people in the table as well as a Dynamic range name using the worksheet change event.

What I am wanting to do is populate the List Box on the form with the distinct names of the people stored in the table (sheet bookings).

I need this to occur each time the user opens the form or adds a record.

Can someone please help as I am stuck with it.

CF
Test.xls
0
creativefusion
Asked:
creativefusion
  • 3
  • 2
1 Solution
 
krishnakrkcCommented:
Hi,

Replace Userform_intialize codes with the following

Private Sub UserForm_Initialize()
    
    Dim CompName
    
    With Sheets("Bookings")
        CompName = UNIQUE(.Range("a1").CurrentRegion.Columns(1).Offset(1))
    End With
    
    txtName.Value = ""
    txtPhone.Value = ""
    With cboDepartment
        .AddItem "Sales"
        .AddItem "Marketing"
        .AddItem "Administration"
        .AddItem "Design"
        .AddItem "Advertising"
        .AddItem "Dispatch"
        .AddItem "Transportation"
    End With
    cboDepartment.Value = ""
    With cboCourse
        .AddItem "Access"
        .AddItem "Excel"
        .AddItem "PowerPoint"
        .AddItem "Word"
        .AddItem "FrontPage"
    End With
    cboCourse.Value = ""
    optIntroduction = True
    chkLunch = False
    chkVegetarian = False
    Me.ListBox1.List = CompName
    
    txtName.SetFocus
End Sub
Function UNIQUE(ByRef r As Range)
    Dim v, i As Long
    
    v = r.Value2
    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For i = 1 To UBound(v, 1)
            If Len(v(i, 1)) Then .Item(Trim$(v(i, 1))) = Empty
        Next
        If .Count Then UNIQUE = .keys
    End With
End Function

Open in new window


Kris
0
 
creativefusionAuthor Commented:
Hi and thanks for your suggestion.

Just to clarify please.

What is CompName = UNIQUE(.Range("a1").CurrentRegion.Columns(1).Offset(1))

Where is Current Region in the range names? Do I need to create this? If so, please specify.
0
 
krishnakrkcCommented:
You could replace that line with

CompName = UNIQUE(Range("CourseName"))

CurrentRegion is hte property of range.

Kris
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
creativefusionAuthor Commented:
Okay. Thanks and got that.

I replaced my initialize code with yours and amended the range name from CurrentRegion to CourseName and I get an error.

Run Time Error '438':
Onject doesn't support this method.

??
0
 
Rory ArchibaldCommented:
(Not for points)

"and amended the range name from CurrentRegion to CourseName and I get an error."

CurrentRegion is not a range name, it's a property of the Range object (it's basically what you get by selecting a cell and pressing Ctrl+A - i.e. the whole table enclosing that cell.)

You should be using the line that Kris posted last.
0
 
creativefusionAuthor Commented:
Great help. Thanks.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now