VBA: SaveAs

[pujo]

Morning all,
For some reason, I am having trouble with saving a file using VBA.

All I need to happen is the save as dialog appear with the location being the same location as the open excel file.
The user will input a file name however the location will be the same as the working file.

Appreciate any assistance.

Thanks!

PuJo

 

[pujo]

I am using this vba however I cannot get it to default to .xlsm
And iseas?

Sub Save_As()
With Application.FileDialog(msoFileDialogSaveAs)
  .AllowMultiSelect = False
  .InitialFileName = ThisWorkbook.FullName
  If .Show = -1 Then .Execute
End With
End Sub

 

[pujo]

So it looks like I am thinking about this all wrong....

I'm stuck...

So, here is the scenario:

We are using excel for purchase orders, when a new PO is made, the user will have to select "SaveAs" and change the file name to the next number, if the user selects "Save" the previous PO will get written over.

That is what we do not want to happen and it happens every day so I am trying to come up with something that will not let the previous PO get overwritten but at the same time, if the PO needs to be edited, it can be saved.

I am out of ideas...got any?

Thanks,
PuJo

 

[Fluff]

Try

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    If Not SaveAsUI Then
        MsgBox "Sorry - You must use Save As"
        Cancel = True
    End If

End Sub

This needs to go in the ThisWorkbook module

 

[pujo]

Fluff,
Thanks for the reply.

Using this method would not enable a user to edit a previously created PO.
There must be away that I can prompt the user rather than disable the save function and I tried a few, but cant figure out how to continue with a save
after the user selects yes.

answer = MsgBox("Are you sure you want to write over the existing PO?", vbYesNo + vbQuestion, "Requisition")
If answer = vbYes Then
        'how to continue to save the workbook
    Else
        'Save As
    End If

Thanks so much for the reply.
PuJo

 

[mse330]

pujo, since you have asked for ideas why don't you have a button with the below macro so if anyone needs to create a new PO all they need to do is click on the button & it will create a copy of the same worksheet in the same workbook which they can edit.

Sub NewPO()

ActiveSheet.Copy Before:=ActiveSheet

End Sub

If you can manage to make the end users work easier, they'll defiantly use the button. For example, the code will clear all the data that they need to delete/override for the new PO or have the new PO number automatically generated with today's date

Sub NewPO()

ActiveSheet.Copy Before:=ActiveSheet

With ActiveSheet
    .Range("B7:B12").ClearContents ' change the range as per your PO layout
    .Range("B5").Value = Date      ' change the cell location to the appropriate cell date
End With

End Sub

 

[Fluff]

There must be away that I can prompt the user rather than disable the save function and I tried a few, but cant figure out how to continue with a save
after the user selects yes.

How about

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    If Not SaveAsUI Then
        If MsgBox("Are you sure you want to save this file?", vbYesNo) = vbNo Then
            Cancel = True
        End If
    End If

End Sub

 

[pujo]

pujo, since you have asked for ideas why don't you have a button with the below macro so if anyone needs to create a new PO all they need to do is click on the button & it will create a copy of the same worksheet in the same workbook which they can edit.

Great idea, since I am already using a macro to clear the worksheet I can just make a copy.

Thanks for the idea!

PuJo

 

[pujo]

How about

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    
    If Not SaveAsUI Then
        If MsgBox("Are you sure you want to save this file?", vbYesNo) = vbNo Then
            Cancel = True
        End If
    End If

End Sub

Fluff,
Thanks for the code, this does the job.

Cheers!
-PuJo

 

[Fluff]

Glad we could help & thanks for the feedback