List Object ..

Apr 13, 2007

I have some spreadsheets that rely on data being pulled in from external querys (from an SQL database).

the below line of code is part of a Workbook_Open() sub, which refreshes the data.

Selection.QueryTable.Refresh BackgroundQuery:=False

I have just got my hands on acopy of Excel 2007 (Yes!!!), but when i open my sheets my code now fails

Ater some messing around Ihave found that if I add ListObject" as below, it now works.

Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False

trouble is it doesn't work for the rest of the office, who didn't get Excel 2007

is it possible for me to have this so that it works for both flavours of Excel?

View 9 Replies


ADVERTISEMENT

Excel 2010 :: Can't Delete Filtered / Visible List Rows From List Object

May 19, 2014

I have pulled a SharePoint list into my workbook. The list object (table) is still linked to the SharePoint list, as I'd like to synchronize it later on. I have filtered it with an autofilter. I'd like to delete all of the visible rows. I have tried a billion things to no avail. I have been searching Google for hours now. None of the examples work.

View 5 Replies View Related

Display Object From Non Value List?

Dec 5, 2012

In my example you will see my name list that changes with a array to data validation. Seems like you can find ways around things most of the time but this time

View 4 Replies View Related

Get Total From List Object?

Aug 30, 2012

I need the sum of a column in a table. In the sheet I am using "=SUBTOTAL(109;[Total])", but I need the absolute total in VBA. How is that possible?

View 1 Replies View Related

List Of Object Names

Dec 7, 2007

I am trying to rename a text box, but it doesn't let me. The naming convention is fine, as it works with others. But it thinks that name is taken or something. So how can I see the names of all objects on a sheet?

View 9 Replies View Related

Dynamically Select List Object Name?

May 13, 2013

I have a workbook with three sheets:

-"DB" database sheet containing multiple tables (20 to be exact, named as "CityA", "CityB", etc.)

-"Threat Data" reference sheet containing a "City_Ref" table with the list of tables names in the "DB City ID" column and the unique city name assigned to each table in the "City" column (the city name is populated in a "City" column of each table in the database sheet).

-a dashboard sheet containing an interactive userform for which to populate the database tables.

In this userform, there are two comboboxes:

-a "CbxCity" combobox which lists all the city names from a "City" column in the "City_Ref" reference table

- a "CbxAsset" combobox which should automatically list all the values in the "Asset" column of the selected city table based on "CbxCity"

My issue lies in filling "CbxAsset" based on the selection in "CbxCity" ; how do I dynamically select a ListObject name based on a selection?

The code I am working with is:

[Code] .....

In the CbxCity_Change() sub, I am not sure how to name the ListObject and my code currently gets an error at r = Me.CbxCity.Value

Which is strange because that is showing the selected city name when I run the cursor over the bug.

View 14 Replies View Related

Get Cursor Location In A List Object Table

May 19, 2014

I know how to get the row number of the cursor in a spreadhseet (Activecell.Row), but how do I get the cursor location in a list object so that I can then insert a new row for the user at that point?

BTW, I am using tables (ListObjects) because when a row is inserted, all of my formulas are automatically inserted.

View 1 Replies View Related

Passing Columns As Paramater To Work On List Object

Apr 21, 2009

I'm trying to write a remove duplicates sub that can be passed the worksheet name and columns on which to check. It's pasted below. Above the key line is a commented out line of code that worked. So it works to pass theh worksheet, but I'm hung up on how to pass varying columns to it.

Sub RemoveDuplicatesSub(wksht, cols)
'Remove duplicates.
'Assumes that the data range is in a table
'Assumes the header row starts at row 7

Dim WS As Worksheet
Dim TableName As String

Set WS = Worksheets(wksht)

TableName = WS.ListObjects(1).Name

'WS.Range(TableName).RemoveDuplicates columns:=Array(3, 4, 5, 6, 7, 8, 9, 10), Header:=xlYes
WS.Range(TableName).RemoveDuplicates columns:=Array(cols), Header:=xlYes

End Sub

View 9 Replies View Related

Controls Added To UserForm Not In Form's Object List

Jan 20, 2008

I have been working on large project using Excel VBA for several days. My code seems to be working correctly, but I have more to do and now, when I drag an object from the toolbox onto a UserForm, the object is not added to the list of objects on the form. If I go back to versions of the project that I was working on several days ago, there is no problem. If I run "Workbook Rebuilder", the objects that I have dragged onto the form are then added to the object list, but I still can't add new objects to forms from within the VBA editor. Is the project corrupted, or is there some other explanation, and are there any fixes? The code runs about 50 pages, and there are over 20 forms, so redoing from scratch is only a last resort option.

View 3 Replies View Related

Object Library Invalid Or Contains References To Object Definitions

Jan 17, 2005

I'm getting the following error:

"Object library invalid or contains references to object definitions that could not be found"

I wasn't getting that error last night and I'm not sure what I may have done to cause this error.

It seems to be cause by code running on one sheet of my workbook, but I'm not really sure about that. I'm still a bit of a novice at VBA.

I'm using Excel 2002 SP3 and I'm running MS XP Home as my OS.

Do you have any ideas what can cause this error and/or how to trace down the offending objects/code?

View 9 Replies View Related

Pivot Chart Object: Find Any Suitable Object To Choose From To Make A Pivot Chart In Powerpoint

Mar 21, 2007

1) i have office 2003 on a laptop. within powerpoint, i can create a 'microsoft excel chart 11' object. to create a link to the excel data source, do i have to go through the odbc sql setup? it works, but i don't want my powerpoint to be dependent on some excel file somewhere. what are the other options to insert/make a functional pivot chart in powerpoint with the data also within powerpoint? the data as sheet option does not result in the chart being a pivot, it's just a plain chart. it has to be a proper object, not an image paste or a chart that updates links with the excel file open.

2) i have office 2007 on my other laptop. i can not find any suitable object to choose from to make a pivot chart in powerpoint. what's the best way to go about in 2007 version?

3) am i going about this the wrong way with the objects? should i be after vba code?

View 4 Replies View Related

Object-defined Error 1004 Application-defined Or Object-defined Error.

May 13, 2009

I am having difficulty getting a form to work the way that I would like it to work. I have a form that is used to display questions that my students will be answering. The form also is used to put the answers into a worksheet. I have 2 sheets. Sheet2 has the questions, student answers, and correct answers. Sheet1 is used to indicate correct answers and to keep track of percentage correct. I am fairly new to VB. I have 2 pieces of code that I am going to post. The first one works and the second one doesn't.

This is in the "This Workbook" section and it works.
Option Explicit
Public intNoQ As Integer
Public strNoQ As String
Public NumberofQuestions As Integer
Dim StudentName As String
Dim InputBoxAnswer As String
Public Sub Workbook_Open()
Application.Visible = False 'Hide Excel
Load Questions
Load NumberCorrect
NumberofQuestions = Worksheets("Sheet1").Range("K3").Value
For intNoQ = 1 To NumberofQuestions
strNoQ = VBA.CStr(intNoQ)
If intNoQ = 1 Then
Questions.Controls("QuestionNumberBox").Value = "Question#" & strNoQ
Questions.Controls("QuestionBox").Value = Worksheets("Sheet2").Range("B1").Value
End If
Questions.Controls("CorrectBox" & strNoQ).Visible = True
Questions.Controls("CorrectLabel" & strNoQ).Visible = True
Next intNoQ...............

View 9 Replies View Related

Object Variable Not Set ..

Jan 3, 2010

This code is part of a routine that copies and pastes from CASH RECEIPTS sheet to YEARLY TOTALS sheet in the same workbook. rngCash is assigned as a Range variable.

In the various fixes I’ve tried, the code worked to varying degrees.

View 14 Replies View Related

Set Range With Object?

Feb 15, 2013

Have the following code:

Code:
Dim Recurring_Total as Range
With .Range("A" & Rows.Count).End(xlUp).Offset(,2)
Set Recurring_Total = .Range("A" & Rows.Count).End(xlUp).Offset(,2)
.Font.Bold = True
End With

What do I replace part in red with?

View 9 Replies View Related

Object Required

Sep 27, 2007

i am having alittle trouble with this line of coding.

With ActiveWorkbook
.Sheets("Hidden Data").Cells(rngFound.Row, 9) = Sheets("Data").Range("L18")
.Sheets("Hidden Data").Cells(rngFound.Row, 12) = Sheets("Data").Range("l20")
.Sheets("Hidden Data").Cells(rngFound.Row, 15) = Sheets("Data").Range("l22")
.Sheets("Hidden Data").Cells(rngFound.Row, 18) = Sheets("Data").Range("l24")
.Sheets("Hidden Data").Cells(rngFound.Row, 21) = Sheets("Data").Range("l26")
.Sheets("Hidden Data").Cells(rngFound.Row, 24) = Sheets("Data").Range("l28")
End With

with the line "Sheets("Hidden Data").cells(rngfound.row, 9) = sheets("data").range("l18")" the error messages states Object Required, but i am not sure what this is trying to say.

any idea's or thoughts would be greatly appreciated.

at the top of the coding i have listed "Public rngFound"

View 9 Replies View Related

Insert OLE Object Using C#

Oct 22, 2008

I don't know if this is where I should post this thread, but I'm looking for this for the last 4 days, so any thing will help...

I'm trying to insert OLE object using c#, but two method I've found are not working.

excel object for .net (ws is worksheet object)
1.
ws.Shapes.AddOLEObject(XlOLEType.xlOLEEmbed, fileName, false, false, missing, missing, missing, 50, 50, 150, 200);

creating new macro and execute it (security problems)
2.
string macro = "";
macro += "Sub addObject()
";
macro += " msgbox("123")
";
macro += " OLEObjects.Add(Filename:="c:\123.txt", Link:=False, DisplayAsIcon:=False).Select
";
macro += "End Sub";

Microsoft.Vbe.Interop.VBComponent module = null;
Console.WriteLine(macro);
module = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);
module.CodeModule.AddFromString(macro);

ws.Application.Run("addObject()", missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing, missing);

View 19 Replies View Related

Object Browser (F2)

Jan 11, 2009

Is there any way to treat the VBA Object Browser with code?

For example: Put on a UserForm.Lable.Caption data from the Object Browser:

All Classes in MSForms, and all Members of MSForms 'CheckBox' class.

View 9 Replies View Related

Open Object

Oct 19, 2009

I have inserted a workwook in another workbook that the user will have to edit and save changes from time to. I have the excel file inserted as an object. when I double click it the file opens and I can save changes made.

I'm trying to create a macro that can open the object from other tabs. i used the macro recorder and got this

Range("I21:O25").Select
Sheets("Formulas").Select
ActiveSheet.Shapes("Object 3").Select
Windows("Worksheet in SBATrendReport2010").Visible = True
Selection.Verb Verb:=xlPrimary
ActiveWindow.WindowState = xlMaximized
End Sub

The problem I'm having is I get a error message when I run the macro
"subscript out of range.

View 9 Replies View Related

Add Hyperlink To Object

Dec 9, 2009

I have a sub which loads a bitmap image onto a sheet, lets say to sheet1 ,cell B2, the reference sheet1!b2 is held on sheet2 in column A.

how to add a hyperlink to the image so that when it is clicked it looks at looks at the value on sheet2 and creates hyperlink to that cell, effectively selecting the cell underneath the image. I've tried this with text - using the following in a cell:

=HYPERLINK("[LoCQ34.xlsm]indirect(Sheet2!A2",Sheet2!B2) - this works for a text link, using the add hyperlink from the right click menu but doesnt do what I want with a bitmap, and I'm not sure how I'd do this in VB.

So, i would like to know it can it be applied to an image, and how can it be done in VB as the value in Sheet2 changes?

View 9 Replies View Related

Set Object Variables To Nothing

Dec 7, 2006

How important is it? I've read some books that recommend it and others that don't even mention it. I have a procedure that uses a lot range, worksheet and workbook variables. Should take the time to set each one to "Nothing"?

View 3 Replies View Related

Object Variable Not Set

Jun 15, 2007

I'm writing a little program for a co-worker of mine to make their job a little easiar. Here is the code that I have written so far. The first 2 parts of it work perfectly fine, but if it gets to the 3rd part I get an Object Variable or With Not Set. I understand this is horribly written code as I am kind of a beginner and just do things by trial and error.

Sub Test3() ....

View 3 Replies View Related

Object Variable Not Set Error?

Jul 24, 2012

Am getting Object variable not set error in the selection.find code. I dont see anything wrong with the code..

VB:
rivate Sub Btn_Edit_Click() ' code for Edit Function
Dim findvalue As String
Dim FileName As String

[Code].....

I can see the value of "findvalue" do exist in the column "A:A".

View 3 Replies View Related

Set The Color Of The Chart Object?

Sep 20, 2012

I want to set the color of the line.

VB:
With Sheets("Graph").ChartObjects("Chart 3")
.Activate
.Chart.SeriesCollection(1).XValues = Sheets("Data").Range("D2:D" & Sheets("Data").Cells(Rows.Count, "D").End(xlUp).Row)
.Chart.SeriesCollection(1).Values = Sheets("Data").Range("E2:E" & Sheets("Data").Cells(Rows.Count, "E").End(xlUp).Row)
.Chart.SeriesCollection(1).(What Do I put here To Set the color of the line)
End With

View 1 Replies View Related

Wipe Object Values With Nothing?

Dec 18, 2012

When calling the code below, oTbl seems to retain the data from the previous call to the function. During debug, the Nothing statment clears oTbl and I can see that it is empty. However once Set oTbl = oFunc.Tables("ENTRIES") is called, oTbl contains all the data from the previous call again, i.e. even before the oFunc.Call statement that actually connects and retrieves the data from the remote system.

VB:
' oRS is a global Connection Object variable to a remote system
Public Function fncTest()
Dim oFunc As Object

[Code]....

View 1 Replies View Related

Error Object Or With Variable Not Set

Jul 20, 2013

I am trying to make this code work, I am getting the subject error:

HTML Code:
Private Sub CommandButton1_Click()Dim cols As VariantDim NextRow As ObjectDim Myrow As Object
Sheets("Sheet1").SelectWith Sheet1
Myrow = Range("A" & Rows.Count).End(xlUp).Row + 1

[Code] .....

View 5 Replies View Related

Blinking / Flashing Object

Sep 10, 2008

how to make an object, e.g. Oval Object to flash a solid yellow & white colour? I can make blinking cells, but not objects.

View 12 Replies View Related

Printing A WebBrowser Object

Oct 22, 2008

I'm currently creating a report with a weather forecast on it. the document is Excel based and the weather shows up as a HTML display in a WebBrowser object on one of the sheet.

Unfortunatly, even with the PrintObject property set to True, and try to place the object on the foreplan, I can't seam to ba able to have the object show up on the preview or print.

View 3 Replies View Related

Looking For A Way To Hover Over Object And Have Image Appear

Jan 6, 2010

I don't know if this is possible or not but I am looking for a way to hover over a cell and have an image appear. If this is not possible is there a way to add something like a comment on the cell but have it be an image not text? Currently I have the cell hyper-linked to the image but it would be nice if I did not need a whole other window to pop up.

View 3 Replies View Related

Display Object By Cell Value

Dec 5, 2012

Not sure of best way for this.If cell G10 has value show object1, if cell G10 has no value show object 2 ?

View 7 Replies View Related

How To Refer To Object Which Is In Another Subroutine

Apr 3, 2014

Is it possible to refer to an object which is in another subroutine?

Or should i have to give its definition in each sub i need it?

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved