I have the following code but when I run it from the form input, it jumps to the Lookuplist sheet when it sorts and back to saveas sheet tab after. the code work, but I don't want to see the jumping from one sheet tab to another. I just want it to stay on saveas tab. If I take out The first part which is the sort part it won't flicker to the LookupList. Any suggestions
Sheets("LookupLists").Select
Columns("E:E").Select
ActiveWorkbook.Worksheets("LookupLists").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("LookupLists").Sort.SortFields.Add Key:=Range( _
"E:E"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("LookupLists").Sort
.SetRange Range("E:H")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("SaveAs").Select
Here is the whole code:
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
'check for a Item number
If Trim(Me.txtItem.Value) = "" Then
Me.txtItem.SetFocus
MsgBox "Please enter a Item number"
Exit Sub
End If
'check for a Discription
If Trim(Me.txtDisc.Value) = "" Then
Me.txtDisc.SetFocus
MsgBox "Please enter Full discription of item"
Exit Sub
End If
'check for Case count
If Trim(Me.txtCaseQty.Value) = "" Then
Me.txtCaseQty.SetFocus
MsgBox "Please enter # of Cases per dispenser"
Exit Sub
End If
'check for CUP count
If Trim(Me.txtCupQty.Value) = "" Then
Me.txtCupQty.SetFocus
MsgBox "Please enter # of Cups per dispenser"
Exit Sub
End If
If Application.WorksheetFunction.CountIf(Sheets("LookupLists").Range("E:E"), txtItem.Value) = 0 Then
'does not already exist
With ws
'copy the data to the database
.Cells(iRow, 5).Value = Me.txtItem.Value
.Cells(iRow, 6).Value = Me.txtDisc.Value
.Cells(iRow, 7).Value = Me.txtCaseQty.Value
.Cells(iRow, 8).Value = Me.txtCupQty.Value
Sheets("LookupLists").Select
Columns("E:E").Select
ActiveWorkbook.Worksheets("LookupLists").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("LookupLists").Sort.SortFields.Add Key:=Range( _
"E:E"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("LookupLists").Sort
.SetRange Range("E:H")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("SaveAs").Select
End With
'clear the data
Me.txtItem.Value = ""
Me.txtDisc.Value = ""
Me.txtCaseQty.Value = ""
Me.txtCupQty.Value = ""
Me.txtItem.SetFocus
Else
'Duplicate
'clear the data
MsgBox "This Item Number already exists"
Me.txtItem.Value = ""
Me.txtDisc.Value = ""
Me.txtCaseQty.Value = ""
Me.txtCupQty.Value = ""
Me.txtItem.SetFocus
End If
End Sub
Bookmarks