File/New in Excel

[SDecou]

When I click on the menu item File/New or the toolbar button File/New I would like the New Dialog box to open instead of going to the Task Pane and then having to select "on my computer". Is there a way to do this?

 

[Frank Kabel]

Hi
AFAIK this is one of the wonderful new Excel 2003 features
No way around it (like the new *improved* help function)

 

[Dave Peterson]

Maybe you could use a macro to display that old dialog.

You could incorporate this into an existing workbook or create a new workbook:

Option Explicit
Sub auto_open()
Dim myCtrl As CommandBarControl
Call DeleteMyNew
With Application.CommandBars("worksheet menu bar").Controls("file")
Set myCtrl = .Controls.Add(Type:=msoControlButton, before:=1, _
temporary:=True)
With myCtrl
.OnAction = ThisWorkbook.Name & "!ShowMyNewDialog"
.Caption = "MyNew"
End With
End With
End Sub
Sub ShowMyNewDialog()
Application.Dialogs(xlDialogWorkbookNew).Show
End Sub
Sub auto_close()
Call DeleteMyNew
End Sub
Sub DeleteMyNew()
With Application.CommandBars("worksheet menu bar").Controls("file")
On Error Resume Next
.Controls("MyNew").Delete
On Error GoTo 0
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm


Short course:
Start a new workbook
Hit alt-f11 to get to the VBE (where macros/UDF's live)
hit ctrl-R to view the project explorer
Find your workbook
should look like: VBAProject (book1.xls)

right click on the project name
Insert, then Module
You should see the code window pop up on the right hand side

Paste the code in there.

Now back to excel and save this as an addin in your XLStart folder.

Microsoft Addin will show up at the bottom of the "save as type" dropdown in the
File|SaveAs dialog.

And you can find your XLStart folder by going back to the VBE (alt-F11).
hit ctrl-g to see the immediate window
and type this and hit enter:

?application.StartupPath

I get this using win98 and xl2002:
C:\WINDOWS\Application Data\Microsoft\Excel\XLSTART

 

[Gord Dibben]

There are two "New" items available for the File>New menu.

One has the ellipse after it New... which brings up the Task Pane.

The other has no ellipse New

This one inserts a new workbook without intervention.

Gord Dibben Excel MVP

 

[Curt]

stumped or can't see:
Know it must be possible. Trying to save a copy of workbook to a folder
named by user. then return to workbook and complete operation of updateing
workbook "dataentry" for next year. Any help appreciated.
Thanks Much
[email protected]

 

[Dave Peterson]

Take a look at .savecopyas in VBA's help

If that's not what you mean, you may want to enhance your description.

 

[Curt]

I finally found .savecopyas what a search when words won't come. Now I've got
to try to get it to save a copyas using inpitbox from user. Then return to
the active sheet and complete the other code I've got for it to do.First
question I put to user with msgbosx is have you made a bachup. no then do
what I just mentioned if yes it goes on and does its thing.
Thanks for the responce. I am just learning how to use vbe and it sure is
fun. A old dog can learn new tricks. It is supriseing a lot of my friends.
Any way Thanks for your input From what I see you are sharp in writing code.
It takes me a while but sooner or later.
Thanks Again
Curt

 

[Dave Peterson]

Instead of using an inputbox to get the filename, you can use
application.getsaveasfilename.

Maybe this will help. (Remember, .savecopyas doesn't save the original--it just
saves a copy as it exists at that moment.)

Option Explicit
Sub testme()

Dim BackupMade As Long
Dim myFileName As Variant

BackupMade = MsgBox(Prompt:="Have you made a backup?", Buttons:=vbYesNo)

If BackupMade = vbYes Then
Exit Sub '???
End If

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

If myFileName = False Then
Exit Sub 'user hit cancel
End If

On Error Resume Next
ActiveWorkbook.SaveCopyAs Filename:=myFileName
If Err.Number <> 0 Then
MsgBox "Workbook wasn't saved!"
myFileName = ""
Err.Clear
End If
On Error GoTo 0

End Sub

 

[Curt]

Want to save a copy as it now is. When useing getsave as it removes active
workbook that I wish to up date. I do want want a copy as a backup record.
The active workbook has all the formulas to do what is needed. Also I have
been able to get it toupdat as needed. Only problem is getting a copy for
record. If I get a copy of active when it upates it takes care of other 3
worksheet that need ot be printed. Do I make it clear as mud?
If I read right all that is needed is to change 'GetSaveAs" to 'SaveCopyAs"
Hope I am right.
Thanks much
Curt

 

[Curt]

used the code you sent worked great and saves leaves workbook to update only
doesn't update will copy code that is not copying e17 to d6 as befor. Any
Ideas nothing has been changed. Thanks again hopefull Curt
ActiveSheet.Unprotect
Range("D6").Select
Selection.Locked = False
Selection.FormulaHidden = False
Range("E17").Select
Selection.copy
Range("D6").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlAdd, SkipBlanks:=False _
, Transpose:=False
Application.CutCopyMode = False
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("A2").Select

Want to save a copy as it now is. When useing getsave as it removes active
workbook that I wish to up date. I do want want a copy as a backup record.
The active workbook has all the formulas to do what is needed. Also I have
been able to get it toupdat as needed. Only problem is getting a copy for
record. If I get a copy of active when it upates it takes care of other 3
worksheet that need ot be printed. Do I make it clear as mud?
If I read right all that is needed is to change 'GetSaveAs" to 'SaveCopyAs"
Hope I am right.
Thanks much
Curt

 

[Dave Peterson]

If you want to save the file with its existing name and make a copy...

Option Explicit
Sub testme()

Dim BackupMade As Long
Dim myFileName As Variant

BackupMade = MsgBox(Prompt:="Have you made a backup?", Buttons:=vbYesNo)

If BackupMade = vbYes Then
Exit Sub '???
End If

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

If myFileName = False Then
msgbox "Both File and Backup Were Not Saved!"
Exit Sub 'user hit cancel
End If

Activeworkbook.Save 'original workbook

On Error Resume Next
ActiveWorkbook.SaveCopyAs Filename:=myFileName
If Err.Number <> 0 Then
MsgBox "Workbook wasn't saved!"
myFileName = ""
Err.Clear
End If
On Error GoTo 0

End Sub

Want to save a copy as it now is. When useing getsave as it removes active
workbook that I wish to up date. I do want want a copy as a backup record.
The active workbook has all the formulas to do what is needed. Also I have
been able to get it toupdat as needed. Only problem is getting a copy for
record. If I get a copy of active when it upates it takes care of other 3
worksheet that need ot be printed. Do I make it clear as mud?
If I read right all that is needed is to change 'GetSaveAs" to 'SaveCopyAs"
Hope I am right.
Thanks much
Curt

 

[Dave Peterson]

I'm not quite sure how this fits in the other code, but you can remove all those
..selects and use something like:

with ActiveSheet
.Unprotect
with .Range("D6")
.Locked = False
.FormulaHidden = False
end with
.range("e17").copy
with .range("D6")
.pastespecial Paste:=xlAll, Operation:=xlAdd, _
SkipBlanks:=False, Transpose:=False
.locked = true
.formulahidden=false
end with
.protect DrawingObjects:=True, Contents:=True, Scenarios:=True
.Range("A2").Select
end with

Do you just pick this up and paste it in the other code?

If you do, you may want to change:

with Activesheet
to
with worksheets("yoursheetnamehere")

It might make it a little more difficult to screw up your workbook.

 

[Curt]

When you complete the data entry sheet and update it moves the values in diff
clmns to location to be used to compute new values. This is an ongoing meter
reading
Thanks so much for help

 

[Dave Peterson]

Hope it's working well for you.

When you complete the data entry sheet and update it moves the values in diff
clmns to location to be used to compute new values. This is an ongoing meter
reading
Thanks so much for help