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.

 

[SkipVought]

hi,

Does the built-in feature that lists all worksheets for navigation, not meet your requirements?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[shelby55]

Hi Skip

Nope, thus the need for this macro. Thanks.

 

[SkipVought]

What is the marco supposed to do? Please list ALL features.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[shelby55]

Hi

Thanks for your willingness to help but I figured out the problem: the button was on each worksheet which was affecting it so I added it to the quick toolbar and now it appears to be working correctly.

Thanks.

 

[SkipVought]

Wonderful!

The next 2 suggestions would have been...

1) if Mr X designed the code, then maybe Mr X might be a better source to answer a question about his code and

2) just stating , "I get error xyz" is not particularly helpful, especially when you have posted over 100 lines of code -- WHICH LINE has the error?

But there is no need to pursue either line of reasoning at this point.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[shelby55]

Hi Skip

Thanks....but just an FYI that I did post at the bottom of the original that the code stopped at "If .Show".....

 

[shelby55]

Hi

Sorry but this code is still causing issues and stops at the line of code If .Show Then. The code that I referenced was from a google search from many years ago so thanks for the suggestion of trying to find the original author but that appears to be a moot point.

What this code is supposed to do is open up a dialog box showing all visible worksheets and the user selects which sheet they wish to go to, select it, and hit OK to go there. If Cancel is hit then the dialog box closes with the message of "nothing selected.

The full section of the code:

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

Any assistance in figuring this out would be greatly appreciated. Thanks.

 

[SkipVought]

If you have a DOT reference, like .Show, that statement needs to refer to some object in a With...End With

Application.ScreenUpdating = True[b]
With SomeObject[/b]
   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 [b]
End With[/b]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kjv1611]

From what I could find on this DialogSheet, that's an outdated method. You can do the same thing much more quickly and simply with FileDialog, assuming you need to see Workbooks (files), not really Worksheets (sheets or tabs within the sheets).

From your description, I'm assuming you meant workbooks.

Instead of typing up an example, I'll point you to a reference - and I'm sure there are plenty more - this just happens to be one I came across in a quick search:

http://www.wiseowl.co.uk/blog/s209/type-filedialog.htm

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

 

[shelby55]

Hi Skip

Thanks but I think it is referring to the dialogframe i.e.

[highlight #EF2929]With .DialogFrame[/highlight]
.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
[COLOR=#EF2929]If .Show Then[/color]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

Kjv1611: thanks fo the link which I'll check out but I'm not understanding the assumption that I wish to see workbooks? I do want the dialog box to show all visible worksheets, not workbooks....is that what you meant?

 

[SkipVought]

It is a best practice to INDENT block of code in order to make these kinds of mistakes more easily identified...

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

that is ALL that refers to the DialogFrame! And you have other problems that you need to debug before you get clean code.

Debug > Compile your project and you'll begin to find them.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[kjv1611]

Workbooks = Excel Files
Worksheets = parts within Excel files

So does the DialogSheet list out all worksheets within a given workbook? I was under the impression that you were really talking about Workbooks. So perhaps I was wrong on that. It just sounded to me to be the same thing as the FileDialog.

Sorry.. I guess I assumed wrong on that one.

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

 

[kjv1611]

And here's an attempt to clean-up your code as far as the tabbing structure. This might make it easier to find your errors:

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

If you can't remember to do that yourself, then perhaps you should use some of the built-in tools in MZ-Tools. that suite of tools has loads of little helps. There are least 2 or 3 different pieces for documenting your code, and I believe there is at least one for getting everything tabbed/spaced right.. but I'm just going from memory, not looking through the menus.

Here's the site:

http://www.mztools.com/index.aspx

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

 

[SkipVought]

@kjv: For me, the Worksheet List feature (right-click in the Workbook Navigation Controls) is the simplest way to navigate a workbook with many sheets. The dialog box is clunky and a totally unnecessary effort.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[shelby55]

Thanks very much, Kjv1611. I've tried to debug and can't seem to find what the issue is....maybe because I've been staring at it for so long before asking for help.

Skip, I realize you don't agree with this development but it's done and now i have to get it to work. If I promise to never add something like this again and just use the Worksheet List Feature will you please help get this to work?

Thanks to both of you.

 

[SkipVought]

Have you run the debugger as many times as is required to correct and generate clean code, meaning code that is syntactically without error, although it may not be logically without error.

Have you restructured your code to make the defined blocks (Sub...End Sub, With...End With, For...Next, If...Else...End If, etc) quickly identifiable and therefore your code more understandable?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

shellby55, the code posted 5 Sep 12 10:34 run (as is) without error in Excel 2007, but with no change for the active sheet.
To get it to work, I've modified it as this:

...
    If .Show Then
        For i = 1 To .OptionButtons.Count
            Set cb = .OptionButtons(i)
            If cb.Value = xlOn Then
                ActiveWorkbook.Worksheets(cb.Caption).Activate
                Exit For
            End If
        Next
    Else
        MsgBox "Nothing selected"
    End If
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[shelby55]

Hi

Thanks very much PHV. What happens with the current code and the code you provided is that it works for a few worksheets i.e. go from 1 to 15 then from 15 to 18 all correctly using this macro but then the same message shows up and it stops.

Could it be that there needs to be reference to where it currently is? I ask because sometimes going back to a worksheet that worked i.e. 1, then it's working again.

I also ask this because sometimes when the dialog box opens the current worksheet is selected (as I would expect) but there is also another worksheet that I haven't selected that is selected which appears to be random. Maybe I need to clear the dialog box prior to it opening up?

I'm really at my wit's end so any assistance is greatly appreciated. Thanks.

 

[SkipVought]

I can use the code that kjv posted on 5 Sep 12 10:34, sucessfully, multiple times, without error in a workbook with 18 sheets.

Please describe the process that leads up to the error you stated.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]