-
Jul 8th, 2018, 11:35 AM
#1
Thread Starter
New Member
Excel Macro (ActivX component cant create object) Error
Dear all,
I am working with Macros on Excel and i had a file that's programmed and working fine with Macro.
Today all of the suddent it gave me a new message when am trying to run the Macro on it
This new message is the following " ActivX component cant create object "
Am going crazy as the file was working fine... Also, i tried to copy all the programming to another excel file and started it from scratch and it worked !! that what made me got crazier.
The file stops at the first line now.
Sub StartUpdate()
Please help.
Thank you.
-
Jul 8th, 2018, 04:07 PM
#2
Re: Excel Macro (ActivX component cant create object) Error
The file stops at the first line now.
irrelevant, post the entire procedure
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jul 9th, 2018, 11:00 AM
#3
Thread Starter
New Member
Re: Excel Macro (ActivX component cant create object) Error
Originally Posted by westconn1
irrelevant, post the entire procedure
Sub StartUpdate()
Dim UpdateSheet
UpdateSheet = Range("F2").Value
Sheets(UpdateSheet).Select
End Sub
Sub update()
Dim sourceSheet As Worksheet
Set sourceSheet = ActiveSheet
Dim Lastrow1 As Long
Dim Lastrow2 As Long
Dim CopyValue1
Dim CopyValue2
Dim CopyValue3
Dim CopyValue4
Dim CopyValue5
Dim OccuranceDate
OccuranceDate = ActiveSheet.Name & Range("H1").Value & Range("I1").Value
Sheets("Action Tracker").Select
With ActiveSheet
Lastrow2 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
Lastrow2 = Lastrow2 + 1
Call sourceSheet.Activate
With ActiveSheet
Lastrow1 = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
For I = 11 To Lastrow1
CopyValue1 = Range("B" & I).Value
CopyValue2 = Range("D" & I).Value
CopyValue3 = Range("H" & I).Value
CopyValue4 = Range("I" & I).Value
CopyValue5 = Range("A" & I).Value
Sheets("Action Tracker").Select
Range("A" & Lastrow2).Value = CopyValue1
Range("B" & Lastrow2).Value = OccuranceDate
Range("C" & Lastrow2).Value = CopyValue2
Range("D" & Lastrow2).Value = CopyValue3
Range("E" & Lastrow2).Value = CopyValue4
Range("F" & Lastrow2).Value = "Pending"
Range("G" & Lastrow2).Value = CopyValue5
Lastrow2 = Lastrow2 + 1
Call sourceSheet.Activate
Next I
Sheets("Action Tracker").Select
End Sub
Sub EscalationUpdate()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim Lastrow As Long
Dim Lastrow1 As Long
Dim Update1
Dim Update2
Dim Update3
Dim counter
Sheets("Action Tracker").Select
Workbooks.Open ("C:\Users\Briefing \ Director DDS.xlsm")
Sheets("Action Tracker").Select
With ActiveSheet
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
For I = 4 To Lastrow
counter = 0
Workbooks("Director DDS.xlsm").Activate
Sheets("Action Tracker").Select
If Range("J" & I).Value = "JACC" Then
If Range("G" & I).Value = "Yes" Then
Update1 = Range("A" & I).Value
Update2 = Range("H" & I).Value
Workbooks("JACC DDS.xlsm").Activate
Sheets("Action Tracker").Select
With ActiveSheet
Lastrow1 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
For j = 4 To Lastrow1
If Range("A" & j).Value = Update1 Then
If Not Range("H" & j).Value = Update2 Then
Range("H" & j).Select
Selection.Value = Update2
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Else
Range("H" & j).Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
End If
Next j
End If
End If
Next I
For I = 4 To Lastrow
Workbooks("Director DDS.xlsm").Activate
Sheets("Action Tracker").Select
With ActiveSheet
Lastrow1 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
If Range("F" & I).Value = "Done" Or Range("F" & I).Value = "done" Then
Update1 = Range("A" & I).Value
Workbooks("JACC DDS.xlsm").Activate
Sheets("Action Tracker").Select
With ActiveSheet
Lastrow1 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
For j = 4 To Lastrow1
If Range("A" & j).Value = Update1 Then
Range("F" & j).Value = "Done"
End If
Next j
End If
Next I
Workbooks("Director DDS.xlsm").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close
Workbooks.Open ("C:\Users\Briefing Director DDS.xlsm")
Sheets("Action Tracker").Select
With ActiveSheet
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
For I = 4 To Lastrow
counter = 0
Workbooks("Director DDS.xlsm").Activate
Sheets("Action Tracker").Select
If Range("J" & I).Value = "JACC" Then
If Range("G" & I).Value = "Yes" Then
Update1 = Range("A" & I).Value
Update2 = Range("H" & I).Value
Workbooks("JACC DDS.xlsm").Activate
Sheets("Action Tracker").Select
With ActiveSheet
Lastrow1 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
For j = 4 To Lastrow1
If Range("A" & j).Value = Update1 Then
If Not Range("H" & j).Value = Update2 Then
Range("H" & j).Select
Selection.Value = Update2
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Else
Range("H" & j).Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End If
End If
Next j
End If
End If
Next I
For I = 4 To Lastrow
Workbooks("Director DDS.xlsm").Activate
Sheets("Action Tracker").Select
With ActiveSheet
Lastrow1 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
If Range("F" & I).Value = "Done" Or Range("F" & I).Value = "done" Then
Update1 = Range("A" & I).Value
Workbooks("JACC DDS.xlsm").Activate
Sheets("Action Tracker").Select
With ActiveSheet
Lastrow1 = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
For j = 4 To Lastrow1
If Range("A" & j).Value = Update1 Then
Range("F" & j).Value = "Done"
End If
Next j
End If
Next I
Workbooks("Director DDS.xlsm").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub
-
Jul 9th, 2018, 11:01 AM
#4
Thread Starter
New Member
Re: Excel Macro (ActivX component cant create object) Error
Originally Posted by westconn1
irrelevant, post the entire procedure
There you go !
-
Jul 9th, 2018, 12:17 PM
#5
Re: Excel Macro (ActivX component cant create object) Error
Can you zip and attach the actual workbook, if the data isn't too "sensitive?"
-
Jul 9th, 2018, 04:33 PM
#6
Re: Excel Macro (ActivX component cant create object) Error
you should avoid working with the selection object or active any thing, use full qualified ranges, workbook and sheet objects
some example
Code:
with Workbooks("Director DDS.xlsm")
.Save
.Close
end with
OR
Code:
set wbdds = Workbooks("Director DDS.xlsm")
wbdds.close true
when working with multiple worksheets, it is better to set sheet objects at the start, then work with them throughout the procedure
having said all that, i do not see any thing in sub startupdate to cause your error, so posting a sample workbook would have to be the best option
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jul 10th, 2018, 11:27 AM
#7
Thread Starter
New Member
Re: Excel Macro (ActivX component cant create object) Error
Originally Posted by vbfbryce
Can you zip and attach the actual workbook, if the data isn't too "sensitive?"
Sorry i cant its too sensetive.
-
Jul 10th, 2018, 11:27 AM
#8
Thread Starter
New Member
Re: Excel Macro (ActivX component cant create object) Error
Originally Posted by westconn1
you should avoid working with the selection object or active any thing, use full qualified ranges, workbook and sheet objects
some example
Code:
with Workbooks("Director DDS.xlsm")
.Save
.Close
end with
OR
Code:
set wbdds = Workbooks("Director DDS.xlsm")
wbdds.close true
when working with multiple worksheets, it is better to set sheet objects at the start, then work with them throughout the procedure
having said all that, i do not see any thing in sub startupdate to cause your error, so posting a sample workbook would have to be the best option
I didnt quite understand why it is better to set sheet objects at the start... what is the use of it ?
-
Jul 10th, 2018, 12:54 PM
#9
Re: Excel Macro (ActivX component cant create object) Error
The use of it is that you then know you're always working on the sheet you intend to be working on. Sometimes the "active sheet" isn't what you think it is.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|