[RESOLVED] Please help a VBA newbe

[ROB IN RWC]

This is what I need.
1. When the workbook is opened for the very first time, I need it to prompt (see existing macro) for the year that the information is being entered.
2. This entry needs to be added to the end of all existing worksheets, i.e. Jan06, Feb06, ...... NOTE: This should only happen the first time it is opened!
3. On closing, it nees to prompt for a "saveas" name so the original does not get overwritten.

Thanks in advance.
-robert My email

p.s. In trying to learn, it seems as though a bunch of crap was attached to the VBA section. How and what do I clean up?

[Webtest]

Robert:

I don't have zip capability here ... do all your sheets have default starting names like "Jan", "Feb", etc.? Then you append the Year name on them? Are there any sheets other than the "Jan" - "Dec" sheets?

If the 'template' is set up right, on opening the workbook you can just test the sheet names for a sheet with the Date suffix already installed. If this is the case, you just continue with the open Workbook. If the suffixes are NOT installed, then you pop up your user input box for entering the year, and then write the suffix to the appropriate sheets. That satisfies points 1 and 2.

What are the rules for your "SaveAs" in point 3?

[Santos7772005]

To get a new save name for your worksheet you could write something like this:

(where excelApp is an excel.application object)

fName = excelApp.GetSaveAsFilename(InitialFilename:=fileName, FileFilter:="Excel Files (*.xls), *.xls")

then you can save your workbook like this (where excelWB is excel.workbook object)

excelWB.SaveAs fileName:=fName
        excelWB.Close savechanges:=True

[ROB IN RWC]

How would I write the code for the testing of the tabs to see if the date information is present. Basically, how would I write the entire code?

[Webtest]

Incidently Rob, Welcome to the Forum! We hope your journey here is productive and pleasant!

Hey ... please answer my question first!

do all your sheets have default starting names like "Jan", "Feb", etc.? Then you append the Year name on them? Are there any sheets other than the "Jan" - "Dec" sheets?

Can't write any code without knowing the details!

I've been thinking about the process of creating a new book ... you need to do that too! I don't know how you have planned to do it, but if you have a "Template" book, it will never have the year embedded in the Sheet Names, and so you can always pop up the User Input prompt for the processing year. Then you can copy the appropriate template sheets into a new Workbook, and then rename the sheets with the correct year. This way, once a working book is created, it will have the correct sheet names, but it will not have the macro in it to worry about. Do youi have any other macros that will need to be included in the new book?

These are the types of things that need to be laid out first before you start banging on code. I certainly wouldn't write any code without these answers. That doesn't mean that the code won't change as you learn more about the application ... hey, hacking is fun! But you need to build on a solid foundation.

[ROB IN RWC]

Check this out and it may answer your questions. My Webpage Again, I would like to use this as the template. When it is first opened, it would pull up a msg box asking for the year (format yy) then put that yy at the end of every tab.

When closing, it would open a msg box asking for a SaveAs name, then save it accordingly.

When it was reopened, it would not ask either of these again.

I don't know the code for this and any help would be great.

Thanks
-Rob

[ROB IN RWC]

OK, again.

I have an excel spreadsheet (.xls)
The tabs at the bottom of the screen are Jan, Feb, etc... through Dec.
I want to keep this original sheet as the template that is used year after year. I don't want it to be overwritten; so I need a fail safe.

When the sheet is FIRST OPENED I would like it to prompt for a year in a msg box in the format of yy.
I would like the answer to that question to rename the sheets, i.e. Jan06, Feb06, etc... through Dec06

Once the user inputs their data, UPON CLOSE, I would like a msg box to pop up asking for a Save Name. That would save the sheet with the new name so as not to overwrite the original.

I only want these msg boxes to appear the first time the spreadsheet is accessed. NOT on the second opening of the renamed sheet.

Is that possible? If so, what is the code?

Thanks.

[DKenny]

Once the user inputs their data, UPON CLOSE, I would like a msg box to pop up asking for a Save Name. That would save the sheet with the new name so as not to overwrite the original.

If you save the template as a .xlt file then Excel will automatically prompt the user for a save name. That's the easiest way to handle that piece.

Here'e the code to change the sheet name, appending the year number. You will need to copy this code into the code page for the "Thisworkbook" object.

Private Sub Workbook_Open()
Dim sYearNum As String
Dim wksWorkSheet As Worksheet
    
    'First we check to see if the name of the first sheet
    'has been changed.
    If ThisWorkbook.Worksheets(1).Name = "Jan" Then
        
        'If it hasn't, then prompt the user for the yearnum
        'Continue to prompt until the year number is numeric
        'and 2 characters long
        Do While (Not IsNumeric(sYearNum) Or Len(sYearNum) <> 2)
            sYearNum = InputBox("Please enter the Year Number (yy)")
        Loop
        
        'loop through each sheet in the book
        'appending the year num to the sheet name
        For Each wksWorkSheet In ThisWorkbook.Worksheets
            wksWorkSheet.Name = wksWorkSheet.Name & sYearNum
        Next wksWorkSheet
    End If
    
End Sub

[Webtest]

So you want the Month Sheets in the master book to be used as templates. The user selects ONE sheet for entering data. You want to spin off that one sheet with a year code appended to the sheet name and save it as a new book. Correct?

If this is correct, there will be no macro in the new book, and so it will never ask about changing the sheet name again. When the new book is reopened, you will just get the sheet with the year appended to the name.

[ROB IN RWC]

Hey Kenny,
It worked for changing the sheet names perfectly. Thank You.
The part about the save as (using .xlt) isn't exactly what I was looking for.
I am looking to have a msg box (just like the yy box) that will ask for a "SaveAs" name (required). The person who is going to use this sheet will invaribly overwrite the original by not knowing that they need to change the file name. I want it to be "any monkey could do it EASY"
With the xlt it prompts with the question, "do you want to save" and that save could still overwrite the original.
I hope that makes sense.
-Rob

[DKenny]

he person who is going to use this sheet will invaribly overwrite the original by not knowing that they need to change the file name.

If the original is saved as a template file(.xlt extension) ,e.g. RobReport.xlt, then when the user opens a copy it will automatically be named RobReport1, if they open another it will be called RobReport2, etc.
When they try to save, the file will get a .xls extension and they will be prompted to give a location.
All of this make it fairly difficult for JoeUser to overwrite your template...

[ROB IN RWC]

This is from another post and similar to what I am looking for.....

I've got the following code to save a workbook with a user specific name and
it checks to see if the file already exists and displays a message to inform
the user, if the filename does not exist then it saves the workbook.


All seems to work apart from the fact that if you click on 'cancel' on the
save as alert box then the workbook still gets saved as 'false.xls'. I know
there must be a bug in my code but unfortunately this is my first
spreadsheet using VBA and I need some assistance.


If anyone can point me in the right direction (ignore the astrLinks).


Sub SaveSummarySheet()


Dim astrLinks As Variant
Dim stSaveName As String


Application.DisplayAlerts = False
ActiveWorkbook.Activate


stSaveName = Application.GetSaveAsFilename( _
fileFilter:="Excel Files (*.xls), *.xls")


If FileExists(stSaveName) Then
MsgBox stSaveName & " Already Exists", Buttons:=vbOKOnly +
vbInformation, Title:="File Already Exists"
SaveSummarySheet
Else
ActiveWorkbook.SaveAs Filename:=stSaveName,
FileFormat:=xlWorkbookNormal, ReadOnlyRecommended:=True
astrLinks =
ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
MsgBox "File Saved As: " & stSaveName, Buttons:=vbOKOnly +
vbInformation, Title:="File Saved"
End If


Sheets("Summary").Select
Application.DisplayAlerts = True


End Sub


Function FileExists(stFile As String) As Boolean
If Dir(stFile) <> "" Then FileExists = True
End Function

[ROB IN RWC]

Basically, when the user clicks on the close button, a msg box would pop up asking for the saveas name. That is the only was to close the document.