asked on 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
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
Microsoft Excel
Last Comment
creativefusion
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
You could replace that line with
CompName = UNIQUE(Range("CourseName")
CurrentRegion is hte property of range.
Kris
CompName = UNIQUE(Range("CourseName")
CurrentRegion is hte property of range.
Kris
ASKER
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.
??
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.
??
(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.
"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.
ASKER
Great help. Thanks.
Just to clarify please.
What is CompName = UNIQUE(.Range("a1").Curren
Where is Current Region in the range names? Do I need to create this? If so, please specify.