But there are significant changes to this part of the object model and I am only going to touch on the basic parts here. Name = _ "Table1" ' No go in 2003 Active Sheet. Table Style = "Table Style Light2" End Sub But the new stuff is right there already: Table Styles. Line Style = xl Dash End Sub This changes the linestyle of the bottom of your table. If you have any other workbook open, all tables with the same tablestyle appear in your changed style! Select ' Select only data of first column ' No go in 2003 . 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 rg Cell As Range For Each rg Cell In Intersect(Target, Range("Tasks"). Converting a range to a table starts with the same code as in Excel 2003: Sub Create Table() Active Sheet. A collection of objects which are a member of the Workbook object. You can change the formatting of a table Style, e.g. But if you save your file, close Excel and open Excel again with the file, the changes are gone. 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). I have a problem since a few weeks whereby both Msg Box and the popup from Application. It seems to be a general popup issue, and it does not matter where in the code it is.
Sometimes after I click on the VB editor, I can go back to the spreadsheet and I can see the popup, but nothing happens when I click on it. An example of where it gets stuck is the following: (you can see it turns off events, screenupdating, etc.). Screen Updating ' disable events, alerts, automatic calculation & screen updating With Application . Without seeing more, that's the only thing I can come up with.I have this for several macros, so it doesn't seem related to a specific macro. This might run around 6-7 times at best, or sometimes just 3-4 times before it crashes with the popup. Just to let you know that this appears to have resolved it, so thanks very much for your help! Now I just have Excel freezing when there is an error in the code - so an unexpected popup. It's a pity it loses the line number of the regular error message though. Sub Sorting And Filtering() ' No Go in 2003 With Active Workbook. Screen Updating=false Then at the end, turn it back on: Application. But you can tell Excel to hide the title row by unchecking the box "Header row" on the table tools tab of the ribbon. Apparently I noticed, that I could use the displayed value as well... Is there any reference where I could familarize myself with object properties etc? Color Index = 5 'Blue End With Case "Behind Schedule" With rg Cell. I'm only showing a tiny bit here, a Sort on cell color (orangish) and a filter on the font color. 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. It may help to turn off screenupdating at the beginning of your code though: Application. If I apply table style using VBA, it adds "Column1" for the first cell, which is not necessary. Thanks, Mohan Hi Mohan, You cannot prevent the title row from appearing, as Excel needs that for referencing columns in the table. Once I set up the Combo Box properties as you advised, it does return the value I wanted. Color Index = 44 'Gold End With Case "Late" With rg Cell. Assuming your cell is within the data Body Range of the table and you have no column striping you'd get something like this: Function Get Style Element From Table Cell(o Cell As Range, o Lo As List Object) As Table Style Element '------------------------------------------------------------------------- ' Procedure : Get Style Element From Table Cell ' 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 l Row As Long 'Determine on what row we are inside the table l Row = o Cell. Just a few new things have been added, like the "Always Insert" argument to the List Rows. If anyone has a better solution, please let me know... At first you must activate List Object to get Insert Row Range in Excel 2003. I am storing data in Excel 2007 tables and use INDEX function in excel to select required data from specific row in the table. How do I inicialize form to include proper items from a specific table into this combo box? thanks, Radek Created a macro for sorting the excel worksheet according to colour . Afterwardd when I run the command button , I am getting the following error. 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 did some googling and this is what I've come up with. Dim i As Integer For i = 1 To 29 // I have 29 rows in my table If combo Box1. Altho in the VB editor I still see the 3 sheets in the structure.
Another part in which lists already had most of the functionality. In that case you have to get the last row of the table and move down one row from that. List Objects(1) Set obj List Rng = Get Insert Row(lo) obj List Rng. 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. I've created spreadsheet which automaticaly calculates data, based on used values. Some fields are combo boxes, and I need to load data from a column into these combos. 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 Combo Box). Let me explain When I open a new xls I have 3 sheets. 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.
I keep having to force quit (through task manager), then restart Excel, and then inevitably after 3 or 4 runs it happens again. (I have around 3600 lines, although the code for any specific macro I run is just several hundred lines of code). Can you give an example of code that will definitely crash after a few times? Screen Updating = False End With Load Vars 'this runs another Sub of 50 lines of code, which grabs various variable values from the current sheet Dim category Names(500) As String Dim indic IDArray(0 To 10000, 0 To 1) As String Dim category Names Counter As Integer Dim indic IDCounter As Integer Program Workbook = Active Workbook. Get Open Filename("Data files, *.*", , "Please select a file containing the data you wish to import.") If Data File = False Then End End If ...freezes with this popup Try to set screenupdating to true and add a 'doevents' before the "Program Workbook = Active Workbook.
What happens is when the popup appears, I can no longer select my spreadsheet and see the popup (when I click on the window, nothing happens). I am using Windows 8 with Excel 2013 Home & Student edition. The code could definitely be more efficient, but a lot of code is unavoidable - there's around 25 different functions that need to be performed, each ranging from several lines to up to around 700 lines of code for the largest. Name" and then turn off screenupdating again after the popup is closed.
Select End With ' No go in 2003 '2: with the range object 'select an entire column (data only) o Sh. Select 'select an entire column (data plus header) o Sh. Select 'select entire data section of table o Sh. Or what is the best way to handle diffrent tables or sheets in a XLA.
Add method: Sub Table Inserting Examples() 'insert at specific position Selection. Function Get Insert Row(obj List As List Object) As Range obj List. Run-time error - 2147319765 Automation error Element not found. Thanks Regards S Srinivas Hi Jan, Thanks for the hint! How can I reach the sheets in the xla by a self written function or procedure?
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. The code comments show you where Excel 2003 differs from 2013, 20.