0

I created a vba macro a while ago for work. For me it has always run but recently others have found that my code runs into an error when creating a new sheet with a specific name. The error is "ActiveX component can't create object". 

Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets.Add(After:= _
             ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ws.Name = "Assessment Stats"
lynch1234
  • 31
  • 4
  • Which line produces the error? – riskypenguin Sep 26 '19 at 14:40
  • 3rd line ```ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))``` – lynch1234 Sep 26 '19 at 14:43
  • Have you checked if the workbook already has a sheet with that name? – Zac Sep 26 '19 at 14:43
  • 2
    `The error is "ActiveX component can't create object".` Did you recently install a new ms-office version on an existing version? – Siddharth Rout Sep 26 '19 at 14:44
  • Yeah I've checked that's not the issue. I have multiple macors with that code for different sheets and all have stopped working – lynch1234 Sep 26 '19 at 14:44
  • To my knowledge we haven't installed a new version of excel but i'll have to check with IT – lynch1234 Sep 26 '19 at 14:45
  • 2
    ok check two things for me **[1]** If a new version was installed on an existing version **[2]** If Excel(you as a user) have enough `Rights`. Try this. Ask the IT guy to right click on excel.exe and click on "run as administrator" now try you code. If you do not get the same error then it is "Rights" issue. – Siddharth Rout Sep 26 '19 at 14:47
  • Yeah the same error is coming up for IT, nothing has changed for excel. – lynch1234 Sep 26 '19 at 15:01
  • 2
    Does it error no matter what name you use? Do you have an event handler attached to the workbook_newsheet event? Either in that workbook or in an add-in? Try setting your error handling preferences to "Break in class module" and see if anything changes. – Tim Williams Sep 26 '19 at 15:05
  • [Possibly relevant](https://stackoverflow.com/questions/30430942/error-429-activex-component-cant-create-object-when-copying-cells-to-new-work) – xidgel Sep 26 '19 at 15:44
  • 1
    Break it down. Run `?ThisWorkbook.Sheets.Count` in the immediate pane (Ctrl+G), then try `ThisWorkbook.Sheets.Add` (still in the immediate pane) - does it work? Next try `Set ws = ThisWorkbook.Sheets.Add(3)`, where `3` is the hard-coded value you got from the `.Count` test. Does it work? If the workbook has event handlers for a sheet being created or activated, try `Application.EnableEvents = False` before you add the sheet, and `Application.EnableEvents = True` after -- does it still blow up? – Mathieu Guindon Sep 26 '19 at 15:44

0 Answers0