VBA for OptionButton on DialogSheet not working for some users but OK for others...

[ZedLeb]

Hi

I recently used some code kindly supplied 8 years ago (!) by Tom Urtis

http://www.mrexcel.com/forum/excel-questions/321894-userform-select-worksheet.html

and it works brilliantly for my purposes - BUT only on my machine + my session/profile.

On other machines or on my machine with someone else's login it fails here:

If .Show = True Then
For Each objOpt In wsDlg.OptionButtons
If objOpt.Value = xlOn Then
optCaption = objOpt.Caption
Exit For
End If
Next objOpt
End If

The message I'm getting is that objOpt is not defined.

I know this is a really specific issue - so I'm kinda hoping Tom will see this and have some idea what gives
But if there's anyone else that has any ideas why I'm blessed to have this working and not my poor colleagues please jump in.

Cheers
Zoe

 

[ZedLeb]

If .Show = True Then
For Each objOpt In wsDlg.OptionButtons
If objOpt.Value = xlOn Then
optCaption = objOpt.Caption
 Exit For
 End If
 Next objOpt
 End If

I've done a bit more debugging using a colleagues laptop in parallel with running my code.

The problem comes at line 2 (line 67 in the original code).

On my machine it progresses happily to the next line and I can hover to see that

objOpt = -4146

and

objOpt.Value = -4146

On my colleague's machine it leaps to the last

End if

(line 73 in original code). In this case I see that

objOpt = Nothing

and

objOpt.Value

give a not defined error.

I am logged onto both machines...is it a combination of my login settings and my machine? Someone else logging onto my machine has the same bug.

I'm out of my depth if it's to do with network settings etc.....Anyone?....

Cheers
Zoe

 

[John_w]

Your debugging on your colleague's machine indicates that the dialog sheet (wsDlg) doesn't contain any option buttons. To confirm this, make the sheet visible instead of hidden:

.Visible = xlSheetVisible

and set a breakpoint (F9 key) at the following line:

                For Each objOpt In wsDlg.OptionButtons

When the code execution reaches that line, look at the sheet, and in the Immediate Window enter:
?wsDlg.OptionButtons.Count

I would also comment out the On Error Resume Next line, and see if you get any errors.

A much simpler approach, and less code, is to use a userform with a listbox with its ListStyle property set to ListStyleOption.

 

[ZedLeb]

This looks promising! I need to wait until my colleague comes in so I can borrow her laptop again - and will try straight away.

In the meantime I will look into the userform suggestion. I was quite lazy when I picked Tom's code - it just worked so well straight off that I didn't investigate any other solution but I've discovered since that dialog sheets are very old and perhaps not the best way to carry on.

Thank you so much John for your rapid response.

 

[ZedLeb]

Hi John

I've just ran a parallel check again and two interesting things:

a) There was a different result of the count of option buttons between the working code (32) and the non-working code (33)

b) Once I took the On Error off both codes fell over at Ln21 (Subscript out of range) but only after a second run (first run OK)

ActiveWorkbook.DialogSheets(SheetID).Delete

I've pasted the full code below if it is of any help.

I really appreciate your help with this - it would be great to understand why it is not working on one machine and OK on another.

The rest of the morning will be spent looking at UserForm alternative

Cheers from France
Zoe

Option Explicit
' Code to jump to selected worksheet from Tom Urtis.
' [URL]http://www.mrexcel.com/forum/excel-questions/321894-userform-select-worksheet.html[/URL]
Sub SheetSelectorAvant()
Const ColItems  As Long = 20
Const LetterWidth As Long = 20
Const HeightRowz As Long = 18
Const SheetID As String = "__SheetSelection"
 
Dim i%, TopPos%, iSet%, optCols%, intLetters%, optMaxChars%, optLeft%
Dim wsDlg As DialogSheet, objOpt As OptionButton, optCaption$, objSheet As Object
Dim iRet As Integer
optCaption = "": i = 0
 
Application.ScreenUpdating = False
 
'On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.DialogSheets(SheetID).Delete
Application.DisplayAlerts = True
Err.Clear
 
Set wsDlg = ActiveWorkbook.DialogSheets.Add
With wsDlg
.Name = SheetID
.Visible = xlSheetHidden
iSet = 0: optCols = 0: optMaxChars = 0: optLeft = 78: TopPos = 40
 
For Each objSheet In ActiveWorkbook.Sheets
If objSheet.Visible = xlSheetVisible Then
i = i + 1
 
If i Mod ColItems = 1 Then
optCols = optCols + 1
TopPos = 40
optLeft = optLeft + (optMaxChars * LetterWidth)
optMaxChars = 0
End If
 
intLetters = Len(objSheet.Name)
If intLetters > optMaxChars Then optMaxChars = intLetters
iSet = iSet + 1
.OptionButtons.Add optLeft, TopPos, intLetters * LetterWidth, 16.5
.OptionButtons(iSet).Text = objSheet.Name
TopPos = TopPos + 13
 
End If
Next objSheet
 
If i > 0 Then
 
.Buttons.Left = optLeft + (optMaxChars * LetterWidth) + 24
 
With .DialogFrame
.Height = Application.Max(68, WorksheetFunction.Min(iSet, ColItems) * HeightRowz + 10)
.Width = optLeft + (optMaxChars * LetterWidth) + 24
.Caption = "Select sheet to compare"
End With
 
.Buttons("Button 2").BringToFront
.Buttons("Button 3").BringToFront
Application.ScreenUpdating = True
 
If .Show = True Then
For Each objOpt In wsDlg.OptionButtons
If objOpt.Value = xlOn Then
optCaption = objOpt.Caption
Exit For
End If
Next objOpt
End If
 
If optCaption = "" Then
MsgBox "Vous n'avez pas selectionner un onglet.", 48, "Annulé"
Exit Sub
Else
 
iRet = MsgBox("Vous avez selectionné l'onglet ''" & optCaption & "''." & vbCrLf & "C'est bien?", vbYesNo, "FYI:")
    If iRet = vbNo Then
         Exit Sub
    Else
        Sheets(optCaption).Activate
        StaticToAncienne
 
    End If
 
End If
 
End If
 
Application.DisplayAlerts = False
.Delete
Application.DisplayAlerts = True
 
End With
End Sub

 

[John_w]

Looking more at the code, the On Error Resume Next is only there to ignore the error which occurs if the dialog sheet doesn't exist when the following statement is executed (subscript out of range):

ActiveWorkbook.DialogSheets(SheetID).Delete

However due to a bug in the logic, this sheet is only deleted if you get to the final prompt and click Yes. Otherwise this sheet remains in the workbook, hence the need to delete it at the start of the procedure. To fix this, delete or comment out the two Exit Sub lines, so that the execution always drops through to the .Delete line at the end of the procedure. Then the On Error and deleting the dialog sheet at the start of the procedure aren't needed.

 

[John_w]

Here is the basic code for the alternative approach using a userform and listbox control (ListBox1). Set its ListStyle to fmListStyleOption, BackColor to the same as the form and BorderStyle to fmBorderStyleNone so that it looks like a set of option buttons.

Private Sub UserForm_Initialize()

    Dim ws As Worksheet
    
    With Me
        .ListBox1.Height = ActiveWorkbook.Worksheets.Count * 18  '18 = height of each line in listbox
        .ListBox1.Top = 5
        .ListBox1.Left = 10
        .Width = .ListBox1.Width + 10
        .Height = .ListBox1.Top + .ListBox1.Height + 20
        For Each ws In ActiveWorkbook.Worksheets
            If ws.Visible = xlSheetVisible Then
                .ListBox1.AddItem ws.Name
            End If
        Next
    End With
    
End Sub

Private Sub ListBox1_Click()
    Worksheets(ListBox1.Value).Select
End Sub

That just shows the basic technique - you can add command buttons, MsgBox prompts, etc. as needed.

 

[ZedLeb]

Hi John

However due to a bug in the logic, this sheet is only deleted if you get to the final prompt and click Yes.

That's very interesting - I wonder why my settings cope with this bug and not the other users...

However - enough of dialog sheets! I took up your suggestion and rewrote the thing using UserForms and it works like a dream - so thank you very much for your help. The workbook's been released and others are able to use it with no problems (yet!)

Have a great weekend
Zoe