I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)

krunal123

Board Regular
Joined
Jun 26, 2020
Messages
177
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
New Userform.jpg




My Userform not working to my VBA coding As per below



Private Sub TextBox2_Change()

End Sub

Private Sub TextBox3_Change()

End Sub

Private Sub UserForm_Initialize()
' Initialize Sr. No. to 1 when the form loads
TextBox1.Text = 1

' Populate Combobox1 with Product Names from Table1
With ComboBox1
.Clear
.List = Worksheets("Sheet2").Range("Table1[Product Name]").Value
End With
End Sub

Private Sub Combobox1_Change()
' Populate Combobox2 with HSN Codes based on the selected Product Name
Dim i As Long
Dim arr As Variant
arr = Worksheets("Sheet2").Range("Table1").Value

ComboBox2.Clear
For i = LBound(arr) To UBound(arr)
If arr(i, 1) = ComboBox1.Value Then
ComboBox2.AddItem arr(i, 2)
End If
Next i
End Sub

Private Sub AddNew_Click()
' Validate input
If Trim(TextBox2.Text) = "" Then
MsgBox "Please enter a Quantity", vbExclamation
TextBox2.SetFocus
Exit Sub
End If

If Trim(TextBox3.Text) = "" Then
MsgBox "Please enter a Rate", vbExclamation
TextBox3.SetFocus
Exit Sub
End If

' Add data to ListBox1
With ListBox1
.AddItem
.List(.ListCount - 1, 0) = TextBox1.Text
.List(.ListCount - 1, 1) = ComboBox1.Value
.List(.ListCount - 1, 2) = ComboBox2.Value
.List(.ListCount - 1, 3) = TextBox2.Text
.List(.ListCount - 1, 4) = TextBox3.Text
End With

' Increment Sr. No.
TextBox1.Text = Val(TextBox1.Text) + 1

' Clear input fields
ComboBox1.Value = ""
ComboBox2.Value = ""
TextBox2.Text = ""
TextBox3.Text = ""
End Sub

Private Sub Delete_Click()
' Remove selected item from ListBox1
If ListBox1.ListIndex <> -1 Then
ListBox1.RemoveItem ListBox1.ListIndex
Else
MsgBox "Please select an item to delete", vbExclamation
End If
End Sub

Private Sub Update_Click()
' Transfer data from ListBox1 to Sheet1
Dim i As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

' Clear existing data
ws.Range("A10:E" & ws.Rows.Count).ClearContents

' Transfer data
For i = 0 To ListBox1.ListCount - 1
ws.Cells(10 + i, 1).Value = ListBox1.List(i, 0)
ws.Cells(10 + i, 2).Value = ListBox1.List(i, 1)
ws.Cells(10 + i, 3).Value = ListBox1.List(i, 2)
ws.Cells(10 + i, 4).Value = ListBox1.List(i, 3)
ws.Cells(10 + i, 5).Value = ListBox1.List(i, 4)
Next i
End Sub


NOTE:
i was design this userform, but i don't know how to write Step by step vba coding for this userform excel. structure of userform as per below:
TextBox1= "Sr. No." (Automatic generate serial number) Combobox1= "Product Name" Combobox2= "HSN Code" TextBox2= "Qty" (manual filled) TextBox3= "Rate" (manual filled)
Combobox2 are dependant on Combobox1 with link "Table1" on Sheet2 Combobox1 Range =Table1[Product Name] Combobox2 Range =Table1[HSN Code]
(Three Command Buttons) Command button 1 = "Add New" (add data To ListBox 1) Command button 2 ="Delete Command button 3 =" Update"
We need to add data to ListBox after when i click "update" to transfer data to Sheet1 to Range (A10: A)
Arrange VBA Coding ror my userform structure in excel
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
can't follow you unfortunately. To respond to the title of this topic, putting current listbox items to a sheet could be done with two lines of code

VBA Code:
ar = ListBox1.List
Sheets("yourSheet").Range("A1").Resize(UBound(ar) + 1, UBound(ar, 2) + 1) = ar
 
Upvote 0
can't follow you unfortunately. To respond to the title of this topic, putting current listbox items to a sheet could be done with two lines of code

VBA Code:
ar = ListBox1.List
Sheets("yourSheet").Range("A1").Resize(UBound(ar) + 1, UBound(ar, 2) + 1) = ar
Pleased do correction my vba coding
 
Upvote 0
Nope, can't follow you, sorry. This is just an example of how you should do it
 
Upvote 0

Forum statistics

Threads
1,224,465
Messages
6,178,822
Members
452,881
Latest member
motivationgyan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top