Home |
Search |
Today's Posts |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
File/New in Excel
Take a look at .savecopyas in VBA's help
If that's not what you mean, you may want to enhance your description. Curt wrote: 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 "Dave Peterson" wrote: 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, befo=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 SDecou wrote: 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? -- Dave Peterson -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
File/New in Excel
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" wrote: Take a look at .savecopyas in VBA's help If that's not what you mean, you may want to enhance your description. Curt wrote: 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 "Dave Peterson" wrote: 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, befo=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 SDecou wrote: 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? -- Dave Peterson -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
File/New in Excel
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 wrote: 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" wrote: Take a look at .savecopyas in VBA's help If that's not what you mean, you may want to enhance your description. Curt wrote: 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 "Dave Peterson" wrote: 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, befo=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 SDecou wrote: 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? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
File/New in Excel
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" wrote: 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 wrote: 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" wrote: Take a look at .savecopyas in VBA's help If that's not what you mean, you may want to enhance your description. Curt wrote: 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 "Dave Peterson" wrote: 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, befo=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 SDecou wrote: 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? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
File/New in Excel
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 "Curt" wrote: 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" wrote: 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 wrote: 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" wrote: Take a look at .savecopyas in VBA's help If that's not what you mean, you may want to enhance your description. Curt wrote: 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 "Dave Peterson" wrote: 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, befo=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 SDecou wrote: 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? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
File/New in Excel
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 Curt wrote: 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" wrote: 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 wrote: 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" wrote: Take a look at .savecopyas in VBA's help If that's not what you mean, you may want to enhance your description. Curt wrote: 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 "Dave Peterson" wrote: 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, befo=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 SDecou wrote: 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? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
File/New in Excel
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 wrote: 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 "Curt" wrote: 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" wrote: 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 wrote: 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" wrote: Take a look at .savecopyas in VBA's help If that's not what you mean, you may want to enhance your description. Curt wrote: 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 "Dave Peterson" wrote: 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, befo=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 SDecou wrote: 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? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
File/New in Excel
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" wrote: 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 wrote: 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 "Curt" wrote: 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" wrote: 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 wrote: 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" wrote: Take a look at .savecopyas in VBA's help If that's not what you mean, you may want to enhance your description. Curt wrote: 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 "Dave Peterson" wrote: 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, befo=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 SDecou wrote: 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? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
File/New in Excel
Hope it's working well for you.
Curt wrote: 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" wrote: 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 wrote: 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 "Curt" wrote: 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" wrote: 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 wrote: 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" wrote: Take a look at .savecopyas in VBA's help If that's not what you mean, you may want to enhance your description. Curt wrote: 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 "Dave Peterson" wrote: 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, befo=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 SDecou wrote: 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? -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Group" function very slow with Excel 2003 :( ... While very quick with Excel2000 :O) | Excel Discussion (Misc queries) | |||
Stop Excel Rounding Dates | Excel Discussion (Misc queries) | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
Excel error - Startup (and Acrobat PDFMaker) | Setting up and Configuration of Excel | |||
Excel 2002 and 2000 co-install. Control Which Starts ? | Excel Discussion (Misc queries) |