Browse Dialog Not Working 3

[shelby55]

Hello

I have a macro that displays all visible worksheets for the user to select which worksheet to go to. The code originated with Bob Phillips and edited by AlphaFrog.

The macro works with the initial selection but then I get the error message "run-time 1004: unable to get show property of the dialogsheet class". Interestingly when I go back to the first worksheet, it works again BUT it actually has multiple sheets selected within the dialog box.

The code is:

Sub BrowseSheets()
Const nPerColumn As Long = 58 'number of items per column
Const nWidth As Long = 13 'width of each letter
Const nHeight As Long = 18 'height of each row
Const sID As String = "___SheetGoto" 'name of dialog sheet
Const kCaption As String = " Select worksheet to goto"
'dialog caption

Dim i As Long
Dim TopPos As Long
Dim iBooks As Long
Dim cCols As Long
Dim cLetters As Long
Dim cMaxLetters As Long
Dim cLeft As Long
Dim thisDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As OptionButton
Dim ws As Worksheet

Application.ScreenUpdating = False

If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.DialogSheets(sID).Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set CurrentSheet = ActiveSheet
Set thisDlg = ActiveWorkbook.DialogSheets.Add

With thisDlg

.Name = sID
.Visible = xlSheetHidden

'sets variables for positioning on dialog
iBooks = 0
cCols = 0
cMaxLetters = 0
cLeft = 78
TopPos = 40

For Each ws In ActiveWorkbook.Worksheets
If ws.Visible = xlSheetVisible Then
iBooks = iBooks + 1

If iBooks Mod nPerColumn = 1 Then
cCols = cCols + 1
TopPos = 40
cLeft = cLeft + (cMaxLetters * nWidth)
cMaxLetters = 0
End If
cLetters = Len(ws.Name)
If cLetters > cMaxLetters Then
cMaxLetters = cLetters
End If
iBooks = iBooks + 1
With .OptionButtons.Add(cLeft, TopPos, cLetters * nWidth, 16.5)

.Text = ws.Name
.Value = ws Is ActiveSheet
End With
TopPos = TopPos + 13 'controls space between them vertically
End If

Next ws

.Buttons.Left = cLeft + (cMaxLetters * nWidth) + 24

CurrentSheet.Activate

With .DialogFrame
.Height = Application.Max(68, _
Application.Min(iBooks, nPerColumn) * nHeight + 10)
.Width = cLeft + (cMaxLetters * nWidth) + 24
.Caption = kCaption
End With

.Buttons("Button 2").BringToFront
.Buttons("Button 3").BringToFront

Application.ScreenUpdating = True
If .Show Then
For Each cb In thisDlg.OptionButtons
If cb.Value = xlOn Then
ActiveWorkbook.Worksheets(cb.Caption).Select
Exit For
End If
Next cb
Else
MsgBox "Nothing selected"
End If

Application.DisplayAlerts = False
.Delete

End With

End Sub

The code stops at If .show. Thanks for any assistance.

 

[shelby55]

Hi

So I have a workbook with 374 worksheets. Sometimes I may manually go to the next worksheet and generally the BrowseSheets is used when the worksheet is farther from where I am.

Starting at 1 I select 216 and it takes me there no problem. From 216 I run the macro again (which is via a button) and get the error message. Sometimes it can work quite well for a few worksheets i.e. 1 to 10, from 10 to 216 but then doesn't. If I go back to the first sheet and try it from there then the dialog box always shows up for me to pick a worksheet.

Note also that if I select "cancel" because I'm testing i.e. on worksheet 1, invoke macro but don't select a page, manually got to second worksheet, invoke macro but don't select a page, etc. for many, many pages then every single time the dialog box shows up.

I also just did a test where the dialog box was invoked on sheet 40 and then I continued on up (manually) through the different worksheets to see if I could invoke it for each of them. When I reached a worksheet where the error message showed up (120) then I went back to 40 and it still works on 40 but not 120.

It appears that it works fine on page 1 to 119 but not after that. Having said that, if I select the worksheet beyond this i.e. 140 from pages 1 to 119 then it takes me there but the macro won't work from page 140.

I don't know if this is helpful but that is a description of what it is doing. Thanks.

 

[kjv1611]

Skip, either I had forgotten about the right-click menu you mentioned or else never knew. Wow, that could really come in handy at times! Thanks for mentioning!

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

One other note on the code - just an overall, general, note, as I'm not delving into this one at 10pm my time tonight. I meant to mention it earlier as well. I'm not fond of using so many WITH statements embedded one within another. When it comes time to debug, I think it's more problematic than it is helpful, b/c you have to remember for 100% certain which dot(.) reference goes with which WITH. Whereas if you just stick to 1, max, you KNOW without guessing what is what. But that may just be my opinion.

Have a good evening.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[shelby55]

Hi

Thanks for your comments, Kjv1611. I'll take your comments to heart when creating my own code but in fact this is code I got from the web and thought it would be a good idea because I knew there would be many worksheets.

I just wish I could figure out why it isn't working as it's supposed to. Any assistance in fixing that would be greatly appreciated.