[Excel VBA] Problem with Excel Validation List

[boyshawn]

Hi all

I am pretty new to programming in vba. I need some coding help which I am not able to find the solution after I tried Googling for the solution.

Currently I have a self-defined type called category mapping. The type will be used to contain the items which I want to put as the option for the vadliation list. It looked as below:

Public Type categoryMapping
    messageKey As Long
    description As String
End Type

An example of the categoryMapping is to store gender codes, 6000 stands for Male, 6001 stands for Female.

Display of the Validation List
As I have all stored them in an array, displaying them is not easy. What I have done is as below:

'Validation drop down list for the whole row
If Has_Elements(mapping) Then
    Dim code As String
    Dim options() As categoryMapping
    
    code = ""
    options = mapping
    
    Dim j As Integer
    For j = LBound(options) To UBound(options)
        code = code & options(j).messageKey & ": " & options(j).description & ","
    Next j
    
    With Range(Rows(7).Address).Validation    'TODO: Need to refactor
        .Add Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, _
        Formula1:=code
        .InCellDropdown = True
        .InputMessage = "Please choose from of the following"
        .ShowInput = True
    End With
End If

Is there a easier to display them since I have already put all the item to be displayed into a array? Is it possible that I can call the array directly?

Use Cells
From the code above, it can be seen that I have use the address of the whole row to contain validation list, because actually what I want is the whole row, except the header cells to contain the validation list.

    With Cells(7,1).Validation    'TODO: Need to refactor
        .Add Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, _
        Formula1:=code
        .InCellDropdown = True
        .InputMessage = "Please choose from of the following"
        .ShowInput = True
    End With

I tried using the code above to do, but it fails. Is there any way to go about doing it?

Combine multiple validation delete

Due to the problem mentioned above, I need to put in the code to delete the validation list for some column, as shown below:

'Delete the unneccessary validation
'TODO: refactor the code so that write in 1 line
Range(Columns(1).Address).Validation.Delete
Range(Columns(2).Address).Validation.Delete
Range(Columns(3).Address).Validation.Delete
Range(Columns(4).Address).Validation.Delete

Is there some ways that I can combine all the delete validation into a single statement?

Display part of the option

As you can see from above, when my users select a option from the validation list, the whole string appear.

For example, if I have "6000: Male" & "6001: Female" as the option, and I choose Male, I wished for "6000" to appear instead of "6000: Male". Is there a way to do it?

Validation data not exist after reopen

After I generate the validation list, I close the program and reopen it, there is a error that say "Excel found unreadable content in 'File name.xls'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.

When I click Yes, my excel open but all my validation list is gone! And I got the below as a error message.

<!--?xml version="1.0" encoding="UTF-8" standalone="yes" ?-->
<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
- '<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<logFileName>error078080_01.xml</logFileName>

<summary>Errors were detected in file 'file name.xls'</summary>

- '<removedFeatures summary="Following is a list of removed features:">
'<removedFeature>Removed Feature: Data validation from /xl/worksheets/sheet2.xml part</removedFeature>

'</removedFeatures>


'</recoveryLog>

I was guessing that the error occur because the option in the validation list is not stored in the worksheet, but it is stored in the program memory, hence when I closed the program, the memory is loss.

If my guess is correct, is there any way to go around solving this problem? I was thinking of creating another worksheet that contain all the data in the validation list and let my cell validation list refer to them, but is there better ways to store them in the same worksheet?

 

[lixer]

Hey man, I dunno the exact solutions. But this is what i know and want to clarify.

Display of the validation list

What do you mean by 'easier' way? Do you mean a non-iterative way of accessing arrays elements such as
a print all method? The best way to access an array is through an iterative loop. If you needed something else, you probably have to write the function yourself. Something like this...

Private Function Print_Code() As String
  .....
    Print_Code = code

End Function

    With myRange.Validation    'TODO: Need to refactor
        .Add Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, _
        Formula1:=Print_Code
        .InCellDropdown = True
        .InputMessage = "Please choose from of the following"
        .ShowInput = True
    End With

If you are trying to access the array directly with the Range.Validation, then nope, i dun think so.



Use Cells
Cells(7, 1).Validation works for me. Alternatively, if you want to exclude the headers (which i assume to be the first column), try define your own range.

    Dim myRange As Range
    Set myRange = Range(Cells(7, 2), Cells(7, 7))

......

   With myRange.Validation 
.......
   End With

Combine multiple validation delete
Do the same for this. Define a range to delete. Will not be a one liner. But it still will reduce most of your code.
Check this link out for range selection.
http://support.microsoft.com/kb/291308

Display part of the option
Is it a requirement to show the gender code to the users? If not, just don't concatenate the gender code. Anyway, validation list is meant to restrict users input. Thus, if you want it to display differently, it would be quite troublesome. The only method i know is through this....
Go double click the target worksheet under Microsoft Excel Objects in the editor.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    'Set up the range for the detection of cell change to work
    Set myRange = Range(Cells(7, 2), Cells(7, 7))
    
    'Check whether cell changed in the range
    If Not Intersect(Target, myRange) Is Nothing Then
        
        'Check for single cell content only
        If Target.Cells.Count = 1 Then
            If Target.Cells.FormulaLocal = "6000: male" Then
                Target.Cells.FormulaLocal = "male"
     
            ElseIf Target.Cells.FormulaLocal = "6001: female" Then
                Target.Cells.FormulaLocal = "female"
    
            End If
        End If
            
    End If

End Sub

Hard-coded to check for change in the cell contents. After the user select the gender in validation options, he must click on the cell again, then the content of the cell will change to the desired one.

Validation data not exist after reopen

Ya, man. You are right. You set the validation formula1 source to be code. But, code is an run time variable. You open, close, all the run time memory gone. The most common way i see other people resolve this is by having worksheets that store legends, code references etc.

Anyway, just set a reference to the cells that contain the appropriate data in the same worksheet will work fine. If needed, protect , lock , or even hide the cells.

Not much help. I don't know alot as well.

 

[taurean]

It might prove to be a good idea to use a different worksheet and 'Named Ranges' for using validation.