VBA Sheets.Add disabled

[Willem]

Hi,

We recently upgraded from Office 2003 to Office 2007. In some macro's errors
start occurring, e.g. the Excel macro that adds an extra sheet to a workbook,
and always worked flawless, now stops running.
Also i cannot add a worksheet manually; I can see the tab but it is disabled.
The workbook consists of several (protected) worksheets.
The macro is very simple:
Sub MakeSheet()
' Application.ScreenUpdating = False
Sheets.Add
ActiveSheet.Name = "exporteren data"
End Sub

What can be the problem?

 

[Jacob Skaria]

Try specifying the Workbook name

Workbooks("Book1").Sheets.Add
Workbooks("Book1").ActiveSheet.Name = "exporteren data"

If this post helps click Yes

 

[Willem]

Thanks for your prompt reaction, Jacob.
It won't work, because the file I.m talking about is a template (xltm) and
the users name it themselves.
I have another workbook using the same macro's, and this does not give this
problem. In the other wb i can add sheets, and in thois one it is disavbled
but i don't see how.

 

[muddan madhu]

May be the workbook is protected.

Go to Revew tab | protect workbook | any tick is there for protect
structure and windows | then its protected.

If its not protected then, check the sheet named "exporteren data" is
already exist.

Sub makesheet()
Sheets.add.name = "exporteren data"
end sub

 

[Willem]

yes, thanks. I feel so silly.......
the box for style protectiuon was ticked.
it works like it always did......

thanks again

 

[Jacob Skaria]

Cheers. Good to hear that...

If this post helps click Yes