Dialog Boxes are giving me a headache
Richard Sbragia
Ok, i am using XL 97 and am having trouble showing a dialog box from a
button in a spreadsheet.
This is the macro I am trying to run from a button:
Sub Display()
Application.DialogSheets("Print_Dialog").Show
End Sub
If I run it in the Print_Dialog Code(pressing f5), it will run, but when
I try to assign the display macro, it is nowhere to be found. I can't
find it or the module it is written in. If I copy the macro to a module
I can see, and assign it, I get a run time error. What gives. This is
going on three days and two trips to the bookstore.
Please help.
Richard
Bill Manville
Don't think I quite follow your explanation, but you should be aware
that it is not possible to print anything while a dialog is being
shown.
If you want it to print a named sheet selected by the user on the
dialog then put the following code in a module sheet:
Dim SheetToPrint As String
Sub ShowPrintDialog_Click()
' attach to Print... button on some worksheet
Do
SheetToPrint = ""
If ThisWorkbook.DialogSheets("Print_Dialog").Show = False Then
Exit Sub ' user cancelled
End If
If SheetToPrint<>"" Then Worksheets(SheetToPrint).PrintOut
Loop While SheetToPrint<>"" ' reshow dialog if printed
End Sub
Sub PrintSheet_Click()
' attach to the Print Sheet button on the dialog
' say the sheet to print is selected in a listbox
With ActiveDialog.ListBoxes(1)
SheetToPrint = .List(.ListIndex)
End With
ActiveDialog.Hide ' dismiss the dialog before trying to print
End Sub
Hope this helps
Bill Manville
Oxford, England
Microsoft Excel - MVP
Rich
Thanks Bill!
Sorry for my ambiguous explanation. Brevity is not always the best
approach I suppose...
I have a workbook that has several print macros corresponding to several
sheets, using different headers, footers, etc...(basically, the macros
print reports).
In an effort to consolidate the macros into one central area, I elected
to create a custon dialog box, which contains several command buttons.
I labled these buttons and have assigned the respective print macro.
This dialog box would then be diplayed by clicking on a button I placed
on a sheet within the workbook. Ideally, when you want to print a few
reports, you go to the sheet with the command button, click on it, and
the dialog box will show-up. Then you can select which reports you want
to print.
Problem, When I create the command button in the workbook, it attempts
to assign a macro to it right away, only I can't find the macro to
display the dialog box. If I write the macro in a module I can find(ie,
one of the print macros), I get a runtime error. The only place I seem
to be able to get the macro to run is in the dialog box code
module(double clicking on the dialog box), which is a location that I
can't assign to the command button or even call it. It does not find
it...
Any Ideas
Thanks
Rich
Atlanta
Jerry Hunt
========================================================================
Rich,
It's still fairly early in the AM and I haven't yet consumed my quota of
caffeine. So I'm having a little trouble following your post, so I'll
walk through some of the basics (the shotgun approach :o), let me know
if I don't hit.
You've setup the dialog box, and if you haven't renamed it, it's
probably called Dialog1. So, for starters, to use this dialog box,
you've created a button. What you want to do is to assign a macro to
that button that will call the dialog box. Usually something simple
like:
Sub RunDialogBox()
DialogSheets("Dialog1").Show
End Sub
That is the macro you want to assign to the button on the worksheet.
Notice, the macro doesn't do much other than show the dialog box.
Okay, let's back up a bit and talk about the dialog box itself. On this
dialog box, you have some buttons. Let's say there are 3 and they are
called Print1, Print2 and Print3. I strongly suggest if you haven't
already done so, give them a name (something descriptive). In case you
don't know how do do this, click the button (while in the dialog sheet)
to activate it, and in the name box (to left of screen, same place you
create range names), type in the name. I strongly suggest you do not
assign macros to these buttons. Instead, assign one macro to all the
choice buttons to capture which button was clicked. First, create a
public variable to capture the button, at the top of the Module (before
any subs) like this
Public WhichButton As String
The macro that you assign to each button is something like
Sub GetButton()
WhichButton = Application.Caller
End Sub
You assign a macro to the OK button, so that when the OK button is
clicked, it's macro runs, which simply determines which button was
clicked and calls the associated macro. So, let's say you name this
macro "SelectReport". It would look like this:
Sub SelectReport()
Select Case WhichButton
Case "REPORT1": PrintReport1
Case "REPORT2": PrintReport2
Case "REPORT3": PrintReport3
End Select
End Sub
Obviously, PrintReport1, etc. are the macros which print the desired
report.
Hopefully this will help you get your dialog box working properly. If
not, feel free to ask more questions.
--
Spam buster:
To send me e-mail, remove the "hello..." from my e-mail address (I
hate spambots).
Aveesh Kumar
Rich,
You are talking about Forms not dialogboxes
To display a userform in a module write code which says
Sub displayForm()
Print_dialog.show
end sub
Assign displayform macro to the button on the sheet. Code behind the form is
not a regular module (it is a class module) and you can call it only by
instantiating the form and calling public methods - you will need toi read
up on that
Aveesh
kum...@micromodeling.com
Rich wrote in article <342091...@msn.com>...
Jerry Hunt
Richard Sbragia wrote:
>
> Jerry, before I ask you anymore questions, is a a dialog1 in your
> example the same as a userform1?
>
> Thanks!
>
========================================================================
You must be using Excel97? I believe that userform1 is the same as
dialog1. Regarding the question in your other post (i.e. does a multi
page tab format make a difference): not having used Excel97 yet, I don't
know. Hopefully one of the other regulars here can help with that
question.
Richard Sbragia
Jerry, before I ask you anymore questions, is a a dialog1 in your
example the same as a userform1?
Thanks!
Jerry Hunt wrote:
Richard Sbragia
Aother question:does it make a difference that I am using a multi page
tab format for this?
Bill Manville
On Wed, 17 Sep 1997 22:28:25 -0400, Rich <sbr...@msn.com> wrote:
> The only place I seem
>to be able to get the macro to run is in the dialog box code
>module(double clicking on the dialog box), which is a location that I
>can't assign to the command button or even call it. It does not find
>it...
>
It sounds to me as though you are talking about an Excel 97 user form
which is a very different beast from an Excel dialog box.
If you confirm that is the case one of us will modify our earlier
answers to be appropriate for a user form.
Bill Manville
>
>You assign a macro to the OK button, so that when the OK button is
>clicked, it's macro runs, which simply determines which button was
>clicked and calls the associated macro. So, let's say you name this
>macro "SelectReport". It would look like this:
>
>Sub SelectReport()
> Select Case WhichButton
> Case "REPORT1": PrintReport1
> Case "REPORT2": PrintReport2
> Case "REPORT3": PrintReport3
> End Select
>End Sub
>
>Obviously, PrintReport1, etc. are the macros which print the desired
>report.
That won't work because the dialog is still active until the end of
the OK button handler. You need to call SelectReport from after the
Show method in RunDialogBox
Richard Sbragia
That is correct, I am using a userform in 97. I thought they were the
same thing...
Bill Manville
>That is correct, I am using a userform in 97. I thought they were the
>same thing...
>
No. A dialog to us old Excel hands is something you design on a
dialogsheet using controls from the Forms toolbar, not something
designed within the VBE.
So ignore any previous answers talking about DialogSheets.
Next question: is the button on the worksheet that runs the macro that
shows the form a button from the Forms toolbar or from the Control
Toolbox. In the former case the code goes in a regular module. In
the latter case the code goes in the private module behind the
worksheet.
The button that runs the form needs to call a procedure on a regular
module sheet.