I copied a routine from this forum that would email a single sheet from a 10-sheet workbook to a recipient. This worked great for a long time, then all of a sudden I started getting the "ActiveX control can't create object" error. Here is my
View 14 RepliesI have an excel application which works extremely well in one single instancec. It has a bunch of ActiveX control objects on the worksheet. Generally I call them by convention Sheet1.Cmd.show.
I have since experimented with having multiple instances of excel running on the machine. I start to notice something very strange. The first instance I open and run all the activeX buttons are all clickable. As soon as I open a second instance of an excel program all the activeX buttons on the worksheet are not even clickable. They are enabled by my program, but not clickable as if they were not there at all.
I also try open a normal excel workbook then followed by my excel program in a second instance. Even though the first excel file does not utilize any activeX objects all my buttons on the subsequent instance are not clickable.
The weird thing is all my activeX control objects on a userforms are still clickable and call to all my planned events in my program. It is just all the activeX controls on my worksheet that do not have any reaction at all.
I always think when one opens a new instance it will be totally indpendent and would even occupy another CPU if my machine has more in it. If they are truly independent then I am at a loss to understand why my activeX controls would fail to work at all.
I am trying to generate excel report output from access by click of a button. Part of the code is as follows:
Dim lngColumn As Long
Dim xlx As Object, xlw As Object, xlc As Object
Dim DBS As DAO.Database
Dim rst As DAO.Recordset
Dim blnEXCEL As Boolean, blnHeaderRow As Boolean
blnEXCEL = False
'If we do not want the first row we set Header to false
blnHeaderRow = False
'Establish an EXCEL application object
On Error Resume Next
Set xlx = GetObject(, "Excel.Application")
If Err.Number <> 0 Then
Set xlx = CreateObject("Excel.Application")
blnEXCEL = True
End If
Err.Clear
I worked on development on a server and worked fine. However when I have moved the code and database to the desktop of mine I am having
the following error in the line
Set xlx = GetObject(, "Excel.Application")
Run-time error '429'
ActiveX component can't create object
Following bit of code runs fine if placed in Word VBA:
Code:
Public Sub TaskUsageInExcel()
Dim tsk As Task
For Each tsk In Tasks 'Gives RTE 429 when run through Excel
Debug.Print tsk.Name
Next tsk
End Sub
So I referenced 'Microsoft Word 12.0 Object Library' in Excel and ran the code and it gave me this error 429. Noticeable part was, no instance of word was running at that time.
So I modified the code as below:
Code:
Public Sub TaskUsageInExcel2()
Dim wdApp As Word.Application
Dim tsk As Task
[Code]....
VBScript code snippet below is being used in an ASP page but the call to GetObject never attaches to a running instance of Excel. Since visible property is set to True, I can see the instances accumulate in the TaskBar each time the ASP page is called, so there are obviously instances to attach to.
Dim excelApp
Set excelApp = Server.GetObject(,"Excel.Application")
If (err <> 0) Then
Set excelApp = Server.CreateObject("Excel.Application")
End If
excelApp.UserControl = True
excelApp.Visible = True
excelApp = Nothing
GetObject errors out with the following error: Error - Number:429 Source:Microsoft VBScript runtime error Description:ActiveX component can't create object
I have some VBA code that in one portion of it an Outlook e-mail is created and the active workbook is attached and sent. This code used to work on Excel & Outlook 2003, but now in Excel & Outlook 2007 I'm getting the error:
Run-time error '429':
ActiveX component can't create object
and is stopping on the line
Set OutApp = GetObject(, "Outlook.Application")
FY If I comment out the Set OutApp... and If OutApp.... lines and uncomment the Set OutApp create object line, I still get the same error.
Dim OutApp As Object
Dim OutMail As Object
Dim SigString As String
Dim Signature As String
'Set OutApp = CreateObject("Outlook.Application")
Set OutApp = GetObject(, "Outlook.Application")
If OutApp Is Nothing Then Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)
I am trying to create a workbook where the first worksheet has a list of Checkboxes and each checkbox 'unhides' or 'hides' subsequent worksheets within the workbook, depending on whether the box is checked. I can make the checkbox, I can record the macro.
Sub Test_checkbox()
' Test_checkbox Macro
Sheets("Dist").Visible = True
End Sub
How do I link them together? Ideally, I'd like to have my boss be able to 'play' with which sheets are visible (check, uncheck, check, uncheck, etc) .
What the max length of an ActivX Control name would be. I mean the name that you would use in coding something, not the actual caption name of the button. IN this case I am using a radio button.
Based on that I would like to create random IDs to assign to the button. I know how to do this in the cell formula but not in code.
I have an ActiveX Label control with a transparent background. When I click on the label the background turns white and stays that way as long as the mouse is over the label, obscuring whatever's underneath. (It also very slightly alters the character spacing.) What object/property controls this? How do I adjust it?
While I'm here, is there an easy programmatic way to assign arbitrary RGB colors to ActiveX controls? The palettes available from the Properties window are fairly limited. A google search for "ActiveX colors" yields a deluge of marketed products, so it's hard to find useful information.
Version: excel 2003
OS: Win XP
Currently using VBA do develop a tool for Excel. I've downloaded a .ocx control and am wondering how one would go about adding it to the toolbox?
Since Form Controls comboboxes don't allow font editing, I need to switch to ActiveX...
The combo box values come from the following cell range:
tbl_arrays!$I$3:$I$6
Those values are, from top to bottom: "select distance", "50 Miles", "100 Miles", "150 Miles".
The following macro is also assigned to the combobox:
[Code].....
I have two issues. The first is that the macro doesn't seem to be working. It is supposed to filter a list of cities within 50, 100 and 150 miles of a user input zip or city. The original Forms Control combo works but not ActiveX after I put the code in the change event:
[Code] .........
The second issue is that the last clicked value in the ActiveX comboBox replaces the top one.
Ex: from top to bottom, the combobox should display:
[Code] ........
but if the user clicks "100 Miles", the combobox is then going to display
[Code] ......
If the user then select "50 Miles", the box displays:
[Code] ........
I have created several macros where i use an active-X control button. This button is pressed and the macro begins...........but every now and again this button changes size for no apparent reason.
I have read countless articles/posts about what "may" be causing the problem but i have never read anything which clearly identifies the soln to this problem and the problem has been out there for years. To make matters worse the problem can not be duplicated on any regular frequency that i am aware of............so its not like i can post a file that will show the problem.............
I am using the following code to copy a template to a new worksheet each month:
Code:
Sub CopyTemplateNewMonth()
Application.ScreenUpdating = False
Dim SourceRange As Range, DestRange As Range
[Code]....
Everything copies over except an Activex Control button. I also have form control buttons with macros assigned to them, and they copy & paste just fine. The code assigned to the ActiveX button also copy's & paste's just fine...but the button itself does not.
RE: Macro ActiveX Click Control--It stops when on another sheet
Macro runs OK when run from menu.
An activeX click button was put on sheet B.
Right click the control, View Code, Paste in code from macro, then click button.
It runs the part of the macro that applies to sheet B, goes to previous sheet A, and then stops. The range select line for the named range on sheet A is highlighted in yellow. It stops when selecting any range or cell on sheet A, like B2.
Any idea why this code does not work on sheet A when ActiveX button is on sheet B? Is there someplace else that the control can go? Using Excel 2000.
See part of code below, which starts on sheet B:
' Macro recorded 11/22/2007
Range("SortAll").Select
Selection.Sort Key1:=Range("T2"), Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveSheet.Previous.Select
Range("FormBase").Select (STOPS HERE)
Selection.Copy
Range("Formul").Select
ActiveSheet.Paste
ActiveSheet.Next.Select
FormBase is a named dynamic range
FormBase=OFFSET(A!$A$2,0,0,1,MATCH(1E+306,A!$2:$2))
After programmatically creating a new activex checkbox, I want to create a sub for it's click event. I have given the object a name, and fortunately the code for it's click event is a one-line call of another sub, but how?
The sub would look like the following except be named after the new control (obviously), and yes, it is just the number in the name that changes:
[Code] .........
I have a form that collects four pieces of information from the user and places each into columns A, B, C, D on a sheet. I would like to automatically add an option button to column E so that a user could select it and then click a control button to delete the corresponding row.
Is this possible or is there maybe a better way to do this in Excel 2007.
Can Active-X control button macro be placed in a regular module, or does it always have to reside in the sheet module?
Or is the only way to accomplish this is to have the macro in the regular module being called from the button macro in the sheet module?
I somehow seem to remember a way to move/assign an Active-X ControlButton macro to a regular module, but maybe I'm wrong?
I have a worksheet that contains a number of ActiveX image controls, which have been scaled to a uniform height to serve as thumbnails.
I have also created a class, where each instance is initialized with one of these image controls, and events (specifically the on click event) are captured. When the user clicks the picture, it opens a userform containg the same picture. However, I'd like that picture to be at its original size, not the scaled-down size.
how can I determine the original dimensions of a picture used in an image control? I can tell that the original resolution is there (by changing the PictureSizeMode to clip), but I can't find any properties that show me how big that image actually is, only ones that return or set the size of the object itself.
I've several check boxes on a worksheet. Some of the check boxes control the values of other through the _click event. However, when I try to change the value the event attached to the control in questions fires.
application.enableevents = False
would prevent events being fire from within the macro but this only seems to work when I step through the code.
I understand that I could set a global variable to do this and will probably go down this route anyway - but I am interested to know how/when I should be using the EnableEvent method (as I am sure this should be the way that I use it)
In A1 there is an ActiveX Control Text Box inserted, now in A1 cell which is beneath this text box contain the formula that if A9 is blank than "Customer Name" should appear else it should show blank. But the problem is when I write some thing in this text box which is connected with A9, formula works as "Customer Name" disappears but when I delete in text box it simply acts as if something is still present in cell A9 and formula does not comeback to "Customer Name" again & therefore in order to appear "Customer Name" I have to go to A9 and press delete button to get the desirous result from the formula.
Readers are requested to make this formula work becz the situation in which this formula has been invented is well fitted and not other formula can supplant it.
I created a macro to create command buttons on sheet 1 of my worksheet. The created buttons using oleobject and class type: forms.commandbutton1, creates an object with no properties on the sheet (i.e. if you right click on it excel thinks it's an object and not a control.) Is there a way to create it as a control?
View 4 Replies View RelatedI'm trying to use the Calendar Control 10.0 object in one of my worksheets. I basically want to be able to display a value by clicking on one of the dates in the calendar. Example: Say I want the contents of cell A1 to be displayed in B1 when somebody clicks on the 17th of February 2006. Is there any way to do this?
View 5 Replies View RelatedIs there an Event Handler that runs a macro whenever any control object in a worksheet has its state changed or whenever any checkbox is checked/unchecked. I want to have a method handle this outside of the checkbox since the number of checkboxes are dynamic and should not have to be individually coded by the user.
View 5 Replies View RelatedSplit from Determine Order Of Event Procedures
I realise that there's no set order for different types of event, because, as you say, it basically depends on what the user does. But what I'd like to control is what order like events fire in.
So let's say that object_1 handles the aplApp_WorkbookNewSheet event (an application-wide event).
Let's say that object_2 is an object of the same type as object_1, and therefore also handles the aplApp_WorkbookNewSheet event.
The objects are entirely independent; they know nothing about each other. However, I would like to be able to control whether the aplApp_WorkbookNewSheet event is fired first in object_1 or first in object_2 when the user triggers this event by adding a new worksheet somewhere.
My workbook simply has multiple columns of data which will be summarized visually in a tag cloud (I have the tag cloud part taken care of already creating either a jpg or png, I simply have to change the extension within a batch file to change between picture formats)
I need to have the tag cloud image display and update depending on which column the user would like to summarize (for now I have simplified the issue by only having one of the data columns in my workbook, and will worry about the user selection of the data column to be summarized later).
My Main Question:
Focusing on the image display and updating now, whether it be an ActiveX Image Control or any other way. I've been playing around manually before I try and code it and I can not get either a jpg or png file to attach to the ActiveX control (both of which cause an Invalid Picture Message).
The file name can stay the same if need be and for now I think/assume that'd be easiest (I have my Tag cloud generator just replacing the previously existing image file of the same name).
Would ActiveX control be the way to go for this dynamically changing image?
I'm creating a custom function to cut down on redundant code. However, in converting the code to a function so that it may be used in different instances, I'm getting an unexpected error when the code executes.
The error is:
Code:
Object doesn't support this property or method.
Here is my function:
Code:
Public Function chkClick(checkboxName As String, tabName As String, chartName As String, seriesNumber As Long)
Sheets(tabName).ChartObjects(chartName).Activate
If Sheets(tabName).checkboxName.Value = True Then
ActiveChart.FullSeriesCollection(seriesNumber).Select
[code]....
The function checks the status of the checkbox name passed to it. If it's checked, it will show a chart series. If it's not checked, it will hide the chart series. The error comes in referencing the "checkboxName".
How might I need to alter the code so that I can use the form control name as a variable? This is ultimately one of the key components of the function as each control has it's own name.
I've used trend lines to find the mean and average but for some reason I have not been able to set an upper and lower limit. without introducing a new column.
View 6 Replies View RelatedI 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
I'm trying to open a file and perform various tasks. This is the structure:
Dim xlApp As Object
Dim xlWb As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWb = xlApp.Workbooks.Open(BackupFile) ' backupfile evaluates to c: esting est.xls
xlWb.Worksheets(1).Activate
Debug.Print xlWb.Worksheets.Count
Dim ws As Worksheet
For Each ws In xlWb.Worksheets
Debug.Print ws.Name
ws.Activate
Next ws
I think I'm totally missing something. The above works, except for the loop. None of the names show up in my immediate window, and none of the worksheets are activated. xlWB evaluates to nothing, which is where the problem has to be...but why would my first .Activate and Debug.Print lines work? Also, If I declare xlWb as Workbook then absolutely nothing works after the file is opened.
Basically what I want to do is open the file, do a bunch of a stuff with it, then save and close it. I have this all working without creating another object, but the opening of the file is still visible to the user. Seems like turning off ScreenUpdating doesn't truly work.
I want to create a macro to open a webpage, and with that webpage fill out the fields on the webpage and then hit enter and move to next record. I am having great difficulties with establishing Internet Explorer as my object and maneuvering around inside of it. My data would being in column A so i would expect to use the string below when referencing my data.
Do Until Workbooks(2).Worksheets(1). Range("A" & row) = ""