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.