Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2009
    Posts
    1

    Unanswered: Problem with macro code in excel

    Hi, I have this code that will save the current workbook, I want to put in place an error check so if the user put's in nothing, or hits cancel (which outputs as 'false.xls) it forces msgbox stating to put in a real file name ... my current code looks like this


    Code:
    ' Save Sheet
    Loop_Start:
        Dim NewName As String
        NewName = Application.GetSaveAsFilename
        ThisWorkbook.SaveAs Filename:=NewName & ".xls"
            If NewName = "False" Then GoTo ErrorMessage
    
            
    ErrorMessage: MsgBox ("You need to specify a Filename when saving this order!")
            GoTo Loop_Start
    It's not working in the sense that it doesn't check to see if the field is true to the statement. Thanks in advance for your help

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Quote Originally Posted by verse.thekeg
    Hi, I have this code that will save the current workbook, I want to put in place an error check so if the user put's in nothing, or hits cancel (which outputs as 'false.xls) it forces msgbox stating to put in a real file name ... my current code looks like this


    Code:
    ' Save Sheet
    Loop_Start:
        Dim NewName As String
        NewName = Application.GetSaveAsFilename
        ThisWorkbook.SaveAs Filename:=NewName & ".xls"
            If NewName = "False" Then GoTo ErrorMessage
    
            
    ErrorMessage: MsgBox ("You need to specify a Filename when saving this order!")
            GoTo Loop_Start
    It's not working in the sense that it doesn't check to see if the field is true to the statement. Thanks in advance for your help
    Hi

    The following code, I believe, does what you have requested
    Code:
    Sub SaveFile()
        Dim NewName As String
        Dim FileSaved As Boolean
        
        FileSaved = False
        
        Do Until FileSaved
            NewName = Application.GetSaveAsFilename(fileFilter:="Excel Files (*.xls), *.xls")
            If NewName <> "False" Then
                If Dir(NewName) = "" Then
                    ThisWorkbook.SaveAs Filename:=NewName
                    FileSaved = True
                Else
                    If MsgBox("File exists. Do you want to overwrite existing file?", vbQuestion + vbYesNo, "Save File") = vbYes Then
                        Application.DisplayAlerts = False
                        ThisWorkbook.SaveAs Filename:=NewName
                        Application.DisplayAlerts = True
                        FileSaved = True
                    End If
                End If
            Else
                MsgBox "You need to specify a Filename when saving this order!"
            End If
        Loop
    End Sub
    However, once the sub is entered you cannot leave it without saving the file (except with Ctl+Break!), which in my experience is never a good idea, and can be very frustrating.


    MTB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •