named ranges

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Contact Us About Resources Consent preferences Sign in

TrustPilot

starstarstarstarstar Excellent

Request Callback We will call you back  0207 987 3777 Call for assistance Your Basket Basket is empty
Callback Sign in Basket
Menu

Call us on 0207 987 3777 or Search our website

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Named Ranges

Named Ranges

resolvedResolved · Medium Priority · Version 2007

James has attended:
Excel VBA Intro Intermediate course

Named Ranges

Hello,

I have named a range in one worksheet and want to assign this range to an Active X "Combo box" in another worksheet. I have tried inputting the name of the range in the proprerty "ListFillRange", but it keeps deleting the name as soon as I hit enter.

Does anyone know how I can sort this?

Thanks in advance!

James

RE: Named Ranges

Hi James

Thanks for getting in touch. ActiveX controls can be strange things. You are better served populating it through code like this:

Sub WorkSheet_Activate()

OLEObjects("ComboBox1").ListFillRange = "MyRangeName"
ComboBox1.ListIndex = 0

End Sub

This code goes on the individual sheet's code (double click the sheet name in the VBE).

Alternatively, consider using a VBA userform. Then after dragging the combo box on to the grid, find the RowSource property and type the range name in there.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Named Ranges

Hi Gary,

Thanks for your response. I tried the code but it has come back with an error "invalid property value".

I was able to create the combobox with the list using a userform as per your advice, but I would prefer not to have the userform (i.e. I would like the combobox on the worksheet itself). Do you know how to solve the error in the code?

Regards

James

RE: Named Ranges

Hi James

A few points I didn't make clear above that you should check: change ComboBox1 to the actual name of the combo box, and change MyRangeName to the actual range name you are using.

Are these correct?

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Named Ranges

Hi Gary,

Yes I did that but it came up with the error.

Regards

James

RE: Named Ranges

Hi James

Difficult to tell what's happening here. Are you able to send the sheet over to me? My address is gary@microsofftraining.net

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

RE: Named Ranges

Hi James

I've had a look at your file. The range name isn't a true range name. It's a table name.

Most of the time you can use this value no problem in a formula or VBA function, but it appears this is one case where you can't use it like that.

I'm certain there is a proper way to fix it, but in the meantime, convert it to a regular range name instead and the ComboBox works fine.

Kind regards

Gary Fenn
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

 

Training courses

 

Recommend training98.7 %
recommend us
More live stats...

Training information:

Active questions
Advanced/consultancy questions
Today's questions
Unanswered questions
Popular forum topics

See also:
Hints and tips
Forum topics

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel articles

Please browse our web site to find out more about
excel training companies london and other training courses.

Excel tip:

Calculate difference between two times

For presenting the result in the standard time format (hours : minutes : seconds . Use the subtraction operator (-) to find the difference between times, and the TEXT function to format the returned value to text in a specific number format.

Hours never exceed 24, minutes never exceed 60, and seconds never exceed 60.

=TEXT(B2-A2,"h")
Hours between two times (4)

=TEXT(B2-A2,"h:mm")
Hours and minutes between two times (4:55)

=TEXT(B2-A2,"h:mm:ss")
Hours and seconds between two times (4:55:00)

Where B2 and A2 must hold the end time and start time respectively formatted as a time format

View all Excel hints and tips


Request Callback

We will call you back

0207 987 3777

Call for assistance

Security verified Microsoft Partner

Formerly Best Training
© 2024 STL. All Rights Reserved.
All prices offered for business users and exclude VAT. E&OE

2nd Floor, CA House, 1 Northey Street, Limehouse Basin, London, E14 8BT. United Kingdom

Terms & Conditions Privacy & Cookie Policy Consent preferences

Server loaded in 0.08 secs.