PDA

View Full Version : Solved: Control listbox results whit a combobox.



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

Rob342
11-11-2011, 06:39 AM
I would create your accounts as a dymanic array and then you could pull in the data when the form initializes.

mikerickson
11-11-2011, 08:22 AM
I'd use a two column List box, driven by the ComboBox1_Change event to show the data from the chosen sheet in the ListBox.

In the attached, not the changed Userform_Initialize event and the ListBox1 and ComboBox1 Change events. (note also the addition of a Close button)

Private Sub ComboBox1_Change()
If ComboBox1.ListIndex <> -1 Then
ListBox1.Clear
With Sheets(ComboBox1.Text)
ListBox1.List = Range(.Cells(1, 2), .Cells(.Rows.Count, 1).End(xlUp)).Value
End With
End If
End Sub

Private Sub ListBox1_Change()
With ListBox1
If .ListIndex <> -1 Then
CommandButton2.Enabled = True
TextBox1.Text = .Value
TextBox2.Text = .Text
Else
CommandButton2.Enabled = False
TextBox1.Text = vbNullString
TextBox2.Text = vbNullString
End If
End With
End Sub

Private Sub CommandButton2_Click()
Sheets(ComboBox1.Text).Range("A1").Offset(ListBox1.ListIndex, 0).EntireRow.Delete
Call ComboBox1_Change
End Sub

Private Sub UserForm_Initialize()
For i = 2 To Sheets.Count
ComboBox1.AddItem Sheets(i).Name
Next i

With ListBox1
.ColumnCount = 2
.ColumnWidths = "60;"
.BoundColumn = 1
.TextColumn = 2
End With
CommandButton2.Enabled = False
ComboBox1.ListIndex = 0
End Sub

Petter120
11-11-2011, 01:29 PM
mikerickson: Exactly what i wanted it to do, thank you verry much :)

Best regards

Petter