View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
FloMM2 FloMM2 is offline
external usenet poster
 
Posts: 207
Default Button to saveas then new sheet asks Questions

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


"Marc" wrote:

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.