Save As

[Troy 2006]

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.

 

[Mike]

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]

Where do I put this, the User Form, as a recorded Macro, a module?

 

[Mike]

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.

 

[FSt1]

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

 

[Troy 2006]

I'm getting a compile error in

one only one.", vbCritical

ActiveWorkbook.SaveAs Filename:=sPath & fName,
FileFormat:=xlExcel8

 

[Chip Pearson]

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)

 

[Mike]

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

 

[Mike]

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]

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.

 

[Troy 2006]

Mike,
I'm lost, I can't figure out why this won't work for me if it works for you...
???

 

[Mike]

Give me your email address and I will send you a working workbook

 

[Troy 2006]

(e-mail address removed) after 7 pm PST
(e-mail address removed) before 7 pm PST