Excel dropdown issues using Macro

[Closed]
Report
-
 vasim -
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!!..

6 replies

Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
Hi
why you need a macro
put a formula =if(dropdownCellAddress="DECEASED","DEC","") in the target cell
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

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.....
i have some arrays in the same work sheet. with reference to the value selected in one drop down list, the 2nd drop down list should change. is thr any way
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..
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
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
Posts
261
Registration date
Saturday April 11, 2009
Status
Member
Last seen
June 21, 2011
307
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.
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
Hi...
thanks a lot..
its done!
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