"database form": populate textbox with value based on listbox selection.

[ovp87]

Hi,

I recently found an awesome userform made my Roy Cox. "Database Form"

What im trying to accomplish is to have on listbox display dataset from a sheet (so far so good). However as there are more than 10 columns of data i soon discovered the limit when using additem.

Ive since realized that i dont actually want to populate the listbox with so many columns of data, what i do want is to populate textboxes in the same userform with values from the same row as the listbox selection. (ie. if A1:C1 is shown in the listbox and selected then textbox1.value = D1).

Being quite new at VB i would appreciate if anyone can help me out. (I dont mind trying to learn on my own, but so far hours of experimenting has failed miserably).

Code as is:

---------------------------------------------------------------------------------------
' Module : Database Form
' DateTime : 31/08/2005 10:55. Updatede 08-02-08
' Author : Roy Cox
' Purpose : Data entry form for Excel, with Search facility
'---------------------------------------------------------------------------------------

Private Sub cmbFind_Click()
    Dim strFind As String    'what to find
    Dim FirstAddress As String
    Dim rSearch As Range  'range to search
    Dim f      As Integer
    Sheets("samtaler").Activate
    Set rSearch = ActiveSheet.Range("a6", Range("a65536").End(xlUp))
    imgFolder = ThisWorkbook.Path & Application.PathSeparator & "images" & Application.PathSeparator
    strFind = Me.TextBoxcoachet.Value    'what to look for
    With rSearch
    Sheets("samtaler").Activate
        Set c = .Find(strFind, LookIn:=xlValues)
        If Not c Is Nothing Then    'found it
            c.Select
            With Me    'load entry to form
                .textboxdato.Value = c.Offset(0, 1).Value
                .TextBoxtype.Value = c.Offset(0, 4).Value
                .TextBoxnotat.Value = c.Offset(0, 28).Value
                .cmbAmend.Enabled = True     'allow amendment or
                .cmbDelete.Enabled = True    'allow record deletion
                .cmbadd.Enabled = False      'don't want to duplicate record
            End With
            FirstAddress = c.Address
            Do
                f = f + 1    'count number of matching records
                Set c = .FindNext(c)
            Loop While Not c Is Nothing And c.Address <> FirstAddress
            If f > 1 Then
                        FindAll
                        Else
                        
                        'do nothing
            
                Me.Height = frmMax
            End If
        Else: MsgBox strFind & " not listed"    'search failed
        End If
    End With
    If ActiveSheet.AutoFilterMode Then ActiveSheet.Range("A8").AutoFilter
End Sub

Sub FindAll()
Dim strFind As String 'what to find
Dim rFilter As Range 'range to search
Dim c As Range, a() As String, n As Long, i As Long
Sheets("samtaler").Activate
Set rFilter = ActiveSheet.Range("a7", Range("f65536").End(xlUp))
Set rng = ActiveSheet.Range("a7", Range("a65536").End(xlUp))
strFind = Me.TextBoxcoachet.Value
With ActiveSheet
    If Not .AutoFilterMode Then .Range("A7").AutoFilter
    rFilter.AutoFilter Field:=1, Criteria1:=strFind & "*"
    Set rng = rng.Cells.SpecialCells(xlCellTypeVisible)
    Me.ListBox1.Clear
    For Each c In rng
        n = n + 1: ReDim Preserve a(1 To 31, 1 To n)
        For i = 1 To 31
            a(i, n) = c.Offset(, i).Value
        Next
    Next
End With
If n > 0 Then Me.ListBox1.Column = a
End Sub

Private Sub ListBox1_Click()
ClearControls
If Me.ListBox1.ListIndex = -1 Then    'not selected
        MsgBox " No selection made"
    ElseIf Me.ListBox1.ListIndex >= 1 Then    'User has selected
        r = Me.ListBox1.ListIndex
        With Me
            .textboxdato.Value = ListBox1.List(r, 1)
            .TextBoxtype.Value = ListBox1.List(r, 2)
            .TextBoxnotat.Value = ListBox1.List(r, 3)
            .cmbAmend.Enabled = True      'allow amendment or
            .cmbDelete.Enabled = True     'allow record deletion
            .cmbadd.Enabled = False       'don't want duplicate
        
        End With
    End If
End Sub

Private Sub UserForm_Initialize()
Sheets("samtaler").Activate
Set MyData = ActiveSheet.Range("a5").CurrentRegion   'database
End Sub
Sub ClearControls()
    With Me
        For Each oCtrl In .Controls
            Select Case TypeName(oCtrl)
                Case "TextBox": oCtrl.Value = Empty
                Case "OptionButton": oCtrl.Value = False
            End Select
        Next oCtrl
    End With
End Sub

So to clarify, i need to listbox click code to add value to more textboxes than the listbox can actually support with current methods.

Thanks in advande, and Merry Christmas!

 

[ovp87]

[solved] Re: "database form": populate textbox with value based on listbox selection.

only needed another hour apprently, finaly got it..

Merry christmas!

 

[shandy367]

Re: [solved] Re: "database form": populate textbox with value based on listbox selection.

Hi ovp87,

do u mind to share how you got it?
cause i am trying to do similar form as well.

many thanks~~~