INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS
Come Join Us!
Are you a
Computer / IT professional?
Join Tek-Tips now!
- Talk With Other Members
- Be Notified Of Responses
To Your Posts
- Keyword Search
- One-Click Access To Your
Favorite Forums
- Automated Signatures
On Your Posts
- Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.
Partner With Us!
"Best Of Breed" Forums Add Stickiness To Your Site
(Download This Button Today!)
Feedback
"...I am so glad that I found your site, it is an excellent resource and has helped me greatly..."
Geography
Where in the world do Tek-Tips members come from?
|
Browse Dialog Not Working (3)
|
|
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:
CODESub 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. |
|
hi,
Does the built-in feature that lists all worksheets for navigation, not meet your requirements? Skip,
 Just traded in my old subtlety...
for a NUANCE! |
|
Hi Skip
Nope, thus the need for this macro. Thanks. |
|
What is the marco supposed to do? Please list ALL features. Skip,
Just traded in my old subtlety...
for a NUANCE! |
|
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. |
|
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,
Just traded in my old subtlety...
for a NUANCE! |
|
Hi Skip
Thanks....but just an FYI that I did post at the bottom of the original that the code stopped at "If .Show"..... |
|
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:
CODEApplication.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.
|
|
If you have a DOT reference, like .Show, that statement needs to refer to some object in a With...End With
CODEApplication.ScreenUpdating = True
With SomeObject
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
End With Skip,
Just traded in my old subtlety...
for a NUANCE! |
|
|
kjv1611 (TechnicalUser) |
5 Sep 12 8:49 |
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...
"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57 |
|
Hi Skip
Thanks but I think it is referring to the dialogframe i.e.
CODEWith .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 ThenFor 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?
|
|
It is a best practice to INDENT block of code in order to make these kinds of mistakes more easily identified...
CODE
With .DialogFrame
.Height = Application.Max(68, _
Application.Min(iBooks, nPerColumn) * nHeight + 10)
.Width = cLeft + (cMaxLetters * nWidth) + 24
.Caption = kCaption
End With
'......
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,
Just traded in my old subtlety...
for a NUANCE! |
|
|
kjv1611 (TechnicalUser) |
5 Sep 12 10:26 |
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 (TechnicalUser) |
5 Sep 12 10:34 |
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:
CODESub 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 |
|
@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,
Just traded in my old subtlety...
for a NUANCE! |
|
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. |
|
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,
Just traded in my old subtlety...
for a NUANCE! |
|
|
PHV (MIS) |
5 Sep 12 11:34 |
|
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. |
|
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,
Just traded in my old subtlety...
for a NUANCE! |
|
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 (TechnicalUser) |
5 Sep 12 21:55 |
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 (TechnicalUser) |
5 Sep 12 21:58 |
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 |
|
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. |
|
|
 |
|
Talk To Other Members
Read More Threads Like This One