update listbox from 2 different sheets

P

Patriot2879

Well-known Member
Joined
Feb 1, 2018
Messages
1,259
Office Version
  1. 2010
Platform
  1. Windows
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?
VBA Code: 
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
 
Sort by date Sort by votes
Patriot2879 said:
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.
Click to expand...

Patriot2879 said:
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.
Click to expand...
Hi Good morning, i hope i have added enough information now for help? please let me know if you require more? hope you can help me please as i am really stuck on this.
 
Upvote 0

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Hi this is all the code i believe below
VBA Code: 
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
  
    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
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
Data not copying fully into ListBox2
Replies
0
Views
321
Patriot2879
P
S
  • Question Question
VBA Copy/Paste from different workbooks
Replies
1
Views
385
NdNoviceHlp
NdNoviceHlp
S
  • Question Question
Copy a column value when the values match to multiple sheets of the workbook
Replies
1
Views
442
sofas
S
I
  • Solved
Simplify my working code please
Replies
7
Views
186
ipbr21054
I
G
  • Question Question
Stop sort after data is sorted with button
Replies
6
Views
160
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,814
Messages
6,181,123
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top