I have a file that contains at least 35 sheets. All of the sheets have some lines that are hidden. I tried selecting all the sheets and unhiding everything but it didn't work because some sheets actually have filters in them and the filter line is also hidden.
View 9 Replies
using Excel 2007/Vista
when I go a new edit a macro, I get the message
"Cannot edit a macro in a hidden workbook. Unhide the workbook using the unhide command"
Where do I find the relevant "unhide command"?
I was wondering if it's possible to unhide a row directly below one that is visible?
I am creating a template for users that allows them to choose which lines (rows) they would like to use. They then hit a button to hide all the other lines they don't wish to use.
For each row that is visible I then need the row directly below it to become visible so that the user can enter associated information.
I'm stumped on how to do it. I have the user entering "a" in column A for any row they wish to keep - but I'm not sure how to incorporate this into unhiding the row below (or if I'm on the wrong track with my thinking).
I want to unhide columns K:DY if cell B2 = ALL, if cell B2 is something other than ALL, hide columns K:DY.
Is there any VBA code for that.
I am trying to use the macro I downloaded from OZ Grid called "Enable Macros" to check if macros are enabled. Its works beautifully, except for the fact that it will unhide all sheets if macros are enabled. I would like to only unhide certain sheets. The sheet names are:
Sheet11(Weight & Balance)
Sheet5(Nav Log)
Sheet2(Database)
I believe this is the part of the code that must be modified, but I'm not sure how:
Code:
Public bIsClosing As Boolean
Dim wsSheet As Worksheet
Sub HideAll()
Application.ScreenUpdating = False
For Each wsSheet In ThisWorkbook.Worksheets
[Code] ..........
Also, I am planning on locking this workbook. If this macro can still run if the workbook is locked?
Link to where the macro can be downloaded:
If I have say ten rows hidden so that the numbers go from 50 to 61, how do I write a macro that will only reveal rows 51 to 60 one at a time, starting with row 51. If the macro is run a second time it should reveal row 52. A thirs time will reveal row 52 and so on. Finally, when all rows are revealed, the macro should not give a error wanring. Instead, it should say something like "No more rows available"
I have 100 rows of formulas hidden from row 5 to 105. Is there a way that I can type in a number, say 7, in cell A1 and have 7 of my hidden rows unhide to display the info in only those 7 rows?
Then, is it possible to make this automatic or will I have to actually run the macro everytime I change the value in A1?
I receive a lot of excel files and would like a macro that I can run to unhide any and all hidden sheets. The sheets will have different tab names. I looked on this forum and could not find the answer to this question, usually I can.
View 5 Replies View RelatedThe code below turns off/on all of the command bars when my workbook opens. The problem is that the Formula Bar does not turn back on when the workbook is closed.
Got the code in this Remove/Show All Toolbars.
Option Explicit
Private mFormulaBar
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim oCB As CommandBar
For Each oCB In Application.CommandBars
oCB.Enabled = True
Next oCB
Application.DisplayFormulaBar = mFormulaBar
End Sub.............
I have this file in which I have 25 empty rows in between different categories. On the side of each category there is a plus sign which is supposed to unhide an empty row below the last used one, and a minus sign which should hide the last empty row within the category (i.e. mobilization, earthworks...). I faced many problems and tried to simplify it as much as possible (trying the code for only one category, msgboxes with the values to understand what was going on) but no luck. The problem that I have been stuck at the moment is that when I try to count the rows from C73 to the last used row, it bypasses the hidden ones. I have a mess of a code and a print screen which I am attaching. printscreen.jpg
VB:
Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Range, Cancel As Boolean)
Dim rInt As Range
[Code].....
Trying to hide a rows based on value being inserted to A1 = 0 (if value is >0, then unhide), but it's not working.
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").Value = 0 Then
Rows("33:42").EntireRow.Hidden = True
Else
Rows("33:42").EntireRow.Hidden = False
End If
I highlighted a selection of rows and clicked HIDE, now I want to unhide certain rows and when I highlight the rows above and below and right click - unhide nothing happens. I need to Unhide to find something.
View 2 Replies View RelatedThe workbook contains 5 worksheets, 4 of which will unhidden just fine; the remaining worksheet ("AnnualBudget" refuses to unhide columns AD to IV. Of course, this is the sheet that contains data in those hidden cells and I cannot get to the data. The sheet is unprotected and all freeze panes removed.
I have copied this sheet to a new sheet in the same workbook:
If I copy only formulas and number formats, the new sheet has all columns unhidden. If I copy formats, I have the same issue with the new worksheet. It’s like these columns are forever locked from view.
FYI I am using Excel 2007 with a 2003 compatability worksheet
A copy of the workbook is attached. Any idea what’s happening?
i was trying to hide my worksheet menu bar, i was searching the web & i got this macro
View 3 Replies View RelatedI've come across a spreadsheet where certain rows and columns (typically top rows or left columns) are hidden; however, there's no way to unhide them (the unhide function is grayed out) and it doesn't seem to be protected or no visible macros/vba on the file.
View 3 Replies View Relatedwhen i type 100 in the cell & press enter it automatically change to 1..and when i type 1000 i change to 10.
i have select B1 to B5 then hide it.then i click A1.how do it unhide it?
I have a workbook with several sheets that I created some time ago. I have hyperlinks on the main sheet which point to other sheets in the same workbook which can be viewed when using the hyperlink, however the linked sheets are hidden. Somehow in this workbook I removed both the horizontal scroll bar and the lsiting of sheet names. I can not figure out how to get them back so I can unhide and or add new sheets.
View 4 Replies View Relatedi want to hide my sheet if A1.value = 0(zero) and if unhide the sheet if A1.value = greater than 0(zero)
View 11 Replies View RelatedI have a workbook with about 20 worksheets in it.
6 are visible
3 are hidden
the remaining are very hidden
I'm creating a "welcome" page to the workbook with instructions on how to update data. The data between the 20 sheets consists of pivot tables, charts and summary data. The Visible sheets are data for management's review (all protected). The 3 hidden sheets are pivot tables that the user needs to pull data from and the very hidden sheets are not to be seen by anyone.
In my "welcome" page, I added the instructions of how to update data, but wanted an area where I could assign a checkbox or button to click on for the user to unhide my 3 hidden sheets (say Sheet1, Sheet2 and Sheet3). I do not want it to unhide my very hidden sheets. Then, when the information needed is retrieved from those sheets, I would like the user to use a checkbox or button to hide the 3 hidden sheets again. Is there a way to do this?
I tried creating custom view but couldn't do that because of the pivot tables (the option was disabled). I'm a beginner in VBA so don't even know how to begin.
What I am trying to do is keep a few worksheets hidden until something is entered onto that sheet using a vlookup formula.
My Setup is that we have a daily schedule that is on "sheet 1" when we enter certain items on that sheet, vlookup allows them to appear on "sheet 4". now when an Items appears on "Sheet 4" that is when we would like the sheet to become "unhidden".
Using MVB I found - Sheets("Sheet4").Visible = True
I tried attaching an IF;then statement to it referencing cell (A5) but either it doens't work like that or the code was wrong.
Is there a VB routine that can unhide rows when I print?
View 9 Replies View RelatedI'm having a challenge with a spreadsheet that puzzles me: I'm using this quite simple code to hide one of the spreadsheets (named "Ark2"). It written directly in a module as a subprocedure and there's no more code than this:....
View 4 Replies View RelatedI have a spreadsheet that has 28 columns for time entries. Typically only the first 12 columns are used, so I would like to hide the remaining 16 columns (which makes the spreadsheet much more user-friendly). It would probably be nearly impossible to teach all of them how to Unhide the remaining columns (and re-Hide), plus I would like to use the full-screen function when employees enter thier times. I would like to use a form control in the column heading so that when the employees 'check' it, it will Unhide and then re-Hide the columns. Any way to do this? Seems like a VB thing to me (out of my league, but would be happy to add one in!).
View 2 Replies View RelatedI have a worksheet with a macro as follows: ...
View 6 Replies View RelatedIs there an easy way to make a macro to unhide all rows? I can do this to unhide a certain range however different rows maybe he hidden and so a set range won't work.
View 2 Replies View RelatedI wanted to know if there is a way to lock some sheets in a workbook to prevent anyone to view them. I have a workbook with sheets that have a database, it is shown the frontsheet, and when a person selects an option it will appear a sheet with the data selected initialy. I wanted the other sheet to be hidden and locked so the one goes to the unhide menu and selects a sheet to unhide a msgbox appears to ask a pass code.
View 3 Replies View RelatedAlready read some threads about how to hide and unhide an excel application.
I was able to hide my excel application, but, somehow I'm not ale to unhide it when I close my Form:
I want to create two buttons.. one name HIDE and the other UNHIDE.
What I need hide button to do when i click it is hide the 4 columns to the right (not always going to be columns b:e)
For the unhide button unhide the 4 columns to the right (not always going to be columns b:e)
I am looking for a way to hide and unhide tabs. The first sheet of the workbook is an information sheet and I have a dropdown box in cell N3 to select Yes or NO to correct financial issues. If I select NO I would like to also hide tabs for sheet 4 and sheet 22.
I have used this for hiding rows:
If ActiveSheet.Cells(2, 2).Value = 0 Then Rows(146).Hidden = True
Can this be adapted to hide tabs?
For the following code:
Code:
Sub ShowAdmin()
Sheets("Admin").Visible = True
End Sub
I'm getting the following error message:
Run-time error '1004' Unable to set the Visible property of the Worksheet class
