• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dropdown List Macro

Shailender

Member
Hi All,
Please help!
I am trying to write a macro that is dependent on the drop down list.
Once a name is selected from the drop down list that will show us some data related to the name selected. I need to copy the resulting data to another workbook.
Problem is, I am unable to see anything when I record the macro to select an item from the drop down list.
Attached is the file.

Here is the code when we record...
Code:
Sub Macro3()
'
' Macro3 Macro
'

'
    Range("A1").Select
End Sub
 

Attachments

Last edited by a moderator:

salim hasan

Member
Try This Macro
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
 If Target.Address = "$A$1" Then
  find_data
  End If
 Application.EnableEvents = True
End Sub
'==============================
Sub find_data()
Dim r%
Range("b6").Resize(, 4).Value = vbNullString
r = Range("M:M").Find(Range("a6")).Row
If r <> 0 Then
 Range("b6").Resize(, 3).Value = _
 Cells(r, "N").Resize(, 3).Value
 End If
End Sub
 

Attachments

Shailender

Member
Try This Macro
Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Address = "$A$1" Then
  find_data
  End If
Application.EnableEvents = True
End Sub
'==============================
Sub find_data()
Dim r%
Range("b6").Resize(, 4).Value = vbNullString
r = Range("M:M").Find(Range("a6")).Row
If r <> 0 Then
Range("b6").Resize(, 3).Value = _
Cells(r, "N").Resize(, 3).Value
End If
End Sub
Thank you for the quick response. I appreciate your help.

But I need:

1) Macro to go select a person’s name from the drop down
2) Copy the resulting data from B6:D6 and paste in new worksheet
3) Do the same task for rest of the names available in the drop down list
This is not based on the worksheet level macro. I am trying write this in the module level.
 

vletm

Excel Ninja
Shailender
If try to check Your needed result then
... Your 'macro' could do like this
... press [ Do It ]-button
or
do that macro paste always to new worksheet?
... Ooops ... that was a wrong named file!
This is same named files as Yours
 

Attachments

Last edited:

vletm

Excel Ninja
salim hasan
As written in
  • PLEASE DON'T SHOUT! We have big ears and will hear you just the same.
There is none passwords!
Case Copy ... as any texts ... select needed area > press Ctrl+C
 

Marc L

Excel Ninja
Hi vletm !​
Under Windows your attachment is again password protected on VBE side and it's not the first time !​
Better is to post the code, thanks …​
 

vletm

Excel Ninja
Marc L
hmm?
Interesting ...
1) open that file...
2) Tools -> Macro -> Visual Basic Editor ->
3) can see Project-window
4) click from there 'Module1'
... what is password protected?
 

Marc L

Excel Ninja
what is password protected?
As I wrote « on VBE side » so the VBAProject of your attachment …​
I will try at night on a more recent Excel version. vletm, Salim, which Excel version do you use ?​
 

vletm

Excel Ninja
Marc L
Did You those steps as I wrote?
... to get any idea ... when comes something which is different there than here with me!

... okay that mystery code which others seems cannot see ...
as written:
If try to check Your needed result then
... Your 'macro' could do like this

Code:
Private Sub Do_It()
    Sheets("Sheet1").Range("M2:P4").Copy Destination:=Sheets("Sheet2").Range("D6")
End Sub
 

Marc L

Excel Ninja
Are you kidding ?‼​
As we yet wrote the VBA project of your attachment is password protected !​
I don't mind 'cause I can crack it but think at least about this forum users !​
As the easy way is to just post the code between code tags as per forum rules …​
 

vletm

Excel Ninja
Marc L
No ... I'm not kidding with You!
There is NONE PASSWORD from this side.
I can do those steps without any ... passwords or cracking.
... What would happen if I try to protect that VBE ... would it be unprotected for others?
This file is VBE protected with Your username
... is there any differences ... of protection?


Yes ... it's easy possible to post the code
and next step is to save that code in correct place
... that as been also many times mission impossible!
 

Attachments

Last edited:

Kenneth Hobson

Active Member
Shailender, post #1 and post #3 have two different goal sets. I will post a solution for #3 shortly. In the interim, I am unclear as to what you want exactly. (1) For the 3 items in the list, you want 3 new workbooks? Or (2), for the 3 items you want 3 rows, 1:3, added to a new workbook?

Regarding the other post attachments:
As Marc said, we can crack the vbaproject passowrd but we should not have to for post #12 file. You should edit your Open event code or delete that code for post #4 file as it shows invalid user and closes on Open.
 

Kenneth Hobson

Active Member
For case 2:
Code:
Sub Main()
    Dim r As Range, c As Range, i As Long, v, ws As Worksheet
   
    Set ws = ThisWorkbook.Worksheets(1)
    With ws
        Set r = .Range(.Range("A1").Validation.Formula1)
        v = .Range("A1")
    End With
   
    With Workbooks.Add(xlWBATWorksheet).Worksheets(1)
        For Each c In r
            i = i + 1
            ws.Range("A1") = c
            ws.Range("B6:D6").Copy
            .Cells(i, "A").PasteSpecial xlValues
        Next c
    End With
   
    ws.Range("A1") = v
    Application.CutCopyMode = False
End Sub
This method can be slow for many rows. In that case, I would use an array to do it.
 

vletm

Excel Ninja
Kenneth Hobson
Yes ... now I got that ... protected file ... that was wrong file for this thread... sorry!
I changed the correct file to #4 reply ... actually, it's same file as #12 reply (which is protected as I've written there)
Also
that ... mystery code can see from #9 reply
which do same result as Shailender has written, but much shorter way - one copy & paste.
Original seems to be to new workbook ... #3 as You written
Seems that only Kenneth Hobson could verify which one.
 

Kenneth Hobson

Active Member
Right, if we go by post #3 to a new worksheet within the same workbook, my code would have one line changed.
Code:
'With Workbooks.Add(xlWBATWorksheet).Worksheets(1)
With Worksheets.Add(after:=Worksheets(Worksheets.Count))
I assumed that #1 file was a shorter version where the table may not always be setup like B6:D6.
 

vletm

Excel Ninja
Kenneth Hobson
There are also 'open'
... to where in any sheet any one time copy should do ... or even randomly?
and
if any macro would run more than once
... should it always do it to new sheet (or even file)?

or
as he wrote ... Problem is, I am unable to see anything when I record the macro to select an item from the drop down list. ?
 

Kenneth Hobson

Active Member
Sometimes I just make assumptions at some point in working up a solution. I do like clarify goals before some solutions or else it does not "work" as expected. Sometimes I "read minds" well, and sometimes not...

As for the not recording anything, that is common for some tasks as experienced coders know. At least some tasks do record which helps beginners get started.
 

vletm

Excel Ninja
Kenneth Hobson
Sometimes ... that's why I tried to make something ... like guess because normally threads are not clear.
Recording ... even any kind of recording needs time ... if everything would be 'start and end' ... that would be too wild.
 

Shailender

Member
Shailender
If try to check Your needed result then
... Your 'macro' could do like this
... press [ Do It ]-button
or
do that macro paste always to new worksheet?
... Ooops ... that was a wrong named file!
This is same named files as Yours

Shailender
If try to check Your needed result then
... Your 'macro' could do like this
... press [ Do It ]-button
or
do that macro paste always to new worksheet?
... Ooops ... that was a wrong named file!
This is same named files as Yours
Thank you for the quick response.

To put it in a simple way, What I exactly want is in the Sheet1 A1 cell I have data validation, whenever I change the name from the dropdown list the data is automatically getting changed. So the question here is how do I change the names from the dropdown list automatically with the help of VBA with any manual intervention?

Hope this is clear now?

thank you.
 

Attachments

vletm

Excel Ninja
Shailender
As Your changed 'wish' is
What I exactly want is in the Sheet1 A1 cell I have data validation, whenever I change the name from the dropdown list the data is automatically getting changed. So the question here is how do I change the names from the dropdown list automatically with the help of VBA with any manual intervention?
... as You've written ... You change name in dropdown list ... the code will change cell A1's value.
If You wanted that automatically, then it's not possible to do manually neither in module level.
 

Shailender

Member
Shailender
As Your changed 'wish' is
What I exactly want is in the Sheet1 A1 cell I have data validation, whenever I change the name from the dropdown list the data is automatically getting changed. So the question here is how do I change the names from the dropdown list automatically with the help of VBA with any manual intervention?
... as You've written ... You change name in dropdown list ... the code will change cell A1's value.
If You wanted that automatically, then it's not possible to do manually neither in module level.
ok, is there any way to do that?
 
Top