Validation List automatically convert string as date in VBA

user_9896279 0
Viewed 188

I'm trying to create a Validation List with worksheets's name. First part I add every sheets's name on an array (Sheets are like "January 2018", "February 2018", etc etc), and the second part create the List by joining every part of the array with a coma separator.

The problem is that the ValidationList convert my strings like "January 2018" to date format "Jan-18". And I can't continue my code because don't respect the sheetname...

Here is my code:

Sub RefreshMonth()
Dim xsheet as Worksheets
Dim monthList(20) As String
Dim valList As String


''' This part add every sheet names on an array and work well '''
i = 0
For Each xsheet In ActiveWorkbook.Sheets()
    If xsheet.Name Like "*20*" Then
        monthList(i) = xsheet.Name
        i = i + 1
    End If
Next xsheet


'This part create the validation list, where the unwanted conversion happend '''
With Range("B1").Validation
    .Delete
    .Add Type:=xlValidateList, _
          Formula1:=Join(monthList, ",")
End With

And here my ValidationList after the code run with unwanted converted sheetname as date format: enter image description here

I tried to use CStr() even after the join() to reforce string conversion but for now i didn't found anything working.

Many thanks by advance for help

vba excel
2 Answers

Try this it's slightly different approach where you are first saving all sheets names somewhere in the sheet (I have used column AA but you can change this) and then you're assigning this range for validation.

Sub RefreshMonth()
Dim xsheet As Worksheet
Dim valList As String
Dim validationrange As Range

i = 1
For Each xsheet In ThisWorkbook.Worksheets
    If xsheet.Name Like "*20*" Then
        Range("AA" & i).NumberFormat = "@"
        Range("AA" & i) = xsheet.Name
        i = i + 1
    End If
Next xsheet

Set validationrange = Range("AA1:AA" & i - 1)
With Range("B1").Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:="=" & validationrange.Address
End With
End Sub
user_52723960

Solution close to your original post

Data validation actually converts strings that can be interpreted as date (according to the language in your local settings) to dates.

To solve your issue you will have to tell the application that you want a string only. I found out by trying that this can be done by adding a Chr(1) character to your MonthList(i) value assignment. The validation list now just show pure string values (e.g. January 2018) as choice whereas the returned cell value (after manual validation) now adds a ' prefix to B1's cell content thus marking/preserving it as string (►'January 2018). So you can overcome the unwanted behaviour described by your post above.

Modified code

Option Explicit

Sub RefreshMonth()
Dim xSheet        As Worksheet              ' corrected to data type WorkSheet
Dim monthList(20) As String
Dim valList       As String
Dim i&                                      ' added declaration (Long)

i = 0
For Each xSheet In ThisWorkbook.Worksheets
    If xSheet.Name Like "*20*" Then
        monthList(i) = xSheet.Name & Chr(1) ' << added chr(1) instead of prefixing apostrophe "'"
        i = i + 1
    End If
Next xSheet

'This unchanged part creates the validation list, but returns string values now (e.g. 'February 2018)
With Range("B1").Validation
    .Delete
    .Add Type:=xlValidateList, _
         Formula1:=Join(monthList, ",")
End With

End Sub
user_64602970
Related