Petter120
11-11-2011, 02:48 AM
Hi
I have a userform were i can add data from 2 textboxes to a specific sheet that i chose whit a combobox.
Everything seems to work fine whit that,, my question is:
- How can i get the combobox that i use to add data, to show all the data on the sheet in the listbox ? (if sheet2 is selected all the data on sheet2 displays in the listbox).
- I also want to beable to delete data trought the listbox.
Many tanx
Petter
Private Sub UserForm_Initialize()
For i = 2 To Sheets.Count
ComboBox1.AddItem Sheets(i).Name
Next i
End Sub
Private Sub CommandButton1_Click()
Dim ws As Worksheet, txtb As Control
Dim aCell As Range, iRow As Integer
If Len(Trim(TextBox1.Value)) = 0 Then
MsgBox "Please enter a Account Number"
TextBox1.SetFocus
Exit Sub
End If
If Len(Trim(TextBox2.Value)) = 0 Then
MsgBox "Please enter Item Name"
TextBox2.SetFocus
Exit Sub
End If
Set ws = Sheets(ComboBox1.Text)
Set aCell = ws.Columns(1).Find(What:=Trim(TextBox1.Value), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False)
If Not aCell Is Nothing Then
MsgBox "Account Already Exists"
Else
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ws.Cells(iRow, 1) = TextBox1.Value
ws.Cells(iRow, 2) = TextBox2.Value
End If
'~~> Clear All textboxes
For Each txtb In Me.Controls
If TypeName(txtb) = "TextBox" Then
txtb.Text = ""
End If
Next
End Sub
I have a userform were i can add data from 2 textboxes to a specific sheet that i chose whit a combobox.
Everything seems to work fine whit that,, my question is:
- How can i get the combobox that i use to add data, to show all the data on the sheet in the listbox ? (if sheet2 is selected all the data on sheet2 displays in the listbox).
- I also want to beable to delete data trought the listbox.
Many tanx
Petter
Private Sub UserForm_Initialize()
For i = 2 To Sheets.Count
ComboBox1.AddItem Sheets(i).Name
Next i
End Sub
Private Sub CommandButton1_Click()
Dim ws As Worksheet, txtb As Control
Dim aCell As Range, iRow As Integer
If Len(Trim(TextBox1.Value)) = 0 Then
MsgBox "Please enter a Account Number"
TextBox1.SetFocus
Exit Sub
End If
If Len(Trim(TextBox2.Value)) = 0 Then
MsgBox "Please enter Item Name"
TextBox2.SetFocus
Exit Sub
End If
Set ws = Sheets(ComboBox1.Text)
Set aCell = ws.Columns(1).Find(What:=Trim(TextBox1.Value), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False)
If Not aCell Is Nothing Then
MsgBox "Account Already Exists"
Else
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
ws.Cells(iRow, 1) = TextBox1.Value
ws.Cells(iRow, 2) = TextBox2.Value
End If
'~~> Clear All textboxes
For Each txtb In Me.Controls
If TypeName(txtb) = "TextBox" Then
txtb.Text = ""
End If
Next
End Sub