Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button to saveas then new sheet asks Questions
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Button to saveas then new sheet asks Questions
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 "Marc" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
avoid message that asks for confirm delete sheet | Excel Programming | |||
Macro button that asks for password before proceeding? | Excel Programming | |||
thanks 2 everybody here who asks questions & answers them! | Excel Programming | |||
How check for No/Cancel Button when SaveAs and file already exists? | Excel Programming | |||
Spin button questions | Excel Programming |