asked on

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

ASKER CERTIFIED SOLUTION

Saqib Husain

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Geekamo

ASKER

@ 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

Saqib Husain

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.

Geekamo

ASKER

Not a problem! :)