Dropdown List to return another value in a table

[Groovy Chick]

Hi, I have an Excel worksheet in column F where I need to refer to numbers instead of putting in text. To explain I have four categories - employee, agency worker, contractor and visitor. Instead of returning the name I want to return 1, 2, 3 or 4 respectively. I have created a dropdown in the cell with the text description. I have a table on another sheet called WorkerCat which is two columns with the text and corresponding number. I've created the following code but it's not working. It finds the text from the dropdown but not the corresponding number in the table. Can anyone tell me what I am doing wrong? Many thanks in advance.

Private Sub Worksheet_Change(ByVal Target As Range)

selectedNa = Target.Value
If Target.Column = 6 Then
selectedNum = Application.VLookup(selectedNa, Sheets("WorkerCat").ListObjects("WorkerCat").DataBodyRange.Select, 2, False)
If Not IsError(selectedNum) Then
Target.Value = selectedNum
End If
End If
End Sub

 

[Groovy Chick]

Hi Dave, tried again and still not working. I've put the code on the active sheet and saved it as an xlsb and enabled content. I assume that's correct?

 

[dmt32]

I've put the code on the active sheet and saved it as an xlsb and enabled content. I assume that's correct?

normal file format is xlsm but should ok

check the following

- macros are enabled
- code is in the activesheet code page
- you have set data validation to allow other entries by unchecking "show error alert"
- the data validation text list matches text in the 1st column of your table

if this is all correct, then should work.

Dave

 

[Groovy Chick]

I've done all of the above, Dave but no luck. I'll try at home tomorrow.

 

[dmt32]

I've done all of the above, Dave but no luck. I'll try at home tomorrow.

really sorry not able to resolve can't help but think I am missing something

Dave

 

[Fluff]

Have you tried closing Excel completely?

 

[dmt32]

Have you tried closing Excel completely?

@Fluff - covered the workbook closed in #Post 6 not sure if OP close Excel entirley

Dave

 

[Fluff]

If events are disabled I think that xl needs to be closed, rather than just the workbook. Either that or re-enabled by code.

 

[dmt32]

If events are disabled I think that xl needs to be closed, rather than just the workbook. Either that or re-enabled by code.

I appreciate that & may have directed OP incorrectly - hopefully that all it is.

Dave

 

[Groovy Chick]

Tried again this morning with shutting Excel down completely. That worked! Awesome, thank you so much to you both for your help. Much appreciated.

 

[dmt32]

Tried again this morning with shutting Excel down completely. That worked! Awesome, thank you so much to you both for your help. Much appreciated.

I said I missed something and as @Fluff reminded me, should have said to close the excel application & not just the workbook to reset EnableEvents.

Glad we were able to help & issue resolved - appreciate feedback

Dave