update listbox from 2 different sheets

[Patriot2879]

Hi good afternoon, hope you can help me please. I am trying to get ListBox2 to update when data is entered into a sheet called 'Raised' which is 'sh3 and a sheet called 'Outages' which is sh1, the first part of the code works for 'sh3' but the other part i added for 'sh1' doesn't work, i hope you can help me please?

Private Sub CommandButton8_Click()
    Dim i As Long, itm As Long
  
    Application.ScreenUpdating = False
  
    For i = 0 To Me.ListBox2.ListCount - 1
        If Me.ListBox2.Selected(i) Then
            itm = i + 2
            Exit For
        End If
    Next i
 
    sh3.Rows(itm).Copy
    sh2.Range("A" & lrB + 1).PasteSpecial xlValues
    sh3.Rows(itm).ClearContents
    Application.CutCopyMode = False
    sh3.Range("A1:J" & sh3.Range("A" & Rows.Count).End(xlUp).Row).Sort Key1:=sh3.Range("A1"), Order1:=xlAscending, Header:=xlYes
  
         End If
    Next i
    
    sh1.Rows(itm).Copy
    sh2.Range("A" & lrB + 1).PasteSpecial xlValues
    sh1.Rows(itm).ClearContents
    Application.CutCopyMode = False
    sh1.Range("A1:J" & sh1.Range("A" & Rows.Count).End(xlUp).Row).Sort Key1:=sh1.Range("A1"), Order1:=xlAscending, Header:=xlYes
  

    Application.ScreenUpdating = True

    ws1Rng
    ws2Rng
    ws3Rng
    

 

End Sub

 

[DanteAmor]

Hi @Patriot2879, I hope you are well too.

First, the macro you put in is not complete, so I'm going to assume everything.

1. If you only need the first selected item or to know the selected item, you do not need to do a loop:

    For i = 0 To Me.ListBox2.ListCount - 1
        If Me.ListBox2.Selected(i) Then
            itm = i + 2
            Exit For
        End If
    Next i

Only the following line:

itm = ListBox2.ListIndex + 2

2. If in both sheets you have the same number of records and the itm number selected in listbox2 corresponds to the same record in both sheets, then it is correct to copy the same row from both sheets, otherwise, then you will have to explain which row you want to copy from each sheet.

3. I assume that in the lrB variable you have the last row, in the code you put I don't see how you fill the variable, if you don't fill it then the value of the variable is 0.

4. If in lrB + 1 you are putting the data of Sh3, then in lrB + 2 you must put the data of Sh1...

    sh3.Rows(itm).Copy
    sh2.Range("A" & lrB + 1).PasteSpecial xlValues
    sh3.Rows(itm).ClearContents
    Application.CutCopyMode = False
    sh3.Range("A1:J" & sh3.Range("A" & Rows.Count).End(xlUp).Row).Sort Key1:=sh3.Range("A1"), Order1:=xlAscending, Header:=xlYes
  
         End If
    Next i
    
    sh1.Rows(itm).Copy
    sh2.Range("A" & lrB + 2).PasteSpecial xlValues
    sh1.Rows(itm).ClearContents

I hope that helps you a little, otherwise you will have to put the complete code and explain what you want to copy and where you want to paste it.

 

[Patriot2879]

Good morning Rich, thank you for your amazing feedback and help, i have attached my file into a drop box, as some of the code is linked in other places too, easier than explaining, i hope you can have a look at my file please and help, much appreciated for your time.

https://www.dropbox.com/scl/fi/x2wrtq03nmjj9tio80qnl/Complex-Inday-3-test.xlsm?rlkey=465lc3abcfmtag8j566hfyo9r&dl=0

 

[Patriot2879]

Hi good morning, hope you can help please, hope the Dropbox link is ok. Thank you

 

[Patriot2879]

Hi good morning hope you can still help me with this please?

 

[DanteAmor]

For security reasons I should not download files. As I mentioned, you should put all your code here and explain in detail.

Use the XL2BB tool to put examples of what you have on each sheet before the process, explain based on those examples what you want to copy and where you want to paste it. It is also necessary that you give an example of the result after the process.
Without that information, help is not possible, as I mentioned, I would be assuming everything.

 

[Patriot2879]

Hello good morning, please see all of the code below and a screen shot, when 'outages', 'replan', 'job search' and 'issue log' is clicked the data should go into listbox2, then once that is completed they click the complete button it should be then moved into listbox1, i hope this makes sense, i am hoping you can help me please?

Option Explicit

Dim sh1 As Worksheet, lrA As Long
Dim sh2 As Worksheet, lrB As Long
Dim sh3 As Worksheet, lrC As Long


Private Sub CommandButton10_Click()
Application.ScreenUpdating = 0
Unload Me
UserForm1.Show
Application.ScreenUpdating = 1

End Sub

Private Sub CommandButton11_Click()
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    ' Create the HTML body with a table
    Dim emailBody As String
    emailBody = "<html><body>"
    
    ' Add the "Hi there" text
    emailBody = emailBody & "<p>Hi there,</p>"

    ' Display ListBox values horizontally
    emailBody = emailBody & "<p><strong>Chasing:</strong></p>"
    emailBody = emailBody & "<p>"

    emailBody = emailBody & "<table border='1' cellpadding='5' cellspacing='0' style='border-collapse:collapse;'>"
    emailBody = emailBody & "<tr><td>" & Me.ListBox2.Column(0) & "</td></tr>"
    emailBody = emailBody & "<tr><td>" & Me.ListBox2.Column(1) & "</td></tr>"
    emailBody = emailBody & "<tr><td>" & Me.ListBox2.Column(2) & "</td></tr>"
    emailBody = emailBody & "<tr><td>" & Me.ListBox2.Column(3) & "</td></tr>"
    emailBody = emailBody & "<tr><td>" & Me.ListBox2.Column(4) & "</td></tr>"
    emailBody = emailBody & "<tr><td>" & Me.ListBox2.Column(5) & "</td></tr>"
    emailBody = emailBody & "</table>"

    ' Add the "Thank you" text
    emailBody = emailBody & "<p>Thank you,</p>"
    emailBody = emailBody & "<p>Complex Planning Team</p>"
    emailBody = emailBody & "</body></html>"

    With OutMail
        .To = "test@gmail.com"
        .CC = "esuk-complex@eonenergy.com"
        .subject = "In Day Chaser"
        .HTMLBody = emailBody
        .Display
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub


Private Sub CommandButton2_Click()
    Unload Me
End Sub

Private Sub CommandButton3_Click()
    UserForm2.Show
End Sub

Private Sub CommandButton4_Click()
    UserForm5.Show
End Sub

Private Sub CommandButton5_Click()
    UserForm4.Show
End Sub

Private Sub CommandButton6_Click()
    UserForm3.Show
End Sub

Private Sub CommandButton7_Click()
    UserForm8.Show
End Sub

Private Sub CommandButton9_Click()
    UserForm7.Show
End Sub

Private Sub CommandButton8_Click()
    Dim i As Long, itm As Long
  
    Application.ScreenUpdating = False
  
    For i = 0 To Me.ListBox2.ListCount - 1
        If Me.ListBox2.Selected(i) Then
            itm = i + 2
            Exit For
        End If
    Next i
 
    sh3.Rows(itm).Copy
    sh2.Range("A" & lrB + 1).PasteSpecial xlValues
    sh3.Rows(itm).ClearContents
    Application.CutCopyMode = False
    sh3.Range("A1:J" & sh3.Range("A" & Rows.Count).End(xlUp).Row).Sort Key1:=sh3.Range("A1"), Order1:=xlAscending, Header:=xlYes
  
         End If
    Next i
    
    sh1.Rows(itm).Copy
    sh2.Range("A" & lrB + 1).PasteSpecial xlValues
    sh1.Rows(itm).ClearContents
    Application.CutCopyMode = False
    sh1.Range("A1:J" & sh1.Range("A" & Rows.Count).End(xlUp).Row).Sort Key1:=sh1.Range("A1"), Order1:=xlAscending, Header:=xlYes
  

    Application.ScreenUpdating = True

    ws1Rng
    ws2Rng
    ws3Rng
    

 

End Sub

Private Sub ListBox1_Click()

End Sub

Private Sub ListBox2_Click()

End Sub

Private Sub TextBox6_Change()

End Sub

Private Sub UserForm_Initialize()
    Set sh1 = Sheets("Outages Data")
    Set sh2 = Sheets("Additional Job")
    Set sh3 = Sheets("Raised")
    ws1Rng
    ws2Rng
    ws3Rng
    
    UserForm1.TextBox2.Value = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "*CAG*")
    UserForm1.TextBox3.Value = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "*CC*")
    UserForm1.TextBox4.Value = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "*Additional Job*")
    UserForm1.TextBox5.Value = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "*Sickness*")
    UserForm1.TextBox6.Value = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "*Stores*")
    UserForm1.TextBox7.Value = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "*Vehicle Issues*")
    

TextBox8.Value = Format(Date, "dd/mm/yyyy")

End Sub


Sub ws1Rng()
    Dim rng1 As Range
 
    lrA = sh1.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    If lrA = 1 Then lrA = 2
    Set rng1 = sh1.Range("A2:J" & lrA)
 
    With ListBox2
      .ColumnCount = 10 'Set the column Amount
      .ColumnHeads = True
      .RowSource = rng1.Address(, , , 1) 'Fill the Listbox
    End With
End Sub




Sub ws2Rng()
    Dim rng2 As Range
 
    lrB = sh2.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    If lrB = 1 Then lrB = 2
    Set rng2 = sh2.Range("A2:J" & lrB)
 
    With ListBox1
      .ColumnCount = 10 'Set the column Amount
      .ColumnHeads = True
      .RowSource = rng2.Address(, , , 1) 'Fill the Listbox
    End With
End Sub

Sub ws3Rng()
    Dim rng1 As Range
 
    lrC = sh3.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    If lrC = 1 Then lrA = 2
    Set rng1 = sh3.Range("A2:J" & lrA)
 
    With ListBox2
      .ColumnCount = 10 'Set the column Amount
      .ColumnHeads = True
      .RowSource = rng1.Address(, , , 1) 'Fill the Listbox
    End With
End Sub

 

[Patriot2879]

Hello good morning, please see all of the code below and a screen shot, when 'outages', 'replan', 'job search' and 'issue log' is clicked the data should go into listbox2, then once that is completed they click the complete button it should be then moved into listbox1, i hope this makes sense, i am hoping you can help me please?

Option Explicit

Dim sh1 As Worksheet, lrA As Long
Dim sh2 As Worksheet, lrB As Long
Dim sh3 As Worksheet, lrC As Long


Private Sub CommandButton10_Click()
Application.ScreenUpdating = 0
Unload Me
UserForm1.Show
Application.ScreenUpdating = 1

End Sub

Private Sub CommandButton11_Click()
    Dim OutApp As Object
    Dim OutMail As Object

    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)

    ' Create the HTML body with a table
    Dim emailBody As String
    emailBody = "<html><body>"
   
    ' Add the "Hi there" text
    emailBody = emailBody & "<p>Hi there,</p>"

    ' Display ListBox values horizontally
    emailBody = emailBody & "<p><strong>Chasing:</strong></p>"
    emailBody = emailBody & "<p>"

    emailBody = emailBody & "<table border='1' cellpadding='5' cellspacing='0' style='border-collapse:collapse;'>"
    emailBody = emailBody & "<tr><td>" & Me.ListBox2.Column(0) & "</td></tr>"
    emailBody = emailBody & "<tr><td>" & Me.ListBox2.Column(1) & "</td></tr>"
    emailBody = emailBody & "<tr><td>" & Me.ListBox2.Column(2) & "</td></tr>"
    emailBody = emailBody & "<tr><td>" & Me.ListBox2.Column(3) & "</td></tr>"
    emailBody = emailBody & "<tr><td>" & Me.ListBox2.Column(4) & "</td></tr>"
    emailBody = emailBody & "<tr><td>" & Me.ListBox2.Column(5) & "</td></tr>"
    emailBody = emailBody & "</table>"

    ' Add the "Thank you" text
    emailBody = emailBody & "<p>Thank you,</p>"
    emailBody = emailBody & "<p>Complex Planning Team</p>"
    emailBody = emailBody & "</body></html>"

    With OutMail
        .To = "test@gmail.com"
        .CC = "esuk-complex@eonenergy.com"
        .subject = "In Day Chaser"
        .HTMLBody = emailBody
        .Display
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub


Private Sub CommandButton2_Click()
    Unload Me
End Sub

Private Sub CommandButton3_Click()
    UserForm2.Show
End Sub

Private Sub CommandButton4_Click()
    UserForm5.Show
End Sub

Private Sub CommandButton5_Click()
    UserForm4.Show
End Sub

Private Sub CommandButton6_Click()
    UserForm3.Show
End Sub

Private Sub CommandButton7_Click()
    UserForm8.Show
End Sub

Private Sub CommandButton9_Click()
    UserForm7.Show
End Sub

Private Sub CommandButton8_Click()
    Dim i As Long, itm As Long
 
    Application.ScreenUpdating = False
 
    For i = 0 To Me.ListBox2.ListCount - 1
        If Me.ListBox2.Selected(i) Then
            itm = i + 2
            Exit For
        End If
    Next i
 
    sh3.Rows(itm).Copy
    sh2.Range("A" & lrB + 1).PasteSpecial xlValues
    sh3.Rows(itm).ClearContents
    Application.CutCopyMode = False
    sh3.Range("A1:J" & sh3.Range("A" & Rows.Count).End(xlUp).Row).Sort Key1:=sh3.Range("A1"), Order1:=xlAscending, Header:=xlYes
 
         End If
    Next i
   
    sh1.Rows(itm).Copy
    sh2.Range("A" & lrB + 1).PasteSpecial xlValues
    sh1.Rows(itm).ClearContents
    Application.CutCopyMode = False
    sh1.Range("A1:J" & sh1.Range("A" & Rows.Count).End(xlUp).Row).Sort Key1:=sh1.Range("A1"), Order1:=xlAscending, Header:=xlYes
 

    Application.ScreenUpdating = True

    ws1Rng
    ws2Rng
    ws3Rng
   

 

End Sub

Private Sub ListBox1_Click()

End Sub

Private Sub ListBox2_Click()

End Sub

Private Sub TextBox6_Change()

End Sub

Private Sub UserForm_Initialize()
    Set sh1 = Sheets("Outages Data")
    Set sh2 = Sheets("Additional Job")
    Set sh3 = Sheets("Raised")
    ws1Rng
    ws2Rng
    ws3Rng
   
    UserForm1.TextBox2.Value = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "*CAG*")
    UserForm1.TextBox3.Value = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "*CC*")
    UserForm1.TextBox4.Value = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "*Additional Job*")
    UserForm1.TextBox5.Value = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "*Sickness*")
    UserForm1.TextBox6.Value = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "*Stores*")
    UserForm1.TextBox7.Value = Application.WorksheetFunction.CountIf(ThisWorkbook.Sheets("In Day VL").Range("A:A"), "*Vehicle Issues*")
   

TextBox8.Value = Format(Date, "dd/mm/yyyy")

End Sub


Sub ws1Rng()
    Dim rng1 As Range
 
    lrA = sh1.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    If lrA = 1 Then lrA = 2
    Set rng1 = sh1.Range("A2:J" & lrA)
 
    With ListBox2
      .ColumnCount = 10 'Set the column Amount
      .ColumnHeads = True
      .RowSource = rng1.Address(, , , 1) 'Fill the Listbox
    End With
End Sub




Sub ws2Rng()
    Dim rng2 As Range
 
    lrB = sh2.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    If lrB = 1 Then lrB = 2
    Set rng2 = sh2.Range("A2:J" & lrB)
 
    With ListBox1
      .ColumnCount = 10 'Set the column Amount
      .ColumnHeads = True
      .RowSource = rng2.Address(, , , 1) 'Fill the Listbox
    End With
End Sub

Sub ws3Rng()
    Dim rng1 As Range
 
    lrC = sh3.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    If lrC = 1 Then lrA = 2
    Set rng1 = sh3.Range("A2:J" & lrA)
 
    With ListBox2
      .ColumnCount = 10 'Set the column Amount
      .ColumnHeads = True
      .RowSource = rng1.Address(, , , 1) 'Fill the Listbox
    End With
End Sub

Hope this all makes sense and you can help please?

 

[DanteAmor]

Although I would like to continue helping, it is not possible, because there is not enough information, as I requested in previous posts:

explain what you want to copy and where you want to paste it

Use the XL2BB tool to put examples of what you have on each sheet before the process, explain based on those examples what you want to copy and where you want to paste it...

 

[Patriot2879]

Although I would like to continue helping, it is not possible, because there is not enough information, as I requested in previous posts:

Although I would like to continue helping, it is not possible, because there is not enough information, as I requested in previous posts:

Hi Good morning, Sorry i haven't explained very well i will try again, hope this all makes sense. I have a home screen which is 'UserForm1' , where 'ListBox2' is at the top in yellow. and 'ListBox1' is at the bottom in white. To the left of 'UserForm1' i have several command buttons. For Example when they click on 'Outages' please see screen shot outages, there is a form for them to fill in, once completed they click on 'Update' commandbutton and this data goes into sheet 'Outages Data' see screeshot. Then this data is viewed in 'ListBox2' then in 'ListBox2' they can click on each line and once that action has been completed they can click on the 'Completed' button to the right of listbox2' then this will get moved into sheet 'Additional Job' where it will also be viewed in 'ListBox1'.

For the 'Replan and Jobs' the form data once completed goes into sheet 'Raised' and once completed also gets moved into sheet 'Additional Job' where it gets viewed in 'ListBox1'.

Hope this all makes sense, and really hope you can still help me please.