Msgbox Saving file

[Zahhhaaaa]

Hello again, could you someone give me a help with this?

I'm trying to create macro-code that when user selects commandbutton "save" on worksheet (that is linked to cell E2), msgbox will pop-up asking "save file as__E2-value__", "yes/no".

Save-button is linked to E2, where data changes daily and file is saved as data-name. One day cell E2-value can be "blaah", so file is saved as blaah, second day cell value can be "blaahblaah", so file is saved blaahblaah...etc.

Here's code I'm using;

 Sub Save()
If MsgBox("Saves file as ", vbYesNo, "Save As") = vbYes Then
ActiveWorkbook.Save
Filename = Range("E2")
ActiveWorkbook.SaveAs Filename:=Filename
End If
End Sub

´


For now, it does save the file, but when msgbox appears, filename doesn't appear on it.

 

[Sektor]

filename doesn't appear on it.

Pardon?

 

[Ruddles]

Is this what you're trying to do?

Sub Save()
  If MsgBox("Saves file as " [B][COLOR=red]& Range("E2").Value[/COLOR][COLOR=red] & "?"[/COLOR][/B], vbYesNo, "Save As") = vbYes Then
    ActiveWorkbook.Save
    Filename = Range("E2")
    ActiveWorkbook.SaveAs Filename:=Filename
  End If
End Sub

 

[Zahhhaaaa]

That was exactly what I ment, thank you!

 

[Zahhhaaaa]

Ruddles, I realized that code DOES work like I wanted, but it also overwrites default file.

I open my workbook named "Aaa", I do my stuff with this workbook and then I save file as name appearing on value "e2", let's say "Bbb", file is saved as "Bbb".

When I open my workbook "Aaa" again to do my stuff differently, it has all the changes I did when I was saving "Bbb", and I have to clear all the cells manually before I can start.

Is it possible that it won't overwrite previous file?

 

[Ruddles]

So what do you want it to do instead? Not save at all? Prompt for a different name?

 

[Zahhhaaaa]

I want it to save file with name based on e2-value, but I don't want it to overwrite default file.

Like this,

"A-file" is default, I open it, I write "fileB" on E2-value, I do some changes, write something on it and then I press "save" (commandbutton which includes this macro-code). File is saved as "fileB", but I don't want that it overwrites "A-file".

I was using this

Sub Save()
    Filename = Range("E2")
    ActiveWorkbook.SaveAs Filename:=Filename
End Sub

This one does not overwrite default file, I don't know why your code didn't work 'cuz it looks the same with msgbox.

 

[Ruddles]

This is the command which saves the 'default' workbook:-

ActiveWorkbook.Save

You had it in your original code so I included it when I placed the MsgBox around it. Your latest code doesn't have that command in it.

 

[Zahhhaaaa]

Ok, I removed that from code, so now it's working like i wanted! Thanks for your patience!

 

[Ruddles]