I am taking data from a userform entry and adding to excel & then I need to resort the spreadsheet data.
How do I resort the sheet after the data is entered. See the code below.
If runs fine but I do not want it to make the sheet "cds" visible to the user while the macro runs
Thanks
How do I resort the sheet after the data is entered. See the code below.
If runs fine but I do not want it to make the sheet "cds" visible to the user while the macro runs
Thanks
VBA Code:
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
' SAVE BUTTON
Dim db As Worksheet
Set db = Worksheets("cdb")
' check cust name not blank
If Me.cust = "" Then
Beep
MsgBox ("Customer name required")
Exit Sub
End If
' check cust does not exist
ui = CStr(Me.cust)
If Not IsError(Application.Match(ui, db.Range("A:A"), 0)) Then
Beep
Beep
MsgBox ("Customer name already exists")
Exit Sub
End If
'add customer
lastrow = db.Cells(Rows.Count, 1).End(xlUp).Row + 1
db.Cells(lastrow, 1) = Trim(Me.cust)
db.Cells(lastrow, 2) = Trim(Me.TextBox2)
db.Cells(lastrow, 3) = Trim(Me.TextBox3)
db.Cells(lastrow, 4) = Trim(Me.TextBox4)
db.Cells(lastrow, 5) = Trim(Me.TextBox6)
db.Cells(lastrow, 6) = Trim(Me.TextBox5)
db.Cells(lastrow, 7) = Trim(Me.TextBox7)
' RESORT Customer Database
lr = db.Cells(Rows.Count, 1).End(xlUp).Row
' HOW DO I DO THIS W/O SELECTING THESHEET?? -----------------------------------
Sheets("cdb").Select
Range("A1:G" & lr).Select
ActiveWorkbook.Worksheets("cdb").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("cdb").Sort.SortFields.Add2 Key:=Range("A2:A15"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("cdb").Sort
.SetRange Range("A1:G" & lr)
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' end of issue ------------------------------------------------------------- thanks
' Enter Invoice if box is checked
If Me.enterinv = True Then 'enter invoice
Gcname = Me.cust
Unload Me
Call newinfromdash
End If
Worksheets("Main").Select
Unload Me
Application.ScreenUpdating = True
End Sub