Centering The Active Object
I have a basic data table with KPI labels down one side and dates across the top. Using this data table as the series source, I create a number of charts below it with serialised names eg Chart0, Chart1, Chart2, etc. I am attempting to enable the KPI labels so that when a user clicks it, the spreadsheet will navigate to the appropriate chart. I place the name value for the chart object in the cell immediataly to the left of each KPI label (and hide it). This way I know which chart object below is related to which KPI label above. That part works.
I have a Worksheet_SelectionChange() event procedure attached to the charting worksheet. When a user clicks into one of the KPI labels, the event procedure fires and the appropriate chart is activated. The code is:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("B5:B19")) Is Nothing Then
On Error Resume Next
chtObjName = Range(Target.Address).Offset(0, -1).Range("A1").Value
If Left(chtObjName, 5) = "Chart" Then
ActiveSheet.ChartObjects(chtObjName).Select
If Err.Number <> 0 Then
MsgBox "No such chart exists.", vbCritical, _
"Chart Not Found"
End If
End If
On Error Goto 0
End If
End Sub
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Error # 429 Active X Can't Create The Object
I am having trouble with the following code... I get error # 429 Active X can't create the object... Dim blnIOpened As Boolean Err.Number = 0 On Error GoTo failed blnIOpened = False Set MyXL = GetObject(, Excel.Application) failed: If Err.Number = 429 Then Set MyXL = CreateObject(Excel.Application) blnIOpened = True End If If Err.Number 429 Then MsgBox Err.Number End If
View Replies!
View Related
Create Macro To Chart Data With Location As Object In Active Sheet
I have been trying to create a macro in excel to chart a selection of data and to output the chart on the active sheet where the data was taken (as opposed to a named sheet). So basically, I have about 300 worksheets with data, and I would like to have a button on each page that automatically charts that data when clicked, and outputs the chart to the page where the macro was clicked. However, I have not been able to figure out a relative reference that will allow me to make the LocationasObject reference simply the ActiveSheet as opposed to a specifically named sheet. See my code below, which references an output to a worksheet called "Charts". Right now, all of my charts are outputting to the sheet called "Charts", as opposed to the active sheet. Sub ConsDiscChart() ActiveCell.Offset(29, 11).Range("A1").Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlToLeft).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlUp).Select Selection.End(xlDown).Select ActiveCell.Offset(0, 1).Range("A1:B1").Select Range(Selection, Selection.End(xlDown)).Select ActiveCell.Offset(0, -1).Range("A1:C24").Select Charts.Add ActiveChart.ChartType = xlLineMarkers ActiveChart.Location Where:=xlLocationAsObject, Name:="Charts" With ActiveChart .HasTitle = False .Axes(xlCategory, xlPrimary).HasTitle = False .Axes(xlValue, xlPrimary).HasTitle = False End With End Sub
View Replies!
View Related
Centering
1st: when a cell = claim* I need to remove formatting (pattern fill) and then need to recenter and reset font size. 2nd: CHAR(10) does not work for me in a range("A20).value=range("A20").value & date cell & " - " & additional info & CHAR(10)
View Replies!
View Related
Application-defined Or Object-defined Error On An Active Cell
I have a very strange problem in DEBUG mode, because i get this error "Application-defined or object-defined error" when referring to a cell and assigning it a value so it goes to my error handler and i have a Resume Next there. It continues to go through the code whilst continuing to go to the error handler but when i step out of the function it restarts again from the beginning on the called function and then on the second run of my code it seems to WORK!?! So i'm thinking what the hell is going on, it falls over and fails the first time round and works the second time round? In free-run mode from excel i just get a #VALUE!
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
Find Method To Search For The Active And Non Active Values
I have a range of amounts in Sheet 1 from F7:Q13 and im using the find method to search for the active and non active values in the cell. Which means that if there's a value in the cell it will transfer the value in Sheet 2, if nothing is found in the cell the cells in Sheet 2 will return as nothing or null. I think the problem lies on the FindWhat variable. Im getting a compiled error which im not sure what is it. I've attached the spreadsheet so you get a better idea of the problem that i encountered.
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
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
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
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 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 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
Active Area
I have the following macro that works fine. But, I would like it to stop when it gets to the bottom of the active area. Sub Insert_for_SQs() Do Selection.EntireRow.Insert ActiveCell.Offset(6, 0).EntireRow.Activate Selection.EntireRow.Insert ActiveCell.Offset(12, 0).EntireRow.Activate Loop End Sub
View Replies!
View Related
Active Invoices
In column A I have account numbers. I press a button and the selected account number is transfered to another sheet (active invoices) and then is used as a criteria in a msquery. heres the code ....
View Replies!
View Related
Getting The Name Of The Active Button
how can i get the name of the button thats been pressed? i know it will probably involve Application.Caller but i just have no idea how to get it to work Dim BtNm As String BtNm = Application.Caller MsgBox BtNm this doesnt work but that would prob be obvios to you guys.
View Replies!
View Related
Active Window
I've built a macro to run a loop that opens excel files, prints certain sheets and closes them. I have the macro working - to some degree. here is my code (this is excel): Sub RMPProducer() OldPath = "S:RMBS_Performance_Analytics Analysis1 Staging Folder For Monthly Model Templates2007200704VVDeals" Dim t As Workbook Dim s As String Dim a As Window With Application.FileSearch .NewSearch .LookIn = OldPath .SearchSubFolders = False .Filename = "*.xls" .MatchTextExactly = True If .Execute() > 0 Then For i = 1 To .FoundFiles.Count s = .FoundFiles(i) I have 2 problems. First problem is that I am trying to add a function to make the window close once its finished printing. This actually works - but it limits my loop iterations from i = 1 to 2 (so it only runs 2 times and I'm not exactly sure why). If I don't include the statement - this can run for up to 7 - 8 times (these are very large files) then excel runs out of memory. Unfortunately, i can't set the loop to run from i = anything other than one. So What I would prefer is to be able to make the "ActiveWindow.close" command work - because this way I can run the entire loop. But I'm not sure how to. I tried window referencing, I tried adding a new active Window so when the file opens, its opening in the active window already, but nothing works. Preferably, I need to be able to reference both windows, as my next macro will involve referencing 2 windows (one static - always open) and the other the loop above.
View Replies!
View Related
Active X Controls
My excel macro's run fine under Windows 2000 Pro, and Office 2000. The company got a new computer with Windows XP. I now have macros that run OK however I keep getting a pop up message stating. Warning..An active X component is being loaded. I must press OK and the program will run fine. How can I get rid of the Pop up active X message.
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
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
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
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
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" Else MsgBox "found at pos: " & res End If End Sub but get the following error - "object required"
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
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
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) 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 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
Looping With/Through Object Collection
Im workin with a spreadsheet.. At the end of this script you see how i am putting "a" in R11C33, well I want to put the contents of my workbook C3 instead. I just dont know how to do it. After it does that I need it to move on to cell C4 and put it also in r11c33...
View Replies!
View Related
Releasing Object Variables
In a sub with object variables ( ranges, etc) not declared as Static, what is the point of setting them to Nothing prior to completion? When the subroutine terminates and the variables go out of scope, do they not implicitly release memory?
View Replies!
View Related
Object Properties, To Display Far Right
to locate/display a help file, to a far right position with 80% height and 20% width. Where do I find the property commands to do this? (with values explanations) or can someone point me in the right direction. Current working code, but displays centre... Private Sub CB_HelpMain_Click() Set Module1.HELPFILE = CreateObject("InternetExplorer.Application") Module1.HELPFILE.Navigate "c: empsample_htmlfile.html" Module1.HELPFILE.Visible = True End Sub
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
Hyperlink To Embedded Object
I'm using Excel 2003 and have a spreadsheet that I want to distribute to a handful of people around the globe. I want some cells in the page to link to various PDF files which I need to distribute with the worksheet. I can embed the PDF files no problem, but I need each link to the embedded objects to take up no more vertical space than one row of text (without an Icon) - ie I need to be able to access the object through a "hyperlink style" item. I can't figure out how to do this, as the hyperlink function does not seem to permit linking to embedded objects.
View Replies!
View Related
Insert Word Object
insert word object i'm using the following Dim objX As OLEObject Set objX = ActiveSheet.OLEObjects.Add(ClassType:="Word.Document.8", Link:=False, DisplayAsIcon:=False) objX.Border.Color = RGB(255, 255, 255) Set objX = Nothing but the objects opens up very small--i have to resize to use...(it is bothersome to the user...) ?can i have the object up with larger width and lenght dimensions..?
View Replies!
View Related
Object Name As A Variable In A With Statement
Does the object in a 'With' have to be an object or can it be a variable containing the name of an object. I want to carry out the same series of settings on a number of objects and propose to define a Sub and pass the name of the objects as a parameter. I then want to use a 'With' to set that object. i.e. as shown below - though I cant see how it would work. ' combobox objects called Staff, Maker, etc Call Setuplist("Staff") Call Setuplist("Maker") ... Private Sub Setuplist(whatlist As String) 'some complex filtering and sorting goes here ... Set rnData = ActiveSheet.Range(whatlist+"List") vaData = rnData.Value With Me+whatlist ' this is the bit I cant fathom . . End With End Sub
View Replies!
View Related
Code To Insert Object?
Is it possible to use VBA to call an existing Excel function? I want to have code that calls up the "Insert - Object" popup window in the same manner as it would if the user clicked the toolbar.
View Replies!
View Related
|
TRACKER
