Save, Save As vba help

[bob733]

I give up. I have spent so much time searching this forum, YouTube and the internet on how to do something that I know is simple but I just am having a lot of problems finding something easy to use.

I have a user form with three buttons on it. One is a save, one is a save as and one is a print button.
Can somebody just show me a simple VBA routine to save the file worksheet in the current directory. And if I want to do a save as, a routine to save the worksheet in a directory or name of my choice.

The object names are checkbox_save, checkbox_saveas and checkbox_print. I know I can double click on these and the code for it will come up (Sub and End Sub).

I apologize for not being able to successfully research and find something to do this myself but I have just been overwhelmed with all the different things i find, so that now I'm totally confused.

I have been able to use this forum for many things successfully but or some reason I spending an inordinate amount of time trying to do something that is basically very simple.

I apologize for my inability to do this, and thank you in advance.

Bob

PS. And by the way I have a print button that I can get to print but it does not allow me to choose my own printer. It goes to the default printer all the time and while I'm at home it gets hung up because the printer is at work. I would like to be able to specify a printer and preview the document prior. So if you throw in the coding for this that would be nice to. I just love it when I feel this dumb!

 

[dmt32]

Hi,
try this saveas code & see if helps you.

Sub SaveAs()


    Dim FileName As Variant
    Dim FileFilter As String, Title As String
    Dim FilterIndx As Integer
    Dim Response As VbMsgBoxResult


    FilterIndx = 3
    
    Title = "Save File"


    FileFilter = "Excel 97-2003 Workbook (*.xls),*.xls," & _
              "Excel 2007 > (*.xlsx),*.xlsx," & _
              "Excel Marco-Enabled Workbook(*.xlsm),*.xlsm"


    Do
    FileName = Application.GetSaveAsFilename(InitialFileName:=ActiveWorkbook.Name, FileFilter:=FileFilter, FilterIndex:=FilterIndx, Title:=Title)


    If FileName = False Then Exit Sub
    If Dir(FileName) = "" Then Exit Do
             
    Response = MsgBox(FileName & Chr(10) & Chr(10) & Space(Len(FileName) / 2) & "File Exists Overwrite?", 36, "File Exists")
    If Response = vbYes Then Exit Do
    Loop
    
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs FileName
    Application.DisplayAlerts = True
End Sub

You can either place the body of the code inside your forms saveas command button or call it as a separate procedure.

Dave

 

[someguy111]

Hi,

I'm a new user and I can't figure out how to paste my VBA Code in here so it will show up in it's own separate scroll box.
Can someone clear this up for me please?

Thank you !!

 

[JimRau]

Hi,

I'm a new user and I can't figure out how to paste my VBA Code in here so it will show up in it's own separate scroll box.
Can someone clear this up for me please?

Thank you !!

The last button on the formatting line when you type a reply is # ... it inserts the code things ... paste your macro between them and you get the box.

 

[someguy111]

Thanks so much

 

[bob733]

dmt32
It worked like a charm! Thank you ever so much! This forum and individuals like you, make Mrexcel.com one of the best websites in the world.
Bob

 

[dmt32]

dmt32
It worked like a charm! Thank you ever so much! This forum and individuals like you, make Mrexcel.com one of the best websites in the world.
Bob

MrExcel is the best, no silly logins to access content preventing non-members viewing solutions & plenty of friendly contributors.

Many thanks for feedback

Dave