Excel dropdown issues using Macro Closed

Question

Hello friends, 

I have a requirement in excel. I am totally new to macro programming so please guide me.. 

There is a drop down list in a field say M. Now after the user selects any one of the values from the dropdown menu, I should populate the field(M) with a acronym of that value(which is provided and can be hardcoded). 

For example The user selects DECEASED from the drop down menu, but DEC gets stored in the cell. 

for creating the drop down list i have used the following where n1-n18 are the drop down values: 
.Range("m3:m" & iCurrentLastRow).Select 
With Selection.Validation 
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ 
xlBetween, Formula1:="=$n$1:$n$18" 
.IgnoreBlank = True 
.InCellDropdown = True 
.InputTitle = "Withdrawal Code" 
.ErrorTitle = "Withdrawal Code" 
.InputMessage = "Please select a withdrawal code from the drop-down list" 
.ErrorMessage = _ 
"You tried to enter an invalid withdrawal code. Please select a code from the list" 
.ShowInput = True 
.ShowError = True 
End With 

Please help me out!!..

Excelguru · Accepted Answer

Hi 
why you need a macro
put a formula =if(dropdownCellAddress="DECEASED","DEC","") in the target cell

kivuous · Answer

Thanks for your answer.. but Actually the formula is already used for creating the drop down :
Formula1:="=$n$1:$n$18"

where n1-n18 are the values of the drop down in cell M......

Now when i select any item from the list, it should be replaced by a different code ..

Moreover its not a single cell that needs to be changed!! every items after it is selected needs to be changed and then inserted into the cell..

Excelguru · Answer

Hi 
code under the Worksheet_Selection Change in the VBEditor page of that window
IF the target cells value is changed to DECEASED then delete the validation and change its value to DEC
Do a little bit recording first for these two procedures and add to above procedure.

Excelguru · Answer

Hi
how to delete this validation...  try recording by removing the validation and add this to the selection_change procedure
how to get rid of this circular reference - You have referenced the same cell in the formula. Either you continue with circular reference or change to DEC using a macro in the selection_change precedure just after deleting the validation

kivuous · Answer

Hi...
thanks a lot..
its done!

Krish · Answer

Hi,

I have a requirement. I want to populate drop down from one reference table in our DB.
Is there a way that everytime user opens the spreadsheet dropdown is automatically populated from reference table and then user just need to select one and upload spreadsheet?

Please help

Thanks

Excel dropdown issues using Macro

6 responses

Similar discussions

Subscribe To Our Newsletter!