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

Dynamic List -Each Unique Value -List Box-Selectable-& Selected entered below particular column

C

Chirag R Raval

Member
Dear Sir,

I have a file ...(Attached)...
column "A" have many Buyer Nos...(Dynamic..Not fixed How many buyers)
require A User Form or In the place ..List Box ...
Each Unique Buyer as Selectable list...(Like Checkbox-in ListBox)
& Only Selected Buyer Numbers will enter below columns "U"

can it possible with Excel Macro Code?

Hope your Little Help..

Regards,
Chirag Raval
 

Attachments

  • UNIQUE BUYER LIST SELECTABLE-& SELECTED ENTER BELOW COLUMN U.xlsm.xlsx
    19.8 KB · Views: 9
Something like below...

In standard module...
Code: 
Public buyDic As Object

Sub FillDict()
Dim cel As Range
Set buyDic = CreateObject("Scripting.Dictionary")

For Each cel In Sheet1.Range("A2:A" & Sheet1.Cells(Rows.Count, "A").End(xlUp).Row)
    buyDic(cel.Value) = 1
Next
End Sub

Then in Userform Module... (Change control name as needed).
Code: 
Private Sub CmdBttn1_Click()
Dim iCtr As Long, lRow As Long
With Me.ListBox1
    For iCtr = 0 To .ListCount - 1
        If .Selected(iCtr) = True Then
            lRow = Sheet1.Cells(Rows.Count, "U").End(xlUp).Row + 1
            Sheet1.Cells(lRow, "U").Value = .List(iCtr)
        End If
    Next
End With
End Sub

Private Sub UserForm_Initialize()
If buyDic Is Nothing Then Call FillDict
With Me.ListBox1
    .List = Application.Transpose(buyDic.Keys)
    .MultiSelect = fmMultiSelectExtended
End With

End Sub
 
Dear Sir,

view

command button not work ..or may be I not know how to??.

I put in standard module (personel.xlsb) in new module
Code: 
Public buyDic As Object

Sub FillDict()
Dim cel As Range
Set buyDic = CreateObject("Scripting.Dictionary")

For Each cel In Sheet1.Range("A2:A" & Sheet1.Cells(Rows.Count, "A").End(xlUp).Row)
    buyDic(cel.Value) = 1
Next
End Sub

And in user form module..

Code: 
Private Sub CmdBttn1_Click()
Dim iCtr As Long, lRow As Long
With Me.ListBox1
    For iCtr = 0 To .ListCount - 1
        If .Selected(iCtr) = True Then
            lRow = Sheet1.Cells(Rows.Count, "U").End(xlUp).Row + 1
            Sheet1.Cells(lRow, "U").Value = .List(iCtr)
        End If
    Next
End With
End Sub

Private Sub UserForm_Initialize()
If buyDic Is Nothing Then Call FillDict
With Me.ListBox1
    .List = Application.Transpose(buyDic.Keys)
    .MultiSelect = fmMultiSelectExtended
End With

End Sub

though select through control key + Select multiple buyers..but after click
on command button. Nothing happen..

regards,
Chirag Raval
 
Dear Sir,

I also.. put Your code... in Standard Module of Sheet1

Code: 
Public buyDic As Object

Sub FillDict()
Dim cel As Range
Set buyDic = CreateObject("Scripting.Dictionary")

For Each cel In Sheet1.Range("A2:A" & Sheet1.Cells(Rows.Count, "A").End(xlUp).Row)
    buyDic(cel.Value) = 1
Next
End Sub

Though Select multiple buyers with control key & click on command button
nothing happen..

Regards,
Chirag Raval
 
Dear Sir,

Amazing....Really... Great....How to thanks ?...you really great help me in my work..where I am stuck.. I am start to apply this code & already use it in my macro...

but 2 points raised there...for make task more easy..

(1) can we put check box against each list entry of user form..so we must not
continues press control button for select multiple? so we can freely select
multiple entries just click in check box?

(2) can it be achieve in the place? without user form? in the sheet?
same like Excel's Built In Auto filter.?.like after apply auto filter excel
generate unique list & we can select multiple through check box .

hope you understand...& reply soon.

Regards,
Chirag Raval
 
1) Not so simple. What happens when there are enough unique in list to go beyond display (i.e. you need to scroll to see entire list)? You will need to dynamically create checkbox etc. I'd not recommend this approach. Though if in sheet, you can just use cell as checkbox.

2) Upload a sample of what you'd like to see as set up. Normally, I'd advise against using sheet where data is contained as form. It is in my opinion, best to keep data sheet and control sheet separated.
 
Dear Sir,

Okay...I can understand ..

User form method is best but without checkbox...due to dynamically insert this object against each entry can generate trouble in flow of macro..& may face problem in our work.. I also preferred ...list box that..we can select multiple by pressing control key...that's also fulfil requirement.. its okay..

but I have more question about list box. If, there are generate many buyers list, can list box automatically insert vertical scrollbar? and we can like start type & select buyer no like in built Excel's intelligence to reach that buyer no?

Another sheet (control sheet) method can do best..if each unique byes list
fall in sheet 2 or next blank sheet,( if not have blank sheet, auto generate by code itself.) if we can select multiple buyers as through next columns cell as use as checkbox. & finally command button press ..that selected buyers only fall under in data sheet's columns (U") (this is my macro's requirement...that multi buyer no present in under data sheet's column "U" so my macro run for each buyers under column "U"..

That's will be another chapter that we can modify my macro to direct connect with sheet 2 where multiple unique buyer selected..& after press command button of my macro run for each selected buyers..

any way ...I have now 2 option now

(1) your user form code run greatly. Just auto scroll bar require if list
have many unique buyers..
(2) control through next sheet method. Described as above..

can you provide that 2 options..

For option 2..I can not provide desired sample Because its up to you (As you like) & mostly it can be acceptable & perfect..If I want to work properly..
(if anything mismatch we can mentioned in this thread)

hope you little try for that 2 options..in same file you can use that I attached..
& again thanks for your great help..

Regards,

Chirag Raval
 
(1) your user form code run greatly. Just auto scroll bar require if list
have many unique buyers..
Click to expand...

Test it please. It already does this (one of the option in listbox properties).

(2) control through next sheet method. Described as above...
For option 2..I can not provide desired sample Because its up to you
Click to expand...

Sorry, but I won't work on it until there's mock up file. Please demonstrate your desired function in full. I'm not sure I get your need. What is it that list generated in column U supposed to do?
 
Dear Sir,

Thanks for you reply..

I will check user form's list box with vertical scroll bar..if more buyers in list..

I also attached file with requirement in sheet 2

if you can help about that files attached for module 2 ..which only requirement to run
buyer numbers below columns sheet 1's column "U2"..........if module 2's macro run for
sheet 2's checked or selected buyers.....then this thread meet solution...& fulfil its requirement..so user form not require (become option) for this process..

hope you can understand & hope your co-operations.

Regards,
Chirag Raval
 

Attachments

  • UNIQUE BUYER LIST SELECTABLE-& SELECTED ENTER BELOW COLUMN U.xlsm (1).xlsb
    30.4 KB · Views: 16
Last edited:
Ok, so you changed few things which should not have been changed from Narayan's set up.

Such as Criteria range Header, as well as Criteria range column reference in the code...

I've changed them back.

See attached.
 

Attachments

  • UNIQUE BUYER LIST SELECTABLE-& SELECTED ENTER BELOW COLUMN U.xlsm (1) (1).xlsb
    36.3 KB · Views: 23
Dear Sir,

Amazing ..thanks.. its working as desired...many thanks for
your this effort..

(1) can advance filter run for criteria in sheet2 ? or it's main requirement
"must have criteria in main datasheet?"

(2) daily new file of order status i have ..so i must put this 2 things in
Personnel.xlsb that always available for each time opening Excel..

is there any macro for 1 click &

on any file..can auto insert 1 blank sheet as control sheet in it? regardless how many sheets in file? & also Auto appear (insert) 2 Buttons
1 for get unique list & 1 for transfer checked list to sheet1 & run macro?

OR if can i put this 2 "Macro Caller Buttons" in Customized ribbon

(1) To insert new sheet & get unique list in sheet 2

(2) another button in ribbon to run macro for checked buyers

which way more flexible?

again many thanks for your great coding

hope-your co-operation..

Regards,
Chirag Raval
 
You must log in or register to reply here.
Back
Top