Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save As
I have made a custom icon that will eventaully Save a copy of the workbook in
a specified location. Since I am so new to macro and MVB programming, I am need of some serious help. First of all I made a User Form that is a pop-up window that will display which shift the workbook should be saved as "Days" or "Nights". If the "Days" button is clicked then I want a copy of the current state of the workbook saved to "D:\2008\Days\*" If "Nights" is clicked then of course it will be in "D:\2008\Nights\*". * will be "Days Cost" or "Nights Cost" then following this will be today's current date. i.e. if I save it today then it will be saved as "Days Cost 11-23-08" or "Nights Cost 11-23-08", if I save it tomorrow it will be saved as "Days Cost 11-24-08" or "Nights Cost 11-24-08" I also don't know how to get the custom icon in my toolbar to open up the pop-up window when it's pressed. Is any of this possible? I know it's easier to simply go through the motions of using the File Menu but I have posted step-by-step instructions on how to save the file with pictures and still it continues to be messed up. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save As
Try this
Private Sub CommandButton1_Click() Const sPath As String = "D:\2008\Days\" Dim fName As String fName = "" Select Case True Case Is = CheckBox1.Value = True, CheckBox2.Value = True MsgBox "Must have either Day or Night selected. Please select one only one.", vbCritical Case Is = CheckBox1.Value = True fName = "Days cost" & " " & Format(Date, "mm-dd-yy") & ".xls" ActiveWorkbook.SaveAs Filename:=sPath & fName, FileFormat:=xlExcel8 Case Is = CheckBox2.Value = True fName = "Night cost" & " " & Format(Date, "mm-dd-yy") & ".xls" ActiveWorkbook.SaveAs Filename:=sPath & fName, FileFormat:=xlExcel8 End Select End Sub "Troy 2006" wrote: I have made a custom icon that will eventaully Save a copy of the workbook in a specified location. Since I am so new to macro and MVB programming, I am need of some serious help. First of all I made a User Form that is a pop-up window that will display which shift the workbook should be saved as "Days" or "Nights". If the "Days" button is clicked then I want a copy of the current state of the workbook saved to "D:\2008\Days\*" If "Nights" is clicked then of course it will be in "D:\2008\Nights\*". * will be "Days Cost" or "Nights Cost" then following this will be today's current date. i.e. if I save it today then it will be saved as "Days Cost 11-23-08" or "Nights Cost 11-23-08", if I save it tomorrow it will be saved as "Days Cost 11-24-08" or "Nights Cost 11-24-08" I also don't know how to get the custom icon in my toolbar to open up the pop-up window when it's pressed. Is any of this possible? I know it's easier to simply go through the motions of using the File Menu but I have posted step-by-step instructions on how to save the file with pictures and still it continues to be messed up. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save As
Where do I put this, the User Form, as a recorded Macro, a module?
"Mike" wrote: Try this Private Sub CommandButton1_Click() Const sPath As String = "D:\2008\Days\" Dim fName As String fName = "" Select Case True Case Is = CheckBox1.Value = True, CheckBox2.Value = True MsgBox "Must have either Day or Night selected. Please select one only one.", vbCritical Case Is = CheckBox1.Value = True fName = "Days cost" & " " & Format(Date, "mm-dd-yy") & ".xls" ActiveWorkbook.SaveAs Filename:=sPath & fName, FileFormat:=xlExcel8 Case Is = CheckBox2.Value = True fName = "Night cost" & " " & Format(Date, "mm-dd-yy") & ".xls" ActiveWorkbook.SaveAs Filename:=sPath & fName, FileFormat:=xlExcel8 End Select End Sub "Troy 2006" wrote: I have made a custom icon that will eventaully Save a copy of the workbook in a specified location. Since I am so new to macro and MVB programming, I am need of some serious help. First of all I made a User Form that is a pop-up window that will display which shift the workbook should be saved as "Days" or "Nights". If the "Days" button is clicked then I want a copy of the current state of the workbook saved to "D:\2008\Days\*" If "Nights" is clicked then of course it will be in "D:\2008\Nights\*". * will be "Days Cost" or "Nights Cost" then following this will be today's current date. i.e. if I save it today then it will be saved as "Days Cost 11-23-08" or "Nights Cost 11-23-08", if I save it tomorrow it will be saved as "Days Cost 11-24-08" or "Nights Cost 11-24-08" I also don't know how to get the custom icon in my toolbar to open up the pop-up window when it's pressed. Is any of this possible? I know it's easier to simply go through the motions of using the File Menu but I have posted step-by-step instructions on how to save the file with pictures and still it continues to be messed up. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save As
You will need to put it into the user form. You will need 2 checkboxes. One
named Checkbox1 the named Checkbox2. You will need One Button. "Troy 2006" wrote: Where do I put this, the User Form, as a recorded Macro, a module? "Mike" wrote: Try this Private Sub CommandButton1_Click() Const sPath As String = "D:\2008\Days\" Dim fName As String fName = "" Select Case True Case Is = CheckBox1.Value = True, CheckBox2.Value = True MsgBox "Must have either Day or Night selected. Please select one only one.", vbCritical Case Is = CheckBox1.Value = True fName = "Days cost" & " " & Format(Date, "mm-dd-yy") & ".xls" ActiveWorkbook.SaveAs Filename:=sPath & fName, FileFormat:=xlExcel8 Case Is = CheckBox2.Value = True fName = "Night cost" & " " & Format(Date, "mm-dd-yy") & ".xls" ActiveWorkbook.SaveAs Filename:=sPath & fName, FileFormat:=xlExcel8 End Select End Sub "Troy 2006" wrote: I have made a custom icon that will eventaully Save a copy of the workbook in a specified location. Since I am so new to macro and MVB programming, I am need of some serious help. First of all I made a User Form that is a pop-up window that will display which shift the workbook should be saved as "Days" or "Nights". If the "Days" button is clicked then I want a copy of the current state of the workbook saved to "D:\2008\Days\*" If "Nights" is clicked then of course it will be in "D:\2008\Nights\*". * will be "Days Cost" or "Nights Cost" then following this will be today's current date. i.e. if I save it today then it will be saved as "Days Cost 11-23-08" or "Nights Cost 11-23-08", if I save it tomorrow it will be saved as "Days Cost 11-24-08" or "Nights Cost 11-24-08" I also don't know how to get the custom icon in my toolbar to open up the pop-up window when it's pressed. Is any of this possible? I know it's easier to simply go through the motions of using the File Menu but I have posted step-by-step instructions on how to save the file with pictures and still it continues to be messed up. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save As
hi
just as an extra thought, you could put this behide your 2 check boxes so that only one can be checked at a time. in check box 1 code: Private Sub CheckBox1_Click() If CheckBox1 = True Then CheckBox2 = False End If End Sub check box 2 code: Private Sub CheckBox2_Click() If CheckBox2 = True Then CheckBox1 = False End If End Sub just a thought regards FSt1 "Mike" wrote: You will need to put it into the user form. You will need 2 checkboxes. One named Checkbox1 the named Checkbox2. You will need One Button. "Troy 2006" wrote: Where do I put this, the User Form, as a recorded Macro, a module? "Mike" wrote: Try this Private Sub CommandButton1_Click() Const sPath As String = "D:\2008\Days\" Dim fName As String fName = "" Select Case True Case Is = CheckBox1.Value = True, CheckBox2.Value = True MsgBox "Must have either Day or Night selected. Please select one only one.", vbCritical Case Is = CheckBox1.Value = True fName = "Days cost" & " " & Format(Date, "mm-dd-yy") & ".xls" ActiveWorkbook.SaveAs Filename:=sPath & fName, FileFormat:=xlExcel8 Case Is = CheckBox2.Value = True fName = "Night cost" & " " & Format(Date, "mm-dd-yy") & ".xls" ActiveWorkbook.SaveAs Filename:=sPath & fName, FileFormat:=xlExcel8 End Select End Sub "Troy 2006" wrote: I have made a custom icon that will eventaully Save a copy of the workbook in a specified location. Since I am so new to macro and MVB programming, I am need of some serious help. First of all I made a User Form that is a pop-up window that will display which shift the workbook should be saved as "Days" or "Nights". If the "Days" button is clicked then I want a copy of the current state of the workbook saved to "D:\2008\Days\*" If "Nights" is clicked then of course it will be in "D:\2008\Nights\*". * will be "Days Cost" or "Nights Cost" then following this will be today's current date. i.e. if I save it today then it will be saved as "Days Cost 11-23-08" or "Nights Cost 11-23-08", if I save it tomorrow it will be saved as "Days Cost 11-24-08" or "Nights Cost 11-24-08" I also don't know how to get the custom icon in my toolbar to open up the pop-up window when it's pressed. Is any of this possible? I know it's easier to simply go through the motions of using the File Menu but I have posted step-by-step instructions on how to save the file with pictures and still it continues to be messed up. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save As
I'm getting a compile error in
one only one.", vbCritical ActiveWorkbook.SaveAs Filename:=sPath & fName, FileFormat:=xlExcel8 "Mike" wrote: You will need to put it into the user form. You will need 2 checkboxes. One named Checkbox1 the named Checkbox2. You will need One Button. "Troy 2006" wrote: Where do I put this, the User Form, as a recorded Macro, a module? "Mike" wrote: Try this Private Sub CommandButton1_Click() Const sPath As String = "D:\2008\Days\" Dim fName As String fName = "" Select Case True Case Is = CheckBox1.Value = True, CheckBox2.Value = True MsgBox "Must have either Day or Night selected. Please select one only one.", vbCritical Case Is = CheckBox1.Value = True fName = "Days cost" & " " & Format(Date, "mm-dd-yy") & ".xls" ActiveWorkbook.SaveAs Filename:=sPath & fName, FileFormat:=xlExcel8 Case Is = CheckBox2.Value = True fName = "Night cost" & " " & Format(Date, "mm-dd-yy") & ".xls" ActiveWorkbook.SaveAs Filename:=sPath & fName, FileFormat:=xlExcel8 End Select End Sub "Troy 2006" wrote: I have made a custom icon that will eventaully Save a copy of the workbook in a specified location. Since I am so new to macro and MVB programming, I am need of some serious help. First of all I made a User Form that is a pop-up window that will display which shift the workbook should be saved as "Days" or "Nights". If the "Days" button is clicked then I want a copy of the current state of the workbook saved to "D:\2008\Days\*" If "Nights" is clicked then of course it will be in "D:\2008\Nights\*". * will be "Days Cost" or "Nights Cost" then following this will be today's current date. i.e. if I save it today then it will be saved as "Days Cost 11-23-08" or "Nights Cost 11-23-08", if I save it tomorrow it will be saved as "Days Cost 11-24-08" or "Nights Cost 11-24-08" I also don't know how to get the custom icon in my toolbar to open up the pop-up window when it's pressed. Is any of this possible? I know it's easier to simply go through the motions of using the File Menu but I have posted step-by-step instructions on how to save the file with pictures and still it continues to be messed up. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save As
one only one.", vbCritical This belongs on the same line of code as the MsgBox line that is above it. The post got line wrapped. The code starting with MsgBox and ending with vbCritical should be on a single line within VBA. Cordially, Chip Pearson Microsoft MVP Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sun, 23 Nov 2008 12:05:01 -0800, Troy 2006 wrote: I'm getting a compile error in one only one.", vbCritical ActiveWorkbook.SaveAs Filename:=sPath & fName, FileFormat:=xlExcel8 "Mike" wrote: You will need to put it into the user form. You will need 2 checkboxes. One named Checkbox1 the named Checkbox2. You will need One Button. "Troy 2006" wrote: Where do I put this, the User Form, as a recorded Macro, a module? "Mike" wrote: Try this Private Sub CommandButton1_Click() Const sPath As String = "D:\2008\Days\" Dim fName As String fName = "" Select Case True Case Is = CheckBox1.Value = True, CheckBox2.Value = True MsgBox "Must have either Day or Night selected. Please select one only one.", vbCritical Case Is = CheckBox1.Value = True fName = "Days cost" & " " & Format(Date, "mm-dd-yy") & ".xls" ActiveWorkbook.SaveAs Filename:=sPath & fName, FileFormat:=xlExcel8 Case Is = CheckBox2.Value = True fName = "Night cost" & " " & Format(Date, "mm-dd-yy") & ".xls" ActiveWorkbook.SaveAs Filename:=sPath & fName, FileFormat:=xlExcel8 End Select End Sub "Troy 2006" wrote: I have made a custom icon that will eventaully Save a copy of the workbook in a specified location. Since I am so new to macro and MVB programming, I am need of some serious help. First of all I made a User Form that is a pop-up window that will display which shift the workbook should be saved as "Days" or "Nights". If the "Days" button is clicked then I want a copy of the current state of the workbook saved to "D:\2008\Days\*" If "Nights" is clicked then of course it will be in "D:\2008\Nights\*". * will be "Days Cost" or "Nights Cost" then following this will be today's current date. i.e. if I save it today then it will be saved as "Days Cost 11-23-08" or "Nights Cost 11-23-08", if I save it tomorrow it will be saved as "Days Cost 11-24-08" or "Nights Cost 11-24-08" I also don't know how to get the custom icon in my toolbar to open up the pop-up window when it's pressed. Is any of this possible? I know it's easier to simply go through the motions of using the File Menu but I have posted step-by-step instructions on how to save the file with pictures and still it continues to be messed up. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save As
Try this
Private Sub CommandButton1_Click() Const sPath As String = "D:\2008\" Dim fName As String fName = "" Application.ScreenUpdating = False Select Case True Case Is = CheckBox1.Value = True And CheckBox2.Value = True MsgBox "Must have either Day or Night selected. Please select " _ & "one only one.", vbCritical Case Is = CheckBox1.Value = True ActiveSheet.Copy fName = "Days\Days cost" & " " & Format(Date, "mm-dd-yy") & ".xls" ActiveWorkbook.SaveAs Filename:=sPath & fName, _ FileFormat:=xlExcel8 With ActiveWorkbook .Save .Close End With Case Is = CheckBox2.Value = True ActiveSheet.Copy fName = "Nights\Night cost" & " " & Format(Date, "mm-dd-yy") & ".xls" ActiveWorkbook.SaveAs Filename:=sPath & fName, _ FileFormat:=xlExcel8 With ActiveWorkbook .Save .Close End With End Select Application.ScreenUpdating = True End Sub "Troy 2006" wrote: I'm getting a compile error in one only one.", vbCritical ActiveWorkbook.SaveAs Filename:=sPath & fName, FileFormat:=xlExcel8 "Mike" wrote: You will need to put it into the user form. You will need 2 checkboxes. One named Checkbox1 the named Checkbox2. You will need One Button. "Troy 2006" wrote: Where do I put this, the User Form, as a recorded Macro, a module? "Mike" wrote: Try this Private Sub CommandButton1_Click() Const sPath As String = "D:\2008\Days\" Dim fName As String fName = "" Select Case True Case Is = CheckBox1.Value = True, CheckBox2.Value = True MsgBox "Must have either Day or Night selected. Please select one only one.", vbCritical Case Is = CheckBox1.Value = True fName = "Days cost" & " " & Format(Date, "mm-dd-yy") & ".xls" ActiveWorkbook.SaveAs Filename:=sPath & fName, FileFormat:=xlExcel8 Case Is = CheckBox2.Value = True fName = "Night cost" & " " & Format(Date, "mm-dd-yy") & ".xls" ActiveWorkbook.SaveAs Filename:=sPath & fName, FileFormat:=xlExcel8 End Select End Sub "Troy 2006" wrote: I have made a custom icon that will eventaully Save a copy of the workbook in a specified location. Since I am so new to macro and MVB programming, I am need of some serious help. First of all I made a User Form that is a pop-up window that will display which shift the workbook should be saved as "Days" or "Nights". If the "Days" button is clicked then I want a copy of the current state of the workbook saved to "D:\2008\Days\*" If "Nights" is clicked then of course it will be in "D:\2008\Nights\*". * will be "Days Cost" or "Nights Cost" then following this will be today's current date. i.e. if I save it today then it will be saved as "Days Cost 11-23-08" or "Nights Cost 11-23-08", if I save it tomorrow it will be saved as "Days Cost 11-24-08" or "Nights Cost 11-24-08" I also don't know how to get the custom icon in my toolbar to open up the pop-up window when it's pressed. Is any of this possible? I know it's easier to simply go through the motions of using the File Menu but I have posted step-by-step instructions on how to save the file with pictures and still it continues to be messed up. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save As
Put this into Module1
Sub showForm() 'put this into Module1 UserForm1.Show End Sub 'Put these into the Thisworkbook Module. Close the workbook and reopen 'Do a right click you will a option saying save copy. This will show your form. Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Application.CommandBars("Cell").Controls("Save Copy").delete End Sub Private Sub Workbook_Open() Dim NewControl As CommandBarControl Application.OnKey "+^{C}", "Module1.showForm" On Error Resume Next Application.CommandBars("Cell").Controls("Save Copy").delete On Error GoTo 0 Set NewControl = Application.CommandBars("Cell").Controls.Add With NewControl .Caption = "Save Copy" .OnAction = "Module1.showForm" .BeginGroup = True End With End Sub "Troy 2006" wrote: I'm getting a compile error in one only one.", vbCritical ActiveWorkbook.SaveAs Filename:=sPath & fName, FileFormat:=xlExcel8 "Mike" wrote: You will need to put it into the user form. You will need 2 checkboxes. One named Checkbox1 the named Checkbox2. You will need One Button. "Troy 2006" wrote: Where do I put this, the User Form, as a recorded Macro, a module? "Mike" wrote: Try this Private Sub CommandButton1_Click() Const sPath As String = "D:\2008\Days\" Dim fName As String fName = "" Select Case True Case Is = CheckBox1.Value = True, CheckBox2.Value = True MsgBox "Must have either Day or Night selected. Please select one only one.", vbCritical Case Is = CheckBox1.Value = True fName = "Days cost" & " " & Format(Date, "mm-dd-yy") & ".xls" ActiveWorkbook.SaveAs Filename:=sPath & fName, FileFormat:=xlExcel8 Case Is = CheckBox2.Value = True fName = "Night cost" & " " & Format(Date, "mm-dd-yy") & ".xls" ActiveWorkbook.SaveAs Filename:=sPath & fName, FileFormat:=xlExcel8 End Select End Sub "Troy 2006" wrote: I have made a custom icon that will eventaully Save a copy of the workbook in a specified location. Since I am so new to macro and MVB programming, I am need of some serious help. First of all I made a User Form that is a pop-up window that will display which shift the workbook should be saved as "Days" or "Nights". If the "Days" button is clicked then I want a copy of the current state of the workbook saved to "D:\2008\Days\*" If "Nights" is clicked then of course it will be in "D:\2008\Nights\*". * will be "Days Cost" or "Nights Cost" then following this will be today's current date. i.e. if I save it today then it will be saved as "Days Cost 11-23-08" or "Nights Cost 11-23-08", if I save it tomorrow it will be saved as "Days Cost 11-24-08" or "Nights Cost 11-24-08" I also don't know how to get the custom icon in my toolbar to open up the pop-up window when it's pressed. Is any of this possible? I know it's easier to simply go through the motions of using the File Menu but I have posted step-by-step instructions on how to save the file with pictures and still it continues to be messed up. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save As
Well, I don't know what I'm doing wrong because once I do this, my errors
went away but I can click on "Days" all day and nothing happens. I'm doing something wrong but I don't know what. "Chip Pearson" wrote: one only one.", vbCritical This belongs on the same line of code as the MsgBox line that is above it. The post got line wrapped. The code starting with MsgBox and ending with vbCritical should be on a single line within VBA. Cordially, Chip Pearson Microsoft MVP Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sun, 23 Nov 2008 12:05:01 -0800, Troy 2006 wrote: I'm getting a compile error in one only one.", vbCritical ActiveWorkbook.SaveAs Filename:=sPath & fName, FileFormat:=xlExcel8 "Mike" wrote: You will need to put it into the user form. You will need 2 checkboxes. One named Checkbox1 the named Checkbox2. You will need One Button. "Troy 2006" wrote: Where do I put this, the User Form, as a recorded Macro, a module? "Mike" wrote: Try this Private Sub CommandButton1_Click() Const sPath As String = "D:\2008\Days\" Dim fName As String fName = "" Select Case True Case Is = CheckBox1.Value = True, CheckBox2.Value = True MsgBox "Must have either Day or Night selected. Please select one only one.", vbCritical Case Is = CheckBox1.Value = True fName = "Days cost" & " " & Format(Date, "mm-dd-yy") & ".xls" ActiveWorkbook.SaveAs Filename:=sPath & fName, FileFormat:=xlExcel8 Case Is = CheckBox2.Value = True fName = "Night cost" & " " & Format(Date, "mm-dd-yy") & ".xls" ActiveWorkbook.SaveAs Filename:=sPath & fName, FileFormat:=xlExcel8 End Select End Sub "Troy 2006" wrote: I have made a custom icon that will eventaully Save a copy of the workbook in a specified location. Since I am so new to macro and MVB programming, I am need of some serious help. First of all I made a User Form that is a pop-up window that will display which shift the workbook should be saved as "Days" or "Nights". If the "Days" button is clicked then I want a copy of the current state of the workbook saved to "D:\2008\Days\*" If "Nights" is clicked then of course it will be in "D:\2008\Nights\*". * will be "Days Cost" or "Nights Cost" then following this will be today's current date. i.e. if I save it today then it will be saved as "Days Cost 11-23-08" or "Nights Cost 11-23-08", if I save it tomorrow it will be saved as "Days Cost 11-24-08" or "Nights Cost 11-24-08" I also don't know how to get the custom icon in my toolbar to open up the pop-up window when it's pressed. Is any of this possible? I know it's easier to simply go through the motions of using the File Menu but I have posted step-by-step instructions on how to save the file with pictures and still it continues to be messed up. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save As
Mike,
I'm lost, I can't figure out why this won't work for me if it works for you... ??? "Mike" wrote: Put this into Module1 Sub showForm() 'put this into Module1 UserForm1.Show End Sub 'Put these into the Thisworkbook Module. Close the workbook and reopen 'Do a right click you will a option saying save copy. This will show your form. Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Application.CommandBars("Cell").Controls("Save Copy").delete End Sub Private Sub Workbook_Open() Dim NewControl As CommandBarControl Application.OnKey "+^{C}", "Module1.showForm" On Error Resume Next Application.CommandBars("Cell").Controls("Save Copy").delete On Error GoTo 0 Set NewControl = Application.CommandBars("Cell").Controls.Add With NewControl .Caption = "Save Copy" .OnAction = "Module1.showForm" .BeginGroup = True End With End Sub "Troy 2006" wrote: I'm getting a compile error in one only one.", vbCritical ActiveWorkbook.SaveAs Filename:=sPath & fName, FileFormat:=xlExcel8 "Mike" wrote: You will need to put it into the user form. You will need 2 checkboxes. One named Checkbox1 the named Checkbox2. You will need One Button. "Troy 2006" wrote: Where do I put this, the User Form, as a recorded Macro, a module? "Mike" wrote: Try this Private Sub CommandButton1_Click() Const sPath As String = "D:\2008\Days\" Dim fName As String fName = "" Select Case True Case Is = CheckBox1.Value = True, CheckBox2.Value = True MsgBox "Must have either Day or Night selected. Please select one only one.", vbCritical Case Is = CheckBox1.Value = True fName = "Days cost" & " " & Format(Date, "mm-dd-yy") & ".xls" ActiveWorkbook.SaveAs Filename:=sPath & fName, FileFormat:=xlExcel8 Case Is = CheckBox2.Value = True fName = "Night cost" & " " & Format(Date, "mm-dd-yy") & ".xls" ActiveWorkbook.SaveAs Filename:=sPath & fName, FileFormat:=xlExcel8 End Select End Sub "Troy 2006" wrote: I have made a custom icon that will eventaully Save a copy of the workbook in a specified location. Since I am so new to macro and MVB programming, I am need of some serious help. First of all I made a User Form that is a pop-up window that will display which shift the workbook should be saved as "Days" or "Nights". If the "Days" button is clicked then I want a copy of the current state of the workbook saved to "D:\2008\Days\*" If "Nights" is clicked then of course it will be in "D:\2008\Nights\*". * will be "Days Cost" or "Nights Cost" then following this will be today's current date. i.e. if I save it today then it will be saved as "Days Cost 11-23-08" or "Nights Cost 11-23-08", if I save it tomorrow it will be saved as "Days Cost 11-24-08" or "Nights Cost 11-24-08" I also don't know how to get the custom icon in my toolbar to open up the pop-up window when it's pressed. Is any of this possible? I know it's easier to simply go through the motions of using the File Menu but I have posted step-by-step instructions on how to save the file with pictures and still it continues to be messed up. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Save As
Give me your email address and I will send you a working workbook
"Troy 2006" wrote: Mike, I'm lost, I can't figure out why this won't work for me if it works for you... ??? "Mike" wrote: Put this into Module1 Sub showForm() 'put this into Module1 UserForm1.Show End Sub 'Put these into the Thisworkbook Module. Close the workbook and reopen 'Do a right click you will a option saying save copy. This will show your form. Private Sub Workbook_BeforeClose(Cancel As Boolean) On Error Resume Next Application.CommandBars("Cell").Controls("Save Copy").delete End Sub Private Sub Workbook_Open() Dim NewControl As CommandBarControl Application.OnKey "+^{C}", "Module1.showForm" On Error Resume Next Application.CommandBars("Cell").Controls("Save Copy").delete On Error GoTo 0 Set NewControl = Application.CommandBars("Cell").Controls.Add With NewControl .Caption = "Save Copy" .OnAction = "Module1.showForm" .BeginGroup = True End With End Sub "Troy 2006" wrote: I'm getting a compile error in one only one.", vbCritical ActiveWorkbook.SaveAs Filename:=sPath & fName, FileFormat:=xlExcel8 "Mike" wrote: You will need to put it into the user form. You will need 2 checkboxes. One named Checkbox1 the named Checkbox2. You will need One Button. "Troy 2006" wrote: Where do I put this, the User Form, as a recorded Macro, a module? "Mike" wrote: Try this Private Sub CommandButton1_Click() Const sPath As String = "D:\2008\Days\" Dim fName As String fName = "" Select Case True Case Is = CheckBox1.Value = True, CheckBox2.Value = True MsgBox "Must have either Day or Night selected. Please select one only one.", vbCritical Case Is = CheckBox1.Value = True fName = "Days cost" & " " & Format(Date, "mm-dd-yy") & ".xls" ActiveWorkbook.SaveAs Filename:=sPath & fName, FileFormat:=xlExcel8 Case Is = CheckBox2.Value = True fName = "Night cost" & " " & Format(Date, "mm-dd-yy") & ".xls" ActiveWorkbook.SaveAs Filename:=sPath & fName, FileFormat:=xlExcel8 End Select End Sub "Troy 2006" wrote: I have made a custom icon that will eventaully Save a copy of the workbook in a specified location. Since I am so new to macro and MVB programming, I am need of some serious help. First of all I made a User Form that is a pop-up window that will display which shift the workbook should be saved as "Days" or "Nights". If the "Days" button is clicked then I want a copy of the current state of the workbook saved to "D:\2008\Days\*" If "Nights" is clicked then of course it will be in "D:\2008\Nights\*". * will be "Days Cost" or "Nights Cost" then following this will be today's current date. i.e. if I save it today then it will be saved as "Days Cost 11-23-08" or "Nights Cost 11-23-08", if I save it tomorrow it will be saved as "Days Cost 11-24-08" or "Nights Cost 11-24-08" I also don't know how to get the custom icon in my toolbar to open up the pop-up window when it's pressed. Is any of this possible? I know it's easier to simply go through the motions of using the File Menu but I have posted step-by-step instructions on how to save the file with pictures and still it continues to be messed up. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save, save as, page setup dimmed out in unprotected excel sheet? | Excel Discussion (Misc queries) | |||
SAVE and SAVE AS options disappeared from the drop down FILE menu | Excel Discussion (Misc queries) | |||
Save As and save current numbers not the actual formulas and links | Excel Discussion (Misc queries) | |||
Command Button Save As Application.ExecuteExcel4Macro ("SAVE.AS?() | Excel Discussion (Misc queries) | |||
when i save xls file, debug script is running and canno't save fil | Excel Discussion (Misc queries) |