Dialog Box Macro

[netsurfer802]

Hi Programmers:

I'm having a difficult time running a macro in Excel that is supposed
to make a dialog box come up with various options with check boxes.

Every time it is run I get:

Run-time error "9": Subscript out of range

When I choose Debug it brings me to the line that I've put an arror
next to:
Set OptionsDlg = ThisWorkbook.DialogSheets("OptionsDlg")<--------------(See
below). I've tried looking up in help what it means but I just
get lost, as it defines it as an issue with an array. Don't
understand
what it would have to do with an array.

Thanks for any response.
===========================================================================
Sub SetOptions()
' Set the options for the Quizzes
Dim OptionsDlg As Object
Dim Admin As Object
Dim item As Object
Dim OptionsOK As Integer

Set OptionsDlg =
ThisWorkbook.DialogSheets("OptionsDlg")<----------------
Set Admin = ThisWorkbook.Worksheets("Admin")

' Make sure there is at least one quiz sheet

If QuizCount() = 0 Then
MsgBox "There are no Quizzes in " & ThisWorkbook.Name,
vbExclamation, AppName
End
End If

' Get current settings stored in Admin worksheet
OptionsDlg.CheckBoxes.Value = xlOff
If Admin.Range("BackTrack") = True Then _
OptionsDlg.CheckBoxes("cbBacktrack") = xlOn
If Admin.Range("DisplayScores") = True Then _
OptionsDlg.CheckBoxes("cbDisplayScores") = xlOn
If Admin.Range("DisplayTime") = True Then _
OptionsDlg.CheckBoxes("cbDisplayTime") = xlOn
If Admin.Range("AllowReview") = True Then _
OptionsDlg.CheckBoxes("cbAllowReview") = xlOn
If Admin.Range("SaveResults") = True Then _
OptionsDlg.CheckBoxes("cbSaveResults") = xlOn
If Admin.Range("HideStuff") = True Then _
OptionsDlg.CheckBoxes("cbHideStuff") = xlOn

' Show the dialog
OptionsOK = OptionsDlg.Show
If Not OptionsOK Then End

' Transfer dialog box info to Admin worksheet
If OptionsDlg.CheckBoxes("cbBacktrack") = xlOn Then _
Admin.Range("BackTrack") = True _
Else Admin.Range("BackTrack") = False
If OptionsDlg.CheckBoxes("cbDisplayScores") = xlOn Then _
Admin.Range("DisplayScores") = True _
Else Admin.Range("DisplayScores") = False
If OptionsDlg.CheckBoxes("cbDisplayTime") = xlOn Then _
Admin.Range("DisplayTime") = True _
Else Admin.Range("DisplayTime") = False
If OptionsDlg.CheckBoxes("cbAllowReview") = xlOn Then _
Admin.Range("AllowReview") = True _
Else Admin.Range("AllowReview") = False
If OptionsDlg.CheckBoxes("cbSaveResults") = xlOn Then _
Admin.Range("SaveResults") = True _
Else Admin.Range("SaveResults") = False
If OptionsDlg.CheckBoxes("cbHideStuff") = xlOn Then
Admin.Range("HideStuff") = True
QuizMode
End If

If OptionsDlg.CheckBoxes("cbHideStuff") = xlOff Then
Admin.Range("HideStuff") = False
AdminMode
End If

End Sub

 

[Roger Whitehead]

ThisWorkbook.DialogSheets
refers to an Excel5 Dialogue Sheet. Is this what you are using, or do you
have a UserForm?


--
Roger
Shaftesbury (UK)

 

[Jim Rech]

You have to have an actual dialogsheet (not worksheet) named OptionsDlg in
the workbook in which the code is running. That error is exactly what you
get if a dialogsheet with that exact name does not exist. Check the tabs in
the workbook.

As Roger says, userforms have superceded dialog boxes for some time now
(since Excel 97 was released in 1996), so are you sure that's not what
you're talking about?

Jim Rech
Excel MVP

 

[netsurfer802]

Ok...I'm some what new in programming with Excel. (I've been
trying to simulate this file, but I really don't know what a
dialog or dialogsheet is. Is it the same as a dialog box?

The file I'm trying to learn from has 4 dialog sheets, but
I can't see any of them without running the macros. (Are
they temporary dialog sheets perhaps.) I am mystified because
when I check the properties of the file it shows that there
are indeed 4 dialogs....when I check mine...0. When the
macros are run the dialog boxes pop up...but other than that
they are invisible.

Are dialog sheets usually invisible unless programming statements
declare them as visible objects. (I'm new in programming, if you
can't tell already).

Your making sense because the file was originally created with
Excel for Windows 95...yet it functions fine of course in
Excel 2000.

Anyway Thanks any explainaion on this.

Regards,

Mr. Eager To Learn