PDA

View Full Version : [SOLVED] Form Control Listbox help



siva.apps123
09-28-2017, 11:15 AM
Hello all..

I am new to VBA and this forum too.i am working on a report and i am able to send c,e fields from column a to column b by using active x controls but i dont know how do it by using"form controls".can someone tell that please .thank you very much




column a





column b











a





c



b





e



d








f











i am enclosing vba code i used...


Sub Workbook_Open()
With Sheet4.ListBox1
row_review = 0
Dim sheet As Worksheet
Set sheet = Sheets("Customization")
ListBox1.Clear
Do
DoEvents
row_review = row_review + 1
item_in_review = sheet.Range("D" & row_review)
If Len(item_in_review) > 0 Then ListBox1.AddItem (item_in_review)
Loop Until item_in_review = ""
End With
End Sub

Sub MoveAllLeft_Click()[/FONT][/COLOR]
Dim iCnt As Integer[/FONT][/COLOR]
'Move allItems from ListBox2 to ListBox1
For iCnt = 0 To Me.ListBox2.ListCount - 1
Me.ListBox1.AddItem Me.ListBox2.List(iCnt)
Next iCnt
'Clear ListBox1 After moving Items from ListBox1 to ListBox2
Me.ListBox2.Clear
End Sub

Sub MoveAllRight_Click()
Dim iCnt As Integer
'Move all Items from ListBox1 to ListBox2
For iCnt = 0 To Me.ListBox1.ListCount - 1
Me.ListBox2.AddItem Me.ListBox1.List(iCnt)
Next iCnt
'Clear ListBox1 After moving Items from ListBox1 to ListBox2
Me.ListBox1.Clear
End Sub

Sub MoveSelRight_Click()
Dim iCnt As Integer
'Move Selected Items from Listbox1 to Listbox2
For iCnt = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(iCnt) = True Then
Me.ListBox2.AddItem Me.ListBox1.List(iCnt)
End If
Next
For iCnt = Me.ListBox1.ListCount - 1 To 0 Step -1
If Me.ListBox1.Selected(iCnt) = True Then
Me.ListBox1.RemoveItem iCnt
End If
Next
End Sub

Sub MoveSelLeft_Click()
Dim iCnt As Integer
'Move Selected Items from Listbox1 to Listbox2
For iCnt = 0 To Me.ListBox2.ListCount - 1
If Me.ListBox2.Selected(iCnt) = True Then
Me.ListBox1.AddItem Me.ListBox2.List(iCnt)
End If
Next
For iCnt = Me.ListBox2.ListCount - 1 To 0 Step -1
If Me.ListBox2.Selected(iCnt) = True Then
Me.ListBox2.RemoveItem iCnt
End If
Next
End Sub

mdmackillop
09-28-2017, 11:49 AM
Post a sample workbook; Go Advanced / Manage Attachments

siva.apps123
09-28-2017, 12:17 PM
Hi

Thanks for your response.Attached worksheet.I sent lbr_cost,mkt_derived,drive_cd from list 1 to list 2 using "active x controls".But i need to use "form controls" as per management .i don't know that can you please tell thanks

mdmackillop
09-28-2017, 12:57 PM
Refer to post #2

SamT
09-28-2017, 01:38 PM
A ScreenShot is not a Workbook

siva.apps123
09-28-2017, 07:17 PM
understood. thank you both. here's the attachment.

Aussiebear
09-29-2017, 03:04 AM
VBA HELP ! URGENT..No worries.... I shall just step out on to the front porch and holler for a marshal....

Me: "Marshal!!!"
Marshal: "What is the problem son?"
Me: err.. don't know. The OP didn't exactly say"
Marshal: "Somebody done rob the bank?"
Me: ...."no"
Marshal: "Somebody done shot the fella?"
Me: ...."No"
Marshal: "Somebody rustle his cattle?"
Me: .... No, dont think so"
Marshal: "Has he been bit by a rattlesnake whilst gittin his neck streched by a lynch mob?"
Me: ... No, he didn't say that"
Marshal:" Well what did he say son?"
Me: "well... VBA Help! Urgent"
Marshal: (.....Sigh) Son, that could mean he's either dropped his spoon from the high chair .... or somebody's about to go to boot hill. Don't holler for me if you can't tell me the issue in a few short words"
Me: "I wish the OP could read this Marshal"

mdmackillop
09-29-2017, 05:43 AM
Thanks Ted. A great start to the day!

Refer here for useful code (https://wellsr.com/vba/2016/excel/complete-guide-to-excel-vba-form-control-listboxes/)

I should be able to convert the list 2 items like the "Priview sheet".
I don't know what this means.

SamT
09-29-2017, 07:54 AM
Copy all ActiveX Subs to a standard Module. Remove the "_Click" from the names.
With Form Controls, assign Subs in Standard Modules to the Controls. Edit Subs as needed

jolivanes
09-29-2017, 09:08 PM
BTW, the "Urgent" can be solved by going to the "Consulting Services" at the top.

mdmackillop
09-30-2017, 05:25 AM
Post retitled

Aussiebear
09-30-2017, 04:46 PM
Post retitled ..... redundancy. Oh well it served a point I guess.

siva.apps123
10-04-2017, 11:19 AM
thank you very much who helped me.