simple VBA code adjustment (I'm guessing)

[palaeontology]

Hi all,

I wish (at some point) for the user to click on the tab for the sheet called RANK.

When clicked, I wish UserForm3 to appear with six Command Buttons, each to do something slightly different.

simple enough ...

 Private Sub Worksheet_Activate()UserForm3.Show
End Sub

If the user clicks on the first command button, I wish the following code to run ....

Private Sub CommandButton_Rank_Sem1_Click()    Sheets("RANK").Select
    ActiveSheet.Unprotect Password:="Malibu00"
    Cells.Select
    Selection.Delete Shift:=xlUp
    Sheets("Entry").Select
    Cells.Select
    Selection.Copy
    Sheets("RANK").Select
    Range("A1").Select
    ActiveSheet.Paste
    Rows("8:200").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("RANK").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("RANK").Sort.SortFields.Add Key:=Range("CA8:CA199") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveWorkbook.Worksheets("RANK").Sort.SortFields.Add Key:=Range("B8:B199"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("RANK").Sort
        .SetRange Range("A8:OC199")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("E8").Select
    ActiveWindow.FreezePanes = False
    ActiveWindow.FreezePanes = True
    ActiveWindow.SmallScroll ToRight:=40
    Columns("CB:MV").Select
    Selection.EntireColumn.Hidden = True
    ActiveWindow.ScrollColumn = 336
    ActiveWindow.ScrollColumn = 77
    ActiveWindow.ScrollColumn = 69
    ActiveWindow.ScrollColumn = 43
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.SmallScroll ToRight:=42
    ActiveSheet.Protect Password:="Malibu00"
    Sheets("Entry").Select
    Range("LW1:MR2").Select
    ActiveWindow.ScrollColumn = 322
    ActiveWindow.ScrollColumn = 315
    ActiveWindow.ScrollColumn = 281
    ActiveWindow.ScrollColumn = 136
    ActiveWindow.ScrollColumn = 45
    ActiveWindow.ScrollColumn = 5
    Range("E1:CA1").Select
    ActiveWorkbook.Save
End Sub

However, it's getting hung up on the fact that when the code goes away from the Sheet called RANK, (to the sheet called Entry) and then back to the sheet called RANK, it's wanting Userform3 to show again, but I don't want it to show again ... I want the code to be able to access the sheet called RANK without Userform3 being activated again.

So I have two questions ...

1. how can I change the code so the userform3 isn't activated when the code is running
2. I recorded the code above using Developer Record macro, so am wondering if I can streamline all the 'activewindow' stuff ... if not, don't worry.

Kind regards,

Chris

 

[palaeontology]

I should have said, the error message I'm receiving is ...

Run-Time error '400':

Form already displayed; can't show modally





Any ideas ?

 

[Ells_]

I should have said, the error message I'm receiving is ...

Run-Time error '400':

Form already displayed; can't show modally





Any ideas ?

Where in the VBA code does it get stuck when you get that error?

 

[palaeontology]

Hi Ells,

it's showing the error is with the line ... UserForm3.Show

Private Sub Worksheet_Activate()
UserForm3.Show
End Sub

 

[Fluff]

How about this

Private Sub CommandButton_Rank_Sem1_Click()
    
    With Sheets("RANK")
        .Unprotect Password:="Malibu00"
        .Cells.Delete
        Sheets("Entry").Cells.Copy .Range("A1")
        .Rows("8:200").Select
        Application.CutCopyMode = False
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=.Range("CA8:CA199") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=.Range("B8:B199"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With .Sort
            .SetRange Range("A8:OC199")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        .Columns("CB:MV").EntireColumn.Hidden = True
        .Protect Password:="Malibu00"
    End With
    Range("E1:CA1").Select
    ActiveWorkbook.Save
End Sub

 

[Fluff]

Please ignore the code in the previous post & use this

Private Sub CommandButton_Rank_Sem1_Click()
    
Application.ScreenUpdating = False

    With Sheets("RANK")
        .Unprotect Password:="Malibu00"
        .Cells.Delete
        Sheets("Entry").Cells.Copy .Range("A1")
        .Sort.SortFields.Clear
        .Sort.SortFields.Add Key:=.Range("CA8:CA199") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        .Sort.SortFields.Add Key:=.Range("B8:B199"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        With .Sort
            .SetRange Range("A8:OC199")
            .Header = xlGuess
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
        .Columns("CB:MV").EntireColumn.Hidden = True
        .Protect Password:="Malibu00"
    End With
    ActiveWorkbook.Save
End Sub

 

[palaeontology]

Thankyou, Fluff, that worked brilliantly.

Perfect result.

Thankyou so much.

Very kind regards,

Chris

 

[Fluff]

Glad to help & thanks for the feedback