none
Special Data Validation RRS feed

  • Question

  • Hi All.......

    Windows XP and 7, and excel 2010

    I wish to use the value in a cell as part of a filename when saving a worksheet.  I have macros to do all that and they work fine, except for when and if the User enters one or more of the Windows Restricted Characters in the cell.  Then, the SAVE macro crashes.

    I would like to have a data validation condition on the cell that will pop-up a message warning the user that the use of those characters is not allowed when they first try to enter them.  I have looked at the standard Data Validation section but don't see how to do it there.

    Any help would be apprecaited.

    Vaya con Dios,

    Chuck, CABGx3


    Chuck, CABGx3

    Thursday, March 3, 2016 6:41 PM

Answers

  • Hi Chuck,

    I've made a sample, according to a example that Hans has shown.
     
    ' --- [Save File] button : click
    Private Sub btn_SaveFile_Click() Dim saveFileName As String saveFileName = Range("C2").Value ' --- If Len(saveFileName) > 0 Then If IsValidFileName(saveFileName) = True Then MsgBox "OK: valid characters"
    ' --- save file Else MsgBox "There exist invalid characters in cell [C2]" End If End If End Sub ' --- function : check if input charecters are valid for file/folder name Function IsValidFileName(ByVal sFileName As String) As Boolean Dim vaIllegal As Variant Dim i As Long ' --- vaIllegal = Array("/", ":", "*", "?", "< ", ">", "|", """") IsValidFileName = True ' --- For i = LBound(vaIllegal) To UBound(vaIllegal) If InStr(1, sFileName, vaIllegal(i)) > 0 Then IsValidFileName = False Exit Function End If Next i End Function
    Regards.
    • Marked as answer by CABGx3 Monday, March 7, 2016 12:47 AM
    Friday, March 4, 2016 4:02 AM

All replies

  • See http://dailydoseofexcel.com/archives/2010/04/20/illegal-file-names/ for some examples of custom VBA functions to check the validity of filenames.

    You could use such a function in a helper cell to return TRUE/FALSE, and refer to that helper cell in your data validation.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, March 3, 2016 9:08 PM
  • Hi Chuck,

    I've made a sample, according to a example that Hans has shown.
     
    ' --- [Save File] button : click
    Private Sub btn_SaveFile_Click() Dim saveFileName As String saveFileName = Range("C2").Value ' --- If Len(saveFileName) > 0 Then If IsValidFileName(saveFileName) = True Then MsgBox "OK: valid characters"
    ' --- save file Else MsgBox "There exist invalid characters in cell [C2]" End If End If End Sub ' --- function : check if input charecters are valid for file/folder name Function IsValidFileName(ByVal sFileName As String) As Boolean Dim vaIllegal As Variant Dim i As Long ' --- vaIllegal = Array("/", ":", "*", "?", "< ", ">", "|", """") IsValidFileName = True ' --- For i = LBound(vaIllegal) To UBound(vaIllegal) If InStr(1, sFileName, vaIllegal(i)) > 0 Then IsValidFileName = False Exit Function End If Next i End Function
    Regards.
    • Marked as answer by CABGx3 Monday, March 7, 2016 12:47 AM
    Friday, March 4, 2016 4:02 AM
  • Many thanks Ashidacchi.........

    Not being too familiar with Functions, I was not able to get any of the previous suggestions to work for me.  

    But with YOUR code, I achieved the desired success.  Thanks again,

    Vaya con Dios,

    Chuck, CABGx3


    Chuck, CABGx3

    Monday, March 7, 2016 12:51 AM