Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Open Object

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

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 Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Method Open Of Object Workbooks Failed
I need some help with this error. I have some VBA code in Outlook that runs whenever a task reminder goes off. The code is supposed to open a workbook, send email based on the info in the workbook, then close the workbook and quit excel.

The task reminders are set to go off every two hours. Sometimes this will work fine for days and then randomly (as far as I can tell) I get the error: "Method Open of Object Workbooks failed". If I press Debug and then Run without changing anything at all, it works fine.

I am using Outlook 2007 and Excel 2007. The workbook is a shared workbook on a network drive.

I can post code if needed, but will have to go to that computer.

Why do I occasionally get this error when the workbook definitely exists and has the same name and path?

What does entering debug mode do that then allows the macro to run without error?

View Replies!   View Related
Object Defined Error When Code In Workbook Open Event
I have a macro that checks if a username is in a particular list, and if it is, it unhides certain sheets in the workbook.

The code runs fine if I just run it as a macro or off a command button, but I am trying to execute it when the workbook opens and I keep getting a 57121, Application defined or object defined error.

The code is below;

Private Sub Workbook_Open()


Dim Res1 As VbMsgBoxResult
Dim GovRng As Range

For Each GovRng In Sheets("Map").Range("GovernanceMembers")
If GovRng.Value = Application.UserName Then Goto 111
Next GovRng

Exit Sub

View Replies!   View Related
Embedded Pdf File Open Error "Cannot Start The Source Application For This Object."
I have embeded a PDF file in an Excel Worksheet. When I double-click to open the PDF file the I get an error message: "Cannot start the source application for this object." However, the pdf file opens anyway. how to prevent the error message from displaying (without stopping real error messages)? (...happens in Excel 2000 and Excel 2003) NB: I researched this site first - user "Ed" raised the same issue 27/1/03 - but it doesn't appear to have been resloved.

View Replies!   View Related
Application-defined Or Object-defined Error :: On Open Everytime
I had to change sheet one but everytime I open this workbook, it keep saying "Application-defined or object-defined error"

but I cannot find what is causing the bug. I have checked the name of sheet and it seem correct for me in the codes.

View Replies!   View Related
Object Library Invalid Or Contains References To Object Definitions
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 Replies!   View Related
Pivot Chart Object: Find Any Suitable Object To Choose From To Make A Pivot Chart In Powerpoint
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 Replies!   View Related
Check If Workbook Is Open & Open If Not Open
I would like to write a Sub that will see if a workbook is open and if it is not then open it. I know how to have a macro automatically open a workbook, but I run into problems when the macro runs and tries to open an already opened workbook.

View Replies!   View Related
The Workbook Open Minimzed Until The The Splash Screen Closes Then Open Properly
My Splash screen opens a few seconds after the workbook has loaded. Is there a way to make the workbook open minimzed until the the splash screen closes then open properly? What I trying to say is that only the splash screen is visible until it closes.

View Replies!   View Related
Macro -open The Look In List” And Stop So I Can Pick A File To Open
I want to create a macro that will “open the look in list” and stop so I can pick a file to open. I’ve tried to use “record a macro” and “ctrl-o”, but the record a macro won’t stop until I pick a file or cancel the file list. I also tried to use “o” in the short cut key box

View Replies!   View Related
Create Open Copy Of Open Workbook
Create a full copy of an open workbook (eg. activeworkbook MyFile.xls) using VBA, with the new copy (eg Book1.xls) open as well ,without having to save a copy first then open it ?

View Replies!   View Related
Suppress Open Events When Using Workbooks.Open
I have built a sub that prompts the user for a folder then opens every workbook in the folder 1 at a time to get stats on the contents of each workbook. Worked like a dam until I ran into an unexpected bug. Some of the users built on open events in their workbooks. ...

Right now my routine inventories workbooks to get formula counts, cell counts, most complex formula, highest value... it does this by looping throught the sheets and the cells. If there is a way of obtaining those stats without opening the workbook I may need to rethink a lot of my work.

way to suppress the code in the target workbook I open through

View Replies!   View Related
Check To See If File Is Open, And If It Is Open, Activate It
I have two workbooks. One is a no-nonsense form interface that my bosses will use to enter safety information. I'll call this workbook "Form". This file is stored locally on each of their computers. The other workbook is stored on a common drive. I'll call it "Master".

When my bosses fill out the Form and click "Submit", the Master file is opened, and certain cells are populated based on information entered in the Form. This is the code I am using to make this happen:

View Replies!   View Related
Force Re-Open Of Open Workbook With No Confirmation
this is a relatively straightforward query, would be obliged for any tips on same. I have the following piece of -

View Replies!   View Related
MacRO: Check If WorkBook Open. If Not, Open It.
I did a macro on my mac to transfer a sheet from one workbook to another worbook. It works very well when the destination workbook is open. Therefore I wanted to add some piece of code to check if the destination workbook is open. If not then I wanted the macro to open it before tranfering the sheet. Here is the code I´m using for tranfering the sheet

Sub Transfer_Sluttet()
If ActiveSheet.Index <> Sheets.Count Then
Application.DisplayAlerts = False
Set ws = ActiveSheet
Sheets(ws.Index + 1).Delete
ws.Move Before:=Workbooks("Sluttet.xls").Sheets("sheet2")
'Moves active sheet to beginning of named workbook.
'Replace Test.xls with the full name of the target workbook you want.
Application.DisplayAlerts = True
End If
End Sub

This is the type of macro I useually use on my pc to check if a workbook is open and if not then open it

If IsWorkbookOpened("Filename.xls", "C:Documents and ..................

View Replies!   View Related
Get Open File Name To Open Multiple Files
I'm doing the following:

Dim myname As String
myname = Application. GetOpenFilename

I use it to attempt to open first a QuickBooks file and then an excel workbook.
The Quickbooks file opens fine. The Excel workbook never appears to open but
I do get the full file pathname to my excel workbook returned which I then
parse off to get just the workbook name.

Why would it behave this way? I open both these files manually all the time.

Also, I'm trying to use a filter as follows:

myname = Application.GetOpenFilename("*.QBW")

I get a compile error saying that I'm calling the function wrong.

View Replies!   View Related
Open Message Box Or Disable Option On Open
how to make a MsgBox open anytime a cell in Column A was double-clicked--and the content of the MsgBox was from the cell in the same row in column Z. I've now realized the double-clicking isn't an ideal solution, as it's easy to click in another cell and mess up all the formulas.

(1) Code that deactivates the "edit directly in cell" option when the workbook is opened.

(2) Code that opens the MsgBox when a cell in Column A is clicked just once (or similarly less fool-proof than double-clicking or right-clicking).

View Replies!   View Related
Open Workbook, Open Worksheet
is it possible to write vb code to open the workbook, scroll through everysheet? extract the values from each worksheet?

let's just say, I only know the workbook name, but not the sheets of the name inside. So the vb code could scroll through every worksheet without knowing the worksheet name, only the workbook name(workbook.xls)

View Replies!   View Related
Check If Workbook Open If Not Open It
Need some VB Code to check if a workbook is open or not. Either way, it needs to be opened and made active.

I have tried using some of the answers already on here but not getting anywhere.

View Replies!   View Related
Open File/Workbook If Not Already Open
Looking for a macro, to run upon opening file, that opens a linked file and if the file is already open does nothing.

View Replies!   View Related
If-sequence To Not Open Workbook If Already Open
This is pretty basic I just don't know what command to use. I have a macro that's supposed to copy data from one workbook to another, it works fine but In the macro it open the other workbook then closes it. I would like the macro to not reopen the second workbook if it is already open (since reopening causes it to lose the newly updated info). I think it can be done with a If command but I don't know which tried the following: If workbooks("M:/database") Is Open but of course 'is open' doesn't work, but can anyone tell me what to use instead.

View Replies!   View Related
Object-defined Error 1004 Application-defined Or Object-defined Error.
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 Replies!   View Related
Make Workbook Open- Open Correct Month Workbook
I have Monthly sales sheets that import my cash register data into them. I wanted to set them up to do everything without being there. So I have my task manager open excel at 9:30pm everyday and it runs the macro to import the data into the correct day of the month. Here is the workbook

open macro-

Private Sub Workbook_Open()
Dim dTime As Date
dTime = Time
If dTime >= TimeValue("9:30 PM") And _
dTime < TimeValue("9:40 PM") Then
End If
End Sub

This is in my July spreadsheet only. So is there a way to make it know which month spreadsheet to open on the 1st of the month? So come August 1st it will automatically open the August workbook and input the data for the first day? By using the date?

View Replies!   View Related
Automatically Open New Workbook After 1st Workbook Open 4 Seconds
I have a presentation that I open from a short-cut. After the "Welcome Page" is opened, I want to open a second workbook in a new instance of Excel after 4 seconds.

I think that I can open the new instance of excel, but I don't know how to activate the macro after 4 seconds.

I'm sure there is a function someplace for this that can be used in a macro.

Then, after the second Workbook is opened, I want the Welcome Page "Workbook" closed, leaving the second Worbook open.

View Replies!   View Related
Object Required
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 Replies!   View Related
Object Variable Not Set ..
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 Replies!   View Related
Add Hyperlink To Object
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 Replies!   View Related
Insert OLE Object Using C#
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)
ws.Shapes.AddOLEObject(XlOLEType.xlOLEEmbed, fileName, false, false, missing, missing, missing, 50, 50, 150, 200);

creating new macro and execute it (security problems)
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;
module = wb.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule);

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 Replies!   View Related
Object Browser (F2)
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 Replies!   View Related
List Object ..
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 Replies!   View Related
Set Object Variables To Nothing
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 Replies!   View Related
Object Variable Not Set
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 Replies!   View Related
Blinking / Flashing Object
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 Replies!   View Related
Selecting A Picture Object
On one sheet I have a table of contents that has a name in column A. In column B I currently have a hyperlink to some text to take me to a cell under a drawing object in another sheet. I wanted to make this more elegant and easier to see which object you are supposed to look at. To do this I thought of using VBA and anchoring a text box in column B of the TOC sheet that I could attach a macro to. Below is my code but it means I need a separate macro for each object. I have 200 and counting. The text in the adjacent cell in column A contains the name. Is there some way for the macro to know what row the control object is in so that it can get the name from the adjacent cell?

Second question. Is there a routine like .OnSelection where I could change the background color of the object only when it is selected? So when another object is selected the previous one reverts back to the original condition.

View Replies!   View Related
Error '424' Object Required
Sub Macro2()
' Macro2 Macro
' Macro recorded 7/28/2007 by i8ig

If Target.Column = 1 Then
If Target.Value = "Med" Then
Rows(Target.Row).Interior.ColorIndex = 4
ActiveCell.FormulaR1C1 = "=IF(RC[3]="""","""",RC[3]-3)"
If Target.Value = "Tasc" Then
Rows("4:4").Interior.ColorIndex = 44
ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-2)"
If Target.Range = "NBAR" Then
ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-5)"
ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-2)"
ActiveCell.FormulaR1C1 = "=IF(RC[1]="""","""",RC[1]-2)"
End If
End If
End If
End If
End Sub
I continue to receive an error '424' object required and I cant find it

View Replies!   View Related
For Loop Object Defining
I tried to run this code and encountered an error telling me that N should be an object.

N has to be a variable number representing the amount of sheets in the workbook -2.

Does anyone know how this is written?

View Replies!   View Related
How To Get The Cells In Which The Shape Object Is There
I have a shape object which is created dynamically.

Now onAction event of the shape object, I need to get the cells row and column number where the shape object is sitting. How can i get it?

View Replies!   View Related
Vlookup For Object With Subtotals
I'm trying to look-up the sub-total of sheet 1, Fund 7000107000(which is an object) from the pink cell of sheet 2.

What formula can I use? ....

View Replies!   View Related
Error Object Required
I have two worksheets one called "invoice" and one called "tenants"
"Invoice" has a userform where I want to enter a tenancy number and then lookup the tenants name and address from "tenants"

I have put the following macro in

Sub lookup()
Dim res As Variant
res = Application.VLookup("A1", Tenants.xlsx("Sheet1"), "$A:$H", 2, False)
If IsError(res) Then
MsgBox "not found"
MsgBox "found at pos: " & res
End If
End Sub

but get the following error - "object required"

View Replies!   View Related
Error 424 Object Required
I am trying to get the code to search for me specific words in the excel files in my Folder as specified, however when i try to run it, there will be a Error 424 : Object required.

Sub SearchText()

cr = vbCrLf
quot = Chr(34) 'quotes

Dim l As FileSearch
Set l = Application.FileSearch

s = InputBox("Search", " Enter the text you're looking for.")

With l
.LookIn = "D:FinancialNews"
.SearchSubFolders = True
.FileName = "*.xls"
.MatchTextExactly = True ............................

View Replies!   View Related
Macro - Object Variable Not Set
My code as below

Application.Goto Reference:="CURRENT"

C1 = Range(Cells(4, 8), Cells(4, 200)).Find("HC4").Value.Row + 1


Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False


I want to copy the range 'Current' , this part, the easy part works.

Next I want to look in the range of H4 to GR4 for the value that also appears in HC4

In my example this would be CW4 then I want to paste 'Current' to the cell 1 row below, ie CW5

View Replies!   View Related
Pass Custom Object To UDF
I used to be pretty good with VBA, and I know I am a quite decent programmer in other languages, but I have been working on this for six hours and have no idea what I am doing wrong. Any help would be appreciated.

Here is my class (I know I can do better with access modifiers/ properties in a serious version):

'The class is called ConsCell

Public Car As Variant
Public Cdr As Variant

Private Sub Class_Initialize()
End Sub

Here is the code from the module which tries to call it:

Function MakeCell()
Cell = New ConsCell 'Execution always stops here-- but with no error message
Cell.Car = 15
Cell.Cdr = "NIL"
MakeCell = Cell
End Function

Function PrintCell(c As ConsCell)
PrintCell = c.Car
End Function

The code for a cell in the spreadsheet itself is this:


But it gives me the #VALUE! error.

View Replies!   View Related
How To Know Which Object Library To Choose
how you know which object library to add to references when you want to automate an application?

for example Adobe acrobat.

or internet explorer ( ie).

I know that the .dll for ie id shdocvw (an i know it's explicitly listed under internet controls) but how would i know this is it wasn't listed?

Also, correct me if i'm wrong, but usung the shdocvw.dll will only give you access to the main controls of Ie nd you would need to add a HTML library to do anything use full. How do you know if there are other libraries available can make the 'usefulness' of one library more useful?

View Replies!   View Related
Arrays As Object Properties--VBA
After much online digging, and experimentation, I think I determined earlier that you can't really expose an array all-at-once if it is an object/class property, as you can with an array variable. Now I'd like to confirm this, if I could, because it would be neater to run my simulation in an object instead of a giant and labrynthine user-defined type.

I try the following ....

View Replies!   View Related
Object Destroyed As Soon As It's Created
I've tried declaring my object variable (TabOrder) as module-level within a Sheet Module and also as local within a Subroutine in the Sheet Module. In both cases, when the debugger steps through the code, the object is destroyed almost as soon as it is created.

View Replies!   View Related
List Of Object Names
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 Replies!   View Related
Use The Result Of A Filedialog Object
I want to use the selected file (using FileDialog Object) as an argument in the Import External Data wizard.

here's the code I got so far:

Sub SelectFile()
Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
Dim SelectedFile As Variant
With fd
.AllowMultiSelect = False
If .Show = -1 Then
For Each SelectedFile In .SelectedItems
Next SelectedFile

View Replies!   View Related
Looking For A Way To Hover Over Object And Have Image Appear
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 Replies!   View Related
Printing A WebBrowser Object
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 Replies!   View Related
Assign Value To Range Object
I'm doing somthing basically wrong here but i'm not sure what, everything seems to work as planned except in each case where a value is assigned the value isn't actually assigned. any ideas?

The basic objective is to convert a list of numbers (1 to 3 digits) to the format "000-" i.e. all three digits with a hyphen at the end.

View Replies!   View Related
Using SetFocus To Any Other Object On Userform
I have been trying to setfocus to any other object on my userform with absolutely no luck at all?

In the code below I open a dialog box to import a text file (with CommandButton1), if no file is selected the user is prompted, this works fine. However it leaves focus to the textbox and the user would have to click on some other object and re-enter the textbox to open the dialog box again.

View Replies!   View Related
Copyright © 2005-08, All rights reserved