Listbox

[stewartd]

I have a spreadsheet, that when the combobox name is selected the range appears in the list box, and then if you double click the selected line, a userform opens with the name of the person updating it and "Yes" which is then placed in columns 6 and 7 of the listbox line, but is supposed to update the same line in the sheet1 (Cells), but currently I keep getting an error, I did a basic course of VB many years ago, but use it to do VBA, so I am relatively novice still, can anyone advise.

Private Sub CmdEnter_Click()
Dim DBS As Range
Dim IND, X As Integer
With UserForm1.ListBox1
.Column(7) = Me.TxtName
.Column(8) = Me.TxtConfirm

Set DBS = Sheets("Calls").Range("A10000").End(xlUp)
For X = 8 To 9

IND = .ListIndex + 2
Sheet1.Cells(IND, X) = .Column(X - 1)

DBS.Offset(1, X - 1) = .Column(X - 1)

Next X
End With

Unload Me
End Sub

 

[DanteAmor]

Hi and welcome to MrExcel.

when the combobox name is selected the range appears in the list box

I need to see all the code, even the code you use to populate the listbox. If you are going to update the data on the sheet, but in the listbox you loaded data by doing a filter, then not necessarily record 1 of the listbox corresponds to record 1 of the database.

 

[stewartd]

Hello, this is the code from both userforms.

Private Sub CmbKeyworker_Change()
    Dim X, y(), i As Long, ii As Integer, iii As Integer
    
    X = Sheets("Calls").Cells(1).CurrentRegion
    For i = 1 To UBound(X, 1)
        If X(i, 1) = CmbKeyworker Then
            ii = ii + 1: ReDim Preserve y(1 To 7, 1 To ii)
            For iii = 1 To 7
                y(iii, ii) = X(i, iii)
            Next
        End If
    Next
    If Not IsEmpty(y) Then
        ListBox1.List = Application.Transpose(y)
    Else
        MsgBox "No Incidents with a " & CmbKeyworker & " Status."
    End If
   
    With Me.ListBox1
        For i = 0 To .ListCount - 1
            .List(i, 3) = Format(.List(i, 3), "hh:mm")
        Next i
    End With

'    ListBox1.Clear
'Dim rngToSearch As Range
'    Dim rngToFind As Range
'    Dim valToFind As Variant
'    Dim arrClearList()
'
'    valToFind = CmbKeyworker.Value 'Edit ComboBox1 to your ComboBox name
'
'    With Worksheets("Calls")
'        Set rngToSearch = .Columns("A")
'    End With
'
'    Set rngToFind = rngToSearch.Find(What:=valToFind, _
'            LookIn:=xlFormulas, _
'            LookAt:=xlWhole, _
'            SearchOrder:=xlByRows, _
'            SearchDirection:=xlNext, _
'            MatchCase:=False)
'
'    If Not rngToFind Is Nothing Then
'
'        'Call ClearList(Me.ListBox1)     'Optional to clear existing list
'
'        ListBox1.AddItem
'
'        With ListBox1
'            .List(.ListCount - 1, 0) = rngToFind.Value  'ID Col A
'            .List(.ListCount - 1, 1) = rngToFind.Offset(0, 1).Value 'Agent Name Col B
'            .List(.ListCount - 1, 2) = rngToFind.Offset(0, 2).Value 'Address Col D
'            .List(.ListCount - 1, 3) = rngToFind.Offset(0, 4).Value 'Organization Col E
'            .List(.ListCount - 1, 4) = rngToFind.Offset(0, 5).Value 'Phone Col F
'            .List(.ListCount - 1, 5) = rngToFind.Offset(0, 6).Value 'Email col G
'        End With
'
'    Else
'        MsgBox valToFind & " not found in worksheet."
'    End If
End Sub

Private Sub CmbKeyworker1_Change()
    Dim X, y(), i As Long, ii As Integer, iii As Integer
    
    X = Sheets("Calls").Cells(1).CurrentRegion
    For i = 1 To UBound(X, 1)
        If X(i, 1) = CmbKeyworker1 Then
            ii = ii + 1: ReDim Preserve y(1 To 9, 1 To ii)
            For iii = 1 To 9
                y(iii, ii) = X(i, iii)
            Next
        End If
    Next
    If Not IsEmpty(y) Then
        ListBox1.List = Application.Transpose(y)
    Else
        MsgBox "No Incidents with a " & CmbKeyworker1 & " Status."
    End If
    With Me.ListBox1
        For i = 0 To .ListCount - 1
            .List(i, 3) = Format(.List(i, 3), "hh:mm")
        Next i
    End With
End Sub

Private Sub CmdAddData_Click()
    Call Module1.Data
End Sub

Private Sub ListBox1_Click()
Dim rngToSearch As Range
Dim rngToFind As Range
Dim valToFind As Variant

valToFind = ListBox1.Value 'Edit ListBox1 to your ListBox name
With Worksheets("Calls")
    Set rngToSearch = .Columns("A")
End With

    Set rngToFind = rngToSearch.Find(What:=valToFind, _
            LookIn:=xlFormulas, _
            LookAt:=xlWhole, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False)
          
    If Not rngToFind Is Nothing Then
        'The GoTo command is a one line operation to
        'Select a worksheet and then select a range
        Application.Goto rngToFind
    Else
        MsgBox valToFind & " not found in worksheet."
    End If
End Sub

Private Sub UserForm_Initialize()
    CmbKeyworker.Clear
    CmbPatient.Clear
'    ComboBox1.List = Application.WorksheetFunction.Transpose(Range("Keyworker"))
    CmbKeyworker.RowSource = "Keyworker"
    CmbKeyworker1.RowSource = "Keyworker"
   

'Dim LstRw As Long, Rw As Long
'    With Sheets("Calls")
'      LstRw = .Cells(Rows.Count, "A").End(xlUp).Row
'      For Rw = 2 To LstRw
'        Me.ComboBox1.AddItem (.Cells(Rw, "A"))
'      Next Rw
'    End With
   
'Dim LstRw1 As Long, Rw1 As Long
'    With Sheets("Calls")
'      LstRw1 = .Cells(Rows.Count, "B").End(xlUp).Row
'      For Rw1 = 2 To LstRw1
'        Me.ComboBox2.AddItem (.Cells(Rw1, "B"))
'      Next Rw1
'    End With
'    Range("A2", Range("A" & Rows.Count).End(xlUp)).Name = "Dynamic"
'    Me.ComboBox1.RowSource = "Dynamic"
   
    CmbPatient.RowSource = "Patient"
    TxtName.Value = Application.UserName
    TxtDate.Value = Now
    TxtDate.Value = Format(Date, ddmmyy)
    TxtTime.Value = Now
    TxtTime.Value = Format(Time, "hh:mm")
   
    CmbType.AddItem "Tasked"
    CmbType.AddItem "Emailed"
End Sub

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    UserForm2.Show
End Sub

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Sub UserForm_Initialize()
    TxtName.Value = Application.UserName
    TxtConfirm.Value = "Yes"
End Sub

Private Sub CmdEnter_Click()
Dim DBS As Range
Dim IND, X As Integer
    With UserForm1.ListBox1
    .Column(7) = Me.TxtName
    .Column(8) = Me.TxtConfirm
       
    Set DBS = Sheets("Calls").Range("A10000").End(xlUp)
    For X = 8 To 9
   
    IND = .ListIndex + 2
    Sheet1.Cells(IND, X) = .Column(X - 1)
   
    'DBS.Offset(1, X - 1) = .Column(X - 1)
   
    Next X
    End With

 

[Fluff]

@stewartd when posting code use code. I have added them for you this time. How to Post Your VBA Code

 

[DanteAmor]

@Fluff thanks.


@stewartd, I checked your code and it has an error when you select a keyworker and it only has one incident in the base.

Try the following code to update the listbox and also the "Calls" sheet.

This code in your userform1:

Private Sub CmbKeyworker_Change()
  Dim a As Variant, b As Variant, c As Variant
  Dim i As Long, j As Long, k As Long, lr As Long
 
  a = Sheets("Calls").Range("A1:I" & Sheets("Calls").Range("A" & Rows.Count).End(3).Row).Value
  ReDim b(1 To UBound(a, 1), 1 To UBound(a, 2) + 1)  'Extra column to identify the row
                                                     'of the record in the database.
 
  For i = 1 To UBound(a, 1)
    If a(i, 1) = CmbKeyworker.Value Then
      k = k + 1
      For j = 1 To UBound(b, 2) - 1
        b(k, j) = a(i, j)
      Next
      b(k, UBound(b, 2)) = i            'Store the row number
    End If
  Next
 
  If k > 0 Then
    ReDim c(1 To k, 1 To UBound(b, 2))
    For i = 1 To k
      For j = 1 To UBound(b, 2)
        If j = 4 Then
          c(i, j) = Format(b(i, j), "hh:mm")
        Else
          c(i, j) = b(i, j)
        End If
      Next
    Next
    ListBox1.List = c
  Else
    MsgBox "No Incidents with a " & CmbKeyworker & " Status."
  End If
End Sub

This code in your userform2:

Private Sub UserForm_Initialize()
  TxtName.Value = Application.UserName
  TxtConfirm.Value = "Yes"
End Sub

Private Sub CmdEnter_Click()
  Dim ind As Long
  With UserForm1.ListBox1
    .List(.ListIndex, 7) = Me.TxtName
    .List(.ListIndex, 8) = Me.TxtConfirm
    ind = .List(.ListIndex, 9)    'Gets the row number stored in column 9
    Sheets("Calls").Cells(ind, 8).Value = Me.TxtName
    Sheets("Calls").Cells(ind, 9).Value = Me.TxtConfirm
  End With
End Sub

 

[stewartd]

Thanks for your help, ... I copied the code and run but came to the following error. (Compile error, Invalid outside procedure)

 

[DanteAmor]

You didn't put this line in your code. Review well and copy all the code.
Private Sub CmbKeyworker_Change()

 

[stewartd]

Thanks so much, working perfectly.

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.