Selected Answer
Hi again Abdo
Firstly, your file uses the named range "NAMES" (in column J of worksheet "CUSTOMERS") to set the RowSource property of ComboBox1. That can lead to list values which do not exist in the file.
Accordingly, I've removed that property and added this code to get the names of only those sheets which do exist:
Private Sub UserForm_Initialize()
Dim ws As Worksheet
' loop through sheets
For Each ws In ThisWorkbook.Worksheets
' unless it's the named sheet below...
If ws.Name <> "CUSTOMERS" Then
' add sheet name to ComboBox1
Me.ComboBox1.AddItem ws.Name
End If
Next ws
End Sub
While that should reduce errors, a user could alter a chosen value (e,g, add a number to the end) so I've still tested for that in the revised code below (commented to help):
Private Sub ComboBox1_Change()
Dim ws As Worksheet
Dim LastRow As Long
Dim wsExists As Variant
' check value has not been editted
On Error Resume Next
wsExists = Evaluate("IsRef(" & Me.ComboBox1.Value & "!A1)")
If wsExists <> True Then
' if so, give message in TB1, make yellow and quit
Me.TextBox1.Value = "Pick a valid sheet"
Me.TextBox1.BackColor = vbYellow
Exit Sub
Else
Me.TextBox1.BackColor = vbWhite
End If
' if not, get value from row E of chosen sheet
Set ws = Worksheets(Me.ComboBox1.Value)
LastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
Me.TextBox1.Value = ws.Cells(LastRow, "E")
End Sub
That code is in the revised file attached.
Hope this fixes your problem - if so, please remember to mark this Answer as Selected.
