When I try to open an Excel-file from my employer with Excel UserForms I recieve following error messages
Code:
Component not correctly registered
and
Code:
Unexpected error (336)
I can see the code behind the UserForm, but not the UserForm. I've made the same mistake myself last time I created an UserForm, but I don't know what to do different.
I have built a UserForm (Excel 2003) that looks up a Row dependant on Criteria and displays the values on the User Form.
View 9 Replies View RelatedI was given a task to build a userform and all the info collected from the userform will then transfer to a worksheet named 'Promotion'. I was copying the code from last year's UserForm and with my 0 VBA background.
With the below code (I just extract part of it), the ideal case would be to transfer data to Cell A4 (which is the first blank cell on the worksheet), and when a second form is recored, it will start from A5, so on and so forth. But somehow, the code transfer the data to Cell A1001, and the columns don't match with the info either.
Private Sub cmdAddPromo_Click()
Dim RowCount As Long
Dim r As Long
Const LastRow = 2000
[Code]...
The other thing is I don't really know why there is 'Me.' before each common buttons...
how to check whether an ocx is already registered when template is opened in excel?
this is to register an ocx control used in the xlt.
I have a hyperlink to a PDF file, that when clicked gives me the usual "Some files may contain a virus..." warning, but then gives me a "No program is registered to open this file." message...
I have the latest Adobe Reader installed, and the file opens without problems from any other location (just not from Excel)...
Other hyperlinked file types open fine from within the same worksheet.
PDF is listed in Control Panel > Folder Options > File Types, and is associated to Adobe Reader...
I keep getting this error message: Error 429: ActiveX Component Cannot Create Object
I fond a site that explained the problem: "If Microsoft Excel 97 is installed after an application created with Microsoft Visual Basic 5.0 that uses DAO is installed, Excel does not add the DAO design-time licensing key. This licensing key is required by Excel, but is not required by applications created with Visual Basic."
But I can not figure out how to fix it. I changed the registry and added the licensing key, but I still got the error.
I have been deleting some VBComponents by using the .remove command and they get tagged for deletion but do not actually get deleted until after my VBA sub ends and control is returned to excel.
I would like to save the changes before control is returned to the user. I'm not really sure why the deletion is being held up until execution ends, it even waits until all calling functions have also finished their execution.
I often come across a situation where I know a given total, and I know it comes from numbers within a given list, but I do not know which particular numbers they are. The onyl way to find out is to add every single combination of all the numbers in the list. I want to know if there is any formula within Excel which would help me to do that, given that the numbers of combinations are:
Number of items in list Number of combinations
1 1
2 3
3 7
4 15
5 31
6 63
7 127
8 255
9 511
10 1023
Obviously, I would like to be able to do this for any number of items.
I have this nested if statement, but my last IF statement needs to incorporate another component.
View 2 Replies View RelatedI have a control sheet that needs to fetch numbers from a different worksheets depending on the worksheet name that I type into a cell. So if I type "sheet1" into cell A1 then I want the formulas on the sheet to automatically change fetch the data from sheet1. Then if I type in "sheet2" for example, the formula must then look at sheet2 for example. The source sheets are all identical in layout so not bothered about the actual cell ranges. I can do this in VBA but would like to keep this simple in Excel formulas.
View 2 Replies View RelatedI 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]....
Is it possible to insert a formula or have a calculation performed for the range component when using the countif statement.
I am trying to take the entries in the rows in column B and divide its corresponding row in column A and then count how many meet a certain criteria.
I have a small problem with a list of event codes in the form XX###, (Xs are letters, # numbers), I need to be able to find the largest numerical component in the list, and am having problems finiding a neat way of doing it.
I could do it by using the RIGHT command to get the numerical bit in a seperate column, then convert it to number, then use LARGEST, but this seems somewhat cumbersome!
I am trying to create a life-cycle management sheet that is simple for the user but gives specific data.
The user would input "Date", "Machine type", "Component Set No.", and "Component". This would happen over a period of time and build up a picture of how often each component was lasting and it's average life in days.
I am fine from the user input side but am struggling to figure out how to extract ONLY the data I need to display in a graph.
For example: If I want to see the average no. of days "component X" is lasting before being replaced I've not been able to extract just that data. I have tried using a pivot chart but this won't do exactly what I'm after.
See the attached for a clear explanation : Component LifeCycle Example.xlsx
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 keep getting this error on this line
[code]ThisWorkbook.VBProject.VBComponents("Module2").Export FileName[CODE]
of this
I have a very large inventory spreadsheet (30,000+ lines) where finished goods are listed on one line and the raw materials (parts) that make up that finished good are listed below. After the last raw material for a finished good is listed, the next finished good is on the next line (and so on). First, I need to sort the lines to eliminate certain common raw materials. Next I need to take the lowest raw material inventory level and have that as the default inventory level for the finished good.
View 2 Replies View RelatedI am trying to compare multiple column in a worksheet to find
common component in all the columns and what is unique to a particular
column only. And list the results/finding in adj column. What i am
trying to accomplish is something as below.
BEFORE
Sheet1 Sheet2 Sheet3
Column2 Column2 Column2
02-1234-12 07-1234-12 02-1234-12
04-1234-12 03-1234-12 02-1234-12
05-1234-12 02-1234-12 06-1234-34
AFTER
Common to all Unique to sheet1 Unique to Sheet2
02-1234-12 05-1234-12 07-1234-12
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)
how to use some form of the saveas dialog within excel without quotes appearing in the filename that is passed to the dialog.
For example:
ChDir ("J:TestFolder")
x = Application.GetSaveAsFilename("Test.xls", "Excel files (*.xl*),*.xl*", 1, "Custom Dialog Title")
The above code displays the saveas dialog form but has "Test.xls" surrounded by quotes. Normally, there would be no quotes around the specified filename.
Is there another way I can do this or will I just have to live with it?
I have two lists of components, a component from List A is added to one from List B and a total in £'s needs to be shown, simple enough I hear you say HOWEVER the LENGTH of component B is variable.
For example
Input part # ABC in A1 and £50 is shown as a total in D1, then input part # 123 in B1 and the length of 100 in C1 and the total changes to £100. Then if you change the figure to 200 the total changes to £150
I should i make this formula
A customer is eligible for a discount if the customer’s 2009 sales are greater than or equal to 200000 OR if the customers First Order was placed in 2012. " If its true it would be yes and if false no"
So far i have in the IF function
=IF([@[2009 sales]]>= 200000 ,"Y","N")
How should i insert if the customers OR First Order was placed in 2012?
I have used the Excel program for a number of years to print out "Jukebox Title Strips" for my Jukebox. I have used the same "formula" for cell height, width, etc. during that time.
The outline of the title strips are preprinted and perforated on an 8.5" x 11" sheet. I recently tried to make some new strips and the printing does not line up correctly as it always had done in the past. Now, the titles are not high enough on the top portion of the sheet, and not low enough on the bottom portion of the sheet. So, it is not that I could just raise everything up or lower everything down to line up correctly... it now has to be changed in both directions. Of the 20 title strips on the sheet, the only ones that line up correctly are the two that are halfway down.
I thought that somehow the file had become corrupted, so I made a new template, but I still have the same problem.
Are there any suggestions of what I could do?
In the links below it shows an example of a Title Strip page.
I have three columns expressing death dates (date, month, year). The months and dates are finally sorting correctly within the same year, but I have it set to sort by year first.
The column starts off fine with 1860, goes up to 2013, then starts back at 1884 and goes back up to 2009. I can't find any difference between the two groups: there are some of the same years represented, there are blank month and date cells in both. I have a worksheet with birth dates that sorted fine; the only difference I can find is that birth dates only go up to 1997, but the column is formatted as General, so the turn of the century shouldn't be a problem.
I've tried highlighting the whole thing before sorting as well as selecting one cell as usual. I've also tried formatting the column as Text. I can't think of anything else.
I have a userform with several inputs which are validated, and should the input be invalid an ErrorProcedure is run. This basically displays a message telling the user what is wrong, and it should also be highlighting the text in the relevant field, but for some reason it is not doing that.
View 6 Replies View RelatedLinking two comboboxes. Actually My department consists of 4 divisions and each division contains 3 sections, that is beside Department manager staff and planning group.
In the attached workbook:
- Combobox1 contains All divisions, Department manager staff and planning group)
- Combobox2 contains all sections of the selected division in Combobox1.
What I want to do is the following:
In Combox1, I want to hide all records not related to the selected division.
In Combox1, I want to hide all records not related to the selected section.
Remarks:
I depend for filtering on the org. code for each division and section.
Fifth and Sixth digits of the first Division's Org. Code are 21
Fifth and Sixth digits of the second Division's Org. Code are 22
Fifth and Sixth digits of the third Division's Org. Code are 23
Fifth and Sixth digits of the third Division's Org. Code are 24
First section's org. code for each division ends with 11
Second Section's org. code for each division ends with 21
Third Section's org. code for each division ends with 31
I have created the following VBA codes to reach the said purposes, the first three ptions in Combobox1 work just fine, but the other options sometimes work fine and other times don't!
Moreover, Combobox 2 is not working at all:
Code:
Private Sub ComboBox1_Change()
On Error Resume Next
Application.ScreenUpdating = False
Select Case ComboBox1
Case Is = [AC4].Value
[Code] ........
I have a column with a value resulting from a fairly complex formula:
I will not include the formula, but basically what it does is a number of calculations including subtracting dates, that is
TODAY'S DATE - A GIVEN DATE (provided in a different cell)
That means that, either manually or automatically, those values are constantly recalculating (with every second that goes by, that value changes).
And the number of values/cells to update is not few: about 1100.
Here's the problem.
When I press SORT, Excel seems to be absolutely incapable of sorting that list correctly. The values are completely and randomly out of order.
I wonder if it could be due to the fact that given that those values would be constantly changing, there would be a conflict with the commands
sort
update
I'm trying to simply reference a cell from another tab in the same workbook. In other words I press "=" and then point to the cell in another tab and I would expect to see that value - however I don't. What I see, as an example, is: ='Nav&SPDiary'!B24 instead of the cell value (which I know exists because I've just pointed to it.
Anyone have ANY idea why all of a sudden it's started to do this? The most annoying thing is it worked fine before and now it doesn't do what I want...
I've got a text box with some commentary in the middle of a spreadsheet. The text looks fine on screen, but when I print, it chops the end off some of the words. Is there a way round this?
View 9 Replies View Related