Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 06-05-2012, 10:00 AM
tinfanide tinfanide is offline Different ways to access ListObject Headers??? Windows 7 64bit Different ways to access ListObject Headers??? Office 2010 32bit
Expert
Different ways to access ListObject Headers???
 
Join Date: Aug 2011
Posts: 312
tinfanide is on a distinguished road
Default Different ways to access ListObject Headers???

In VBA,
I know we can do things like:
Code:
Range("oTable[header1]').Select
to access a ListObject Header
But how about


Code:
[oTable].Select
''' anything like this???
[oTable["header1"]].Select ''' return error
Just wondering (not necessarily a matter at all) if there's a simpler way to access a range in a ListObject.
Reply With Quote
  #2  
Old 06-05-2012, 02:49 PM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Different ways to access ListObject Headers??? Windows 7 32bit Different ways to access ListObject Headers??? Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

You can access it via a ListObject object, for example:
Code:
Worksheets("Sheet1").ListObjects("oTable").ListColumns("Header1").Range.Cells(1).Select
You have to be careful with the [ ] because they are also shorthand for the Evaluate method in VBA. This would also potentially work, but (in my opinion) it is confusing and an ugly piece of code best avoided:
Code:
[oTable[[#Headers],[Header1]]].Select
Reply With Quote
  #3  
Old 06-06-2012, 09:30 AM
tinfanide tinfanide is offline Different ways to access ListObject Headers??? Windows 7 64bit Different ways to access ListObject Headers??? Office 2010 32bit
Expert
Different ways to access ListObject Headers???
 
Join Date: Aug 2011
Posts: 312
tinfanide is on a distinguished road
Default

Quote:
Originally Posted by Colin Legg View Post
You can access it via a ListObject object, for example:
Code:
Worksheets("Sheet1").ListObjects("oTable").ListColumns("Header1").Range.Cells(1).Select
You have to be careful with the [ ] because they are also shorthand for the Evaluate method in VBA. This would also potentially work, but (in my opinion) it is confusing and an ugly piece of code best avoided:
Code:
[oTable[[#Headers],[Header1]]].Select
Yeah. Just liked the shortened one. The ListObjects.ListColumns. too long.
Reply With Quote
  #4  
Old 06-06-2012, 10:59 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Different ways to access ListObject Headers??? Windows 7 32bit Different ways to access ListObject Headers??? Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

Why do you want to select a particular header cell in the first place?
Reply With Quote
  #5  
Old 06-07-2012, 06:46 AM
tinfanide tinfanide is offline Different ways to access ListObject Headers??? Windows 7 64bit Different ways to access ListObject Headers??? Office 2010 32bit
Expert
Different ways to access ListObject Headers???
 
Join Date: Aug 2011
Posts: 312
tinfanide is on a distinguished road
Default

Quote:
Originally Posted by Colin Legg View Post
Why do you want to select a particular header cell in the first place?
Scripting something that needs to get the column index of a ListColumn.
Code:
.Select
Just for testing purposes. In the real project, I don't need to select them.
Reply With Quote
  #6  
Old 06-07-2012, 06:59 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline Different ways to access ListObject Headers??? Windows 7 32bit Different ways to access ListObject Headers??? Office 2010 32bit
Expert
 
Join Date: Jan 2011
Location: UK
Posts: 369
Colin Legg will become famous soon enough
Default

Okay, this will give you the index of the column within the table itself:
Code:
Debug.Print Sheet1.ListObjects("oTable").ListColumns("Header1").Index
This code will give you the column number within the worksheet:
Code:
Debug.Print Sheet1.ListObjects("oTable").ListColumns("Header1").Range.Column
Reply With Quote
  #7  
Old 06-07-2012, 07:05 AM
tinfanide tinfanide is offline Different ways to access ListObject Headers??? Windows 7 64bit Different ways to access ListObject Headers??? Office 2010 32bit
Expert
Different ways to access ListObject Headers???
 
Join Date: Aug 2011
Posts: 312
tinfanide is on a distinguished road
Default

Quote:
Originally Posted by Colin Legg View Post
Okay, this will give you the index of the column within the table itself:
Code:
Debug.Print Sheet1.ListObjects("oTable").ListColumns("Header1").Index
This code will give you the column number within the worksheet:
Code:
Debug.Print Sheet1.ListObjects("oTable").ListColumns("Header1").Range.Column
Yes, thanks for telling me the difference between .Index and .Range.Column.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need headers on each sheet Mendopaul Excel 0 02-21-2012 05:51 PM
Different ways to access ListObject Headers??? Searching A Database Two Ways skoz55 Excel 3 11-10-2011 01:01 AM
Creative Ways for a year-to-year comparison??? ridonkulous5 Excel 1 03-23-2011 04:49 PM
Access 2007 Upgrade After Initial Install - Access 2003 Won't Remove wcb Office 1 01-25-2010 04:08 AM
Help with headers Bill Word 0 03-10-2009 05:15 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:04 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft