Patriot2879
Well-known Member
- Joined
- Feb 1, 2018
- Messages
- 1,259
- Office Version
- 2010
- Platform
- Windows
Hi good morning. Hope you are all well? Please can you help me, i have the code below which is not working fully, Listbox2 is not highlighting all the Rows from sheet 'Raised' it only shows the first row. Please can you help.
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 TextBox9_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
' Check if the Return key is pressed (KeyCode = 13)
If KeyCode = 13 Then
' Insert a newline character
TextBox9.Text = TextBox9.Text & vbCrLf
' Cancel the default behavior of the Return key
KeyCode = 0
End If
End Sub
Private Sub UserForm_Initialize()
Set sh1 = Sheets("Outages Data")
Set sh2 = Sheets("Additional Job")
Set sh3 = Sheets("Raised")
ws1Rng
ws2Rng
ws3Rng
' Load the value of the global variable into TextBox6
TextBox6.Value = storesCountGlobal
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
