Macro for copying values from the dropdown list in Excel

[Tulsi]

Hello,

Could somebody give me a macro for copying a particular value from a
dropdown list into a next column.

For example:

If I select "1" (from the dropdown list) then paste "1" to next
column(same row) ,
If I select "2" (from the dropdown list) paste "2" to next column(same
row) on deleting the previously selected option (i.e, "1")

Please help me in writing this Macro.:Blink
Thanks

 

[Simon Lloyd]

Firstly, Welcome to The Code Cage, why would you want a duplicate value
in the next column when you can use the value from your dropdown
(assuming its a data validation list), anyway you don't need code for
that you can simply use =A1 in B1 if your dropdown is in A1.

Hello,

Could somebody give me a macro for copying a particular value from a
dropdown list into a next column.

For example:

If I select "1" (from the dropdown list) then paste "1" to next
column(same row) ,
If I select "2" (from the dropdown list) paste "2" to next column(same
row) on deleting the previously selected option (i.e, "1")

Please help me in writing this Macro.:Blink
Thanks

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[Nigel]

If it is a listbox, then use the change event to apply any selection to a
specific cell.....

Private Sub ListBox1_Change()
If ListBox1.ListIndex > -1 Then
Range("B2") = ListBox1.Value
End If
End Sub

 

[Tulsi]

Hi Simon, (Reply from anyone is appreciated)
Thank u for your instant reply

I am very new to this. I have no idea with the formula =A1 in B1 you
gave. I applied it in the insert function option under formulas. I did
not know how to go about the same.

My actual problem statement is given below:

First of all, i am using MS Excel 2007, Windows XP

I have an excel document with two sheets(sheet1 and Sheet2).
Sheet1 contains student details. And sheet2 contains the dropdown
lists.
I have created the dropdown list using data validation.

The first drop down list is in row "B4" which contains numbers
"1,2,3,4,5,6,7,invalid" as list options.

Second dropdown is in "B8" which contains company names like "ABC
Limited, DCB Limited" and so on..

Third dropdown is in "B12" which contains different posts like
"Testing, Development, Support" and so on...

Hence, when a person selects "1" from the first dropdown list
(B4,Sheet2) it should get copied to the row "D4"(sheet2). Also the
contents of D4 should get copied to H5 and E13 rows of Sheet1

But when a person selects "invalid" from the first dropdown list
(B4,Sheet2) it should allow the user to enter a value in to D4 of sheet2
.. Also that value entered by the user should get copied to H5 and E13
rows of Sheet1

The same is been applied to the second dropdown list and the third
dropdownlist.


For further understanding we have put the scenario in the form of an
algorithm. Let us know if you have problems in understanding the problem
statement.


-do select the Project version number(sheet2)

if priority is "invalid"
{
Create a text box in D5 of sheet2
Allow the user to enter invalid value in D4 of sheet2
}
else
{
copy the respective Project version number to D4 of sheet2 of same
sheet (D4,sheet2).
}

copy the contents of D4, sheet2 to Sheet1(H5 and E13)

end-


Please reply!!

 

[Tulsi]

Hi simon,

I tried the below formula using insert function:
=CHOOSE(B4,1,2,3,4,5,6,7,\"INVALID\") [/B]

HERE AM ABLE TO COPY THE VALUES FROM DROPDOWN LIST (B4,WHICH HAS
DROPDOWN LIST CONTAINING \"1,2,3,4,5,6,7,INVALID\") TO D4 ROW OF THE
SAME SHEET,ONLY IF IT IS A NUMBER(1,2,3,4,5,6,7) .FOR \"INVALID\" OPTION
IT GIVES ERROR AS #VALUE!

I am not able implement this for a dropdown list containing
strings/alphabets.

Also i need to add a textbox once the user selects "invalid" option
from the dropdown list.

Please help in solving this.
Reply from any expert is greatly appreciated!!!.