access course microsoft training - Chnaging range names

The UK's most regular instructor-led training courses.
Training information: access course microsoft training · Microsoft.access.courses · Microsoft Office VBA training

Resolved · Low Priority · Version Standard

Chnaging range names

Alex has attended:
Excel VBA course

by - delegate Alex [3 posts] (2007 Oct 5 Fri, 10:47) Reply

I have range name which is used as the rowsource in a userform.

I want to be able to have the size of this range name be checked and changed when ever the form is called in order to ensure that only “live” rows are included in the list box selection.

The code I have written checks the size of the range using the excel row and match functions to find the start and end rows and should then define the range name size using these limits:

Dim FirstCountryRow As String
Dim LastCountryRow As String

Set FirstCountryRow = Row("Startcountrylist")
Set LastCountryRow = FirstCountryRow + (Match("[Spare]", "FullCountryList", 0) - 2)

ActiveWorkbook = "CountryList"
ActiveWorkbook.Names.Add Name:="TempCountryList", RefersToR1C1:="=CountryList!R4C1:R11C1"

However it’s not working and I’m having trouble debugging it so any help would be gratefully received.

Version	Date	Location	Places available	Book	Next place rate:
Access Intermediate 1 day course
Version	Date	Location	Places available	Book	Card	Invoice
2003	2008 Sep 9 Tue	Bloomsbury	3	book now	£210	£215
2002/XP	2008 Sep 11 Thu	Tooting	0	FULL
2003	2008 Sep 19 Fri	Bayswater	5	book now	£195	£205
2007	2008 Sep 23 Tue	Bloomsbury	5	book now	£212	£224
2002/XP	2008 Sep 24 Wed	Tooting	0	FULL
2003	2008 Oct 7 Tue	Southwark	6	book now	£193	£215
Full Schedule: See all 72 Access Intermediate course dates. Bookings currently available until 24th November 2009.

RE: Chnaging range names

by - trainer Rich platinum contributer [713 posts] (2007 Oct 29 Mon, 12:45) Reply

Hi Alex,

Our VBA trainers have had a look at this, and as this is quite complicated, would recommend coming on to our Access VBA course or arrange a one-to-one consultancy or tailored training.

Contact our enquiries team for more information.

Regards, Rich

· MS Access Top Tips: Takeaways From Microsoft Access Advanced Training
· Microsoft Access Courses
· Do You Have Trouble Understanding Databases? Consider a One-on-One Access Course
· Material covered on a Microsoft Access Course
· The Benefits of Certified Microsoft Training in Access

Access tip:

Switchboard Startup

An option when you open your Access database that contains all of you tables, forms and queries is only the switchboard is displayed.

To achieve this, select Tools click on Startup. On the right hand side of the Startup screen you are able to select which form you want to open when you open you Access Database. Once you have selected this, there is a check box just below that says "Display Database Window", you need to uncheck this option. Then click ok.

Then when you close you Access database and re open it, the database should open with just the switchboard being shown

View all Access hints and tips

Institute of IT Training - Accredited Training Provider

Prodigy Platinum Learning Partner

secure online payments - visa - mastercard

Mini sitemap. These are the main areas of our web site. Full sitemap.

Training by application

Main information pages

access course microsoft training - Chnaging range names

Related articles

Access tip:

Switchboard Startup