As many are aware, there is an option to make a sheet very hidden. However, a "very hidden" sheet remains visible in the VBE project window. A user with basic VBA knowledge can easily selected the hidden sheet & go in the property window and change its visibility setting to "-1 Sheetvisible". Is there any way to make hidden sheet invisible in VBE as well?
View 3 Repliesi have a workbook that has the following sheets
working sheet
job sheet
receipt of deposit letter
completion sheet
delivery note
delivery note (2)
odd
even
t&t
glass
ggf
i want to hide every sheet except the working sheet.
I have tried this but the macros bring up an error when i run the macro
my macros involve printing certain pages dependng on what button is pressed
i get an error whatever
how do i stop this
on sheet1 I have a button I need to do the following when clicked:
(1) name the next 30 sheets based on cell values in sheet1
(2) for those 30 sheets, hide some of them based on a y/n input in sheet 1
To clarify: the worksheets do not need to be created, they already exist. They just need to be renamed and hidden based on that y/n criteria. see attachment with just 1 worksheet for clarification. So - The next 30 sheets are to be named by the following ranges (B7:B16), (B21:B30) and (B35:B44). For every product with a "n" in column C of sheet1, the worksheet for that product needs to be hidden.
A workbook with 7 sheets in it. The first sheet contains buttons with macros that when clicked will navigate you to different sheets in the workbook based on what button you click. Here's what I want to do. When the workbook is opened, I want it open to that first sheet with the buttons, but show no other tabs for the other sheets at the bottom. And then when that button is clicked on the first sheet to go to a different page, I want that page to be the only visible tab in the workbook with the rest still hidden. Is that doable? I already have the buttons and codes and everything for the navigation part down, I just need to have the other hidden when I'm on a different sheet.
Sub Button10_click()
Worksheets("Account Type").Activate
End Sub
Sub Button11_Click()
Worksheets("Name Change").Activate
End Sub
Sub Button12_Click()
Worksheets("Address-Phone").Activate
End Sub
Sub Button13_Click()
Worksheets("Main Menu").Activate
End Sub
Sub Button15_Click()
Worksheets("Cust-Owner").Activate
End Sub
Sub Button16_Click()
Worksheets("Misc").Activate
End Sub
I wanted to know if there is any function/macro/option available which can hide/show sheets based on the value selected in the particular cell.
Basically i will have a table of contents as the first sheet. Post that if 'Yes' is selected against a particular line item, the sheet for the same should appear. If 'no' is selected the sheet would remain hidden. The sheets would be linked via hyperlink to the particulars (name) for each line of the table of contents. If required i can also remove the hyperlinking of cell.
Cells B2:B100 contain a list of sheet names, cells C2:C100 have entries TRUE or FALSE.
Is there away to setup a macro that goes through the list and if false, make the sheet very hidden, if true it should be visible?
I wrote a macro that hides every row that contains an "X" in column X for every sheet. The problem is that it runs very slowly (assumedly because it is checking every cell on every sheet). Is there a way to clean this up and make it run faster?
My code:
Sub HideRows()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Dim Cell As Range
For Each Cell In ws.Range("X:X")
If Cell.Value = "X" Then
Cell.EntireRow.Hidden = True
End If
Next
Next ws
End Sub
Why this doesn't error but yet it doesn't work either.
Sheets("2 plans, 3 plans, 4 plans").Visible = False
Sheets("1 plan").Range("a26:a416").EntireRow.Hidden = True
i have a list of services with "yes" or "no" options in the column beside it. Description for each service is given on separate sheets (Workbook sheet 1, Workbook sheet 2..etc). i want to format it in a way that if i choose "no" for one of the services, its description sheet hides.
View 3 Replies View RelatedI would like to be able to hide the tab i am in and unhide another tab at the click of the button,
I have tried recording the macro myself and it works up to a point,
I want it to finish on the tab i have just unhidden but for some reason it doesn't seem to do that (even though that's the way i recorded it)
I have 2 sheets, List 1 and List 2 Both lists contain generic part numbers in column A, so because there could be 2 manfacturers of the same part there will be duplication of the generic part number.
What I want is to prevent someone entering a generic part number in list 1, which already exists on list 2.
I there any cde which can be put in the sheet which will show an error message for this?
I have a sheet that pulls data from an external source. I want it so that when a person clicks on a cell or a range of cells, it automatically locks and hides the formula. I don't want to lock it by using the conventional method because protecting it will cause the external data not to be able to populate the cells. So my thinking is that when the data is imported, and the user clicks on a cell, it will automatically lock and hide.
I need columns
A C E:BB to be locked and hidden as soon as someone clicks it. Is this possible?
I read somewhere that I may be able to do some type of "Private Sub Workbook_SheetSelectionChange" to achieve this.
I have a workbook that has about 30 different sheets with names titled "Joe", "Jane", "Paul", etc.
I have a Cell (B12) that has been formatted as a dropdown menu with about a dozen different options such as "Audit A" , "Audit B" or "Other" etc. What i'd like to do is hide or display certain sheets based on what the value is in cell B12.
if cell B12 says any of the following "Audit A" or "Audit B" then it would hide certain sheets. If the value in B12 says "Other" or something other than "Audit A" or "Audit B" then it wouldn't hide anything at all. I tried searching and couldn't find anything like this although it may have been answered before.
I have a worksheet that veryhides all the sheets but one when closes and when opens if unhides the ones a user needs. For some reason the quick key Alt + PageUp or Alt + PageDown doesn't work until each sheet is scrolled through by clicking on their tab with the mouse.
View 4 Replies View RelatedRegarding the age old problem of trying to ensure macros are enabled in a workbook, using the process of hiding all sheets bar one:
Automatically Close Workbook If Macros Are Disabled
Reafid inserts some excellent code so that worksheets are hidden BOTH before closing AND on a normal Save event (I have also attached Reafid's zip file with the workbook that includes his/her code).
Having done much searching it seems to be the best solution out there, however I can still break it fairly easily and I'm worndering if anybody can stop me doing the following to break it:
Open the file in its restricted state (i.e. only Warning tab shown, all other sheets very hidden) and NOT enabling macros on opening. I'm therefore at the position Reafid intended; macros are disabled so all sheets are very hidden. Then to break it, in my personal macro workbook I have a simple macro that unhides all worksheets in ThisWorkbook, I run that personal macro and everything in Reafid's file is unhidden.
Reafid's file therefore has been opened with macros DISABLED and now all worksheets are visible.
='NAME OF SHEET'!$#
where # equals the exact cell in the other sheet I want to copy. I'm also starting to realize that with this formula, Senior Monkette and I could take Dingleberry and Bliddiboo and combine them into a more powerful and robust spreadsheet (called "Voltron!").
1. If we're going to be sending Voltron! to the client, all I would need to do is password protect, then hide the Dingleberry sheets so that the client doesn't look at them accidentally, right?
2. If I hide and protect the Dingleberry sheets, the cell information will still show up on the Bliddiboo sheet, right?
3. Since Senior Monkette isn't as Excel-savvy as I am, the entire process would have to be as painless as possible. What I was thinking of doing was having one master Voltron! where Senior Monkette could make her changes and updated. Then every week, save the entire thing, protect/hide the Dingleberry sheets, and then save a copy as a separate Voltron! file, marked by the date, which then gets shipped off to the client. Is there a macro I can run that will do that all with the press of one button? (And how do I install macros?)
I have an activeX combo-box that selects from different pieces of equipment that we supply. Based on that selection, I require ranges from the same page that the combo-box is on to either hide or unhide. Also, I require different tabs to become visible or hidden based on that same selection. So far so good - I have code that does this, and it appears to work without glitch.
Where the problem arises, is in one of the ranges that is unhidden when a particular piece of equipment is selected there is another combo-box that I would like to use (the number of said pieces of equipment to supply) to further hide/unhide additional ranges on the same page, and also hide/unhide certain tabs as well.
When I make a selection from combo-box 1, all works as planned, but when I change the state of combo-box 2, even with no associated coding referring to it, I cannot change combo-box 1 again without getting Error 1004 "Unable to get the Hidden property of the range class".
None of the sheets in the workbook are protected.
I would sincerely appreciate any help/code that could circumvent this error.
I have groups of data in A5:AW200 with various blank rows in between, and would like to be able to automatically hide and unhide (toggle) the blank rows, but only for those rows where cells in column A are blank.
I'd also like to hide those same groups of blank rows on Sheets 2-5, which are laid out exactly the same as Sheet 1, but since they contain cell references to and are mirrors of Sheet 1, certain rows on those sheets may appear blank, but in fact are not. (I should probably have separate buttons on each sheet, but not sure). I know Sheet 1 can be used to test for conditions (blanks rows) and translate the results to Sheets 2-5, but I'm not sure how to do it.
I am using Reafidy nice bit of code to hide worksheets if the user disables macros. The problem, though, is that it doesn't work for Chart Worksheets. How should I adjust this to accommodate charts?
Private Sub Workbook_BeforeClose(Cancel As Boolean)
bIsClosing = True
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim wsArray() As Variant
Dim iCnt As Integer
Application. ScreenUpdating = 0
Splash.Visible = True
I've got a workbook that I'm currently designing and I've just realised that I need to delete a few sheets, paste in some new template sheets and rename them. The problem is, I've already completed my summary sheets and I don't want to have to redo the values. Essentially, I just want to (temporarily) turn off auto-updating of formulas when cells / sheets are changed/moved/deleted.
For practical purposes: I need to delete the sheet named "Jan", paste in a new sheet "MonthTemp", rename "MonthTemp" to "Jan" and have all my formulas not updated (e.g. still referencing "Jan" instead of "#REF!")
I am trying to find code that will allow me to hide a set number of rows based on the value of a specific cell which I need to work for two worksheets in the same workbook. Is that even possible?
For example: when i enter 5 into cell D1, I need five rows to be visible on both sheets.
I have a spreadsheet which has 3 separate external queries running from worksheet 'A'. My user enters a couple of dates in another worksheet 'B' to supply the date parameters for the query and a macro runs to refresh all of the queries and performs a few calculations.This works fine. The trouble is I don't want the user to be able to view all the data on sheet 'A' only the summary on B. My macro ends up on sheet B but whilst the query is refreshing the raw data is displayed to the user on sheet 'A'. When I hide the sheet 'A' i get a run time error '1004' Select method of worksheet class failed.
View 4 Replies View RelatedI have a workbook with the following sheets i do not want to hide called "Detailed Template", "INTERFACES", "STATUS".
I then have a number of worksheets which have their names as cell addresses i.e. "E4", "E5", "D4", "F4", "G7" and so on.
I would like to hide all worksheets (except the 3 mentioned at the top [which are sheets 1-3]) and any which contain say the value of '4'. This value is defined by a cell in the "INTERFACES" worksheet say 'A1' for example.
I want the macro to be able to automatically do this for which ever value is in 'A1'. So if it were to change to '7' it would hide every sheet that doesn't contain '7' in the name.
I've created a spreadsheet that will be protect for use by others. I want to hide cells that are not used so it just leaves to print area in view and gives a simple and clear look.
I know how to hide rows, columns etc but there are always further rows and columns there. I'm sure this must be simple.
I have a VBA application that seems to work fine under vista but once I start working with it in Windows 7 I notice sometimes one of my userforms do not go away when asked to hide. The code is like follows:
Application.ScreenUpdating=true
Waitingform.Hide
I have two command buttons, and need one macro that would hide all blank rows between columns "E" and "O" and from row 9 to 42, and then another macro that would unhide all these rows again.
View 2 Replies View RelatedI have a spreadsheet with 5 worksheets going, I've been trying to find code that will hide and unhide certain tabs depending on the answer to C21 of Worksheet 1.
If C21 = 1 then show sheet 3 but not 4 or 5
If C21 = 2 then show sheet 4 but not 3 or 5
If C21 = 3 then show sheet 5 but not 3 or 4.
I am building a real estate model. What I am trying to do is the following:
If I enter the number of tenants in a cell (let's say the number of tenants is 6 and the cell I enter this number in is cell J11), I would like 6 of 15 rows to be shown below (rows 46-60). Therefore, the 9 unused rows (rows 52-60) would be hidden from sight. Furthermore, if I entered the number 3 for the tenant count, excel would hide 12 rows (rows 49-60). I am sure that this is a VBA code.
I am useing a user Form
On the Form which I call my main menu, theres a help button, with this code
Private Sub cmdHelp_Click()
MsgBox ("Help file not ready yet, good luck on your own, Have a Nice Day!")
End Sub
For some reason when this is click, and after the user clicks ok on the msgbox, the frmmain, which this command is on, minimizes itself
I'm trying to hide a UserForm (using myFrm.hide) once the command button on the form is clicked. I'm getting the following error:
Run-time error '402'
Must close or hide topmost modal form first.
Apparently the form isn't the 1st in the z-order. How can I see the z-order of my forms?