How to auto calculations column 3*4 and result reflect on next last column without using extra textBox…… Any idea vba coding please share…. Look like simple mini bill table userform (Columns : No. / Product name/ Item code /Qty / Rate.). I was try auto calculate multiplication Qty*Rate = show result to amount column, but not use TextBox for this, to next column fix calculations as per value…. In listbox.
Agar aapko VBA mein ListBox ke columns ke niche unka total dikhana hai, toh directly ListBox control mein yeh functionality nahi hoti, lekin aap isko manually handle kar sakte hain. Aap last row mein total add kar sakte hain, ya fir separate label ya textbox ka use karke total display kar sakte hain. Example: ListBox ke Last Row mein Total Dikhana Agar aap ListBox ke ek column ka total calculate karke usko last row mein dikhana chahte hain, toh aap is tarah se code likh sakte hain: Steps: ListBox mein values add karein. Ek loop use karke total calculate karein. Last row mein 'Total' display karein. Example VBA Code: Private Sub UserForm_Initialize() ' ListBox mein 3 columns set karna ListBox1.ColumnCount = 3 ListBox1.ColumnWidths = "50;100;70" ' Data add karna (ID, Name, Salary) ListBox1.AddItem "1, Abhishek, 5000" ListBox1.AddItem "2, Simran, 7000" ListBox1.AddItem "3, Kriti, 6500" ' Total calculate karna Dim totalSalary As Double Dim i As Integer For i = 0 To ListBox1.ListCount - 1 ' Salary (3rd column) ko total mein add karna totalSalary = totalSalary + CDbl(ListBox1.List(i, 2)) Next i ' Last row mein 'Total' add karna ListBox1.AddItem " , Total, " & totalSalary End Sub Explanation: 1. ListBox1.ColumnCount = 3 - ListBox mein 3 columns set karein. 2. AddItem ka use karke data add karein (ID, Name, Salary). 3. Ek loop use karke third column (Salary) ka total calculate karein. 4.Last row mein 'Total' aur calculated value add karein. Alternative Approach: Label ya TextBox mein Total Show karna Agar aap ListBox ke niche alag se Total show karna chahte hain, toh aap ek Label ya TextBox ka use kar sakte hain: Private Sub UserForm_Initialize() ' ListBox mein 3 columns set karna ListBox1.ColumnCount = 3 ListBox1.ColumnWidths = "50;100;70" ' Data add karna ListBox1.AddItem "1, Abhishek, 5000" ListBox1.AddItem "2, Simran, 7000" ListBox1.AddItem "3, Kriti, 6500" ' Total calculate karna Dim totalSalary As Double Dim i As Integer For i = 0 To ListBox1.ListCount - 1 totalSalary = totalSalary + CDbl(ListBox1.List(i, 2)) Next i ' Total ko label mein show karna Label1.Caption = "Total Salary: " & totalSalary End Sub Is code mein Label1 ko use karke total salary ko ListBox ke niche display karenge. Aap in dono approaches mein se apne use case ke hisaab se choose kar sakte hain.
ListBox aur ListView dono hi VBA mein alag-alag controls hote hain. Agar aapko ListBox mein ListView jaise display karna hai, toh yeh directly possible nahi hai, kyunki dono controls ke functionalities alag hain. Lekin aap ListBox mein multiple columns ya data ko structured format mein dikhane ke liye kuch steps follow kar sakte hain. Yeh steps aapko ListBox mein multi-column data dikhane ke liye madad karenge: 1. ListBox ki Properties Set karna ColumnCount property ko set karein jitne columns aapko chahiye. ColumnWidths set karein taaki har column ka size aap define kar sakein. Example VBA code: Private Sub UserForm_Initialize() ' ListBox mein 3 columns set karna ListBox1.ColumnCount = 3 ' Column ki width set karna (Pixel mein) ListBox1.ColumnWidths = "50;100;70" ' Data add karna ListBox1.AddItem "1, Abhishek, Programmer" ListBox1.AddItem "2, Simran, Developer" ListBox1.AddItem "3, Kriti, Designer" End Sub 2. Data Structured Format mein Dikhana Aap AddItem method ka use karke data ko structured format mein multiple columns mein display kar sakte hain. 3. Alternative: ListView Control ka Use Agar aapko ListView ki functionalities zaroori lagti hain (jaise sorting, icons, etc.), toh aap ListView control ka use kar sakte hain. Iske liye aapko Microsoft ListView Control ko toolbox mein add karna hoga. ListView Example: Private Sub UserForm_Initialize() ' Column headers add karna With ListView1 .View = lvwReport .ColumnHeaders.Add , , "ID", 50 .ColumnHeaders.Add , , "Name", 100 .ColumnHeaders.Add , , "Job", 70 ' Rows add karna Dim itm As ListItem Set itm = .ListItems.Add(, , "1") itm.SubItems(1) = "Abhishek" itm.SubItems(2) = "Programmer" Set itm = .ListItems.Add(, , "2") itm.SubItems(1) = "Simran" itm.SubItems(2) = "Developer" End With End Sub Is tarah se aap ListView ki functionalities ko use kar sakte hain VBA UserForm mein.
Aap VBA mein ListBox ke data ko Modify karne ke liye buttons ya doosre UserForm ka use kar sakte hain. Yeh ek practical aur important functionality hai jab aapko existing data ko update karna ho. Main aapko dono approaches (Buttons aur Second UserForm) ke liye step-by-step explain karunga. Approach 1: Modify ListBox Data Using Buttons Steps: 1.Data ko ListBox mein populate karein. 2.Ek button banayein "Edit" ke liye. 3.Jab user kisi row ko select kare, toh usi row ke data ko modify karne ke liye TextBox ya InputField mein dikhayein. 4.Modify karne ke baad "Save" button ka use karke data ko update karein. Example Code: 1. UserForm with ListBox, TextBox, and Buttons: ListBox: Data display ke liye. TextBox: Data modify karne ke liye. Buttons: Modify aur Save karne ke liye. Private Sub UserForm_Initialize() ' ListBox mein data add karna ListBox1.ColumnCount = 3 ListBox1.ColumnWidths = "50;100;70" ListBox1.AddItem "1, Abhishek, Programmer" ListBox1.AddItem "2, Simran, Developer" ListBox1.AddItem "3, Kriti, Designer" End Sub Private Sub ListBox1_Click() ' Jab user row select kare, data ko TextBox mein show karein TextBox1.Text = ListBox1.List(ListBox1.ListIndex, 1) ' Name TextBox2.Text = ListBox1.List(ListBox1.ListIndex, 2) ' Job End Sub Private Sub cmdModify_Click() ' Select ki hui row ke data ko modify karein TextBox ke through ListBox1.List(ListBox1.ListIndex, 1) = TextBox1.Text ListBox1.List(ListBox1.ListIndex, 2) = TextBox2.Text End Sub Explanation: 1.ListBox1 mein 3 columns set kiye jaate hain aur data add kiya jaata hai. 2.Jab user ListBox mein kisi item ko select karta hai, us data ko TextBox1 aur TextBox2 mein dikhaya jaata hai. 3.Jab user "Modify" button dabata hai, tab TextBox ka updated value ListBox mein save ho jaata hai. Approach 2: Modify ListBox Data Using a Second UserForm Is approach mein, jab user kisi row ko edit karna chahta hai, ek doosra UserForm khulega jisme woh data modify kar sakta hai. Uske baad "Save" button ke through woh modified data ListBox mein update ho jaayega. Steps: 1.Ek UserForm1 with ListBox. 2.Ek doosra UserForm2 with TextBox aur Buttons. 3.UserForm2 ko open kar ke selected row ka data usme display karein aur modify karein. Example Code: UserForm1 (Main Form with ListBox): Private Sub UserForm_Initialize() ' ListBox mein data add karna ListBox1.ColumnCount = 3 ListBox1.ColumnWidths = "50;100;70" ListBox1.AddItem "1, Abhishek, Programmer" ListBox1.AddItem "2, Simran, Developer" ListBox1.AddItem "3, Kriti, Designer" End Sub Private Sub cmdEdit_Click() ' Jab user edit kare, UserForm2 ko open karein If ListBox1.ListIndex -1 Then ' Select ki hui row ka data UserForm2 mein bhejein UserForm2.TextBox1.Text = ListBox1.List(ListBox1.ListIndex, 1) ' Name UserForm2.TextBox2.Text = ListBox1.List(ListBox1.ListIndex, 2) ' Job UserForm2.Show Else MsgBox "Please select a row to edit.", vbExclamation End If End Sub UserForm2 (Second Form for Editing): Private Sub cmdSave_Click() ' Modified data ko UserForm1 ke ListBox mein save karna With UserForm1.ListBox1 .List(.ListIndex, 1) = TextBox1.Text ' Name .List(.ListIndex, 2) = TextBox2.Text ' Job End With Unload Me ' UserForm2 ko close karna End Sub Explanation: 1.UserForm1 mein jab user kisi row ko select karke "Edit" button click karta hai, toh UserForm2 open hoti hai. 2.UserForm2 mein selected data TextBox mein aata hai jahan se user usko modify karta hai. 3.Jab user "Save" karta hai, toh updated data wapas UserForm1 ke ListBox mein save ho jaata hai.
How to auto calculations column 3*4 and result reflect on next last column without using extra textBox……
Any idea vba coding please share….
Look like simple mini bill table userform
(Columns : No. / Product name/ Item code /Qty / Rate.).
I was try auto calculate multiplication Qty*Rate = show result to amount column, but not use TextBox for this, to next column fix calculations as per value…. In listbox.
Listbox me column ke niche lastme uska total kese show kare
Agar aapko VBA mein ListBox ke columns ke niche unka total dikhana hai, toh directly ListBox control mein yeh functionality nahi hoti, lekin aap isko manually handle kar sakte hain. Aap last row mein total add kar sakte hain, ya fir separate label ya textbox ka use karke total display kar sakte hain.
Example: ListBox ke Last Row mein Total Dikhana
Agar aap ListBox ke ek column ka total calculate karke usko last row mein dikhana chahte hain, toh aap is tarah se code likh sakte hain:
Steps:
ListBox mein values add karein.
Ek loop use karke total calculate karein.
Last row mein 'Total' display karein.
Example VBA Code:
Private Sub UserForm_Initialize()
' ListBox mein 3 columns set karna
ListBox1.ColumnCount = 3
ListBox1.ColumnWidths = "50;100;70"
' Data add karna (ID, Name, Salary)
ListBox1.AddItem "1, Abhishek, 5000"
ListBox1.AddItem "2, Simran, 7000"
ListBox1.AddItem "3, Kriti, 6500"
' Total calculate karna
Dim totalSalary As Double
Dim i As Integer
For i = 0 To ListBox1.ListCount - 1
' Salary (3rd column) ko total mein add karna
totalSalary = totalSalary + CDbl(ListBox1.List(i, 2))
Next i
' Last row mein 'Total' add karna
ListBox1.AddItem " , Total, " & totalSalary
End Sub
Explanation:
1. ListBox1.ColumnCount = 3 - ListBox mein 3 columns set karein.
2. AddItem ka use karke data add karein (ID, Name, Salary).
3. Ek loop use karke third column (Salary) ka total calculate karein.
4.Last row mein 'Total' aur calculated value add karein.
Alternative Approach: Label ya TextBox mein Total Show karna
Agar aap ListBox ke niche alag se Total show karna chahte hain, toh aap ek Label ya TextBox ka use kar sakte hain:
Private Sub UserForm_Initialize()
' ListBox mein 3 columns set karna
ListBox1.ColumnCount = 3
ListBox1.ColumnWidths = "50;100;70"
' Data add karna
ListBox1.AddItem "1, Abhishek, 5000"
ListBox1.AddItem "2, Simran, 7000"
ListBox1.AddItem "3, Kriti, 6500"
' Total calculate karna
Dim totalSalary As Double
Dim i As Integer
For i = 0 To ListBox1.ListCount - 1
totalSalary = totalSalary + CDbl(ListBox1.List(i, 2))
Next i
' Total ko label mein show karna
Label1.Caption = "Total Salary: " & totalSalary
End Sub
Is code mein Label1 ko use karke total salary ko ListBox ke niche display karenge.
Aap in dono approaches mein se apne use case ke hisaab se choose kar sakte hain.
Listbox me listview kese banaye
ListBox aur ListView dono hi VBA mein alag-alag controls hote hain. Agar aapko ListBox mein ListView jaise display karna hai, toh yeh directly possible nahi hai, kyunki dono controls ke functionalities alag hain. Lekin aap ListBox mein multiple columns ya data ko structured format mein dikhane ke liye kuch steps follow kar sakte hain.
Yeh steps aapko ListBox mein multi-column data dikhane ke liye madad karenge:
1. ListBox ki Properties Set karna
ColumnCount property ko set karein jitne columns aapko chahiye.
ColumnWidths set karein taaki har column ka size aap define kar sakein.
Example VBA code:
Private Sub UserForm_Initialize()
' ListBox mein 3 columns set karna
ListBox1.ColumnCount = 3
' Column ki width set karna (Pixel mein)
ListBox1.ColumnWidths = "50;100;70"
' Data add karna
ListBox1.AddItem "1, Abhishek, Programmer"
ListBox1.AddItem "2, Simran, Developer"
ListBox1.AddItem "3, Kriti, Designer"
End Sub
2. Data Structured Format mein Dikhana
Aap AddItem method ka use karke data ko structured format mein multiple columns mein display kar sakte hain.
3. Alternative: ListView Control ka Use
Agar aapko ListView ki functionalities zaroori lagti hain (jaise sorting, icons, etc.), toh aap ListView control ka use kar sakte hain. Iske liye aapko Microsoft ListView Control ko toolbox mein add karna hoga.
ListView Example:
Private Sub UserForm_Initialize()
' Column headers add karna
With ListView1
.View = lvwReport
.ColumnHeaders.Add , , "ID", 50
.ColumnHeaders.Add , , "Name", 100
.ColumnHeaders.Add , , "Job", 70
' Rows add karna
Dim itm As ListItem
Set itm = .ListItems.Add(, , "1")
itm.SubItems(1) = "Abhishek"
itm.SubItems(2) = "Programmer"
Set itm = .ListItems.Add(, , "2")
itm.SubItems(1) = "Simran"
itm.SubItems(2) = "Developer"
End With
End Sub
Is tarah se aap ListView ki functionalities ko use kar sakte hain VBA UserForm mein.
Listbox me data ko kese kare modify buttons se ya fir second Userform se ? Please help for important query
Aap VBA mein ListBox ke data ko Modify karne ke liye buttons ya doosre UserForm ka use kar sakte hain. Yeh ek practical aur important functionality hai jab aapko existing data ko update karna ho. Main aapko dono approaches (Buttons aur Second UserForm) ke liye step-by-step explain karunga.
Approach 1: Modify ListBox Data Using Buttons
Steps:
1.Data ko ListBox mein populate karein.
2.Ek button banayein "Edit" ke liye.
3.Jab user kisi row ko select kare, toh usi row ke data ko modify karne ke liye TextBox ya InputField mein dikhayein.
4.Modify karne ke baad "Save" button ka use karke data ko update karein.
Example Code:
1. UserForm with ListBox, TextBox, and Buttons:
ListBox: Data display ke liye.
TextBox: Data modify karne ke liye.
Buttons: Modify aur Save karne ke liye.
Private Sub UserForm_Initialize()
' ListBox mein data add karna
ListBox1.ColumnCount = 3
ListBox1.ColumnWidths = "50;100;70"
ListBox1.AddItem "1, Abhishek, Programmer"
ListBox1.AddItem "2, Simran, Developer"
ListBox1.AddItem "3, Kriti, Designer"
End Sub
Private Sub ListBox1_Click()
' Jab user row select kare, data ko TextBox mein show karein
TextBox1.Text = ListBox1.List(ListBox1.ListIndex, 1) ' Name
TextBox2.Text = ListBox1.List(ListBox1.ListIndex, 2) ' Job
End Sub
Private Sub cmdModify_Click()
' Select ki hui row ke data ko modify karein TextBox ke through
ListBox1.List(ListBox1.ListIndex, 1) = TextBox1.Text
ListBox1.List(ListBox1.ListIndex, 2) = TextBox2.Text
End Sub
Explanation:
1.ListBox1 mein 3 columns set kiye jaate hain aur data add kiya jaata hai.
2.Jab user ListBox mein kisi item ko select karta hai, us data ko TextBox1 aur TextBox2 mein dikhaya jaata hai.
3.Jab user "Modify" button dabata hai, tab TextBox ka updated value ListBox mein save ho jaata hai.
Approach 2: Modify ListBox Data Using a Second UserForm
Is approach mein, jab user kisi row ko edit karna chahta hai, ek doosra UserForm khulega jisme woh data modify kar sakta hai. Uske baad "Save" button ke through woh modified data ListBox mein update ho jaayega.
Steps:
1.Ek UserForm1 with ListBox.
2.Ek doosra UserForm2 with TextBox aur Buttons.
3.UserForm2 ko open kar ke selected row ka data usme display karein aur modify karein.
Example Code:
UserForm1 (Main Form with ListBox):
Private Sub UserForm_Initialize()
' ListBox mein data add karna
ListBox1.ColumnCount = 3
ListBox1.ColumnWidths = "50;100;70"
ListBox1.AddItem "1, Abhishek, Programmer"
ListBox1.AddItem "2, Simran, Developer"
ListBox1.AddItem "3, Kriti, Designer"
End Sub
Private Sub cmdEdit_Click()
' Jab user edit kare, UserForm2 ko open karein
If ListBox1.ListIndex -1 Then
' Select ki hui row ka data UserForm2 mein bhejein
UserForm2.TextBox1.Text = ListBox1.List(ListBox1.ListIndex, 1) ' Name
UserForm2.TextBox2.Text = ListBox1.List(ListBox1.ListIndex, 2) ' Job
UserForm2.Show
Else
MsgBox "Please select a row to edit.", vbExclamation
End If
End Sub
UserForm2 (Second Form for Editing):
Private Sub cmdSave_Click()
' Modified data ko UserForm1 ke ListBox mein save karna
With UserForm1.ListBox1
.List(.ListIndex, 1) = TextBox1.Text ' Name
.List(.ListIndex, 2) = TextBox2.Text ' Job
End With
Unload Me ' UserForm2 ko close karna
End Sub
Explanation:
1.UserForm1 mein jab user kisi row ko select karke "Edit" button click karta hai, toh UserForm2 open hoti hai.
2.UserForm2 mein selected data TextBox mein aata hai jahan se user usko modify karta hai.
3.Jab user "Save" karta hai, toh updated data wapas UserForm1 ke ListBox mein save ho jaata hai.