Free Microsoft Excel 2013 Quick Reference

Print Dialog from VBA

Is there a VBA command that will call up the print dialog box before printing
so that number of pages, preview, etc. can be modified? .PrintOut isn't
doing it for me.

Thank you for your help.


This is frustrating, but maybe I'm just being dense. I'm running Excel 2003.
If I click on Print... and then select Preview, there is a button that says
Print... on the resulting Preview window toolbar, but it isn't Print... it's
Print (no ...). The ... is supposed to indicate a dialog.

So, to me, there are 2 problems. First is that the toolbar button is wrong.
I always think it's going to give me a dialog where I can specify
resolution, printer, color, etc., but then it just shoots the job over to
the printer. Any way to change the button and fix this bug?

Second, how can I get to a print dialog from the Print Preview window? I
almost always need to make changes to the print settings, but need to review
the Print Preview first. In virtually every other program with a print
preview option there is a way to get back to the print dialog from the
preview window. Am I just missing this in Excel?

Thanks for any help, insight, or suggestions,
Colin

Hi,

I need some VBA code that invokes the Print dialog box.

I cannot use the Printer icon in Excel to do this because
every time the users need to print the current spreadsheet
they first need to do a backup. They always remember to
get a printout (they need the hard copy) but they forget
to do a backup. This spreadsheet is updated with realtime
data several times each hour so a backup is necessary.

I have already implemented the VBA code in a button
to do the respective backup.

This button has the label "Do Backup".

Now I would like to add a few lines of VBA code to the
same button so that AFTER the backup is done the Print
dialog box shows up.

The button label would then be changed to
"Do Backup and Print ..."

Can you give me a few lines of VBA code that I can
append to do this?

Thanks,
Luther

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

From vba excel I use a browser dialog box where the user selects a file for the application to read. How can I get the servername of the file the user selected? By default I get the mapped drive C:/K: etc which is not what i need.

thx,
-M

I am completely stuck on a VBA problem that I need some help with.

Im designing a survey using VBA Excel.

Workbook1 is where the questions are, and Workbook2 is where I will capture the survey respondant's answers. The reason why I have done this is to ensure that the clients can email their answers back to us in a much smaller file size than the workbook that the questions are in.

I have created a print manager form so that the clients can print their answers as they go through the questionnaire.

This code is launched by a commandbutton in a menu userform where the clients can choose from several different surveys, and once the printing of the document has been executed, I would like it to be able to return to the original "menu" userform.

Answers in workbook2 are formatted so that they can be printed out on single pages. The problem that I have is that once the printing of the document has been executed, the userforms in workbook1 are unloaded, and the user is returned to the workbook1 worksheet view.

My question is how do you print an excel worksheet without the userforms in workbook1 being unloaded by the print dialog box?

In the Userform:

Option Explicit

Sub Userform_Initialize()

LBox1_Fill

End Sub

Private Sub CommandButton1_Click()

Dim i As Integer

Application.DisplayAlerts = False

With UserForm1
.Hide

Application.Visible = 1

For i = 0 To .ListBox1.ListCount - 1

If .ListBox1.Selected(i) Then

Application.ScreenUpdating = 0

Sheets(.ListBox1.List(i)).PrintOut

End If

Next i

End With

End Sub

Private Sub CommandButton2_Click()

Unload Me

End Sub

Private Sub CommandButton3_Click()

Application.Dialogs(xlDialogPrinterSetup).Show

End Sub

Function LBox1_Fill()

Workbooks("Insurance Questionnaire Answers.xls").Activate

Dim sht As Variant

With UserForm1.ListBox1
For Each sht In Sheets
If sht.Visible Then
.AddItem sht.Name
.MultiSelect = 1
If sht.Name = ActiveSheet.Name Then
.Selected(.ListCount - 1) = True
.ListIndex = .ListCount - 1
End If
Err.Clear
Next sht
If .ListCount = 0 Then
UserForm1.CommandButton1.Visible = 0
.AddItem "No Sheets found to Print."
Else
.TopIndex = .ListIndex
End If
End With

End Function

Function Print_Job()

Dim i As Integer

Application.DisplayAlerts = False
With UserForm1
.Hide
Application.Visible = 1
For i = 0 To .ListBox1.ListCount - 1
If .ListBox1.Selected(i) Then
Application.ScreenUpdating = 0
Sheets(.ListBox1.List(i)).PrintOut
End If
Next i
End With

End Function

In ThisWorkbook:

Option Explicit

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Application.EnableEvents = False
Cancel = True
UserForm1.Show
Application.EnableEvents = True

End Sub

My second question which falls into the "nice to have" but isnt really crutial to the whole project, as without it, I will simply hide the worksheets that I dont want to be visible on the fly. Is it possible to add individual worksheet names to a listbox as opposed to just all of the visible worksheets, or the activeworksheets?

I would be extremely grateful for any assistance that anyone can give me,

Many Thanks

Merryn

How can I not show the "Printing" dialog box that comes up when I print from
a VBA routine.

I've had problems when users accidently click the Cancel button and it stops
the code from executing. I figure not showing the dialog box is the best way
to avoid this.

I'm trying to print a given Acrobat Reader PDF file from VBA. (Office 2000 &
Acrobat Reader 6)
I've included the reference to the Adobe Acrobat Control for ActiveX
(pdf.ocx),
but I'm unable to actually create an instance of the PDF control.
I've also tried putting a Microsoft Webbrowser control an my Excel
worksheet, and calling the
Webbrowser1.Navigate "someserversomefoldersomefile.pdf" method.
The webbrowser control indeed shows the PDF file, but I did not find a way
to print it's contents from VBA.

I'm looking for someone who can put me on the right track for this seemingly
simple problem.

Matthias Claes
matthias.claes(-at-)stadsbader.com

I was wondering how I would go about actually viewing a txt file from vba without having a open file dialog display appear. I know how to open and read the file in memory, but I am not sure how to do it so it appears on the screen. Thanks.

Dear All,

I have created a macro that copys pivot table from one Workbook to new workbook. Problems I am encountering are print selecting from source workbook are not copied to new workbook. Does anyone know Macro code that can help me?

MY VBA Code as follows:-


	VB:
	
 
Dim ColumnCount As Long, RowCount As Long 
Dim ThisWorkbookName As String, ActiveWorkbookName As String, ActiveSheetName As String 
Public Sub CreateGroups() 
    Application.ScreenUpdating = False 
    Sheets("Preferences").Select 
    ThisWorkbookName = ActiveWorkbook.Name 
    AmountofColumns = Range("A1").End(xlToRight).Column 
     
    For ColumnCount = 2 To AmountofColumns 
        Range("A1").Select 
        AmountofRows = Cells(1, ColumnCount).End(xlDown).Row 
        ActiveWorkbookName = WorkbookCreate(Cells(1, ColumnCount).Value) 
         
        For RowCount = 2 To AmountofRows 
            Call CreateWorksheets 
        Next RowCount 
         
        Call CleanWorksheets 
    Next ColumnCount 
     
    Windows(ThisWorkbookName).Activate 
    Sheets("Instructions").Select 
    Application.ScreenUpdating = True 
    MsgBox "Completed Groups", vbInformation, "Completed" 
End Sub 
Private Function WorkbookCreate(WorkbookSaveAs As String) As String 
    Set NewBook = Workbooks.Add 
    Do 
        fName = Application.GetSaveAsFilename(WorkbookSaveAs, "Excel Workbook (*.xls), *.xls", , "Save GroupWorkbook") 
    Loop Until fName  False 
    NewBook.SaveAs Filename:=fName 
    WorkbookCreate = NewBook.Name 
End Function 
Private Sub CreateWorksheets() 
    On Error Resume Next 
    Windows(ThisWorkbookName).Activate 
    Sheets("Preferences").Select 
    ActiveSheetName = Cells(RowCount, ColumnCount).Value 
    Sheets(ActiveSheetName).Cells.Copy 
    Windows(ActiveWorkbookName).Activate 
    Sheets.Add 
    Sheets(ActiveSheet.Name).Name = ActiveSheetName 
    Range("A1").PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 
    Range("A1").PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False 
    Range("A1").Select 
    Windows(ThisWorkbookName).Activate 
    Application.CutCopyMode = False 
    Sheets("Preferences").Select 
End Sub 
Private Sub CleanWorksheets() 
    Windows(ActiveWorkbookName).Activate 
    Application.DisplayAlerts = False 
    Worksheets("Sheet1").Delete 
    Worksheets("Sheet2").Delete 
    Worksheets("Sheet3").Delete 
    ActiveWorkbook.Save 
    Application.DisplayAlerts = True 
    Windows(ThisWorkbookName).Activate 
    Sheets("Preferences").Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Biz

when printing "something" from vba, can I force the pop up of standard excel boxes such as the print dialog or the page setup dialog?

I have a spreadsheet that users will be asked to complete.

I have approximately 1200 lines that they can enter data in. Only a few will require that many, but I have to build it for all users.

Is there a way to set the print area (via VBA calculate or change event) to print only the rows that contain values? That is, print from Row 1 through the last row that has a value in the cell?

All of the cells are blank, but there are validation rules and simple formatting such as borders, number formatting etc. No formulas exist on this sheet. The sheet name is 'FOB Cost Form'.

How do I access the # of copies that a person has chosen
after they hit print on the print dialog box?

My program prints a page several times, making changes to
several values before each print. The program shows the
print dialog box only for the first time, allowing the
user to choose the printer and the number of copies etc.
It then prints the remaining pages.

How do I set NumCopies (in the code below) to the number
of copies the user chose in the print dialog

Application.Dialogs(xlDialogPrint).Show

.... some other code ...

ActiveWindow.SelectedSheets.PrintOut Copies:=NumCopies,
Collate:=True

Your help would be greatly appreciated. Thanks.

Hello -

I am having a hard time with this and hope someone can help me. I have an
excel spreadsheet that has multiple columns, and instead of trying to fit and
print all of the columns into one page, I am using VBA to define two
different print areas. My problem is, since multiple people will be printing
this sheet, not everyone will be using the same printer. I am trying to use
a Print Dialog box so that they can select their printer, but if they would
like to print multiple copies it will print the first area x times, and then
display the print dialog box again so that it can print the second print
area. Is there anyway that I can do this only once? Any help is greatly
appreciated.

Thanks.

Hi,

I have been having trouble merging employment contracts as the page numbering often goes from being 1-30, to 1-300 (dependant on how many records I merge). To get round this I have copied macros from gmayor.com developed by Doug Robbins to split a merged document so that each contract is saved as an individual files and then also treated as an individual print task. My issue is that I would like to call the word print dialog box as part of the print task macro to allow the user to select which printer they would like to use, paper size, tray preference etc. This is what I'm currently using:


	VB:
	
 SplitMergeLetterToPrinter() 
    Dim Letters As Long 
    Dim Counter As Long 
    Letters = ActiveDocument.Sections.Count 
    Counter = 1 
    While Counter < Letters 
        ActiveDocument.PrintOut Background:=False, Range:=wdPrintFromTo, _ 
        From:="s" & Format(Counter), To:="s" & Format(Counter) 
        Counter = Counter + 1 
    Wend 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I'm wondering if anyone could help me adapt the PrintOut argument to call the print dialog box instead, or point me in the direction of a resource with relevant information.

Thanks,

Maria.

Hi,

I am using vba to display the built in Print Dialog box, but i would like to
somehow store the number of copies that one chooses into a variable. So if
one chooses 10 as the number of copies to print, i want to be able to store
10 somewhere in my code. Can anyone help me with this?

Thanks

I have a designed a report in ppt which i would like people to be able to
print from presentation show mode (ie- cant edit the data). Is there anyway
of designing a macro which will open up the print dialog box (so people can
choose colour/b&w print), but not actually print to the default?

I have come across a problem when using the preview function called from VBA. As I prefer end users to remain unaware of the background processes I generally have screen updating switched off and if whilst updating is off a print routine is called that requires a preview then the ribbon remains on the last visible and does not bring up the print preview view. from this point it is possible to return by using the esc key but not to print.

Microsoft have not come up with anything to help as yet and I was wondering if anyone else had had a similar problem. The function works fine in excel 2000 through 2003.

I know that the page setup options are specific to the printer, but I was wondering if there is anyway to access them from VBA.

I regullarly need to overide my companies default "print duplex" option to print single sided. To do this I have to manually go through each worksheet and change the page setup options. It would be fantastic if I could access these options via VBA and write a script to automatically do it ...

Any ideas, or am I as likely to find an answer as George Bush is to find a brain cell?

All,

I am new to VBA, so I could use a guru's help.
I am puzzled by the following behavior. I call a function from a Sub inside
VB IDE and it works fine every time.

I call the same function from a spreadsheet cell and it errors out every time.
The error occurs when I try to assign a value to a Cell or Range. Works when
called from VBA, but not when called from Excel. This is really strange and
makes debugging VBA a nightmare.

Here is the simplest functions I could write to duplicate the behavior.
If I call bareQTest from the IDE, it works. If I call barePlaceQuery from
a spreadsheet, it craps out. The spreadsheet has a named range called
"header" as the destination for the string.
===================================================
Option Explicit

Function barePlaceQuery(query) As Integer
   
    On Error GoTo Failure
       
    Range("header").Cells.Item(1).Value = query
          
    barePlaceQuery = 100
    Exit Function
    
Failure:
    barePlaceQuery = -1
    Debug.Print "Error: " & Err.Description

End Function

Sub bareQTest()
    Dim Count As Integer
    
    Count = barePlaceQuery("[[From Sub bareQTest]]")
    Debug.Print "Record Count: " & CStr(Count)
End Sub
===================================================

Thanks much.

Hello -

I am having a hard time with this and hope someone can help me. I have an
excel spreadsheet that has multiple columns, and instead of trying to fit and
print all of the columns into one page, I am using VBA to define two
different print areas. My problem is, since multiple people will be printing
this sheet, not everyone will be using the same printer. I am trying to use
a Print Dialog box so that they can select their printer, but if they would
like to print multiple copies it will print the first area x times, and then
display the print dialog box again so that it can print the second print
area. Is there anyway that I can do this only once? Any help is greatly
appreciated.

Thanks.

Hi there,

I have a feeling the answer to this is going to be "not possible", but
I figured it couldn't hurt to ask.

I have a multi-page Excel workbook. Each spreadsheet in the workbook
has a print CommandButton I've put at the bottom, plus of course the
print and print preview buttons on the Excel toolbar.

Recently I had to add an item to each sheet where some text in the
footer is created on the fly at printout, but only on the first page if
the printout of the worksheet is multiple pages. To accomplish that I
put code behind the print CommandButtons, and in the
Workbook_BeforePrint sub in ThisWorkbook, so it fires for both my print
button and the Excel toolbar buttons.

To avoid having spreadsheets print twice the code in
Workbook_BeforePrint includes a "Cancel=True" at the end, and looks
like this:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Application.EnableEvents = False
[Set up LeftFooter]
ActiveWindow.SelectedSheets.Printout From:=1, To:=1, Copies:=1
[Clear LeftFooter]
ActiveWindow.SelectedSheets.Printout From:=2, Copies:=1
Application.EnableEvents = True
Cancel = True
End Sub

However, one thing I neglected to think of is that with this code in
place, if the user selects File->Print, the spreadsheet just prints as
though the user had clicked the Excel toolbar print button, the print
dialog box does not come up.

It's not a critical thing as there are only a couple of the 50+ users
who regularly use the print dialog to change printers, but if there's a
way to actually bring up the print dialog when the user does a
File->Print that would be great.

Any thoughts?

Thanks!

Matt

Hi,

I am wondering whether it is possible to call puTTy from VBA. (Just FYI puTTy is a terminal emulator used for remote access to computer systems).

I have a spreadsheet with IP addresses, account names, and passwords, and if possible I would like to then start a puTTy session using VBA, and pass the relevant system information to it. This will allow one-click logging on to another system.

I can do the passing of variables no problem - it's the calling of an application from VBA which I'm having trouble with.

Any help would be very much appreciated - there's no hurry though as this is a "like-to-have" part of my VBA, as opposed to a must.

Thanks in advance

Rich

Hi,
I am trying to write to the event log from VBA. I have managed to write to the Event Log(You need to start the NT Applet, Run - EVENTVWR.EXE) using the following article:
http://support.microsoft.com/?kbid=154576......Since we're using VBA, there is no application.logevent, so you need to use the example shown for VB 4. However the log looks like below as I seem to be missing a registry setting.
Do you know what registry setting I need to get this to work?(Clean error/warning message)
I am using VBA to write out the Log (Excel 2001 and VB 6.3)

EVENT VIEWER: APPLICATION LOG
The description for Event ID ( 1001 ) in Source ( Project1 ) cannot be found. The local computer may not have the necessary registry information or message DLL files to display messages from a remote computer. The following information is part of the event: Information from Project1.

I have a problem,
I chose to have a print button on my form when pressed it would slect a CHART SHEET in my workbook and then bring up the Print dialog window... All is good, the user can then change the printer or the color or the quality and then he/she can press the print button and it would print and be form (since my form covers the whole window, and you cannot close it, since it is not the active form and if you try to get to it by closing every form you will end up closing the whole workbook, I am sure it is not 100% secure but good enough)
The problem:
If the user clicks the "preview" button on the print dialog. If this happens EXCEL FREEZES. and I cannot break the code excution ctr- break to stop my code, and cannot see the print preview or do anything but ctr -Alt - Del

Is there Anything I can do or do I have to live with this bug (my guess it is because of my security measures that I described above), and just tell the users to be aware of this bug??

Layth

guys,
how can we assign a value to the cell(list) with some type text from VBA.
thanks