Excel Sheet Tabs

[villy]

I have been working with MS Excel for several years but started coding (VBA) for just a couple of months and still looking for some ideas..

I know somebody have the answer for my question, here it goes...
Is there a code in vba that I can tell excel how many number of sheet tabs should be in new workbook? In Tools/Options/General I know you can change it manually but I need it in code, why? Because I have a program that reads only when only two sheets exist in a new workbook otherwise it gives an error. I want the users (other than me) be able to use my program without error when they use it in their own computer.

Thanks for those who will share their ideas...
God bless

___________________________________________________
"For human, it is impossible but for God nothing is impossible."

 

[Jerry Sullivan]

Hi Villy,

Is it an option to fix the program that throws an error if you don't have exactly two sheets in your workbook? If you have access to that code, that would seem better.

VBA could be used to limit a workbook to two sheets; but it isn't clear how you would implement this.
You indicated that you know how to setup Excel to generate New Workbooks with 2 sheets but want a VBA solution for other users.

How will you get the VBA code to these users? If the user has a workbook with more than 2 sheets- how will your code know which sheets to delete and do you really want to do that?

You probably have a good rationale for doing this. I'd be glad to help with some code if you can explain a bit more how you want it to work.

 

[villy]

Hi JS411,
I would like to thank you first for your interesting response.
Basically, in the workbook itself I made the program(vba code) that run "Personal.xls" to be specific as I know it is common to all who have MS Excel and it is opening automatically hidden.
What I mean is that I will give them a copy of this (xls) file with the code and created a custom toolbar which they will use.
If they will open the file more than two sheets there is no problem but the problem is when they will open a new workbook with more than 2 sheets then the error comes.
What I need to know is how can I insert a code that will automatically set a new workbook to have only 2 sheets.

Hope I explained it well. Thanks in advance God bless.

________________________________________________
"Life is a gift to be enjoyed not a problem to be solved."

 

[villy]

JS411,
Just to clearly answer your question, I don't want the user to encounter any error because they don't have access to vba codes I created.
Since the error occurs in new workbook it should delete any sheets other than sheet1 and sheet2 specifically.

Do you think I can delete directly other sheets upon opening of the new workbook?What would be the effect for other workbooks to be opened?
And, how can I know that the workbook to be open is new or not?

Thanks again..

________________________
"Curiosity develops wisdom."

 

[Jerry Sullivan]

I don't want the user to encounter any error because they don't have access to vba codes I created.

Will you explain that? If they don't have access to the vba codes you created then where would the error come from?

Also, you didn't explain why you don't just fix the code to not have an unhandled error. The Board can help you with that.

It sounds like you are able to control the user's environment. You are providing them each with a Personal.xlsb file. It would seem simpler to
just set each user's Excel > Options to use 2 sheets for new workbooks.

What version(s) of Excel are being used by your group?

 

[Jerry Sullivan]

Villy,

I'm not convinced this is the best course for you; but I need wrap up for the day
and don't want to leave you empty-handed.

Here is some code that you can try if you decide to go this route.

It provides some checking to see if a worksheet is blank before deleting it,
but there is always some risk using this kind of code.

Copy this into the ThisWorkbook module of the Personal.xlsb file then save and close Excel.
Upon reopening, the code should run automatically each time a new workbook is created.

Option Explicit
 
Public WithEvents App As Application
 
Private Sub Workbook_Open()
     Set App = Application
End Sub
 
Private Sub App_NewWorkbook(ByVal wb As Workbook)
    Dim i As Long
    If wb.Worksheets.Count > 2 Then
        Application.DisplayAlerts = False
        For i = wb.Worksheets.Count To 3 Step -1
            With wb.Worksheets(i)
                If Application.CountA(.Cells) = 0 Then
                    .Delete
                Else
                    MsgBox "Sheet " & .Name & _
                    " is not blank and will not be deleted."
                End If
            End With
        Next i
        Application.DisplayAlerts = True
    End If
End Sub

Good luck!

 

[villy]

I will make it simple: If anyone can tell me how to set the new workbook to limit to 2 sheets only that would be great and where to put the code(in which part?). What I mean is that I dont have access for all the users computer to change it manually supposed I will be leaving the office and have it handover something like that..
Thanks

 

[villy]

This works fine for me thanks.. It is great indeed..
_____________________________________________

I will look for other things to be discovered...

 

[villy]

How can i say this is SOLVED?
thanks

 

[Andrew Poulsom]

Did you try recording a macro while setting the number of sheets in a new workbook?

Application.SheetsInNewWorkbook = 2

If you are going to change this property make sure that you set it back to what it was when done.