Excel 2003 Data Validatation - VBA Help

[conbam]

I have an existing spreadsheet with a drop down list of numbers. Each cell will be used in a calculation.

For example

Data validation list source has 2, 4

I select the value 2 or 4 from the spreadsheet.

What I want is to select a string instead of the number

For example
North
South.

I want to be able to add the cells as if they were numbers not strings.

So I created a enum in VBA
example
Public Enum eHemisphere
North=2
South =4
End Enum

Dim h As eHemisphere

Then I wanted to specify the enum variable in a data list. =h Can someone help.

 

[Gavona]

For dropdown why not use inbuilt Data,Validation function? Your validation lists are held in worksheet ranges, best to use named ranges, the worksheet containing them can be hidden.

If you need to apply by code then post in forum707 but this is pretty much what macro recorder gave me:

With Range(A1).Validation
    .Delete
    .Add Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, _
        Formula1:="=$D$1:$D$3"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = "my input title"
    .ErrorTitle = "my error title"
    .InputMessage = "my input message"
    .ErrorMessage = "my error message"
    .ShowInput = True
    .ShowError = True
End With

Once you input your number in A1, use a vlookup in B1 to get the North, South etc text.

Or in code, run from an event, keeping it all in A1 and with a named range myLookup use:

With Range("A1")
.Formula = "=vlookup(" & .Value & ",myLookup,2,0)"
.Calculate
.Value = .Value 'converts from formula to value
End With

As I say, you are in the wrong forum really so post there to follow up or get alternate responses using controls etc.

Gavin