Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

VBA Code Needs Revision

Hello Experts,

I am currently using this code,...

Sub BuildFileNameAndSave()
    
    Dim SaveStr As Variant
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Worksheets("Input") 'change as needed
    
    SaveStr = False
        
    Do Until SaveStr <> False
        SaveStr = Application.GetSaveAsFilename(CurDir & "\LOT # " & ws.[LotNumber] & " - CUST # " & ws.[CustomerNumber] & " - " & ws.[CustomerName] & ".xlsm", "Excel Workbooks (*.xlsm), *.xlsm", , _
            "Select file name and folder:")
        If SaveStr = False Then
            MsgBox "Please make a selection!", vbCritical
        End If
    Loop
    
    ThisWorkbook.SaveAs SaveStr
    
End Sub

Open in new window


But I ran into two issues, and I don't know my way out of it.

Issue 1 - "CustomerName", needs to be converted to UPPER CASE.  I know in Excel you just use something like this "=UPPER(A1)" - but with this code, I'm unsure of how to do it.

Issue 2 - When you run the code, and you are at the save dialog window - if the user then decides they don't want to save, they would click on "Cancel" - but doing so shows the error message "Please make a selection!", then it goes back to the save dialog window.

When I ran into this issue, I actually couldn't figure out how to stop it - so I had to force close excel.

Any ideas?

Thank you in advance for your help!

~ Geekamo
0
Geekamo
Asked:
Geekamo
  • 2
  • 2
1 Solution
 
Saqib Husain, SyedEngineerCommented:
Try this

Sub BuildFileNameAndSave()
    
    Dim SaveStr As Variant
    Dim ws As Worksheet
    
    Set ws = ThisWorkbook.Worksheets("Input") 'change as needed
    
    SaveStr = False
        
    Do Until SaveStr <> False
        SaveStr = Application.GetSaveAsFilename '(CurDir & "\LOT # " & ws.[LotNumber] & " - CUST # " & ws.[CustomerNumber] & " - " & UCase(ws.[CustomerName]) & ".xlsm", "Excel Workbooks (*.xlsm), *.xlsm", , _
            "Select file name and folder:")
        If SaveStr = False Then
            SaveStr = MsgBox("Please make a selection!", vbRetryCancel)
        End If
        If SaveStr = 4 Then SaveStr = False
    Loop
    If SaveStr <> 2 Then
        ThisWorkbook.SaveAs SaveStr
    End If
End Sub

Open in new window

0
 
GeekamoAuthor Commented:
@ ssaqibh

This is great!  The only thing I noticed is there was an " ' " in the line of code, which turned it into a comment.  I just removed that and it worked correctly.

Thank you very much!

~ Geekamo
0
 
Saqib Husain, SyedEngineerCommented:
Yes, sorry I had to comment that out to be able to run the command on my computer which did not have your directory structure. In the end I forgot to remove it.

Thanks for the points.
0
 
GeekamoAuthor Commented:
Not a problem! :)
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now