1004 Error when sorting worksheet tabs

[Kaitlyn]

I have a workbook where I have created a Table of Contents with hyperlinks to the worksheets. I sort them alphabetically, but I am suddenly getting a 1004 error.

This is the code (which I copied from elsewhere). It works just fine in another workbook, so I copied it to this workbook, when I began getting 1004 errors.

Anyway, what am I doing wrong? If I want to NOT sort descending, do I just delete that code ... and where?


Sub mcr_Workbook_Sort()
Dim i As Integer
Dim j As Integer
Dim iAnswer As VbMsgBoxResult
'
' Prompt the user which direction they wish to
' sort the worksheets.
'
iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _
& "Clicking No will sort in Descending Order", _
vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")
For i = 1 To Sheets.Count
For j = 1 To Sheets.Count - 1
'
' If the answer is Yes, then sort in ascending order.
'
If iAnswer = vbYes Then
If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
Sheets(j).Move After:=Sheets(j + 1)
End If
'
' If the answer is No, then sort in descending order.
'
ElseIf iAnswer = vbNo Then
If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then
Sheets(j).Move After:=Sheets(j + 1)
End If
End If
Next j
Next i
Sheets("1_GO_TO").Select

End Sub

 

[Yongle]

Here is your VBA amended to deal with both a protected workbook and very hidden sheets
- amend the password in this line
Const pWord = "Password Case Sensitive"


The workbook is unprotected
Function GetVeryHidden returns a string of hidden sheet names
Very hidden sheets are given normal hidden status to allow the code to run (by calling HideUnHide)
The same sheets are given very hidden status at the end and the workbook is protected

Sub mcr_Workbook_Sort()
    Dim i As Integer, j As Integer, Hiddn As String, iAnswer As VbMsgBoxResult
    Const pWord = "Password Case Sensitive"
' Prompt the user which direction they wish to sort the worksheets.
    iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _
        & "Clicking No will sort in Descending Order", _
        vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")
'unprotect workbook and unhide sheets
    ThisWorkbook.Unprotect pWord
    Hiddn = GetVeryHidden
    If Not Hiddn = vbNullString Then Call HideUnHide(Hiddn, False)
   
    For i = 1 To Sheets.Count
        For j = 1 To Sheets.Count - 1
'If the answer is Yes, then sort in ascending order.
            If iAnswer = vbYes Then
                If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then Sheets(j).Move After:=Sheets(j + 1)
'If the answer is No, then sort in descending order.
            ElseIf iAnswer = vbNo Then
                If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then Sheets(j).Move After:=Sheets(j + 1)
            End If
        Next j
    Next i
'hide sheets and protect the workbook
    If Not Hiddn = vbNullString Then Call HideUnHide(Hiddn, True)
    ThisWorkbook.Protect pWord
Sheets("1_GO_TO").Select

End Sub

Add these to the SAME module

Private Function GetVeryHidden()
    Dim ws As Worksheet, VH As String
    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVeryHidden Then
            If VH = "" Then VH = ws.Name Else VH = VH & "," & ws.Name
        End If
    Next
    GetVeryHidden = VH
End Function

Private Sub HideUnHide(list As String, hide As Boolean)
    Dim ShName As Variant
        For Each ShName In Split(list, ",")
            With Sheets(ShName)
            If hide Then .Visible = xlSheetVeryHidden Else .Visible = xlSheetHidden
            End With
        Next ShName
End Sub

 

[Kaitlyn]

If your code worked when the workbook was protected, it must mean that the sheets were sorted correctly at the time, and hence the code wasn't trying to move any sheets. But sorting the other way (i.e. ascending instead of descending in your case) would necessarily throw an error.

Is your code still not working on an unprotected workbook? If so, another possibility is that you have very hidden worksheets.

In the VB editor, can you see any sheets that aren't visible in Excel? Are you able to unhide these sheets from within Excel?

YES! This is it! I was using xlVeryHidden for 2 templates. I changed them to regular hidden, and the sort worked! I am going to change them back to very hidden and try your formula. You are amazing! Thank you!

 

[Kaitlyn]

Here is your VBA amended to deal with both a protected workbook and very hidden sheets
- amend the password in this line
Const pWord = "Password Case Sensitive"


The workbook is unprotected
Function GetVeryHidden returns a string of hidden sheet names
Very hidden sheets are given normal hidden status to allow the code to run (by calling HideUnHide)
The same sheets are given very hidden status at the end and the workbook is protected

Sub mcr_Workbook_Sort()
    Dim i As Integer, j As Integer, Hiddn As String, iAnswer As VbMsgBoxResult
    Const pWord = "Password Case Sensitive"
' Prompt the user which direction they wish to sort the worksheets.
    iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _
        & "Clicking No will sort in Descending Order", _
        vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")
'unprotect workbook and unhide sheets
    ThisWorkbook.Unprotect pWord
    Hiddn = GetVeryHidden
    If Not Hiddn = vbNullString Then Call HideUnHide(Hiddn, False)
  
    For i = 1 To Sheets.Count
        For j = 1 To Sheets.Count - 1
'If the answer is Yes, then sort in ascending order.
            If iAnswer = vbYes Then
                If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then Sheets(j).Move After:=Sheets(j + 1)
'If the answer is No, then sort in descending order.
            ElseIf iAnswer = vbNo Then
                If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then Sheets(j).Move After:=Sheets(j + 1)
            End If
        Next j
    Next i
'hide sheets and protect the workbook
    If Not Hiddn = vbNullString Then Call HideUnHide(Hiddn, True)
    ThisWorkbook.Protect pWord
Sheets("1_GO_TO").Select

End Sub

Add these to the SAME module

Private Function GetVeryHidden()
    Dim ws As Worksheet, VH As String
    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVeryHidden Then
            If VH = "" Then VH = ws.Name Else VH = VH & "," & ws.Name
        End If
    Next
    GetVeryHidden = VH
End Function

Private Sub HideUnHide(list As String, hide As Boolean)
    Dim ShName As Variant
        For Each ShName In Split(list, ",")
            With Sheets(ShName)
            If hide Then .Visible = xlSheetVeryHidden Else .Visible = xlSheetHidden
            End With
        Next ShName
End Sub

For the password, do I put my own password there to unprotect the workbook?

 

[Yongle]

If the workbook is protected it is via the Review Tab
- it not your personal password
- this would be whichever password was used to protect the workbook

I think it unlikely that the workbook is protected otherwise you could not amended the status from xlSheetVeryHidden

That being so, remove these 3 lines

Const pWord = "Password Case Sensitive"
ThisWorkbook.Unprotect pWord
ThisWorkbook.Protect pWord

 

[jcrook]

Hi All,
No reply is necessary...
I found this and used it in my application and it worked great!
Yongle - I credited you in the code.
Thank you Kaitlyn for asking and thank you all for replying.
This site has been a wealth of knowledge and allowed me to learn a tremendous amount about VBA macros.

Jay.