Want to do something without selecting the sheet

[johnohio]

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

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

 

[Siddharth Rout]

Is this what you are trying? (UNTESTED)

Private Sub CommandButton1_Click()
    Application.ScreenUpdating = False

    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
   
    With db
        ' check cust does not exist
        ui = CStr(Me.cust)
        If Not IsError(Application.Match(ui, .Range("A:A"), 0)) Then
            Beep
            MsgBox ("Customer name already exists")
            Exit Sub
        End If

        'add customer
        lastrow = .Cells(Rows.Count, 1).End(xlUp).Row + 1
   
        .Cells(lastrow, 1) = Trim(Me.cust)
        .Cells(lastrow, 2) = Trim(Me.TextBox2)
        .Cells(lastrow, 3) = Trim(Me.TextBox3)
        .Cells(lastrow, 4) = Trim(Me.TextBox4)
        .Cells(lastrow, 5) = Trim(Me.TextBox6)
        .Cells(lastrow, 6) = Trim(Me.TextBox5)
        .Cells(lastrow, 7) = Trim(Me.TextBox7)

        ' RESORT Customer Database
        lr = .Cells(Rows.Count, 1).End(xlUp).Row
       
        With .Sort
            With .SortFields
                .Clear
                .Add2 Key:=db.Range("A2:A15"), _
                      SortOn:=xlSortOnValues, _
                      Order:=xlAscending, _
                      DataOption:=xlSortNormal
           
                .SetRange db.Range("A1:G" & lr)
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SortMethod = xlPinYin
                .Apply
            End With
        End With
    End With

    ' 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

 

[RoryA]

Something like:

    With db.Sort
      with .SortFields
          .Clear
          .Add2 Key:=db.Range("A2:A15"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      end with
        .SetRange db.Range("A1:G" & lr)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[johnohio]

This line:

.SetRange db.Range("A1:G" & lr)

creates this error:

Run time error 438
Object Does Not support this property or method

 

[johnohio]

That seems to work, Thank You.

However, I changed
.Add2 Key:=db.Range("A2:A15"),
to
.Add2 Key:=db.Range("A2:A" & lr),

Not sure that matters.

Something like:

    With db.Sort
      with .SortFields
          .Clear
          .Add2 Key:=db.Range("A2:A15"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
      end with
        .SetRange db.Range("A1:G" & lr)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

 

[RoryA]

No, it shouldn't make any difference.

Glad we could help.