Search : in
By :

Excel dropdown issues using Macro

Last answer on Sep 25, 2009 11:28:07 am BST kivuous, on Jun 10, 2009 12:24:38 pm BST 
 Report this message to moderators

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!!..

Configuration: Windows XP Internet Explorer 6.0

Ask your question Reply

1

Excelguru, on Jun 10, 2009 12:38:43 pm BST
  • +1

Hi
why you need a macro
put a formula =if(dropdownCellAddress="DECEASED","DEC","") in the target cell Winners are losers who got up and gave it one more try. -Dennis DeYoung
My Interests are financial Modelling and custom excel development.

   
Reply to Excelguru

8

 Ashwin, on Sep 25, 2009 11:28:07 am BST
  • +1

Why to write a macro?????

Just make an array in the same sheet or define an array in another sheet.
do a vlookup of the corresponding drop down cell....

Let me know if you need more help.....

   
Reply to Ashwin

2

kivuous, on Jun 10, 2009 1:08:47 pm BST

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..

   
Reply to kivuous

3

Excelguru, on Jun 10, 2009 3:17:10 pm BST
  • +1

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. Winners are losers who got up and gave it one more try. -Den­nis DeYoung
My Interests are financial Modelling and custom excel develo­pment.

   
Reply to Excelguru

4

kivuous, on Jun 11, 2009 4:25:01 am BST

Thanks a lot excelguru...
i am very close to solving this problem with your help...........

jus guide me a lil bit more.. i m totally amateur in this, so pardon my standard!

i have used this in my form.....
For a = 1 To iCurrentLastRow
.Range("m" & a).Formula = "=IF(m" & a & "=" & Chr(34) & "Deceased" & Chr(34) & "," & Chr(34) & "DEC" & Chr (34) & "," & Chr(34) & "Not Deceased" & Chr(34) & ")"
.Range("m" & a).FormulaHidden = False
Next a

and later for adding the drop down i hav used ..
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=$n$1:$n$18"

please guide me how to delete this validation... cos its showing circular references are present while opening the excel sheet.......... (When a formula refers back to its own cell, either directly or indirectly, it is called a circular reference) and how to get rid of this circular reference

   
Reply to kivuous

5

Excelguru, on Jun 14, 2009 1:49:09 pm BST

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 Winners are losers who got up and gave it one more try. -Den­nis DeYoung
My Interests are financial Modelling and custom excel develo­pment.

   
Reply to Excelguru

6

kivuous, on Jun 15, 2009 5:07:44 pm BST

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

   
Reply to kivuous

7

Krish, on Jul 23, 2009 7:44:34 pm BST

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

   
Reply to Krish
Ask your question Reply
They need your help