Hi all,

Could you please help me with the following problem?

I am trying to add a worksheet to the active workbook and then format the first cell (A1) with a bold and somewhat larger font. For some weird reason, I can't even get the first thing to work as it should.

This is the code I've written for it:

Public Sub Add_Sheet(sName as string)

With ActiveWorkbook
.Sheets.Add after:=.Worksheets(.Worksheets.Count)
MsgBox "TEST1"

With .ActiveSheet
.Name = sName

MsgBox "TEST2"
With .Cells(1, 1).Font
.Name = "Arial"
.Size = 14
.Bold = True
End With
MsgBox "TEST3"

End With
End With

End Sub

If I put this inside a VBA Macro, it works just fine. I see three messages stating TEST1, TEST2 and TEST3. However, I need to call this subroutine from a form I've created using regular VB 2005.

So basically what I'm doing is the following. I call the form (it's a dll) from within Excel. On that form a button resides that calls back to the above subroutine in the Excel / VBA macro providing it with the required name of the worksheet.

What I c?n do, is have Excel write everything I want on the current ActiveSheet. I can also get it to return all the names of all the current worksheets. So the very 'basics' of Excel still work. However, when I try to simply add a worksheet to the active workbook (doesn't sound very advanced either...), Excel is unable to comply. When I try to add a worksheet, I simply get the following error:

Method 'Add' of object 'Sheets' failed.

The same kind of thing happens when I remove the 'adding' of the worksheet and simply try to change the font of the very first cell:

Method 'Font' of object 'Range' failed.

Writing whatever I like on the active sheet is no problem, changing the name of the sheet or getting all the names of the active sheets neither is, but heaven forbid if I try to do something naughty like adding a worksheet or changing the font of a cell...

Has anyone ever had this kind of strange behaviour and know of a way to solve it?

Thanks in advance for your time,

Richard Meijer

Hi Richard,

I suspect Excel is not seeing the Active Workbook correctly. Try returning the name with ActiveWorkbook.Name, then specifically activate it by name, and the same for the active sheet.

Not sure that this will work but its worth a try.