Add An Option To Print All Except Hidden Sheet

Dec 21, 2006

I have been using this to print multiple sheets. The only problem is if you want all sheets in the workbook you have to check every one. How would I add an option to 'print all'? But I still wouldn't want to print the hidden sheets.


Option Explicit

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.ScreenUpdating = False

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

View 9 Replies


ADVERTISEMENT

Allow Users To Print Hidden Sheet But Not Change Print Settings

Jul 2, 2009

I am having trouble disabling the "Preview" button when calling Application.Dialogs(xldialogprint) in XL2003. I have an xlSheetVeryHidden worksheet containing a form which is made visible programatically when the user wishes to print a copy (code below). I want the user to be able to have access to the functionality of the xlDialogPrint dialog (i.e. select desired print destination, number of copies, pages per sheet etc.) but I also want to prevent them modifying the page setup of the protected worksheet by initiating a preview from the print dialog.

Private Sub btnPrintReturnForm_Click()

wbkRUSC.Unprotect sysPass 'unprotect workbook to enable changes to sheet visibility
' (sysPass is project constant string containing password)

shtStaffForm.Visible = xlSheetVisible 'make staff form visible

shtCalc.Visible = xlSheetVeryHidden 'hide main calculator in case user gets smart and hits..............................

View 5 Replies View Related

Trying To Print Hidden Sheet

Jun 24, 2014

I have protected a workbook with a hidden sheet. I am trying to save the hidden sheet to a .pdf format.

It works well until I hide the sheet. I see from other post that the .select is a big part of my problem but I'm not sure how to get around it.

View 11 Replies View Related

Option To Print - Use Print Macro

Dec 30, 2008

I have a few macros that run in a sheet. After the macros are finnished I would like to present to the user a msgbox that asks if they would like to print. if "Yes" is selected, the print macro runs. If "No" is pressed then nothing happens.

View 3 Replies View Related

Don't Print Hidden Rows

Dec 15, 2009

I have a worksheet with hidden rows and Page Breaks. I know that the page breaks are causing my worksheet to print blank pages where I have page breaks and hidden rows.

I need the page breaks or the form just prints a mess... I tried eliminating them - but that didn't work at all.

I need a macro, which will examine the worksheet, look for the hidden rows, exclude them from the print area, and then Print the worksheet.

View 9 Replies View Related

Add In Another Option To Select Whether To Print The Page As Horizontal Or Landscape

Aug 29, 2003

I'm using the select sheets to print code from: http://j- walk.com/ss/excel/tips/tip48.htm. I need to add in another option to select whether to print the page as horizontal or landscape. I've been able to add the checkbox, but am having a hard time with the loop to process correctly. I also have the landscape changing code as well. Here is my
Option Explicit
Sub SelectSheets()
Dim i As Integer
Dim TopPos As Integer
Dim TopPos1 As Integer
Dim SheetCount As Integer
Dim PrintDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As CheckBox
Application.ScreenUpdating = False
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If
Set CurrentSheet = ActiveSheet............

View 2 Replies View Related

Macro To Set Print Area W/o Hidden Rows

Jun 28, 2008

We run a small gardening shop and use a simple spreadsheet to track of various things.

Row A contains headers, with the data following in the rows underneath. This table is now quite large, and we therefore hide 600 or so rows so that only rows with data from the last week is shown. We often need to print this for easy reference. I currently do this by highlighting the area I want and setting it as print area. The print therefore doesn't include hidden cells, which is what I need.

The problem we have, therefore, is that we have to manually select this print area each time. My experience with macros is VERY limited, to the point where I can record one which will select the print area and print. However, as the list gets longer and more rows are hidden the range obviously needs to change, and my simple macro will not keep up.

So my question is as follows. Is there a simple macro I could write to assign to a button that could "keep up" with the moving range? So either it always prints the header row and the, for example, 50 rows beneath (exlcuding the hidden ones), or, even better, it prints the header row and all rows with data in them that are relevant to the last week. Each row has a cell for the date it concerns so assume this may be possible?

View 9 Replies View Related

Macro To Print Sheets With Value In Cell A1 But Print Dynamic Ranges On Certain Sheet

Sep 24, 2013

I've found some code which works to print certain pages with value in cell A1 but I need to print dynamic ranges on some of the sheets as they will have filters on so the rows ranges will be different each time.

So far this is what I have but the dynamic range part is not working:

VB:
Sub Print_All_Worksheets_With_Value_In_A1()
Dim Sh As Worksheet
Dim Arr() As String
Dim N As Integer

[Code] ....

View 3 Replies View Related

Option Button On Sheet 1 To Change Image On Sheet 2

Jan 15, 2007

Sub OptionButton222_Click()
With Worksheets("Beam Input")
.Shapes("Notch 1").Visible = False
.Shapes("Notch 2").Visible = False
End With
End Sub
Sub OptionButton223_Click()
With Worksheets("Beam Input")
.Shapes("Notch 1").Visible = False
.Shapes("Notch 2").Visible = True
End With
End Sub
Sub OptionButton224_Click()
With Worksheets("Beam Input")
.Shapes("Notch 1").Visible = True
.Shapes("Notch 2").Visible = False
End With
End Sub

i need to modify it so that a selection on the beam input page causes the images to change on the beam output page. Currently this code is in the module section of VBA.

View 2 Replies View Related

Automatically Re-name Sheet To Cell Value & Create Copy Of Hidden Sheet

Aug 10, 2008

I have a workbook with a hidden sheet ("Template") and a visible sheet("New Job"). I need code so when cell F1 in "New Job" is populated:

1-the sheet is renamed to the value of F1,
2-a new tab is made (a carbon copy of the hidden sheet "Template")
3-the new tab is named "New Tab" and marked as unhidden.

Public Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Name = Range("F1").Value
End Sub

Function WorksheetExists(SheetName As String, _
Optional WhichBook As Workbook) As Boolean
Dim WB As Workbook
Set WB = IIf(WhichBook Is Nothing, ThisWorkbook, WhichBook)
On Error Resume Next
WorksheetExists = CBool(Len(WB.Worksheets(SheetName).Name) > 0)
End Function..................

View 2 Replies View Related

Importing CSV File To Hidden Sheet Without Selecting Sheet

Mar 14, 2014

I have this code that first selects a sheet (data) and then imports a csv-fil to the data sheet.

I want to hide the data sheet and the the code doesn't work.

How do I change the import code so that I do not select the sheet?

[Code] ......

I believe it should be something like this: With Sheets("DATA").QueryTables. Add..... but I am not aware of the syntax.

View 1 Replies View Related

VBA To Hide Sheet In Workbook And Copying Hidden Sheet

Dec 12, 2013

I have a userform which clones the latest sheet and produces copy of it on the next sheet. The first sheet they will be cloning is the sheet called 'template', I however want this to be hidden since I do not want anybody to modify a sheet which basically serves purpose of a template. And once cloned, it is no longer needed.

On the click of the command button, the macro will create sheet1 taking the info from sheet named 'Template' in the same workbook . And now on the next click of the command button, Sheet 2 is created taking the info from sheet1 and Sheet 3 is created taking the info from Sheet 2 and so on. Here is what I currently have, so how can i modify it in order for my scnerio to work?

To select the last sheet in the workbook

Code:

Sheets(Sheets.Count).Select
To create new sheet

Code:

Sheets(Sheets.Count).Copy After:=Sheets(Sheets.Count)Sheets(Sheets.Count).Name = MyEvent & " " & MySCN & "(" & ThisWorkbook.Sheets.Count - 2 & ")"

View 3 Replies View Related

Keep Hidden Sheet Made Visible As Active Sheet

Mar 2, 2007

I have a button on a sheet that runs a macro to unhide another sheet. That works, but I want the sheet made visible to remain forward. Instead, the button unhides the sheet and the sheet the button is on comes forward again. I am unable to figure how to keep the sheet made visible forward. Here is the macro 'as recorded'.

Sub UnhideSheet1()
Sheet2.Visible = True
End Sub

View 7 Replies View Related

Dropdown Option For Summary Sheet

Jul 22, 2014

I will have a summary tab and then a number of tabs with various options in detail

What I would like to do is some way of adding the options on the summary tab - some sort of drop down I guess

Is this possible without lots of programming etc...

View 4 Replies View Related

Control Option Buttons On One Sheet?

May 20, 2014

I have a spread sheet and I want to have multiple option buttons on it. but for separate things

e.g. I need roughly 50 option buttons. however. I want about 25 groups of them.

so I want 2 buttons next to a cell where user can select one. then I want 2 more on cells below where user selects one for a different question

but at the moment, every single option box copies every other box when I changed the format control cell reference. even though I haven't copied and pasted them!

View 9 Replies View Related

Some Row In Excel Sheet Is Hidden?

Sep 28, 2013

well , when i save the spread sheet , some rows still hidden condition

i apperaed them , but when exit with save , it will be hidden again

B shift members History list 2013 (1-1-2013) draft.xls

View 5 Replies View Related

Hyperlink With Hidden Sheet

Oct 21, 2011

In a sheet I have a two hyperlinks with Sheet4 and Sheet5. when I am clicking on first hyperlink then the sheet4 is opening and when I am clicking on the other hyperlink then sheet5 is opening. But if I hide these two sheets it is not opening,

Is there any way so that hide sheets should be opened? will I have to write code for that?

View 2 Replies View Related

Protecting Very Hidden Sheet

Dec 6, 2006

way of protecting a very hidden sheet, I will still need to open the sheet myself everytime I use the program. The only person to open this very hidden sheet is myself.

View 9 Replies View Related

Run Macro From Hidden Sheet

Apr 1, 2009

Sub Wright()
'
' Wright Macro
'
'
ActiveSheet.Shapes("Object 5").Select
Selection.Verb Verb:=xlPrimary
End Sub

What do I need to add to this so that the Macro will run when the sheet is hidden? The Macro runs an Embeded presentation, which I do not want to be visible in the workbook.

View 9 Replies View Related

Paste To Hidden Sheet

Nov 22, 2006

Sheets("Sheet1").Select
Range("A1:B2000").Select
Selection.Copy
Windows("SL Forms.xls").Activate
Sheets("Hidden Sheet").Select
Range("A1:B2000").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:=xlNone
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone
Sheets("Hidden Sheet").Select

The macro starts on open, opens the workbook with the data to copy, copies and pastes the data into the original workbook. The problem is that "Hidden Sheet" is hidden, so the macro can't see it!

how do I get the macro to use this hidden sheet without keeping it 'un-hidden'?

View 6 Replies View Related

How To Hide (View Code) Option For A Sheet

Nov 22, 2012

I have a login page for my spreadsheet however a user can simply view the code to get the login username and password.

View 2 Replies View Related

Disable Copy / Paste Option For Particular Sheet?

Dec 8, 2013

I have workbook having 3 sheets out of them 1 sheet name "report". I want vba to disable copy /past option to sheet Report only with msg " copy not allowed" if key clt+c or copy option selected

View 1 Replies View Related

Macro To Go To Specific Sheet Upon Choosing Option

Oct 16, 2007

Im looking to create a macro that goes to a specific sheet on my workbook upon selecting an option. Example being I open the spreadsheet and a list of options come up such as:

BRIAN
JOHN
JAMES
PAUL
STEVEN

Upon selecting one of those names you are transferred to the sheet named "BRIAN", "JOHN", "JAMES" etc etc etc

View 4 Replies View Related

How To Remove Delete Option When Right Click On Sheet Tab

Jun 21, 2014

When right clicking on the sheet tab, I need to know if it is possible to remove the "Delete" from being an option. My workbook is structured to where if a single sheet is deleted, it screws up all my formulas. I tried to just password protect the workbook, but doing that removes the "Rename" function, which I still need.

View 9 Replies View Related

Excel 2013 :: CF With 2 Option Buttons / Multiple Option Groups?

Sep 6, 2013

I have several option groups (Y & N in each) linked to cells to display TRUE / FALSE depending on which option is selected in each group. I have another cell which I want to apply conditional formatting to if EITHER Y or N is selected in all groups. At present I use COUNTIF to check for FALSE=0 in the linked cells which works if all the answers are Y. How do I apply CF if there is a mixture of Y & N (TRUE / FALSE) in all groups (I'm not sure if I've explained that well or not).

Excel 2013

View 1 Replies View Related

Additional Hidden Sheet Names

Mar 10, 2014

I have problem with additional "hidden"(duplicated?) sheets (not by atribute)

This is my excel, created from new fresh file : excel tables.bmp

And this is what i see when i get all sheetnames (with C#, word and some other programs) word-tables.JPG

where A1$ have same value as A1$_4349_inkjet_WZ

I have this problem with some other files but in this one i have 100% confidence that none of sheets is hidden nor very hidden.

I found that 3 proper tables are "System table" and 2 additional are "tables".

[URL] .....

View 1 Replies View Related

Macros Connected To Hidden Sheet

Feb 14, 2014

I have a macro which copy text from a sheet (sheet-1) and paste it into another sheet.

I want sheet-1 to be hidden, or even better, very-hidden, but when I hide sheet-1 the macro run fails. Respons Run-time error '1004'...

Is it a solution around this 'problem' or does sheet-1 always has to be visible ?

View 3 Replies View Related

Compile Error In Hidden Sheet

Jan 14, 2009

I've written some VB code in Excel 2003 which hides and unhides worksheets dependant upon 'Yes' or 'No' in a named range. This works Ok in Excel 2003, however if I try and use it in Excel 2007 it shows error 'Compile Error in Hidden Sheet'. When I debug it the error 'Compile Error - Method or Data Member Not Found' appears. The code in question is as follows:

View 9 Replies View Related

Running Code On A Hidden Sheet

Jul 13, 2009

I have placed the code I am using below. It takes values from a hidden sheet (PACTAct) and places them on a visible sheet (Email). The code moves between the two.

It works at the minute by unhiding the PACTAct sheet then at the end re-hiding it. However I don't want the user to see this sheet whilst the vba is running. Is there a way I can simply refer to the worksheet without unhiding it?

View 6 Replies View Related

Hidden Sheet Shouldn't Be Viewed By Others

Jun 19, 2012

When the sheet is hidden by me in the excel others shouldn't unhide the hidden sheet.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved