CARA memasukkan data TextBox Ke dalam ListBox

memasukkan data TextBox Ke dalam ListBox

Private Sub CommandButton1_Click()

With ListBox1

.AddItem

.List(.ListCount - 1, 0) = TextBox1.Value

.List(.ListCount - 1, 1) = TextBox2.Value

.List(.ListCount - 1, 2) = TextBox3.Value

.List(.ListCount - 1, 3) = TextBox4.Value

End With

End Sub

Cara memasukan isi List Box ke Textbox pada Vba excel.

ini merupakan kode Cara memasukan isi List Box ke Textbox
pada Vba excel.

Private Sub listBox1_Click()
TextBox1.Value = Listbox1.List(listBox1.ListIndex, 1)
TextBox2.Value = Listbox1.List(listBox1.ListIndex, 2)
TextBox3.Value = Listbox1.List(listBox1.ListIndex, 3)
End Sub

Private Sub listBox1_Click()
'masukan teks dalam listbox kolom pertama ke dalam
texbox1
TextBox1.Value = Listbox1.List(listBox1.ListIndex, 1)
'masukan teks dalam listbox kolom kedua ke dalam
texbox2
TextBox2.Value = Listbox1.List(listBox1.ListIndex, 2)
'masukan teks dalam listbox kolom ketiga ke dalam
texbox3
TextBox3.Value = Listbox1.List(listBox1.ListIndex, 3)
End Sub

Menampilkan Data Worksheet Ke dalam ListBox

Menampilkan Data Worksheet Ke dalam ListBox

Sub TampilkanSemua()

Set wsDtbsPlgn = Sheets("Data")

ListBox1.Clear

ListBox1.ColumnCount = 6

ListBox1.ColumnWidths = 80 & ";" & 25 & ";" &

35 & ";" & 65 & ";" & 120

Set rgTampil = wsDtbsPlgn.Range

("A1:A100").SpecialCells(xlCellTypeVisible)

For Each i In rgTampil

With ListBox1

.AddItem

.List(.ListCount - 1, 0) = i.Value

.List(.ListCount - 1, 1) = i.Offset(0, 1).Value

.List(.ListCount - 1, 2) = i.Offset(0, 2).Value

.List(.ListCount - 1, 3) = i.Offset(0, 3).Value

.List(.ListCount - 1, 4) = i.Offset(0, 4).Value

End With

Next i

End Sub

MACAM MACAM INPUT DATA VBA EXCEL

MACAM MACAM INPUT DATA VBA EXCEL

1. Input data hanya 1 sheet

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets(“Barang”)
‘menemukan baris kosong pada database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
‘check untuk sebuah kode
If Trim(TextBox1.Value) = “” Then
TextBox1.SetFocus
MsgBox “Masukan Kode Barang”
Exit Sub
End If
‘copy data ke database
ws.Cells(iRow, 1).Value = TextBox1.Value
ws.Cells(iRow, 2).Value = TextBox2.Value
ws.Cells(iRow, 3).Value = TextBox3.Value
ws.Cells(iRow, 4).Value = TextBox4.Value
‘clear data
TextBox1.Value = “”
TextBox2.Value = “”
TextBox3.Value = “”
TextBox4.Value = “”
TextBox1.SetFocus
END SUB


2. input data berayarat manual sesuai nama sheet
yaitu tambahkan kode pada tombo pesan
Private Sub CommandButton1_Click()
Set ws = Worksheets("BUAH")
Set ws2 = Worksheets("KUE")
Set ws3 = Worksheets("PERMEN")
Set ws4 = Worksheets("NASI")

'menemukan baris kosong pada database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
iRow2 = ws2.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
iRow3 = ws3.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
iRow4 = ws4.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check untuk sebuah kode
If Trim(Me.TextBox1.Value) = "" Then
Me.TextBox1.SetFocus
MsgBox "Masukan Kode Pembeli"
Exit Sub
End If
'copy data ke database
If ComboBox1.Value = "BUAH" Then
With ws
ws.Cells(iRow, 1).Value = Me.TextBox1.Value
ws.Cells(iRow, 2).Value = Me.TextBox2.Value
End With
ElseIf ComboBox1.Value = "KUE" Then
With ws2
ws2.Cells(iRow2, 1).Value = Me.TextBox1.Value
ws2.Cells(iRow2, 2).Value = Me.TextBox2.Value
End With
ElseIf ComboBox1.Value = "PERMEN" Then
With ws3
ws3.Cells(iRow3, 1).Value = Me.TextBox1.Value
ws3.Cells(iRow3, 2).Value = Me.TextBox2.Value
End With
ElseIf ComboBox1.Value = "NASI" Then
With ws
ws4.Cells(iRow4, 1).Value = Me.TextBox1.Value
ws4.Cells(iRow4, 2).Value = Me.TextBox2.Value
End With

End If

'clear data
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox1.SetFocus
MsgBox "DATA TELAH DI SIMPAN"
End Sub

3.A. Input data berdasarkan kolom pencarian 1
Dim Kode
Dim CellTujuan As Range
Kode = TextBox1.Value
Set CellTujuan = Range("C3:ZZ3").Find(What:=Kode,
LookIn:=xlValues, LookAt:=xlWhole)
If Not CellTujuan Is Nothing Then
TextBox2 = Cells(4, CellTujuan.Column)
End If
iRow = ws.Cells(Rows.Count, CellTujuan.Column) _
.End(xlUp).Offset(1, CellTujuan.Column).Row
ws.Cells(iRow, CellTujuan.Column).Value = Textbox2.Value

3.B.Input data berdasarkan kolom pencarian 2
Dim x As Range
For Each x In Range("A1:Z100")
If x.Value = CStr(TextBox1.Value) Then
TextBox2.Value = x.Offset(1, 0).Value
End If
Next
iRow = ws.Cells(Rows.Count, x.Column) _
.End(xlUp).Offset(1, x.Column).Row
ws.Cells(iRow, x.Column).Value = Textbox2.Value

4. Input data KEDALAM SHEET, NAMA SHEET SESUAI TEXTBOX

Dim iRow As Long
Dim ws As Worksheet
nama = TextBulan.value
Set ws = Worksheets(nama)
‘menemukan baris kosong pada database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
‘check untuk sebuah kode
If Trim(TextBox1.Value) = “” Then
TextBox1.SetFocus
MsgBox “Masukan Kode Barang”
Exit Sub
End If
‘copy data ke database
ws.Cells(iRow, 1).Value = TextBox1.Value
ws.Cells(iRow, 2).Value = TextBox2.Value
ws.Cells(iRow, 3).Value = TextBox3.Value
ws.Cells(iRow, 4).Value = TextBox4.Value
‘clear data
TextBox1.Value = “”
TextBox2.Value = “”
TextBox3.Value = “”
TextBox4.Value = “”
TextBox1.SetFocus
END SUB