Button to saveas then new sheet asks Questions

[Marc]

I want to create a button to saveas the current sheet.

when the user a enters a file name it is saved, then the template file (ie
EWR blank.xls) opens and asks the user questions that will go in pre
determined cells.

example.

work order #
Forman
date
work start
work finish
EWR#

I want these questions asked when the sheet is opened.

 

[FloMM2]

Marc,
I can get you started on your project.
This is what I came up with:
On your template file, right-click on the first sheet (in my case Sheet1)
and select "view code"
Type the following "Option Explicit", then press "Enter".
In the upper part of the Visual Basic Editor window you will see "(General)"
on the left, and "(Declarations)" on the right.
Select down arrow next to "(General)" and select "Worksheet". On the
right-hand side, select down arrow and select "Activate".
This will automatically put in the empty space:
"Private Sub Worksheet_Activate()

End Sub"

Now make it look like this:
"Private Sub Worksheet_Activate()
MsgBox "Welcome to EWR blank.xls !"
Dim workordernum As String, Foreman As String
Dim Today As Date, workstart As Date, workfinish As Date
Dim EWRnum As Long

workordernum = InputBox("Work Order # ?")
Foreman = InputBox("Foreman ?")
Today = InputBox("Date (mm/dd/yy)?")
workstart = InputBox("Work start (mm/dd/yy hh:mm)?")
workfinish = InputBox("Work finish (mm/dd/yy) ?")
EWRnum = InputBox("EWR # ?")

Worksheets("Sheet1").Activate
Range("A10").Select
ActiveCell.FormulaR1C1 = workordernum

Range("B10").Select
ActiveCell.FormulaR1C1 = Foreman

Range("C10").Select
ActiveCell.FormulaR1C1 = Today
Selection.NumberFormat = "mm/dd/yy hh:mm"

Range("A13").Select
ActiveCell.FormulaR1C1 = workstart
Selection.NumberFormat = "mm/dd/yy hh:mm"

Range("B13").Select
ActiveCell.FormulaR1C1 = workfinish
Selection.NumberFormat = "mm/dd/yy hh:mm"

Range("D10").Select
ActiveCell.FormulaR1C1 = EWRnum




End Sub"
Without the first and last " marks.
You can change the Range("cell") to what ever you want them to be.

I will need more information to work on the SaveAs part of your question.
Like what name do want to call the file,"current sheet" does not work.
Can you give me an example of what to call it?
hth

 

[JLGWhiz]

This is based on what the posting described. Some liberties were taken with
the predetermined range assignment, but those can be adjusted as needed.
Also, when the file name is entered for saving, they should not include the
file extension (.xls) since that will be automatically added during the
saveas execution.

Sub workstuff()
Dim sh As Worksheet, fName As String
Set sh = Workbooks("EWR blank.xls").Sheets("Sheet1")
fName = InputBox("Enter a File Name", "FILE NAME")
Range("a2") = InputBox("Enter a Work Order Number", "WO NUMBER")
Range("b2") = InputBox("Enter the Foreman's Name", "FOREMAN NAME")
Range("c2") = InputBox("Enter the Date Performed", "DATE PERFORMED")
Range("d2") = InputBox("Enter the Time Started", "START TIME")
Range("e2") = InputBox("Enter the Time Finished", "FINISH TIME")
Range("f2") = InputBox("Enter the EWR Number", "EWR NUMBER")
ActiveWorkbook.SaveAs Filename:=fName & ".xls"
End Sub