macro to save as

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mo - 29 Jun 2005 17:26 GMT

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

Reply to this Message

Bob Phillips - 29 Jun 2005 18:06 GMT

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

Signature

HTH

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

> 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
[quoted text clipped - 4 lines]
>
> Mo

Reply to this Message

Mo - 29 Jun 2005 20:09 GMT

Bob,

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

> sFilename = Format(Worksheets("Sheet1").Range("A1").Value, "yyyy-mm-dd")
> ans = MsgBox ("Save file as " & sFilename)
[quoted text clipped - 13 lines]
> >
> > Mo

Reply to this Message

Bob Phillips - 29 Jun 2005 22:54 GMT

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

Signature

HTH

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

> Bob,
>
[quoted text clipped - 18 lines]
> > >
> > > Mo

Reply to this Message

Mo - 30 Jun 2005 16:51 GMT

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

> You need to put it in a sub:-)
>
[quoted text clipped - 31 lines]
> > > >
> > > > Mo

Reply to this Message

Bob Phillips - 30 Jun 2005 17:20 GMT

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

Signature

HTH

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

> Bob,
> Thanks for your help. I will give it a try.
[quoted text clipped - 43 lines]
> > > > >
> > > > > Mo

Reply to this Message

Mo - 05 Jul 2005 16:55 GMT

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

> Application.Commandbars("Worksheet Menu Bar").Enabled = False
>
[quoted text clipped - 50 lines]
> > > > > >
> > > > > > Mo

Reply to this Message

Bob Phillips - 05 Jul 2005 23:20 GMT

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

Signature

HTH

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

> Bob,
>
[quoted text clipped - 82 lines]
> > > > > > >
> > > > > > > Mo

Reply to this Message

Mo - 06 Jul 2005 21:23 GMT

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

> Mo,
>
[quoted text clipped - 120 lines]
> > > > > > > >
> > > > > > > > Mo

Reply to this Message

Mo - 08 Jul 2005 15:43 GMT

Bob,

Can you look at this?

> Bob,
>
[quoted text clipped - 156 lines]
> > > > > > > > >
> > > > > > > > > Mo

Reply to this Message

maperalia - 25 Apr 2007 06:20 GMT

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

> Mo,
>
[quoted text clipped - 120 lines]
> > > > > > > >
> > > > > > > > Mo

Reply to this Message

Nazzy - 15 Nov 2007 13:59 GMT

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

Thanks

> Application.Commandbars("Worksheet Menu Bar").Enabled = False
>
[quoted text clipped - 50 lines]
> > > > > >
> > > > > > Mo

Reply to this Message

Dave Peterson - 15 Nov 2007 14:05 GMT

Try changing the False to True

and reexecuting that statement

> Hi, I tried this, BUT how do i get the Menu Bar back, cos now i cant get it
> back, please.
[quoted text clipped - 76 lines]
> > > > > > >
> > > > > > > Mo

Signature

Dave Peterson

Reply to this Message

Nazzy - 16 Nov 2007 08:33 GMT

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.

> Try changing the False to True
>
[quoted text clipped - 80 lines]
> > > > > > > >
> > > > > > > > Mo

Reply to this Message