Edit this macro so people dont save as "Type Here!"

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,929
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Everyone,
I have this macro to open the save as dialog box and save the document as a new name,

It works great except people keep saving it as the defualt wording (I know right!)
so i want to add a bit of macro that says if the name you are trying to save at is " " then dont let them,

please help if you can
here my macro



VBA Code:
Sub SaveFile()

Dim FileName As Variant
FileName = Application.GetSaveAsFilename("Type New File Name Here", _
    "Excel files,*.xlsm", 1, "Select your folder and filename")

If TypeName(FileName) = "Boolean" Then
MsgBox "User cancelled", vbCritical, "File Not Saved!"
Exit Sub
End If

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''' THIS BIT DOES NOT WORK!
If FileName = "Type New File Name Here.xlsm" Then
MsgBox "Can't use this name, please try again", vbCritical, "File Not Saved!"
Exit Sub
End If
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


ActiveWorkbook.SaveAs FileName

End Sub



thanks

Tony
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
1,842
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
VBA Code:
Sub SaveFile()
'
    Dim UserFileName
    Dim FileName As Variant
'
    FileName = Application.GetSaveAsFilename("Type New File Name Here", _
    "Excel files,*.xlsm", 1, "Select your folder and filename")
'
    If TypeName(FileName) = "Boolean" Then
        MsgBox "User cancelled", vbCritical, "File Not Saved!"
        Exit Sub
    End If
'
    UserFileName = Mid(FileName, InStrRev(FileName, "\") + 1, InStrRev(FileName, ".") - InStrRev(FileName, "\") - 1)
'
    If UserFileName = "Type New File Name Here" Then
        MsgBox "Can't use this name, please try again", vbCritical, "File Not Saved!"
        Exit Sub
    End If
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
    ActiveWorkbook.SaveAs FileName
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,792
Office Version
  1. 365
Platform
  1. Windows
Another option
VBA Code:
If InStr(1, FileName, "Type New File Name Here.xlsm", vbTextCompare) > 0 Then
MsgBox "Can't use this name, please try again", vbCritical, "File Not Saved!"
Exit Sub
End If
 

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,929
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Thank you jonnyL this was great,
and thank you fluff for another option,
but work great :)
thanks
Tony
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,792
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Forum statistics

Threads
1,147,821
Messages
5,743,393
Members
423,792
Latest member
travisds

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top