Subscribe in a readerWorking with Tables in Excel 2007 (VBA)
This article has also been published on Microsoft Office Online:
Working with Excel tables in Visual Basic for Applications (VBA)
Introduction
In Working with Tables in Excel 2007 I promised to add a page about working with those tables in VBA too. Well, here you go.
It's a ListObject!
On the VBA side there seems to be nothing new about Tables. They are addressed as ListObjects, a collection that was introduced with Excel 2003. But there are significant changes to this part of the object model and I am only going to touch on the basic parts here.
Creating a table
Converting a range to a table starts with the same code as in Excel 2003:
Sub CreateTable()
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$B$1:$D$16"), , xlYes).Name = _
"Table1"
'No go in 2003
ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight2"
End Sub
But the new stuff is right there already: TableStyles. A collection of objects which are a member of the Workbook object. This gives rise to some oddities. You can change the formatting of a tableStyle, e.g. like this:
Sub ChangeTableStyles()
'No Go in Excel 2003
ActiveWorkbook.TableStyles(2).TableStyleElements(xlWholeTable) _
.Borders(xlEdgeBottom).LineStyle = xlDash
End Sub
This changes the linestyle of the bottom of your table. But hold your horses! If you have any other workbook open, all tables with the same tablestyle appear in your changed style! But if you save your file, close Excel and open Excel again with the file, the changes are gone. This is because you've just changed a built-in tablestyle. If you ask me, I find it strange that the Workbook is a tablestyles' parent, whereas built-in table styles behave as if being bound to the Application object.
If you want full control over your table style, you'd better duplicate a built-in style and modify and apply that style to your table.
Listing the tables
Let's start with finding all tables on the active worksheet:
Sub FindAllTablesOnSheet()
Dim oSh As Worksheet
Dim oLo As ListObject
Set oSh = ActiveSheet
For Each oLo In oSh.ListObjects
Application.Goto oLo.Range
MsgBox "Table found: " & oLo.Name & ", " & oLo.Range.Address
Next
End Sub
This snippet of code works exactly the same in Excel 2003, so nothing new there (well, that is, in 2003 those tables ARE called Lists).
Selecting parts of tables
You might need to work with specific parts of a table. Here is a couple of examples on how to achieve that. The code comments show you where Excel 2003 differs from 2007.
Sub SelectingPartOfTable()
Dim oSh As Worksheet
Set oSh = ActiveSheet
'1: with the listobject
With oSh.ListObjects("Table1")
MsgBox .Name
'Select entire table
.Range.Select
'Select just the data of the entire table
.DataBodyRange.Select
'Select third column
.ListColumns(3).Range.Select
'Select only data of first column
'No go in 2003
.ListColumns(1).DataBodyRange.Select
'Select just row 4 (header row doesn't count!)
.ListRows(4).Range.Select
End With
'No go in 2003
'2: with the range object
'select an entire column (data only)
oSh.Range("Table1[Column2]").Select
'select an entire column (data plus header)
oSh.Range("Table1[[#All],[Column1]]").Select
'select entire data section of table
oSh.Range("Table1").Select
'select entire table
oSh.Range("Table1[#All]").Select
'Select one row in table
oSh.Range("A5:F5").Select
End Sub
As you may have spotted, Excel 2007 handles tables like they are range names. Well, that is exactly what is going on. After inserting a table, a range name is defined automatically. These range names are special though. Excel controls them entirely. You cannot delete them and they get renamed automatically when you change a table's name. Remove a table (convert back to range) and the defined name is removed as well.
Inserting rows and columns
Another part in which lists already had most of the functionality. Just a few new things have been added, like the "AlwaysInsert" argument to the ListRows.Add method:
Sub TableInsertingExamples()
'insert at specific position
Selection.ListObject.ListColumns.Add Position:=4
'insert right
Selection.ListObject.ListColumns.Add
'insert above
Selection.ListObject.ListRows.Add (11)
'NoGo in 2003
'insert below
Selection.ListObject.ListRows.Add AlwaysInsert:=True
End Sub
If you need to do something with a newly inserted row, you can set an object variable to the new row:
Dim oNewRow As ListRow
Set oNewRow = Selection.ListObject.ListRows.Add(AlwaysInsert:=True)
If you then want to write something in the first cell of the new row you can use:
oNewRow .Range.Cells(1,1).Value="Value For New cell"
Adding a comment to a table
This is something Excel 2003 cannot do and is related to the fact that a table is a range name. Adding a comment to a table through the UI is a challenge, because you have to go to the Name Manager to do that. In VBA the syntax is:
Sub AddComment2Table()
Dim oSh As Worksheet
Set oSh = ActiveSheet
'NoGo in 2003
'add a comment to the table (shows as a comment to
'the rangename that a table is associated with automatically)
'Note that such a range name cannot be deleted!!
'The range name is removed as soon as the table is converted to a range
oSh.ListObjects("Table1").Comment = "This is a table's comment"
End Sub
Convert a table back to a normal range
That is simple and uses the identical syntax as 2003:
Sub RemoveTableStyle()
Dim oSh As Worksheet
Set oSh = ActiveSheet
'remove table or list style
oSh.ListObjects("Table1").Unlist
End Sub
Special stuff: Sorting and filtering
With Excel 2007 we get a whole new set of filtering and sorting options. I'm only showing a tiny bit here, a Sort on cell color (orangish) and a filter on the font color. The code below doesn't work in Excel 2003. A List in 2003 only has the default sort and autofilter possibilities we have known since Excel 5 and which had hardly been expanded at all in the past 12 years or so.
Sub SortingAndFiltering()
'NoGo in 2003
With ActiveWorkbook.Worksheets("Sheet1").ListObjects("Table1")
.Sort.SortFields.Clear
.Sort.SortFields.Add( _
Range("Table1[[#All],[Column2]]"), xlSortOnCellColor, xlAscending, , _
xlSortNormal).SortOnValue.Color = RGB(255, 235, 156)
With .Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End With
'Only old autofilter stuff works in 2003
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2, _
Criteria1:=RGB(156, 0, 6), Operator:=xlFilterFontColor
End Sub
Accessing the formatting of a cell inside a table
You may wonder why this subject is there, why not simply ask for the cell.Interior.ThemeColor if you need the ThemeColor of a cell in a table? Well, because the cell formatting is completely prescribed by the settings of your table and the table style that has been selected. So in order to get at a formatting element of a cell in your table you need to:
- Find out where in your table the cell is located (on header row, on first column, in the bulk of the table
- Determine the table settings: does it have row striping turned on, does it have a specially formatted first column, ...
- Based on these pieces of information, one can extract the appropriate TableStyleElement from the table style and read its properties.
The function shown here returns the TableStyleElement belonging to a cell oCell inside a table object called oLo:
'-------------------------------------------------------------------------
' Procedure : GetStyleElementFromTableCell
' Company : JKP Application Development Services (c)
' Author : Jan Karel Pieterse
' Created : 2-6-2009
' Purpose : Function to return the proper style element from a cell inside a table
'-------------------------------------------------------------------------
Dim lRow As Long
Dim lCol As Long
'Determine on what row we are inside the table
lRow = oCell.Row - oLo.DataBodyRange.Cells(1, 1).Row
lCol = oCell.Column - oLo.DataBodyRange.Cells(1, 1).Column
With oLo
If lRow < 0 And .ShowHeaders Then
'on first row and has header
Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlHeaderRow)
ElseIf .ShowTableStyleFirstColumn And lCol = 0 Then
'On first column and has first column style
Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlFirstColumn)
ElseIf .ShowTableStyleLastColumn And lCol = oLo.Range.Columns.Count - 1 Then
'On last column and has last col style
Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlLastColumn)
ElseIf lRow = .DataBodyRange.Rows.Count And .ShowTotals Then
'On last row and has total row
Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlTotalRow)
Else
If .ShowTableStyleColumnStripes And Not .ShowTableStyleRowStripes Then
'in table, has column stripes
If lCol Mod 2 = 0 Then
Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlColumnStripe1)
Else
Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlWholeTable)
End If
ElseIf .ShowTableStyleRowStripes And Not .ShowTableStyleColumnStripes Then
'in table, has column stripes
If lRow Mod 2 = 0 Then
Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlRowStripe1)
Else
Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlWholeTable)
End If
ElseIf .ShowTableStyleColumnStripes And .ShowTableStyleRowStripes Then
If lRow Mod 2 = 0 And lCol Mod 2 = 0 Then
Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlRowStripe1)
ElseIf lRow Mod 2 <> 0 And lCol Mod 2 = 0 Then
Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlColumnStripe1)
ElseIf lRow Mod 2 = 0 And lCol Mod 2 <> 0 Then
Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlRowStripe1)
Else
Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlWholeTable)
End If
End If
End If
End With
End Function
You could use this function like this:
Dim oLo As ListObject
Dim oTSt As TableStyleElement
Set oLo = ActiveSheet.ListObjects(1)
Set oTSt = GetStyleElementFromTableCell(ActiveCell, oLo)
With ActiveCell.Offset(, 8)
.Interior.ThemeColor = oTSt.Interior.ThemeColor
.Interior.TintAndShade = oTSt.Interior.TintAndShade
End With
End Sub
Note that the function shown above does not take into account that you can set the width of the stripes, both vertically and horizontally.
Wrap Up
Of course there is more to learn and know about tables and lists. A good way to come acquainted with the VBA behind them is by recording macro's while fooling around with them. Luckily Microsoft did include the table object if it comes to recording your actions, unlike the omission on the charting side...
Comments
All comments about this page:
Comment by: Gilles Frechet (11/7/2007 2:57:31 PM)Hello:
I am currently trying to use a workbook which was developped using a prior version of Excel. This workbook contains multiple sheets and several large Macros. All of the Macros appear to work, however I have found that if I attempt to select a large range of cells in any of the sheets (by using the mouse or with the use of a Macro) the program slows down considerably and the larger the range selected, the longer the time it takes for the system to respond. I would appreciate any help and comments.
Comment by: Jan Karel Pieterse (11/7/2007 9:59:31 PM)Hi Gilles,
Without seeing the code this is hard to analyse. It may help to turn off screenupdating at the beginning of your code though:
Application.ScreenUpdating=false
Then at the end, turn it back on:
Application.ScreenUpdating=True
Comment by: Manuel (11/9/2007 7:40:44 AM)Necesito saber si como se aplica el
AutofilterMode?
Pasa activar o desactivar filtros
Comment by: Jan Karel Pieterse (11/10/2007 8:16:22 AM)Selection.AutoFilter
Will turn on autofilter
ActiveSheet.AutoFilterMode = False
will turn it off.
Comment by: akj (11/15/2007 11:31:19 PM)Excellent
Comment by: Johan Nordberg (12/8/2007 2:20:19 PM)An important difference between Excel 2003 lists and Excel 2007 tables is that the InsertRowRange property of the ListObject only works when the table is empty. When the table has data InsertRowRange returns nothing.
In that case you have to get the last row of the table and move down one row from that.
If anyone has a better solution, please let me know...
// Johan Nordberg
Comment by: Jan Karel Pieterse (12/9/2007 3:47:59 AM)Hi Johan,
Thanks for the comment. I think you have found the only solution to this problem indeed.
Comment by: Andrei Sheshka (1/9/2008 8:07:33 AM)Hi Johan!
At first you must activate ListObject to get InsertRowRange in Excel 2003.
Function GetInsertRow(objList As ListObject) As Range
objList.Parent.Activate
objList.Range.Activate
Set GetInsertRow = objList.InsertRowRange
End Function
Sub Test_GetInsertRow()
Dim lo As ListObject
Dim objListRng As Range
Set lo = Worksheets("Sheet3").ListObjects(1)
Set objListRng = GetInsertRow(lo)
objListRng.Select
End Sub
Comment by: Jose Manuel (1/31/2008 12:57:16 PM)Hi to all!
After 'insert below
Selection.ListObject.ListRows.Add AlwaysInsert:=True
How can I select the cell in the first column of the new row?
Thanks in advance
Comment by: Jan Karel Pieterse (2/3/2008 10:20:12 PM)Hi Jose,
Like this:
Selection.ListObject.Range.End(xlDown).Select
Comment by: Aindril De (5/29/2008 12:46:09 AM)This is real excellent stuff.
Can anyone advice any book that is available, that helps differentiate Excel 2003
VBA vs Excel 2007 VBA?
Thanks in advance
Comment by: Jan Karel Pieterse (5/29/2008 2:06:33 AM)Hi Aindril,
I'd recommend "Excel 2007 VBA programming Reference" (Stephen Bullen et al)
and
"Excel 2007 Power Programming with VBA" (John Walkenbach)
Comment by: Adele Summers (6/9/2008 8:19:09 AM)This may seem like a simple question, but can you set the data source of a table to
come from a sheet other than the current sheet that you are on?
Comment by: Jan Karel Pieterse (6/9/2008 10:18:44 AM)Hi Adele,
I'm not sure what you're looking for. What do you mean by "the data source"? Which
option are you referring to?
Comment by: Martin (6/19/2008 8:26:16 AM)i need call the dialog "Modify table Quick style"
Comment by: Jan Karel Pieterse (6/19/2008 10:51:14 AM)Hi Martin,
I had a look at Application.Dialogs(xlDialog......), but I could not find it.
Comment by: Ann Marie (7/1/2008 6:03:09 AM)How and where do you turn off screenupdating in Office Excel 2007?
Thank you.
Comment by: Jan Karel Pieterse (7/1/2008 10:21:56 AM)Hi,
Application.ScreenUpdating=False
at the start of your code
and
Application.ScreenUpdating=True
at the end.
Comment by: Matt (7/29/2008 3:38:08 PM)Fantastic Article! This has been extremely helpful in my projects.
One thing I'm struggling with is deleting multiple table rows. Recording a macro
of selecting the desired rows, right-clicking and selecting Delete > Table Rows
results in the following code repeated for each row selected:
Selection.ListObject.ListRows(1).Delete.
Running the macro is very, very slow relative to the action from the UI. I reduced
the code to loop through this, but it is still slow. I'm regularly deleting 1000+
rows. Any ideas as to how to streamline this?
Comment by: Mazhar Basa (10/3/2008 2:28:27 AM)I used the code for creating "table comment" however I cannot see anything on excel
sheet. When I msgbox the comment I can see it but on screen no?
Comment by: Jan Karel Pieterse (10/3/2008 5:17:10 AM)Hi Mazhar,
You see the comment if you type the table's name within a formula and you have
formula autocomplete turned on.
Comment by: Luc (10/6/2008 7:23:56 AM)Great job.
Comment by: Scott (10/17/2008 1:16:41 PM)How would you delete a table row based on selection.
"Selection.ListObject.ListRows.Delete"
Comment by: Jan Karel Pieterse (10/19/2008 9:22:17 PM)Hi Scott,
Not sure what you mean; is this a question or a suggestion?
Comment by: Michiel Kotting (1/16/2009 11:34:46 AM)How do you select the last row in a ListObject? In a normal range I use myRange.Rows(myRange.Rows.Count).Select, but in a ListObject I can't get it to work...
Similarly, how do I get the count of the number of rows in a ListObject?
thanks!
Comment by: Jan Karel Pieterse (1/18/2009 11:29:31 PM)Hi Michiel,
This selects the last row:
Dim oL As ListObject
Set oL = ActiveSheet.ListObjects(1)
oL.DataBodyRange.Rows(oL.DataBodyRange.Rows.Count).Select
Comment by: Michiel Kotting (1/19/2009 1:28:25 AM)Thanks, it works! I also picked up Excel 2007 VBA by Bullen e.a. at your recomendation.
Comment by: Mohan Kumar Karunakaran (2/19/2009 3:35:27 PM)Hi,
I have created a table using VBA, but I really want to stop the default text entered in the first row of the table. I will be working mostly on financial tables, which doesn't have any value on the first cell. If I apply table style using VBA, it adds "Column1" for the first cell, which is not necessary. Do you have any idea on how to restrict this.
Thanks,
Mohan
Comment by: Jan Karel Pieterse (2/20/2009 5:11:21 AM)Hi Mohan,
You cannot prevent the title row from appearing, as Excel needs that for referencing columns in the table. But you can tell Excel to hide the title row by unchecking the box "Header row" on the table tools tab of the ribbon.
Comment by: Rangarajan Vijayaraghavan (3/3/2009 8:52:46 AM)Excellent work! Thanks a ton!
Comment by: Dian Leger (3/16/2009 9:35:37 AM)I am using Excel 2007.
When I click in a cell to enter data, a range of cells is automatically selected.
Is there a way to stop this so that when I select a cell only one cell is selected?
Comment by: Jan Karel Pieterse (3/16/2009 11:37:29 AM)Hi Dian,
Odd, that is abnormal behaviour. Please check out my page on Excel start up problems, especially the part about addins:
www.jkp-ads.com/articles/startupproblems.asp
Comment by: Radek Kukuczka (3/17/2009 10:10:05 AM)Hello,
First of all - thanks for this useful guide!
I have a problem, which you may be able to help solve... I've created spreadsheet which automaticaly calculates data, based on used values. I am storing data in Excel 2007 tables and use INDEX function in excel to select required data from specific row in the table. It's all working perfectly.
What I need to do now is add a userf form wizzard. Some fields are combo boxes, and I need to load data from a column into these combos. How do I inicialize form to include proper items from a specific table into this combo box? I hope this makes sense, I'd appreciate your help.
thanks, Radek
Comment by: Jan Karel Pieterse (3/17/2009 11:10:54 AM)Hi Radek,
You can add the contents of a column in a table to a listbox quite easily, for example:
vValues = ActiveSheet.ListObjects(1).DataBodyRange.Columns(1).Value
ListBox1.List = vValues
Comment by: S Srinivas (3/23/2009 4:22:11 AM)Created a macro for sorting the excel worksheet according to colour . Created one command button and pasted the macro . Afterwardd when I run the command button , I am getting the following error.
Run-time error - 2147319765
Automation error
Element not found.
Pl help.
Thanks
Regards
S Srinivas
Comment by: Jan Karel Pieterse (3/23/2009 6:10:32 AM)Hi Srinivas,
I suggest you to go to this site to ask your question:
www.wopr.com/cgi-bin/w3t/login.pl?Cat=
Comment by: Radek Kukuczka (3/26/2009 9:54:14 AM)Hi Jan,
Thanks for the hint! It was very usefull.
Unfortunately I've hit another obstacle... Now, when I load the contens of column 1 to my user form, I need to relate the Cell Y in Row X with Cell Z in the same Row X.
I don't think this makes much sense... Let me explain.
I have a table with some data. In column 1 I have names which I load to the ComboBox in my user form. Column 2 contains a numeric ID(which isn't loaded anywhere), which I need to put in a specific cell when clicking OK in the form(this must be depending on what was choosen in the ComboBox).
I did some googling and this is what I've come up with. Please note the below doesn't work...
Dim i As Integer
For i = 1 To 29 // I have 29 rows in my table
If comboBox1.Value = Worksheets("Data").ListObjects("Table5").DataBodyRange.Columns(1).Rows(i).Value Then
ActiveWorkbook.Sheets("Parameter").Activate
Range("C18").Select
ActiveCell.Value = i
End If
Next i
As you can see, I'm nowhere with this script, I'd appreciate help
thanks, Radek
Comment by: Jan Karel Pieterse (3/27/2009 5:50:14 AM)Hi Radek,
You can simply load both columns into the listbox (which you set to have two columns and set the column width of the second column to zero) and set the boundcolumn property to the second column.
Now the listbox will show the first column, but return the value of the second column.
Comment by: Radek Kukuczka (3/30/2009 4:37:07 AM)Hello Jan,
thank you very much for this precious hint!
Once I set up the ComboBox properties as you advised, it does return the value I wanted. Apparently I noticed, that I could use the displayed value as well... can I somehow access it?
Is there any reference where I could familarize myself with object properties etc?
thanks, Radek
Comment by: Jan Karel Pieterse (3/30/2009 5:39:33 AM)Hi Radek,
I'd start with using F1 (Help), it is quite extensive.
You can access the other values using the list property:
'returns the value of the selected item in column 1
Texbox1.List(TextBox1.ListIndex,1)
'returns the value of the selected item in column 2
Comment by: Bharani (3/31/2009 2:13:33 AM)Thanks a lot....
Comment by: Tom Pirotte (4/17/2009 1:48:44 PM)Hello,
I have a question regarding tables in use with VBA.
I want to use a sheet as "database" for information.
Let me explain
When I open a new xls I have 3 sheets. Sheet1 ,2 and3.
I fill sheet1 with a table (5 x 2.)
When I save the XLS to XLA the sheet with info isn't visible anymore and I can't use my formula, which was written in VBA, to reach the data on the inputted sheet.
Altho in the VB editor I still see the 3 sheets in the structure. How can I reach the sheets in the xla by a self written function or procedure?
Or what is the best way to handle diffrent tables or sheets in a XLA.
Best regards,
Comment by: Jan Karel Pieterse (4/19/2009 7:18:52 AM)Hi Tom,
You should be able to read information from a worksheet contained in an Excel addin without trouble. Post your code here and I'll have a look at the code.
Comment by: Ray Bernard (6/1/2009 7:12:19 PM)For a cell within an Excel 2007 Table (the table is named "Table1"), with banded coloring of cells within the table, the .Interior.ColorIndex property of the cell returns "No fill" regardless of the cell color.
The code in the following post (due to post size limitations) is intended to change the color of a Wingding dot character in a cell based upon the contents of the adjacent cell. However, .Interior.ColorIndex always returns -4142 for both Green and White cells colored by Table banding.
Is the ColorIndex value only available through ListObjects("Table1")? If so, how would I do that? I am new to Excel Macro coding and can't seem to find a reference for the Table object model on the Web or in the Help.
I will submit the code next.
Comment by: Ray Bernard (6/1/2009 7:12:59 PM)Below is the code (provided to me by Ken Johnson) that goes with the previous post I submitted:
'Check for changes to any of the dropdown cells 4 columns to the right of the Tasks column
If Not Intersect(Target, Range("Tasks").Offset(0, 9)) Is Nothing Then
'Format the font color in the cells to the left of the dropdown cells according to the value in the dropdown cell
Dim rgCell As Range
For Each rgCell In Intersect(Target, Range("Tasks").Offset(0, 9)).Cells
Select Case rgCell.Value
Case "Not Started"
'Make the wingding character the same color as the cell interior so that it is not visible
With rgCell.Offset(0, -1)
If .Interior.ColorIndex <> -4142 Then
'-4142 corresponds to No Fill.
'Font.ColorIndex = -4142 causes error
.Font.ColorIndex = .Interior.ColorIndex
Else: .Font.ColorIndex = 2 'White
End If
End With
Case "Started"
With rgCell.Offset(0, -1)
.Font.ColorIndex = 5 'Blue
End With
Case "Behind Schedule"
With rgCell.Offset(0, -1)
.Font.ColorIndex = 44 'Gold
End With
Case "Late"
With rgCell.Offset(0, -1)
.Font.ColorIndex = 3 'Red
End With
Case "Completed"
With rgCell.Offset(0, -1)
.Font.ColorIndex = 10 'Green
End With
End Select
Next
End If
Comment by: Jan Karel Pieterse (6/1/2009 10:15:31 PM)Hi Ray,
You need to find out the proper TableStyleElement that belongs to the cell inside the table. Which tablestyleElement is needed depends on the settings of your table style. Assuming your cell is within the dataBodyRange of the table and you have no column striping you'd get something like this:
'-------------------------------------------------------------------------
' Procedure : GetStyleElementFromTableCell
' Company : JKP Application Development Services (c)
' Author : Jan Karel Pieterse
' Created : 2-6-2009
' Purpose : Function to return the proper style element from a cell inside a table
'-------------------------------------------------------------------------
Dim lRow As Long
'Determine on what row we are inside the table
lRow = oCell.Row - oLo.DataBodyRange.Cells(1, 1).Row
If oLo.ShowTableStyleRowStripes Then
'We are in the table's body
If lRow Mod 2 = 0 Then
Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlRowStripe1)
Else
Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlWholeTable)
End If
Else
Set GetStyleElementFromTableCell = oLo.TableStyle.TableStyleElements(xlWholeTable)
End If
End Function
Sub test()
Dim oLo As ListObject
Dim oTSt As TableStyleElement
Set oLo = ActiveSheet.ListObjects(1)
Set oTSt = GetStyleElementFromTableCell(ActiveCell, oLo)
ActiveCell.Offset(, 3).Interior.ThemeColor = oTSt.Interior.ThemeColor
ActiveCell.Offset(, 3).Interior.TintAndShade = oTSt.Interior.TintAndShade
End Sub
Comment by: Tim (6/8/2009 6:41:54 AM)Excel 2007 tables are named ranges ... but I can't treat them as a database name for SQL queries (example, in the MS Query builder). Named rnages appear as a database table, but not Excel 2007 tables. I have to convert the table to a normal range before the name is recognised by the Excel ODBC driver. This is annoying. Am I doing something wrong?
Comment by: Jan Karel Pieterse (6/8/2009 7:36:00 AM)Hi,
I guess you'll have to define your own "normal" named range for each table to have msQuery pick them up.
Comment by: Brian (7/2/2009 7:59:42 AM)Good morning - maybe this is a stupid question, but how do I use vba to obtain the table name that the activecell is in? eg, I can use CurrentRegion to select the whole table, but how do I obtain the table name so that I can start working with its fields?
Thanks,
Brian
Comment by: Jan Karel Pieterse (7/2/2009 12:19:45 PM)Hi Brian,
You could use something like this:
MsgBox "Not in a table"
Else
MsgBox ActiveCell.ListObject.Name
End If
Comment by: HDR (7/13/2009 12:00:28 PM)Hello,
How would you use VBA to loop through each row of the Excel 2007 table/list and get values from specific columns and work with them? I tried the code below but it's not working (it doesn't like the Structured Reference syntax)
Also, if the Tables are Workbook in scope in Excel 2007, how do I set a reference to them without using the worksheet on which it resides? (see code below)?
Set myTable = ThisWorkbook.Worksheets("Sheet1").ListObjects("myTable")
For CurRow = myTable.DataBodyRange.Row To myTable.ListRows.Count
myVar = myTable[[#This Row], [Header1]]").Value
'do other stuff..
'The #This Row should obviously move to the next row for each iteration of CurRow
next
Comment by: Jan Karel Pieterse (7/15/2009 12:39:53 AM)Hi HDR,
This example runs through the cells in the first column of the list:
Dim oList As ListObject
Dim oCell As Range
Set oList = Worksheets(1).ListObjects(1)
For Each oCell In oList.DataBodyRange.Columns(1).Cells
MsgBox oCell.Address & ":" & CStr(oCell.Value)
Next
End Sub
Comment by: John (8/3/2009 5:15:44 PM)Is it possible to offset by using header names, for instance when using find to locate a cell value and then modifying a value in the located cell's row? Kind of like doing such (with Status and Filing ID being table headers):
For Each acell In Selection
With ext_book.Worksheets("Assignments").Range("AssignmentsTbl[Filing ID]")
Set c = .Find(acell.Value, LookIn:=xlValues)
If Not c Is Nothing Then
c.Offset(0, "[Status]").Value = acell.Offset(0, "[Status]").Value
End If
End With
Next acell
I've been racking my brain on how to do this, any help would be GREATLY appreciated. Many thanks!
Comment by: Robert (8/10/2009 5:41:19 PM)Formulas work well within the same row using [#This Row]
ex. =Table_SDCBIBE01_SDCBFDDS_BF_RetailSummary[[#This Row],[RetailSales]]/Table_SDCBIBE01_SDCBFDDS_BF_RetailSummary[[#This Row],[InvPct]]
Is there any way to reference a different row using the table[] syntax? something like [[#This Row](-1),[RetailSales]]?
Comment by: Brent (9/2/2009 9:33:51 AM)Great article. Thanks!!
What VBA code can I use to resize the current table by one row?
Comment by: Jan Karel Pieterse (9/7/2009 8:58:09 AM)Hi John,
Anything is possible. I'd do two finds: one on the header row of the table to find the fieldname you need.
Then the other you already do.
Say the object variable is called oHeader and you have found row c, then the code to update the proper cell is:
Comment by: Jan Karel Pieterse (9/7/2009 9:31:27 AM)Hi Robert,
I don't really know, I don't think so. Look in Help and search fro "Structured references". That should give you all information on how to refer to tables.
Comment by: Jan Karel Pieterse (9/7/2009 10:13:38 AM)Hi Brent,
Like this:
.Resize ActiveSheet.Range(.Range.Resize(.Range.Rows.Count + 1, .Range.Columns.Count).Address)
End With
Comment by: Andreas (9/22/2009 2:39:42 PM)Hi Jan,
I have a bunch of Excel 2003-xlas making heavy use of InsertRowRange.
I want to let them run in 2007 compatibility-mode without any change but that's impossible because InsertRowRange Is Nothing after the 1st row insertion.
In Excel 2003 InsertRowRange was never Nothing when the ListObject was active (ActiveCell within the ListObject).
In Excel 2007 it equals to Nothing after the 1st row insertion despite the ActiveCell is ALWAYS within the ListObject.
The following code of an 2003-xla works fine in 2003 but errors with 2007.
Sub InsertRowRangeTest()
With ThisWorkbook.Worksheets.Add
.Range("A1").Value = "head"
.ListObjects.Add xlSrcRange, Range("$A$1"), , xlYes
.ListObjects(1).InsertRowRange.Value = "1st insert"
' next statement works fine in Excel 2003 but errors in 2007
' Since the ActiveCell is within the ListObject the
' InsertRow should be visible (despite it is not shown in Excel 2007)
' Run time error 91: Object of With variable not set
.ListObjects(1).InsertRowRange.Value = "2nd insert"
End With
End Sub
What is the reason of the error?
Did I miss to set something?
Can I make the InsertRow visible in another way to prevent Excel 2007 to throw errors?
Kind regards,
Andreas
Comment by: Debug (9/23/2009 2:38:34 AM)Hi
I'm look for code to change a standard command buttons color after I have refreshed the data from the server and the text to data has been refreshed. I'm using Excel 2007. If you can't use a standard button it is not a problem to change it to something else. Thanks before hand.
Comment by: Jan Karel Pieterse (9/23/2009 10:07:45 AM)Hi Andreas,
Thanks for letting me know the problem and the fix:
technet.microsoft.com/en-us/library/cc179167.aspx#whatschanged16
Comment by: Ignatius Verbeek (11/2/2009 6:33:25 PM)
If I try to change the formula of a cell in a table (aka listobject) in 2007 using vba I get an error. Here is the psuedo code.
set rng = ' a reference to a cell in a table
rng.formula = "= my formula"
gives error code 1004.
I get around this by unlisting the table adding the formula then relisting it.
Is there a better way? What is throwing the error?
Comment by: Jan Karel Pieterse (11/3/2009 12:40:47 AM)Hi Ignatius,
Seems to me the relevant part of your code is missing, could you please post the real code (or just enough in a sub so it shows the error)?
I just tested and setting a formula to a cell inside a table works without a hitch.
Comment by: Ignatius Verbeek (11/3/2009 4:50:39 AM)Jan,
I just did a test and yes I can get vba to add a simple formula in a simple table. I am sorry to bother you with something I should be able to test myself and thankyou for you comments.
However, I remain curious. In the actual table and vba code I did strike the error that I could not add a formula to a table with vba. After much testing I found that in some instances the formula was not correctly defined and that was the source of the error. After eliminating that problem I still found the formula could not be added. With a deadline looming and hours wasting I found that unlisting the table worked, the formula could be added and appears to be correct. I can only guess that excel is doing and auto correct or something which is masking an error in my formula.
Thanks again for you help.
Comment by: Ignatius Verbeek (11/3/2009 5:01:22 AM)Jan,
When you map a table to xml data you get an "insert row" ie a row at the bottom of the table where if you enter data it automaticall adds a new just like data tables in Access. If you don't map the table to xml you don't get the insert row.
I have worked out a way of emulating the insert row behaviour using the workbook sheet change event. In this way one set of subs and functions works on all tables in a workbook that have been flagged to behave in this way. It was/is a bit tricky to get it working neatly but now that it is, it is a very useful feature.
Do you know of a way that you can get the native "insert row" feature of a table to work for a table the is not mapped to xml.
Comment by: Jan Karel Pieterse (11/3/2009 5:29:07 AM)Hi Ignatius,
You don't really need that insert row, Excel will expand your table automatically when you enter anything below or to the right of the table.
Comment by: Noah Fehrenbacher (11/3/2009 6:23:30 PM)How do we know if sorting (or an autofilter) has been applied to a table since before we used autofiltermode and filtermode to determine it before, and now they don't work. What is the alternative for 2007 tables?
Comment by: amirtha (11/3/2009 10:02:26 PM)hai
i want to create table in the form using vb.i want to know how to implement this.
Comment by: Jan Karel Pieterse (11/4/2009 6:55:51 AM)Hi Noah,
It is not straightforward, this function seems to give what you need:
Dim oFltr As Filter
For Each oFltr In oLo.Range.Parent.AutoFilter.Filters
If oFltr.On Then
HasFilter = True
Exit Function
End If
Next
End Function
Sub Test()
MsgBox HasFilter(ActiveCell.ListObject)
End Sub
Comment by: Jan Karel Pieterse (11/4/2009 6:57:43 AM)Hi Amirtha,
See:
www.jkp-ads.com/Articles/AutoSizeListBox.asp
Comment by: Mark (11/6/2009 10:12:02 AM)I'm trying to create a macro to delete all the rows in a table. I'm very close, but am very confused by the behavior of my macro.
Sub Delete_Lotsa_Rows()
Dim oList As ListObject
Dim oRow As ListRow
Set oList = Worksheets(1).ListObjects(1)
Set oRow = oList.ListRows(1)
For Each oRow In oList.ListRows
oRow.Delete
Next
End Sub
The problem is that the macro only deletes half the rows in the table, then gives me
"RUN-TIME ERROR '1004':
Application-defined or object-defined error"
I even numbered the rows sequentially, and the macro deleted the odd-numbered rows only. When I started with 256 rows, it deleted 128, then 64, then 32, 16, 8, 4, 2, and 1.
It appears that for some reason the code is deleting every other row.
Comment by: Jan Karel Pieterse (11/6/2009 10:37:55 AM)Hi Mark,
The trick is to use a counter instead of a for-each construct and keep deleting the first row:
Dim oList As ListObject
Dim lCt as Long
Set oList = Worksheets(1).ListObjects(1)
Set oRow = oList.ListRows(1)
For lCt=1 To oList.ListRows.Count
oList.ListRows(1).Delete
Next
End Sub
Comment by: Charlie (11/8/2009 11:59:01 AM)Jan,
This is an excellent place for information on the Excell 2007 table Object. However, I am a bit uncertain how to accomplish an action based on a table. I want to set the value of a variable strUserMembership to a value in a table based on another value in the table.
Imagine the table ("tblAdministration") has several FIELDS and one of the fields is [Username]. When the User opens the Workbook, I want to set some Workbook and Worksheet properties based on the User's access level. In the table is another field called [AccessLevel].
So when the User opens the Workbook, I want to find their [Username] in "tblAdministration", and set strUserMembership (variable) to the associated value in the field [AccessLevel].
Thank you so much for your help.
Comment by: Jan Karel Pieterse (11/8/2009 10:51:24 PM)Hi Charlie,
Something like this should do the trick:
Dim oCol As Range
Dim oRow As Range
On Error Resume Next
Set oCol = ActiveSheet.ListObjects(sTableName).Range.Rows(1).Cells.Find("UserName")
Set oRow = Intersect(ActiveSheet.ListObjects(sTableName).Range, oCol.EntireColumn.Cells).Find(sUsername)
Set oCol = Nothing
Set oCol = ActiveSheet.ListObjects(sTableName).Range.Rows(1).Cells.Find("AccessLevel")
GetAccessRightsFromTable = Intersect(oCol.EntireColumn, oRow.EntireRow).Value
End Function
Sub Foo()
MsgBox GetAccessRightsFromTable("tblAdministration", "Smith")
End Sub
Comment by: Ian (11/19/2009 6:38:50 AM)In answer to Robert's question (quoted below)
Table_Name[[#This Row],[ColumnName]] is treated like a cell reference.
I've not tried it myself but wouldn't:
provide you with the result you're seeking?
The only issue I can see with this is when you're approaching the edge of the table (the row above the top row is the header...)
====
Comment by: Robert (8/10/2009 5:41:19 PM)
Formulas work well within the same row using [#This Row]
ex. =Table_SDCBIBE01_SDCBFDDS_BF_RetailSummary[[#This Row],[RetailSales]]/Table_SDCBIBE01_SDCBFDDS_BF_RetailSummary[[#This Row],[InvPct]]
Is there any way to reference a different row using the table[] syntax? something like [[#This Row](-1),[RetailSales]]?
Comment by: Charlie (11/25/2009 11:10:41 PM)Jan,
First, thank you for your help on the previous question I posted (11/8/2009 11:59:01 AM) - worked like a champ.
I have tried to use the information from your answer to Radek (3/17/2009 11:10:54 AM) to populate a data validation drop-down. I am here because I cannot get it to work. the code is:
Dim varValues As Variant
varValues = shtListSource.ListObjects("tblDocumentType").DataBodyRange.Columns(2).value
Range("" & strInCell & "").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:=varValues
.IgnoreBlank = False
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = "Select a value from the drop-down list"
.ErrorMessage = "Only values in the list can be entered"
.ShowInput = True
.ShowError = True
End With
Selection.ClearContents
End Sub
I have tried everything for "Formula1:=" and cannot get it to work. Am I even close?
Comment by: Hi Charlie (11/25/2009 11:17:28 PM)You're close. For a list, the Formula1 property expects the items as a string, separated by a comma, so you could do it like this:
Dim varValues As Variant
Dim sFormula As String
Dim sValues() As Variant
Dim lCt As Long
varValues = shtListSource.ListObjects("tblDocumentType").DataBodyRange.Columns(2).Value
ReDim sValues(1 To UBound(varValues, 1))
For lCt = 1 To UBound(varValues, 1)
sValues(lCt) = varValues(lCt, 1)
Next
sFormula = Join(sValues, ",")
Range("" & strInCell & "").Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:=sFormula
.IgnoreBlank = False
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = "Select a value from the drop-down list"
.ErrorMessage = "Only values in the list can be entered"
.ShowInput = True
.ShowError = True
End With
Selection.ClearContents
End Sub
Comment by: Charlie (12/1/2009 3:14:18 PM)Jan,
Once again your answer to my previous post was very helpful. I have work on the next (and hopefully last) issue all day and am stumped.
How would you apply SEVERAL filters to the table values BEFORE loading the string into the Validation Object? Based on conditions (that will increase in complexity), I would like to reduce the number of records in the table before they end up in the Data Validation Object.
I have tried a two step Autofilter (based on the "Special Stuff" section of your article) approach that I cannot get to work. How would I get Case "tblDSRDocument" filters to work before assigning the varValues variable?
Dim varValues As Variant
Dim varValuesString() As Variant
Dim strFormula1 As String
Dim lngCount As Long
Select Case strTable
Case "tblDSRDocument"
shtListSource.ListObjects("tblDSR").Range.AutoFilter Field:=4, _
Criteria1:="1", Operator:=xlFilterValues
shtListSource.ListObjects("tblDSR").Range.AutoFilter Field:=9, _
Criteria1:="All", Operator:=xlOr, Criteria2:="BW"
varValues = shtListSource.ListObjects("tblDSR").DataBodyRange.Columns(2).value
Case "tblDSRUnassigned"
varValues = shtListSource.ListObjects("tblDocumentType").DataBodyRange.Columns(2).value
End Select
ReDim varValuesString(1 To UBound(varValues, 1))
For lngCount = 1 To UBound(varValues, 1)
varValuesString(lngCount) = varValues(lngCount, 1)
Next
strFormula1 = Join(varValuesString, ",")
....
End Sub
Comment by: Jan Karel Pieterse (12/2/2009 2:10:45 AM)Hi Charlie,
You can't add the visible cells of a filtered list to a variant array unfortunatly, use something like this instead:
Dim varValues As Variant
Dim varValuesString() As Variant
Dim strFormula1 As String
Dim lngCount As Long
Dim oCell As Range
Dim oFilteredRange As Range
Select Case strTable
Case "tblDSRDocument"
shtListSOurce.ListObjects("tblDSR").Range.AutoFilter Field:=4, _
Criteria1:="1", Operator:=xlFilterValues
shtListSOurce.ListObjects("tblDSR").Range.AutoFilter Field:=9, _
Criteria1:="All", Operator:=xlOr, Criteria2:="BW"
Set oFilteredRange = shtListSOurce.ListObjects("tblDSR").DataBodyRange.Columns(2).Value
Case "tblDSRUnassigned"
Set oFilteredRange = shtListSOurce.ListObjects("tblDocumentType").DataBodyRange.Columns(2).Value
End Select
For Each oCell In oFilteredRange.SpecialCells(xlCellTypeVisible).Cells
strFormula1 = strFormula1 & oCell.Value & ","
Next
strFormula1 = Left(strFormula1, Len(strFormula1) - 1)
End Sub
Comment by: Joakim Westin (12/2/2009 4:57:17 AM)Thank you for an excellent post!
Can you add an example of the recommended way to loop through all the data in a ListObject? I am not a seasoned VBA programmer so this may be that I don't understand the difference between a range and the ListObject. But what I want to do is to programatically (VBA) access the ListObject data in order to do things with it.
Any examples and recommendations of how to best do this are most welcome!
/Joakim
Comment by: Jan Karel Pieterse (12/2/2009 11:39:06 AM)Hi Joakim,
This loops through all cells in a listobject:
For Each oCell In ActiveSheet.ListObjects(1).Range
MsgBox oCell.Address & "," & oCell.Value
Next
Comment by: dimson (12/11/2009 2:46:42 AM)Hi
I am using excel sheet1 as table and in the same workbook i have created a module in which i am trying to pull data from the SQL statements using ADODB connection.
i am not able to write correct syntex for 'WHERE' Clause. Actually i am not able to write the field name
EX:
"WHERE" & [Sheet1$].[Acc Ref#] & "= '665544' "
please tell me what is the right way to use the 'Where' Clause of SQL statement in Excel macro
Thanks
dimson
Comment by: Jan Karel Pieterse (12/11/2009 3:36:37 AM)HI dimson,
Looks like the syntax is something like this:
Comment by: Nasser (12/20/2009 12:30:14 AM)Hi,
In excel 2003 changing the color of a line does not make a problem. The code is as follow:
ActiveSheet.Shapes("line 1").Line.ForeColor.SchemeColor = 2
But in excel 2007 the things are a bit different. The line is called" straight connector" and i could not find the right code to change its color. Any help to find the correct code.
Many Thanks
Nasser.
Comment by: Jan Karel Pieterse (12/21/2009 11:55:52 AM)Hi Nasser,
This seems to do the trick (turns line black):
ActiveSheet.Shapes("Name Of Shape").Line.ForeColor.RGB = RGB(0, 0, 0)
Comment by: Nasser (12/21/2009 11:04:09 PM)
Hi karel,
It works, thank you so much. Using rectangle shape it works with numbers but RGBs it does not, strange!
Cheers,
Nasser.
Comment by: Nasser (12/22/2009 1:47:32 AM)Hi,
I know that there 4 methods to trigger a combobox. The only one working me is to populate the combobox through a command button. The code i am using is as follow:
private sub cmdsetcombo_click()
combobox1.clear
combobbox.additem "1"
combobox1.additem "2"
combobox1.additem "3"
When open the program, i just click on the command button and my combobox gets populated. Without adding this button my combobox remains empty.
I checked the other 3 methods also but after opening the program i find the combobox almost empty. Is there another method which does not need a button to trigger the combobox?
Cheers,
Nasser.
Comment by: Jan Karel Pieterse (12/22/2009 6:45:32 AM)Hi Nasser,
You could use the Workbook_Open event in the Thisworkbook module to run the code that fills the combobox.
Comment by: Nasser (12/23/2009 12:25:44 AM)Hi Karel,
I put the whole code below in "thisworkbook" but it does not seem to work.
Option Explicit
Private Sub ComboBox1_Change()
ComboBox1.Clear
ComboBox1.AddItem ("1")
ComboBox1.AddItem ("2")
ComboBox1.AddItem ("3")
[/End Sub
Private Sub Workbook_Open()
If ComboBox1.Value = "1" Then
ActiveSheet.Shapes("line 2").Line.ForeColor.RGB = RGB(0, 0, 0)
ElseIf ComboBox1.Value = "2" Then
ActiveSheet.Shapes("line 2").Line.ForeColor.RGB = RGB(225, 0, 0)
Else
ActiveSheet.Shapes("line 2").Line.ForeColor.RGB = RGB(0, 225, 0)
End If
End Sub]
Thanks your feedback
Nasser.
Comment by: Jan Karel Pieterse (12/23/2009 4:19:14 AM)Hi Nasser,
The change event of the combobox needs to go in the module belonging to the worksheet the combobox is on.
So this goes in ThisWorkbook:
(note that you may need to change Sheet1 in this code to the name as shown in the project explorer in the VBA Editor)
Private Sub Workbook_Open()
Sheet1.ComboBox1.Clear
Sheet1.ComboBox1.AddItem ("1")
Sheet1.ComboBox1.AddItem ("2")
Sheet1.ComboBox1.AddItem ("3")
End Sub
And this goes in the sheet's module:
If ComboBox1.Value = "1" Then
ActiveSheet.Shapes("line 2").Line.ForeColor.RGB = RGB(0, 0, 0)
ElseIf ComboBox1.Value = "2" Then
ActiveSheet.Shapes("line 2").Line.ForeColor.RGB = RGB(225, 0, 0)
Else
ActiveSheet.Shapes("line 2").Line.ForeColor.RGB = RGB(0, 225, 0)
End If
End Sub
Comment by: dimson (12/23/2009 6:16:19 AM)I am trying to pull some data from an external read only file(from defined Table) by using SQL Query.
I am getting all the required result but i am not getting few fields data(I have noticed its Number type data)
the exception is that i am getting few data for the same fields. even when i import data, i am getting the same result
due to this, manual copy pasting only works
what could be the error(is the cariage return in the field causing this)
Comment by: Marek (12/23/2009 8:42:03 AM)Hi Jan,
and thanks for the article. Helped quite a bit where the VBA documentation is lacking ...
One question I have is, how do I use the below line,
but instead of using Cells(1,1) type of referencing I'd rather call it by their field (or column name) name ... like
I've tried many different syntax versions, but it doesn't seem to accept anything of this kind .
Thanks,
Marek
ps. Happy holidays
Comment by: Nasser (12/23/2009 10:04:35 AM)Hi Karel,
Great, it works nicely. Thanks.
As a new learner i want to try to use the form control and see what is the difference between them and the activeXcontrol. Using the ActiveXcontrol i did not have any problem using combobox with the label but with the form control, my label is not responding.
After selecting a drop down from form control,i entered in 3 cells the names "red, black and green".
After highlighting the cells and selecting the cell d7 as the number to be tested. I wrote my program and it works nicely with the straight connector" without the label.
Adding the label to display the color names, it gives errors, " variable label5 not defined and also "can't execute code in
'break mode". All the code is kept in folder "module1".
[Sub DropDown4_Change()
If Range("d7").Value = 1 Then
ActiveSheet.Shapes("straight connector1").Line.ForeColor.RGB = RGB(225, 0, 0)
Label5.Caption = "red"
ElseIf Range("d7").Value = "2" Then
ActiveSheet.Shapes("straight connector 1").Line.ForeColor.RGB = RGB(0, 0, 0)
Label5.Caption = "black"
Else
ActiveSheet.Shapes("straight connector 1").Line.ForeColor.RGB = RGB(0, 225, 0)
Label5.Caption = "green"
End If
End Sub]
Cheers
Comment by: Jan Karel Pieterse (12/23/2009 11:12:34 AM)Marek: I don't think that syntax will work, because it probably points to an entire column (the one with that heading).
Nasser: The proper syntax is:
Comment by: Nasser (12/24/2009 12:40:26 AM)
Great! Thanks
Merry Chrismas
Comment by: Nasser (12/24/2009 1:08:24 AM)Hi Karel,
I am using a timer in my application. Here is the code which works fine so far.
Application.Wait Now + TimeValue("00:00:02")
MSGBox ("Clearing time 1.75 seconds")
I want to show exactely 1.75 sec. Since i can not adjust this particular time so i put 2 secs. Is there a way to program my VBA for less than 1 second since i have different timing 0.01, 0.15, 0.75 sec. and so one.
Cheers,
Nasser.
Comment by: Jan Karel Pieterse (12/24/2009 5:49:47 AM)Hi Nasser,
Use the Timer function in combination with a Do loop:
Dim dTime As Double
dTime = Timer
Do
Loop Until Timer - dTime >= 1.75
MsgBox Timer - dTime
End Sub
Comment by: Nasser (12/24/2009 8:49:08 AM)Hi Karel,
Thanks, that work fine but only thing it displays sometimes more than 1.75, 0.17 or 0.01. What i did is changing the MsgBox contain " Timer - Dtime" by the timing above like MsgBox( 1.75 secs"). That will also satisfy my need. Any better idea will be welcomed.
Many many thanks
Nasser.
Comment by: Nasser (12/24/2009 8:57:55 AM)Hi Karel,
I am making one Multi-choice Question. It works fine but when i click on "cancel or close mark" to exit from the window, it says " Invalid Entry" which normally should not.
With wrong entry other than 1,2,3 it displays "Invalid Entry" which is fine. Something needs to be added to my program, isn't it?
Private Sub question1_click()
Dim answer As Variant
answer = (InputBox( _
"Fuses have an inverse type of current-time operating characteristic. What does it mean?" & vbCrLf & _
"1) The higher the current, the slower the operating time." & vbCrLf & _
"2) The smaller the current, the faster the operating time." & vbCrLf & _
"3) The higher the current, the faster the operating time." & vbCrLf & _
" Your answer to question 1 is "))
Select Case answer
Case 1
MsgBox ("WRONG: When the current is high, the fuse must strip quickly.")
Case 2
MsgBox ("WRONG: For a small current close to the fuse rating, the fuse should not trip.")
Case 3
MsgBox ("CORRECT: In case of a higher current, the fuse should trip very quickly.")
Case Else
MsgBox (" Invalid Entry ")
End Select
End Sub
Thanks and Regards
Nasser.
Comment by: Jan Karel Pieterse (12/25/2009 3:28:28 AM)Hi Nasser,
You need one extra test:
Case ""
Msgbox "Cancelled"
Case Else
'Existing code
Comment by: Geoff Hasforth (1/3/2010 4:38:33 PM)I have created an Excel Table from an external database using the ListObjects.Add method then added additional calulated fields to the right of the data range.
The formulas fill down automatically when first entered, but if the data range expands downward when updated I only get one formula in the very bottom row. This leaves a gap which I then have to manually fill. Nor are the formats copied down with each refresh
I can't seem to get the FillAdjacentFormulas working nor can I find a box to turn it on like I use to get with MSQuery querytable. According to what I have read the option should be available in Data Properties menu.
I am trying to get all this working in VBA and would greatly appreciate some help.
Yours Sincerely
Geoff Hasforth
Comment by: Jan Karel Pieterse (1/3/2010 11:28:29 PM)Hi Geoff,
This should work "out of the box". Make sure the formula column is included in the table range. On the table tools tab of the ribbon (only visible when you're within a table), click the design tab and locate the "Properties" group on the far left. It has a button called "Resize table".
Comment by: Dennis Ceralde (1/31/2010 7:36:54 PM)Hi Jan,
On a 1/3/2010 11:28:29 PM posting of yours you stated:
This should work "out of the box". Make sure the formula column is included in the table range. On the table tools tab of the ribbon (only visible when you're within a table), click the design tab and locate the "Properties" group on the far left. It has a button called "Resize table".
Is there a way to programmatically resize a Excel table via VBA?
Thank you,
Dennis
Comment by: Jan Karel Pieterse (1/31/2010 10:20:06 PM)Hi Dennis,
The example code is already on this page, sub called "TableInsertingExamples". This adds a column:
Selection.ListObject.ListColumns.Add
Comment by: Ramu (2/3/2010 7:04:56 AM)Hi Karel,
I've created a user form using Excel 2007 to enter data in a worksheet and everything works fine as long as I write my data into the defined range of the worksheet. But when I convert the data range into a table, the data entered using the form are put outside the table. What's wrong. Many thanks in advance for your help.
Ramu
Comment by: Jan Karel Pieterse (2/3/2010 7:08:44 AM)Hi Ramu,
Without any code examples this is very hard to solve.
I advise you to post your question -with sample file- here:
eileenslounge.com
Comment by: Ramu (2/3/2010 10:24:19 PM)Hi Karel,
Sorry, and here is the code:
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Offres")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'check for a part number
If Trim(Me.txtCmpny.Value) = "" Then
Me.txtCmpny.SetFocus
MsgBox "Veuillez introduire une offre !"
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtCmpny.Value
ws.Cells(iRow, 2).Value = Me.txtFName.Value
ws.Cells(iRow, 3).Value = Me.txtName.Value
ws.Cells(iRow, 4).Value = Me.txtProduct.Value
ws.Cells(iRow, 5).Value = Me.txtSubBy.Value
ws.Cells(iRow, 6).Value = Me.txtDate.Value
ws.Cells(iRow, 7).Value = Me.txtOValue.Value
ws.Cells(iRow, 8).Value = Me.txtDelai.Value
ws.Cells(iRow, 9).Value = Me.txtConclusion.Value
ws.Cells(iRow, 10).Value = Me.txtIndex.Value
ws.Cells(iRow, 11).Value = Me.txtStatus.Value
'clear the data
Me.txtCmpny.Value = ""
Me.txtFName.Value = ""
Me.txtName.Value = ""
Me.txtProduct.Value = ""
Me.txtSubBy.Value = ""
Me.txtDate.Value = ""
Me.txtOValue.Value = ""
Me.txtDelai.Value = ""
Me.txtConclusion.Value = ""
Me.txtIndex.Value = ""
Me.txtStatus.Value = ""
Me.txtCmpny.SetFocus
End Sub
Comment by: Jan Karel Pieterse (2/3/2010 11:00:02 PM)Hi Ramu,
This adds a new row at the bottom of your list and enters a number in each of its cells:
Dim oNewRow As ListRow
Dim oCell As Range
With ActiveCell.ListObject
Set oNewRow = .ListRows.Add
For Each oCell In oNewRow.Range
oCell.Value = "1"
Next
End With
End Sub
Comment by: Ramu (2/4/2010 8:16:40 AM)Hi Karel,
Thanks for the time you've spent on my problem. I put your piece of code first on top and then after my "Privat Sub cmdAdd_Click()" but nothing changed. The data are still outside the table. So, what did I wrong. (I'm a newbie to VBA). Can you help me out?
Ramu
Comment by: Jan Karel Pieterse (2/4/2010 8:27:51 AM)Hi Ramu,
I expect this is what you need:
Dim ws As Worksheet
Dim oNewRow As ListRow
Set ws = Worksheets("Offres")
With ws.ListObjects(1)
Set oNewRow = .ListRows.Add
End With
'check for a part number
If Trim(Me.txtCmpny.Value) = "" Then
Me.txtCmpny.SetFocus
MsgBox "Veuillez introduire une offre !"
Exit Sub
End If
'copy the data to the database
oNewRow.Range.Cells(1, 1).Value = Me.txtCmpny.Value
oNewRow.Range.Cells(1, 2).Value = Me.txtFName.Value
oNewRow.Range.Cells(1, 3).Value = Me.txtName.Value
oNewRow.Range.Cells(1, 4).Value = Me.txtProduct.Value
oNewRow.Range.Cells(1, 5).Value = Me.txtSubBy.Value
oNewRow.Range.Cells(1, 6).Value = Me.txtDate.Value
oNewRow.Range.Cells(1, 7).Value = Me.txtOValue.Value
oNewRow.Range.Cells(1, 8).Value = Me.txtDelai.Value
oNewRow.Range.Cells(1, 9).Value = Me.txtConclusion.Value
oNewRow.Range.Cells(1, 10).Value = Me.txtIndex.Value
oNewRow.Range.Cells(1, 11).Value = Me.txtStatus.Value
'clear the data
Me.txtCmpny.Value = ""
Me.txtFName.Value = ""
Me.txtName.Value = ""
Me.txtProduct.Value = ""
Me.txtSubBy.Value = ""
Me.txtDate.Value = ""
Me.txtOValue.Value = ""
Me.txtDelai.Value = ""
Me.txtConclusion.Value = ""
Me.txtIndex.Value = ""
Me.txtStatus.Value = ""
Me.txtCmpny.SetFocus
End Sub
Comment by: Ramu (2/4/2010 10:48:20 AM)Hi Karel,
Great, it works. Many thanks for your precious help.
Ramu
Comment by: Andrew Morgan (3/9/2010 10:19:19 PM)How or can I select 2 non adjacent columns in range by referencing their column headings? The data comes from an external query and the column order may change, but not the names. I can do 1 column at a time, but not more. The following did not work:
Comment by: Jan Karel Pieterse (3/9/2010 10:27:01 PM)Hi Andrew,
You have to use a slightly different syntax:
Range("Table_Query_from_LegrandDB[[#All],[who]],Table_Query_from_LegrandDB[[#All],[when]]").Select
Comment by: damian (3/17/2010 5:27:58 PM)Hi
I was testing vba code below, and need to trap if there are no rows in the table (ie the header record exists and but there are NO other rows)
Dim oL As ListObject
Set oL = ActiveSheet.ListObjects(1)
oL.DataBodyRange.Rows(oL.DataBodyRange.Rows.Count).Select
Comment by: Jan Karel Pieterse (3/17/2010 10:54:32 PM)Hi Damian,
The DataBodyRange is not behaving very nicely, if there are no rows, it returns a runtim error when you try to access it. One way of doing it is by turning off error handling temporarily:
Dim oLo As ListObject
Dim oRows As Range
Set oLo = ActiveSheet.ListObjects(1)
On Error Resume Next
Set oRows = oLo.DataBodyRange
On Error GoTo 0
If oRows Is Nothing Then
MsgBox "List has no rows"
Else
MsgBox "List contains " & oRows.Rows.Count & " rows"
End If
End Sub
Comment by: Sasya (3/18/2010 2:50:38 AM)Im working on 2007 Excel. Im trying to run a macro on an excel sheet from a different excel sheet. The macro is to open an excel and perform some operations on it. With the macro below I am able to open the excel( 2003 only but not 2007 ) but I am not able to perform any operation on it.
Sub Open()
Dim objXL
Set objXL = CreateObject("Excel.Application")
With objXL
.Workbooks.Open ("E:\CAT\wordlist.xls")
.Visible = True 'Might not want this
End With
Set objXL = Nothing
End Sub
Comment by: Jan Karel Pieterse (3/18/2010 3:43:07 AM)Hi Sasya,
You normally do not have to open a new Excel session to do something with an Excel sheet. What is it you are trying to do?
Comment by: Damian (3/18/2010 5:24:53 AM)Hi Jan,
Thank you for the error trapping on dataBodyRange. Next I Have another question is there a way to do subtotals in table based on a code in a column. I don't really want to use a pivot table as it will sort columns and row etc
ie
Code Amt
1 10.00
1 15.00
total 1 25.00
2 5.00
total 2 5.00
total 30.00
Thanks
Damian
Comment by: William White (3/25/2010 1:51:30 PM)Hint: Trapping ListObjects with an empty DataBodyRange
With Range("tbTable").ListObject
If .ListRows.Count > 0 Then
.DataBodyRange.etc...
End If
End With
Comment by: William White (3/25/2010 1:55:32 PM)Is it possible to delete all ListRows where values meet an error criteria: Example..
Range("tbData[Criteria]").SpecialCells(xlHasFormula, xlErrors).EnitreRow.Delete
which does not work.
However, filtering [Criteria] = #N/A and "selecting" the visible cells (.Select) followed by Selection.EntireRow.Delete does appear to work.
Would like a direct solution without filtering or using Select.
Comment by: Jan Karel Pieterse (3/26/2010 3:18:52 AM)Hi William,
You have to step through the column cell-by-cell, starting from the bottom so Excel does not loose track of which row it is processing:
Dim lCt As Long
With Range("Table1[UserName]")
For lCt = .Rows.Count To 1 Step -1
If IsError(.Cells(lCt, 1)) Then
.Cells(lCt, 1).EntireRow.Delete
End If
Next
End With
End Sub
Comment by: William White (3/26/2010 7:21:18 AM)This is the fastest I have, however, the table has many columns and even with xlCalcManual and screenupdating off, the deletes are taking quite a long time. In the spreadsheet itself, manually I an filter on the errors, select all the errors and select delete. It asks me "Delete Entire Rows", to which I say "yes", and the deletion is done it appears in a single swipe. Perhaps the internal code is able to control calculation better than I can. Anyway, this code identifies contiguous groups of cells meeting the criteria and can remove them as a block:
Sub remNaItems(rng)
' ----- Remove all rows where "rng" has an error value
'
' "rng" = table column reference, eg = "table[Column]"
Range(rng).Worksheet.Calculate
savedCalc = Application.Calculation
savedUpdating = Application.ScreenUpdating
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
On Error GoTo exitSub
Set items = _
Range(rng).SpecialCells(xlCellTypeFormulas, xlErrors)
On Error GoTo 0
Range(rng).Worksheet.Unprotect
n = items.Areas.Count
For i = n To 1 Step -1
items.Areas(i).EntireRow.Delete
Next i
Range(rng).Worksheet.Protect
exitSub:
Application.Calculation = savedCalc
Application.ScreenUpdating = savedUpdating
Exit Sub
End Sub
Comment by: Rick Tipton (3/29/2010 6:09:02 AM)Below allows me to select a cell in the data area of a 2007 pivot based on the cell value and formats the row. I have tried using "For Each C In .ListColumns(5).Cells
" in place of the .DataBodyRange but I have errors.
What needs to be changed to select the cell based on the Row Labels?
Dim C As Range
With ActiveSheet.PivotTables("Page01Pvt1")
'Reset default formatting
With .TableRange1
.Font.Bold = False
.Interior.ColorIndex = 0
.Font.ColorIndex = 0
End With
'Apply formatting to each row if condition is met
For Each C In .DataBodyRange.Cells
If C.Value = 1 Then
With .TableRange1.Rows(C.Row - .TableRange1.Row + 1)
.Font.Bold = True
.Interior.ColorIndex = 46
.Font.ColorIndex = 2
.Borders.ColorIndex = 0
End With
End If
Next
End With
End Sub
Comment by: Jan Karel Pieterse (3/29/2010 6:24:50 AM)Hi Rick,
The proper syntax is:
For Each C In .ListColumns(5).Range.Cells
Note that the index is zero-based, so this is the sixth column of the table we're addressing here...
Comment by: William White (4/14/2010 1:20:41 PM)We're moving files from Windows servers to SharePoint and I need to convert over some of my file manipulation code.
Old code:
FileCopy saCostsFolder & costModel, fnCostModel
This works when saCostsFolder is a Windows address, eg. "\\Cffp\costs\" but seems to fail when I need a file located at "domain.com/SharePointSite/costs".
The error is #52 - "Bad file name or number".
Another method tried is fso.CopyFile, where fso is a File System Object.
The file is not an Excel file.
A more general question is whether there is an equivalent for the "Directories and Files Keywords" (that operate only on Windows style file systems as I understand it) for sharepoint files, or web-based files in general.
Comment by: Jan Karel Pieterse (4/14/2010 9:19:31 PM)Hi William,
See if this page gets you there:
www.tek-tips.com/viewthread.cfm?qid=1497864&page=2
Comment by: hermine viaene (4/15/2010 3:30:24 PM)dear Mr.Pieterse
hope you are well.First of all many thanks for all your very interesting info and tips , really very very usefull ..
I am trying in VBA to handle a table where I have to do several steps , I succeed to do a part of it ,but not completely.
in short : i have a table per mth starting 2010 with textnumbers(exc 22105,3506 eur)
I want to have this table per mth(alocated at the same place) starting 2009 and with values divided by 1000
(so resulting in 22,1053 )Do you think this can be done in 1 macro ?
Superthks on beforehand for your help - really appreciated
kindest regards
hermine
Comment by: Jan Karel Pieterse (4/16/2010 3:02:14 AM)Hi Hermine,
I certainly do.
I advise you to go to this site to ask your question:
www.eileenslounge.com
Comment by: Patrick Matthews (4/18/2010 5:39:17 PM)Jan Karel,
Excellent article! Interesting that the ListColumns collection is zero based. This is consistent with how table fields are handled in ADO and DAO, but of course inconsistent with just about all of the established Excel collections!
Oh well :)
Patrick
Comment by: Jan Karel Pieterse (4/19/2010 12:46:26 AM)Hi Patrick,
Thanks! Yes, I love these inconsistencies in Excel (NOT) :-(
It does keep me in business though :-)
Comment by: Carim (4/22/2010 4:23:11 AM)Jan Karel,
Thanks for your very interesting article ...
Is there a VBA code to easily delete all filtered rows from table... since my current loop takes way too long ...
Thanks in advance for your advice
Carim
Comment by: Asher Rodriguez (4/22/2010 6:52:01 AM)I have a table linked to an ODBC data connection. I need to keep a history of the data before I update it. I have created a history table in another sheet in the workbook and manual copy the existing data to the end of the history table before updating the ODBC link.I recorded a macro to do this but it always pastes the data to a specific line of the history table (the line that was the end of table when I recorded it). I cannot find anywhere the code to alter my macro to set it to paste the data to the END of the history table. Any help?
Comment by: Jan Karel Pieterse (4/22/2010 7:58:33 AM)Hi Carim,
Something like this :
With ActiveSheet.ListObject1.DataBodyRange
.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Comment by: Jan Karel Pieterse (4/22/2010 8:04:00 AM)Hi Asher,
You could use a line of code like this to select the last used cell in column A:
.Range("A" & .Rows.Count).End(xlUp).Offset(1).Select
End With
Comment by: Carim (4/22/2010 8:31:47 AM)Hi Jan Karel,
Many thanks for your suggestion ...
Will try it right away ... It will be a relief for over 60'000 rows to be filtered ...
Cheers
Carim
Comment by: Carim (4/22/2010 10:25:24 AM)Hi Jan Karel,
Sorry to bother you again ... but following code just filters and does not delete ...
I am using Excel 2010 beta... does it matter ?
With ActiveSheet.ListObjects("Table1")
.Range.AutoFilter Field:=56, Criteria1:="0"
.DataBodyRange.SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
If need be, I can try on another machine with Excel 2007
Thanks again for your kind assistance
Best Regards
Carim
Comment by: Jan Karel Pieterse (4/23/2010 1:12:27 AM)Hi Carim,
I'm sorry, the code I gave appears to be wrong.This should do the trick:
Dim lCt As Long
Dim oArea As Range
With ActiveSheet.ListObjects(1)
.Range.AutoFilter Field:=1, Criteria1:="0"
For Each oArea In .DataBodyRange.SpecialCells(xlCellTypeVisible).Areas
For lCt = oArea.Rows.Count To 1 Step -1
oArea.EntireRow.Rows(lCt).Delete
Next
Next
End With
End Sub
Comment by: Carim (4/23/2010 9:27:09 AM)Hi Jan Karel,
Thanks a lot for your help ...
Your first solution works fine, provided sorting is applied beforehand ...
And your second solution does the trick as expected, but without the need to sort !!! Excellent !!!
Again, my sincere thanks for all your advice
Best Regards
Comment by: Asher Rodriguez (4/28/2010 8:02:55 AM)Hi Jan Karel,
Thanks for your suggestion, I used a different form of it but it sure did point me in the right direction.
I have another question. I am pasting my code in so you can see what I am doing this time :-)
This code worked just fine yesterday, but today it give me a run-time error and stops at Line 9: Sheets("Test Request Table CURRENT").Select
Can you help?
Sub TR_Tracking_Refresh()
Sheets("Test Request Table CURRENT").Select
Range("Table_SysTest").Select
Selection.Copy
Sheets("TR_Table_Recent_Past").Select
Range("Table_Recent_Past").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
Sheets("Test Request Table CURRENT").Select
ActiveWorkbook.RefreshAll
Range("Table_SysTest[Download Date]").FormulaR1C1 = "=TODAY()"
Range("Table_SysTest[Download Date]").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A2").Select
Application.CutCopyMode = False
Range("A2").Select
End Sub
Comment by: Jan Karel Pieterse (4/28/2010 10:12:13 AM)Hi Asher,
My guess is that there is no sheet called
"Test Request Table CURRENT"
in the workbook that is active when trying to execute that line.
Comment by: Asher Rodriguez (5/6/2010 6:04:32 AM)Hello again Jan Karel :-)
I have another issue I was hoping you could assist me with.
My range is cells U4:AF302.
The columns represent one month in the fiscal year. The rows are the tasks for a project. People input percent complete values into the cells.
What I am trying to do is make it so that if the is a value of 100 in the column to the left (starting with column V) then it will automatically fill the rest with 100 through the last column.
I can't do it in cell with an IF statement (ex. for cell V4: IF(U4=100,100,"")) like I planned to at first because the users don't like typing over formulas.
Can you help me figure out how do it in the page code?
Comment by: Martin Bustios (5/6/2010 7:48:06 PM)Jan,
Thanks very much for posting this information on tables. I was wondering if you new what is the VBA code to turn off the auto-expansion property of tables.
Thanks ! in advance
Martin
Comment by: Jan Karel Pieterse (5/7/2010 5:17:30 AM)Hi Martin,
I recorded a macro whilst turning it off and got this:
Comment by: Jan Karel Pieterse (5/7/2010 5:53:51 AM)Hi Asher,
If you rightclick the sheet tab and select View code and paste in this code I think it will do what you need:
If Intersect(Target, Range("U4:AF302")) Is Nothing Then Exit Sub
If Target.Cells(1, 1).Value = 100 And Target.Cells(1, 1).Offset(, 1).Column <= Range("AF1").Column Then
Target.Cells(1, 1).Offset(, 1).Value = 100
End If
End Sub
Comment by: Asher Rodriguez (5/7/2010 12:17:23 PM)Jan Karel YOU ROCK! That code worked perferctly! :-)
If I could ask one last thing on this sheet I would be much grateful.
I have been searching many forums and books and still can't find a code that works for this task:
I need to allow ONLY paste values in ONLY in that same Range of cells. Range("U4:AF302")
I have tried many suggested bits of code from forums I found and so far nothing has worked right and now, somehow I can no longer Paste Special in ANY worksheet.
Please help. I really miss being able to paste special, and I would really love to be able to make just that onerenage within that one sheet be resticted to paste values only.
Comment by: Jan Karel Pieterse (5/8/2010 3:01:40 AM)Hi Asher,
You would use code like the one I propose in this article:
www.jkp-ads.com/articles/catchpaste.asp
But you need to use worksheet events to turn that code on or off where needed.
I would suggest to turn it on or off for the entire worksheet, using the worksheet_activate and worksheet_deactivate events.
Comment by: Asher Rodriguez (5/10/2010 7:43:20 AM)Before I can try this I still need to figure out how to re-enable the paste-special function on my right-click mouse menu.
I have tried lots of code and it seems as though somewhere in there it got disabled.
Any suggestions?
Comment by: Jan Karel Pieterse (5/10/2010 11:20:39 PM)Hi Asher,
This little macro resets all right-click menu's of Excel:
Dim cbar As CommandBar
For Each cbar In Application.CommandBars
If cbar.Type = msoBarTypePopup Then
cbar.Reset
End If
Next cbar
End Sub
Comment by: kunal (5/24/2010 1:44:43 AM)I have a dump of data in excel which is arranged in the format of a table. I want to write an excel code that processes the required information from the data dump and creates another table with my required rows and columns.
Could you please provide me with sample codes relevant to my problem ?
Comment by: Asher Rodriguez (5/24/2010 6:24:43 AM)Jan Karel,
Thanks so much for the macro to re-enable my right click menu's. I learned a very valuable lesson about thoroughly checking out code before trying it.
I'm going to try and get that book on ribbons soon, thank so myuch for the reference, and I'm still studying how to work your catchpaste macro.
Thanks for making your expertise available for us beginners :-)
Comment by: Jan Karel Pieterse (5/24/2010 9:49:59 AM)Hi Kunal,
Without knowing what processing is needed, this is hard to answer.
Comment by: Asher Rodriguez (5/24/2010 10:10:22 AM)Hi again Jan Karel,
I am trying to create a macro that will:
1. create a table from exported data
2. hide all columns so i can then:
3. show only columns necessary (there are crazy amt of columns in this export)
I started with this code:
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$2:$CI$66"), , xlYes).Name = _
"Table1"
Range("Table1").EntireColumn.Hidden = True
Range("Table1[Report]").EntireColumn.Hidden = False
Range("Table1[Project]").EntireColumn.Hidden = False
End Sub
-And it works, but I need a lot of columns, so in order to avoid retyping this:
Range("Table1[Project]").EntireColumn.Hidden = False
for each column I tried to make this Sub-Procedure:
Sub showColumn(cName)
Range("Table1[cName]").EntireColumn.Hidden = False
End Sub
-And I tried to call it using:
showColumn "Status"
-But I keep getting this error:
Method 'Range' of object '_Global' failed
-Any suggestions?
Comment by: Jan Karel Pieterse (5/25/2010 12:04:12 AM)Hi Kunal,
This code should do it:
With ActiveSheet.ListObjects(sTableName)
.ListColumns(sColumnName).DataBodyRange.EntireColumn.Hidden = True
End With
End Sub
Comment by: Asher Rodriguez (5/25/2010 6:45:53 AM)Good Morning Jan Karel :-)
Thanks for that code snippet. I modified it for my need and this is what I have now (deleted some columns in the interest of length):
"Table1"
Range("Table1").EntireColumn.Hidden = True
showColumn "Table1", "Report"
showColumn "Table1", "Project"
showColumn "Table1", "Status"
showColumn "Table1", "Date Created"
'Formatting table view
Range("Table1").WrapText = True
Columns("A:A").ColumnWidth = 8
Columns("B:B").ColumnWidth = 30
Columns("U:U").ColumnWidth = 30
End Sub
Sub showColumn(tName As String, cName As String)
With ActiveSheet.ListObjects(tName)
.ListColumns(cName).DataBodyRange.EntireColumn.Hidden = False
End With
End Sub
It works great!
I have a question though...
Is there a way I can create the table if the range of data is unknown? I never know how many lines there will be on the export.
Also, is there any way I can get this to work even if the table has a different name? What if there is already a table 1 when they run the code.
I keep searching the internet and my VBA books but nothing seems to fit quite right. I think I need to make a variable for the table name but not sure where to put it. I'm a bit frustrated since I took a VB Course last semester but it was all about making programs with GUI Forms and did not mention Excel at all. I thought it would help but it hasn't so far.
Thanks again so much for all your help, much more valuable than the course I took.
-Asher
Comment by: Joe Salmi (6/14/2010 3:38:30 PM)Hi, I'm wondering if someone could PLEASE help me with this. I have a formatted table in Excel 2007 that I am pulling data from a home tab and pasting it into my table. My problem is I need the script to find the last row of data then move to the last column of the table so it can tab down and create a new row then paste in the information. With that being my main issue I come to another problem that I don't always have all the information I need at the time of pasting it.
My table headers are on Row 3 and my data goes from B4:L4. My last row of data is B37:L37 with 2 cells with out information in them. A constant would be my D column because it will always have data in it but the rest may not.
I'm looking to find the last row in column D then move over to the L column then tab to the new row (which would out me at B38 and then paste the information...
Can anyone help me with this?
Comment by: Jan Karel Pieterse (6/15/2010 12:47:48 AM)Hi Joe,
If you already have something in your clipboard to paste, then this pastes that immediately below the table and makes it become a part of that table:
Dim oLo As ListObject
Set oLo = ActiveSheet.ListObjects("Table1")
ActiveSheet.Paste oLo.DataBodyRange.Offset(oLo.DataBodyRange.Rows.Count).Resize(1, 1)
End Sub
Comment by: Jo Salmi (6/15/2010 2:01:18 PM)Jan Kare,
Thanks for the help, I am going to change it to your way but I did end up getting it working with this little bit:
Range("AA17:AK17").Select
Selection.Copy
ActiveWindow.ScrollColumn = 1
Sheets("No List").Select
Range("D4").End(xlDown).Offset(0, 8).Select
Selection.ListObject.ListRows.Add AlwaysInsert:=False
Range("D4").End(xlDown).Offset(1, -2).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Home").Select
Everything else after that is the macro clearing the cells on my home sheet.
Joe
Comment by: Olivier (6/15/2010 5:46:05 PM)I am trying to use a ListObject in a function, e.g.,
Function Test(Table As ListObject)
MsgBox TypeName(Table)
End Function
but when I call that function in a formula, with a table name as argument where the ListObject is expected, e.g.
=Test(MYTABLE)
I get a value error saying that the data type is wrong. Is "MYTABLE" construed as a range rather than a ListObject and if so how should I pass the table?
Comment by: Jan Karel Pieterse (6/16/2010 12:24:21 AM)Hi Jo,
Thanks for letting me know!
Comment by: Jan Karel Pieterse (6/16/2010 12:26:01 AM)Hi Olivier,
Excel cells can only pass ranges and values to VBA functions, so your declaration must be changed to:
MsgBox TypeName(Table)
End Function
Comment by: Tjeerd (7/13/2010 8:14:15 AM)Hello,
How can I fill the values of a range of cells with the visible values of a column of a listobject (without using the copy/paste method, which always gives me many runtime errors), using VBA?
This works for the complete list:
Range(destination).value = Listobjects("x").listcolumns(3).databodyrange.value
But I need this for only a part of the list values, based on a criterium. You know of a way to do this without copy/paste?
Thx in advance
Comment by: Jan Karel Pieterse (7/13/2010 10:17:34 AM)Hi Tjeerd,
You could loop through the specialcells collection, cell type xlCellTypeVisible and use that.
Dim lCt As Long
Dim ocell As Range
For Each ocell In ActiveSheet.ListObjects(1).DataBodyRange.SpecialCells(xlCellTypeVisible).Cells
lCt = lCt + 1
Range("a20").Offset(lCt).Value = ocell.Value
Next
End Sub
Comment by: MARCO (7/13/2010 3:06:31 PM)Hi,thank you for the usefull tips,
have you some experience in searching data within a table.
in excel is there the Vlookup function that works fine. Is there something similar the have a specific info from a table in VBA?
thank you for tha answer...
BRGDS
Marco
Comment by: Jan Karel Pieterse (7/13/2010 11:58:02 PM)Hi Marco,
In VBA you can use
Application.WorksheetFunction.VLookup([argument list])
Comment by: Fred (8/5/2010 4:50:41 AM)How can I know if the selected cell is a table cell?
Comment by: Kurt Roy (8/10/2010 12:56:54 PM)Hello,
I have a range of cells bound to a ListObject. I would like to be able to have users type into one of its cells "=SUM(A2:B2)" (or whatever), and have the cell "remember" the formula.
Currently, When I type a formula into a cell backed by a list object, it immediately runs the formula for all cells in the column. I realize this is the "Calculated Fields" feature, but I would like to turn this off.
Any ideas would be appreicated.
Comment by: Rhoda Collins (8/10/2010 3:13:30 PM)My excel spreadsheet is telling me when I try to access Visual Basics, that I am out of Memory.....How do I add more memory
Rhoda
Comment by: JEFF (8/16/2010 3:19:25 AM)I am attempting to link a view in Sharepoint 3.0 with Excel 2007 using the following code:
ActiveSheet.ListObjects.Add SourceType:=xlSrcExternal, _
Source:=Array("server:45261/sites/Schaefer/Quality/_vti_bin", _
"{94F47A76-7A06-4BBC-B057-4D036508CD24}", _
"{BC63DC59-6685-4613-8F19-3BBABAB840ED}"), _
LinkSource:=True, Destination:=Range("A10")
End Sub
It works fine and will synchronize fine. The problem is the view in Sharepoint shows all folders and files in folders and subfolders. The linked view shows only the three folders of the root library.
How can I show all files and folders in the linked table?
Thanks in advance for your help.
Comment by: Jan Karel Pieterse (8/16/2010 3:20:23 AM)Hi Jeff,
I'm sorry, I know close to nothing about Sharepoint lists!
Comment by: Jan Karel Pieterse (8/16/2010 3:29:42 AM)Hi Fred,
Code like this:
MsgBox "Not in a table"
Else
MsgBox ActiveCell.ListObject.Name
End If
Comment by: Jan Karel Pieterse (8/16/2010 4:31:10 AM)Excel automatically fills a formula entered into a table cell in the entire column. You can click undo to undo that.
Comment by: Jan Karel Pieterse (8/16/2010 4:32:23 AM)Hi Rhoda,
I expect memory has little to do with this. Maybe there is a problem with your VBA project. Try downloading and running Rob Bovey's code cleaner, to be found at www.appspro.com
Comment by: Damian (9/2/2010 9:44:34 PM)How do determine if a column exists in a table?
Comment by: Jan Karel Pieterse (9/3/2010 7:30:00 AM)Hi Damian,
You could loop through the cells of the listobject's HeaderRowRange:
Dim oCell as Range
For Each oCell in ActiveSheet.ListObjects(1).HeaderRowRange
'Test here
Next
Have a question, comment or suggestion? Then please use this form.
If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.