473,804 Members | 3,074 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Selecting and printing multiple pages in Excel using VB Script

1 New Member
I'm pretty good at Excel, but my skills in Visual Basic are very limited. I found script on-line that creates & opens a user form, allows you to select your worksheets, prints the selected worksheet, and then deletes the user form. I tried running it, but I keep on getting a "Run-time error '1004'". Do you have any suggestions on how to fix it? Here's the script and thank you in advance:

Sub SelectSheets()
Dim i As Integer
Dim TopPos As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As CheckBox
Application.Scr eenUpdating = False

' Check for protected workbook
If ActiveWorkbook. ProtectStructur e Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If

' Add a temporary dialog sheet
Set CurrentSheet = ActiveSheet
Set PrintDlg = ActiveWorkbook. DialogSheets.Ad d

SheetCount = 0

' Add the checkboxes
TopPos = 40
For i = 1 To ActiveWorkbook. Worksheets.Coun t
Set CurrentSheet = ActiveWorkbook. Worksheets(i)
' Skip empty sheets and hidden sheets
If Application.Cou ntA(CurrentShee t.Cells) <> 0 And _
CurrentSheet.Vi sible Then
SheetCount = SheetCount + 1
PrintDlg.CheckB oxes.Add 78, TopPos, 150, 16.5
PrintDlg.CheckB oxes(SheetCount ).Text = _
CurrentSheet.Na me
TopPos = TopPos + 13
End If
Next i

' Move the OK and Cancel buttons
PrintDlg.Button s.Left = 240

' Set dialog height, width, and caption
With PrintDlg.Dialog Frame
.Height = Application.Max _
(68, PrintDlg.Dialog Frame.Top + TopPos - 34)
.Width = 230
.Caption = "Select sheets to print"
End With

' Change tab order of OK and Cancel buttons
' so the 1st option button will have the focus
PrintDlg.Button s("Button 2").BringToFron t
PrintDlg.Button s("Button 3").BringToFron t

' Display the dialog box
CurrentSheet.Ac tivate
Application.Scr eenUpdating = True
If SheetCount <> 0 Then
If PrintDlg.Show Then
For Each cb In PrintDlg.CheckB oxes
If cb.Value = xlOn Then
Worksheets(cb.C aption).Select Replace:=False
End If
Next cb
ActiveWindow.Se lectedSheets.Pr intOut copies:=1
ActiveSheet.Sel ect
End If

Else
MsgBox "All worksheets are empty."
End If


' Delete temporary dialog sheet (without a warning)
Application.Dis playAlerts = False
PrintDlg.Delete

' Reactivate original sheet
CurrentSheet.Ac tivate
End Sub
Oct 4 '06 #1
0 4356

Sign in to post your reply or Sign up for a free account.

Similar topics

5
2230
by: Mark Preston | last post by:
Admission first - I don't actually have a problem here but have noticed that a lot of people have been asking similar questions and getting very varied answers. What I've done is to sort of "compile the questions" into a theoretical problem to see what people think should be done to solve it. Maybe it will be a worthwhile discussion, but more importantly maybe it will find out the very best way to sort this kind of problem out so that...
3
4622
by: Joakim Olesen | last post by:
Hi I have an issue with an asp page that reads from an Excel file. I read the file using OLEDB. I create a new Excel file using "MS Excel 2003" and put the following data into a sheet called "Ark1" (2 columns and 5 rows): 1234 reer 1234 feb 4467 heerh 4467 123123
4
4741
by: Sami | last post by:
I hope someone will tell me how to do this without having to do any VB as I know nothing in that area. I am a rank beginner in using Access. I have created a database consisting of student athletes.  I have now learned how to join two different tables in a query so that I might generate a report. Specifically, student athletes at a community college are required to graduate with an AA or AS degree.  Consequently, various steps are in...
16
48890
by: cyranoVR | last post by:
This is the approach I used to automate printing of Microsoft Access reports to PDF format i.e. unattended and without annoying "Save As..." dialogs, and - more importantly - without having to use a commercial program such as Adobe Acrobat and its associated API. The technique uses Ghostscript and Redirection Port Monitor - two free programs for creating PDF documents provided free by Russell Lang. The actual automation requires VBA...
4
6485
by: Suzanka | last post by:
Hello, I have an application written in C# on visual studio .NET. It is a web aplication. The application consists of many different forms, that users occassionaly want to print out for filing. When they log to application (through web browser) and choose the print option, on the right margin few cm get cut off (so some fields do not print out). Is there any function that ensure that when user pritns he gets the
1
1501
by: ProgramAnalyst | last post by:
Hey guys, I've got a page that has multiple tables (all same format, they just contain diffrent information) and when u print that page the tables get cut up and spread out on multiple pages. I've done my research and it looks like it isn't impossible to print the tables properly even when using CSS but I just need to make sure before I go tell my manager he can't have what he wants.... so is there anyway to print the tables properly?...
2
8198
by: ray well | last post by:
hi, i need to print multiple pages on a printer. this is what i'm using now Sub Print() Dim PrintDoc As New PrintDocument AddHandler PrintDoc.PrintPage, AddressOf Me.PrintText Do While bPrintingNotDone PrintDoc.Print()
6
4092
by: Siv | last post by:
Hi, I am getting into printing with VB.NET 2005 and want to implement the usual capability that a user can select a selection of pages. I have a report that is generated by my application that if the user wants all pages will produce 3 pages. I want to offer the user the ability to select via the print dialog that only pages 1 and 2 of it are printed or possibly pages 1 and 3 but not 2. At the moment I can produce all three pages...
8
5916
by: Neo Geshel | last post by:
Greetings. BACKGROUND: My sites are pure XHTML 1.1 with CSS 2.1 for markup. My pages are delivered as application/xhtml+xml for all non-MS web clients, and as text/xml for all MS web clients (Internet Explorer). My flash content was originally brought in via the “flash satay†method, but I have since used some server-side magic do deliver one <objecttag
0
9704
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9569
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
1
10302
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10069
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7608
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5503
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5636
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4277
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
3802
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.