Results 1 to 9 of 9

Thread: Excel Macro (ActivX component cant create object) Error

  1. #1

    Thread Starter
    New Member
    Join Date
    Jul 2018
    Posts
    6

    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.

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  3. #3

    Thread Starter
    New Member
    Join Date
    Jul 2018
    Posts
    6

    Re: Excel Macro (ActivX component cant create object) Error

    Quote Originally Posted by westconn1 View Post
    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

  4. #4

    Thread Starter
    New Member
    Join Date
    Jul 2018
    Posts
    6

    Re: Excel Macro (ActivX component cant create object) Error

    Quote Originally Posted by westconn1 View Post
    irrelevant, post the entire procedure
    There you go !

  5. #5
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Excel Macro (ActivX component cant create object) Error

    Can you zip and attach the actual workbook, if the data isn't too "sensitive?"

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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

  7. #7

    Thread Starter
    New Member
    Join Date
    Jul 2018
    Posts
    6

    Re: Excel Macro (ActivX component cant create object) Error

    Quote Originally Posted by vbfbryce View Post
    Can you zip and attach the actual workbook, if the data isn't too "sensitive?"
    Sorry i cant its too sensetive.

  8. #8

    Thread Starter
    New Member
    Join Date
    Jul 2018
    Posts
    6

    Re: Excel Macro (ActivX component cant create object) Error

    Quote Originally Posted by westconn1 View Post
    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 ?

  9. #9
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    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
  •  



Click Here to Expand Forum to Full Width