macro to save as

[Guest]

I need to create a macro & assign it to a button. the macro needs to save
the current file based on a date that is entered in a cell. also the macro
needs to prompt the user to verify the filename(date) and then save it and
return to the current worksheet.

thanks

Mo

 

[Bob Phillips]

sFilename = Format(Worksheets("Sheet1").Range("A1").Value, "yyyy-mm-dd")
ans = MsgBox ("Save file as " & sFilename)
if ans = vbOK Then
Activeworkbook.SaveAs Filename:= sFilename
End If

should do it

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Bob,

I got an error message - "Complie Error, Invalid outside procedure"
it highlite the ("A1")

 

[Bob Phillips]

You need to put it in a sub

Sub SaveMyFile()
sFilename = Format(Worksheets("Sheet1").Range("A1").Value, "yyyy-mm-dd")
ans = MsgBox ("Save file as " & sFilename)
if ans = vbOK Then
Activeworkbook.SaveAs Filename:= sFilename
End If
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Bob,
Thanks for your help. I will give it a try.
I have one general question to ask. How or (can) do I hide the worksheet
menu bar. I tried View, Toolbars, Customize and unclick the menu bar. But
it still defaulted to show the menu bar. My intention is to not allow the
user to delete, save as, or use other menus while in the work book. The
workbook is all driven by macros and there is no need for user to use or
"play" with the menu bar.

Thanks
Mo

 

[Bob Phillips]

Application.Commandbars("Worksheet Menu Bar").Enabled = False

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Bob,

Can you look at this macro and let me know why its not working. I'm trying
to create a macro when prombted "yes", it will save file based on cell
contents prior to exiting Excel.

Thanks Mo

Sub cmdExit_Click()
Dim Response As String
Dim msg As String
Dim Style As String

msg = "Are you sure you want to Exit the application and Close Excel?"
Style = vbYesNo + vbInformation + vbDefaultButton2

Response = MsgBox(msg, Style)
If Response = vbYes Then
sFilename = Format(Worksheets("MainMenu").Range("C5").Value,
"mm-dd-yyyy")
ans = MsgBox("Save File As" & sFilename)
If ans = vbOK Then
ActiveWorkbook.SaveAs Filename:=sFilename
ActiveWorkbook.Close savechanges:=True
Application.Quit
Application.StatusBar = "Application Closing."
Else
ActiveWorkbook.Activate
End If

End Sub

 

[Bob Phillips]

Mo,

How about this?

Sub cmdExit_Click()
Dim Response As String
Dim msg As String
Dim Style As String
Dim sFilename As String
Dim ans

msg = "Are you sure you want to Exit the application and Close Excel?"
Style = vbYesNo + vbInformation + vbDefaultButton2

Response = MsgBox(msg, Style)
If Response = vbYes Then
sFilename = Format(Worksheets("MainMenu").Range("C5").Value,
"mm-dd-yyyy")
ans = MsgBox("Save File As " & sFilename)
If ans = vbOK Then
ActiveWorkbook.SaveAs Filename:=sFilename
Application.Quit
Application.StatusBar = "Application Closing."
End If
Else
ActiveWorkbook.Activate
End If

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Bob,

I revised the macro to save to a specific path/directory. Your suggestion
was very helpful. I want the macro to do one more procedure. The macro
should give the user the option (yes or no) after the message of "Save File
As ...".

Sub cmdExit_Click()
Dim Response As String
Dim msg As String
Dim Style As String
Dim sPath As String
Dim sFilename As String
Dim ans

msg = "Are you sure you want to Exit the application and Close Excel?"
Style = vbYesNo + vbInformation + vbDefaultButton2

Response = MsgBox(msg, Style)
If Response = vbYes Then
sPath = "C:\MetroWest\"
sFilename = Format(Worksheets("MainMenu").Range("C5").Value,
"mm-dd-yyyy")
ans = MsgBox("Save File As " & sFilename)
If ans = vbOK Then
ActiveWorkbook.SaveAs sPath & sFilename
ActiveWorkbook.Close savechanges:=True
Application.Exit
Application.StatusBar = "Application Closing."
End If
Else
ActiveWorkbook.Activate
End If

End Sub

 

[Guest]

Bob,

Can you look at this?

Bob,

I revised the macro to save to a specific path/directory. Your suggestion
was very helpful. I want the macro to do one more procedure. The macro
should give the user the option (yes or no) after the message of "Save File
As ...".

Sub cmdExit_Click()
Dim Response As String
Dim msg As String
Dim Style As String
Dim sPath As String
Dim sFilename As String
Dim ans

msg = "Are you sure you want to Exit the application and Close Excel?"
Style = vbYesNo + vbInformation + vbDefaultButton2

Response = MsgBox(msg, Style)
If Response = vbYes Then
sPath = "C:\MetroWest\"
sFilename = Format(Worksheets("MainMenu").Range("C5").Value,
"mm-dd-yyyy")
ans = MsgBox("Save File As " & sFilename)
If ans = vbOK Then
ActiveWorkbook.SaveAs sPath & sFilename
ActiveWorkbook.Close savechanges:=True
Application.Exit
Application.StatusBar = "Application Closing."
End If
Else
ActiveWorkbook.Activate
End If

End Sub

 

[Guest]

Bob;
I wonder if you can help me with this problem.

I have an excel file "Products Weight.xls" that is located on the share
directory ("S drive"). This file has been used to load weight of the
different products found on the market so is basically a database.

The problem is that we have ten people doing this reseach and loading the
data they found and most of the time the ten people are loading the data at
the same time. Therefore, just one is able to load the data and save the
file. Meanwhile, the rest could not save because they have gotten this file
as a read only.

I wonder if there is any way to have a macro to allow the ten people to save
the file eventhougth it is open at the same time for all of them.
Could you please help me with this matter.

Thanks in advance.
Maperalia

 

[Guest]

Hi, I tried this, BUT how do i get the Menu Bar back, cos now i cant get it
back, please.

Thanks

 

[Dave Peterson]

Try changing the False to True

and reexecuting that statement

 

[Guest]

But i cant do that cos when im in Excel i cant see the menu bar with
Tools-Macros. So how do I get to edit the macro...cos i cant see the Tools
menu to get into the macro.