Value = "2nd insert" End With End Sub 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. If you can't use a standard button it is not a problem to change it to something else. If I try to change the formula of a cell in a table (aka listobject) in 2007 using vba I get an error. set rng = ' a reference to a cell in a table rng.formula = "= my formula" gives error code 1004. 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)? Sub sub Drop Down Activate(str Range, str Tab As Object, str Table As String) Dim var Values As Variant Dim var Values String() As Variant Dim str Formula1 As String Dim lng Count As Long Select Case str Table Case "tbl DSRDocument" sht List Source. Option Explicit Private Sub Combo Box1_Change() Combo Box1.
Value Re Dim s Values(1 To UBound(var Values, 1)) For l Ct = 1 To UBound(var Values, 1) s Values(l Ct) = var Values(l Ct, 1) Next s Formula = Join(s Values, ",") Range("" & str In Cell & ""). How would I get Case "tbl DSRDocument" filters to work before assigning the var Values variable? Auto Filter Field:=4, _ Criteria1:="1", Operator:=xl Filter Values sht List Source. Is there another method which does not need a button to trigger the combobox? Hi Karel, I put the whole code below in "thisworkbook" but it does not seem to work.
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. On Then Has Filter = True Exit Function End If Next End Function Sub Test() Msg Box Has Filter(Active Cell. Delete Next End Sub Jan, This is an excellent place for information on the Excel 2007 table Object. Find(s Username) Set o Col = Nothing Set o Col = Active Sheet.
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.
Add(Always Insert:=True) If you then want to write something in the first cell of the new row you can use:o New Row. Adding a comment to a table through the UI is a challenge, because you have to go to the Name Manager to do that. Hello: I am currently trying to use a workbook which was developped using a prior version of Excel. Select, but in a List Object I can't get it to work... Value = i End If Next i As you can see, I'm nowhere with this script, I'd appreciate help thanks, Radek 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. Show Table Style Row Stripes Then 'We are in the table's body If l Row Mod 2 = 0 Then Set Get Style Element From Table Cell = o Lo. Table Style Elements(xl Row Stripe1) Else Set Get Style Element From Table Cell = o Lo. Table Style Elements(xl Whole Table) End If Else Set Get Style Element From Table Cell = o Lo. Table Style Elements(xl Whole Table) End If End Function Sub test() Dim o Lo As List Object Dim o TSt As Table Style Element Set o Lo = Active Sheet.
Value="Value For New cell" This is something Excel 2003 cannot do and is related to the fact that a table is a range name. Luckily Microsoft did include the table object if it comes to recording your actions, unlike the omission on the charting side...
Value) Next End Sub 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? In Excel 2007 it equals to Nothing after the 1st row insertion despite the Active Cell is ALWAYS within the List Object. If you don't map the table to xml you don't get the insert row. =Table_SDCBIBE01_SDCBFDDS_BF_Retail Summary#This Row],[Retail Sales/Table_SDCBIBE01_SDCBFDDS_BF_Retail Summary#This Row],[Inv Pct Is there any way to reference a different row using the table syntax? Jan, First, thank you for your help on the previous question I posted (11/8/2009 AM) - worked like a champ.
Value, Look In:=xl Values) If Not c Is Nothing Then c. In Excel 2003 Insert Row Range was never Nothing when the List Object was active (Active Cell within the List Object). I can only guess that excel is doing and auto correct or something which is masking an error in my formula. 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. Find("Access Level") Get Access Rights From Table = Intersect(o Col. 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 PM) Formulas work well within the same row using [#This Row] ex.
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.
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?
Finally it applies the tablestyle and deletes the temporary style: Note that the function shown above does not take into account that you can set the width of the stripes, both vertically and horizontally. I have to convert the table to a normal range before the name is recognised by the Excel ODBC driver. eg, I can use Current Region to select the whole table, but how do I obtain the table name so that I can start working with its fields? So when the User opens the Workbook, I want to find their [Username] in "tbl Administration", and set str User Membership (variable) to the associated value in the field [Access Level]. Function Get Access Rights From Table(s Table Name As String, s Username As String) As String Dim o Col As Range Dim o Row As Range On Error Resume Next Set o Col = Active Sheet.