I have looked through alot of this board and see the codes to hide all tabs old and new and code for each sheet.
My question is:
Is there a macro that can be used to do this as well. Say a button to hide all tabs and a button to show all tabs or does it only hide/show when the workbook is re-opened?
I am trying to find a library with vb/macros that can hide and unhide all worksheet tabs except one, the formula, and the menu bar.
View 1 Replies View RelatedI am trying to make a save&close workbook macro.
I found several examples on google, but unfortunatly it conflicts with another macro I use for forceing users to enable macros (hide all sheets except one if macros are disabled).
The attached file is an example contaning the save&close code and the show/hide sheets depending on macros enabled.
If the file is opened with macros disabled then only one sheet will be visible.
If the file is opened with macros enabled other sheets are visible.
The problem if that this code uses a custom save, witch makes the save&close not save... (in module1 and in ThisWorkbook)
The pourpose of the save&close is to make sure some users don't forget the excel open and thus block access to it. So if a certain idele time passes excel has to save and close without any confirmation messages.
I'm trying to create a macro that will hide all the rows where the value in column E is equal to zero.
I'm currently using rows 1:700, but I may add to it.
I have a standard laid out spreadsheet, but in column C, D and E, there are comments in each cell which are filled with a lot of text. I would like to set a macro so that whenever an 'a' is in column A, the comments for that row are shown/hidden (the 'a' being a tick symbol in Webdings). So if I place an 'a' in A4 and A23, and click a show/hide button, it causes the comments in C4, D4, E4; C23, D23 and E23 to be displayed, and when it is clicked again (or it could be a separate button), they disappear. If no row has an 'a' in the A column, then the button has no action. I have recorded a Macro of me showing and hiding comments, but of course that is for a specific row.
View 6 Replies View RelatedI have a simple macro that I have been using to hide columns in a very large spreadsheet. Essentially, the user has access to buttons that allow him to choose between a variety of the most commonly used views. For some reason, when I add columns and adjust the code to hide/reveal these columns, I get:
"Run-time error '1004' - Unable to set the Hidden property of the range class"
with the Debugger highlighting the code for "BO:DC". This problem occurs for several of the similar buttons, including toggle buttons, that hide/reveal columns. I am aware that custom views can be created in the drop-down menu, but I wanted to keep these buttons on the sheet as a quick means of moving from view to view and toggling columns between hidden and revealed.
Private Sub CBMonographMLA_Click() ...
I got a quite huge excel file with multiple sheets. For convenience sake I want to group and hide all the sheets not necessary for the viewer.
View 14 Replies View RelatedGetting a macro to work. I've looked through the forums pretty extensively but ad I'm not too hot with the old vba, I haven't been able to get it working.
I have two worksheets in the same workbook. The first worksheet, let's call it Input, is one for data entry; and the second one, let's call in Output, is formatted for printing.
There are 8 drop down boxes from a data validation list, that when a particular option or three are selected, I need the Output worksheet to unhide only the rows associated with those options.
In trying to get this all to work, I'd added a function in the cell to the left of each option in the Output page that will show the text "show" when that option is selected on the Input page, or the text "hide" if not selected.
I think I could do this with some time with a clunky and long macro, but would prefer to us some kind of "for each" option to hide rows that have "hide" shown in column A, as I'm looking at a range of 100~ cells.
I found this code for a button, so I can collapse and expand a set number of rows within that sheet. It works exactly the way i want it to, however, now I have a second sheet in my workbook, and I used the same button. Problem here is that when I activate the button, it opens the same rows in EVERY sheet in the workbook. "For each ws in Worksheets" so my question is what is the term for it to only work on a certain worksheet?
Code:
Private Sub ToggleButton1_Change()Application.ScreenUpdating = False
Dim ws As Worksheet
With ToggleButton1
If ToggleButton1.Value = True Then
For Each ws In Worksheets
[Code] ........
Other questions is about the ability to send an MS Outlook email from inside of excel.
I've been trying to find something that does the following:
Click button, Form pops up, has drop downs to select recipient, has field for subject, has field for message body, sends email.
I have put together a workbook that utilzes 28 seperate worksheets. Of which I currently get it to show 9 worksheet names. Is there a way to show more tab names, like by having 2 rows of tabs instead of 1 or something like that?
View 5 Replies View RelatedI have a "master" tab that has a dropdown menu (via a validation list) which will determine what the spreadsheet does. Based on the value selected via this dropdown, I would like to show the relevant tabs and hide the irrelevant tabs. I would prefer for this to happen automatically without a macro.
View 5 Replies View RelatedI 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?
I was wondering if it is possible to hide workbook tabs according to cell value?
Take for instance, if cell B30 in sheet "Fee Schedule Finder" is blank then the sheet "ES Schedule" is hidden. I know it would be some sort of VBA code but I am lost on this one.
If I didn't explain well enough please let me know!
I've done a search for this but cant find the complete answer for what I'm looking for. Basically I've got a "Home Sheet" set-up where the user can only navigate to worksheets via the buttons on the home page (with a short- cut to go back to the home page). It works ok (very basic though) but the user can still use the worksheet tabs at the bottom of the screen to navigate (making this system void).
how I can, either, hide the worksheet tabs at the bottom of excel, or, a better way of navigating these worksheets (there is only about 6 of them).
Im creating a userform, i have two choices, when a combobox event changes, a second form loads, thats fine, but would it not be more efficient if i were to have hidden tabs on the main form? if so - can anyone advise how i can hide the tab/tabs until the combobox event is triggered.
My "main" aim is to have a VERY quick and slick user interface.
If required i can upload the code - just shout.
knew if there was a way to hide the available tabs in an excel worksheet.
I know you may be thinking just right click and hide, however what I am looking for is a little different.
Basically I have a Macro Enabled work sheet which works on a "one click" system. The work sheet is full of buttons with assigned macros and data validation drop down boxes which link to many different tabs. The entire sheet is for information only so I will be locking the sheets to avoid data manipulation. The problem with right clicking and hiding the tabs is that it interfears with the macros.
All I really want to do is make these tabs not visible. I have set the sheet to open in full screen mode and they obviously are still there, but this is basically what I am looking to do. Not so much hide them in terms of sending them into the background, more so make them "drop off" the bottom of the screen to they are still active but not visible.
I have about a dozen tabs, all of which have vlookups in them and calculate based on what a user pastes into Sheet 1. I need a macro that hides all tabs that have no data, essentially tabs where A1 = ""
However, it also needs to unhide tabs as soon as there is data. So if the user pastes new data into Sheet 1 the vlookups, on the other sheets, still need to run (even if they are hidden) and then check against the macro to determine whether to be hidden or unhidden.
Is it possible to hide specific worksheet tabs.
I have a workbook with many worksheets and I only want to hide Sheet1 and Sheet2.
Does anyone know of a way with VBA to hide the tabs when saving excel as a web page?
View 7 Replies View RelatedI have tabs within my workbook that are pulling from other tabs in the same workbook. There are 30-40 tabs, and only 10-15 are used at a time. Is there a way to hide tabs that are not being used? If I do a calculation in A1 and it's greater than 0, I'd like the tab that is pulling from A1 to be visible. If A1 is 0, then I'd like to hide it.
View 14 Replies View RelatedI have nearly finished my project, but need to make the last real bit of code, then just some general tidying up.
What I want to do
- I currently have two tabs in a userform - one called 'main' and one called 'extra'.
- I want Main to always be shown to the user.
- I want Extra to be hidden (is it possible to make the tabs invisible?) and for this form to be shown only under a specific condition
I have a question on my userform (using Option Buttons):
"Did the customer ask about an extra product today?"
If the answer to this question is no - When the user hits the command button (after filling out the rest of the form), do nothing special - just return all the values to the worksheet
If the answer is yes - When the user hits the command button, I want them to be taken to the 'extra' tab. They will fill out some additional check-boxes, then hit another command box, which will return the info from both the 'main' and 'extra' sheets into the same row of the worksheet.
In brief: I want my 'extra' tab to be hidden, only to appear when 'yes' is selected, and for the results all to go back to the worksheet.
i need a code to hide the sheets tab from the workbook. and only show the horizontal scroll bar and vertical scroll bar.
View 1 Replies View RelatedI need to select all yellow tabs (color code 6) in a workbook with over 70 tabs and hide all empty rows within A1:I36 on each of these yellow tabs. the position of the tabs needs to be unchanged (sorting by tab color not allowed). I got this code from another excel forum but somehow it only works when i select one yellow tab and run it and the code only works on the one yellow tab i selected. can fix this code so that it can loop through all tabs (yellow and non color) and do what i mentioned above for each yellow tab?
Sub HideMT()
Dim Ws As Worksheet
Dim wsColor As Long
[Code].....
You could use:
If ActiveWorkbook.Sheets.Count < 17 Then
Application.CommandBars("Workbook tabs").ShowPopup
Else
Application.CommandBars("Workbook tabs").Controls("more Sheets...").Execute
End If
The code doesn't do anything on my excel workbook which currently has like 20 workbooks, and it grows daily by like 4 or 5 worksheets.
get my code to work.
I have a sheet with a dropdown box in cell "J1" which is meant as a 'Show only rows containing this value'.
Column Q, from row 3 downward has a pick box which lists the same values as the "J1" drop down box, but uses code to combine whats chosen with commas. An example of what is in a cell in column Q is "SeaHawk, BlackHawk, Squirrel, MRH", where cell J1 would be either "SeaHawk" or "MRH" or something completely different.
I need to only show rows, with text in column Q containing the smae text as J1.
My Excel>Preferences>View>Comments is set to Comment Indicator Only.
Working from keyboard and mouse, when I click on a cell holding a comment ($B$52) , the comment appears. When I click on a different cell the comment disappears.
When I run the code
...
Range("B52").Select
End Sub
the cell is selected, but no comment appears.
When I run
...
With Range("B52")
.Select
.Comment.Visible = True
End With
End Sub
the comment remains visible even after I click off of B52.
I get the same behaviour when I use Application.Goto rather than Select.
I would like to write a routine that
1) creates a comment for cell B52
2) selects B52
3) shows the comment in B52
< VB routine ends >
4) comment disappears when user clicks on different cell.
Does this require event code?
I have two options in a Cell as Applicable and Not Applicable. This is in a Drop-down menu and what I want is that when I choose Applicable, it should bring up additional 5 or 6 cells for me to complete. And if I choose Not Applicable, the additional cells should not show up. The default would be Not Applicable.
I bet you're getting sick of the "X" questions, and I have done an exhaustive search but the search filter kills searching for (X) or 'X' or "X" .. but nothing I did find is what I am looking to do. I have any # of userforms that when you click the "X" in the top right side, it will hide the userform... once.. Is there a fix to the code below to make it do it every time its clicked?
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode <> 1 Then Cancel = 1
UserForm15.Hide
UserForm11.Show
If CloseMode = 1 Then Cancel = 1
UserForm15.Hide
UserForm11.Show
End Sub
Like I said, I don't want to disable it, I tried that off of one of the posts I found and thats just plain annoying, I just want it to hide the one userform and show the other. This will do it once and then you can click it all you want and it wont do anything.. see the annoying trait above.. this lasts until you exit the program.. so the X button can be hit repeatedly though out the day. The userform 15 is visible, userform 11 isn't shown, IE not behind userform 15.
how to creat a userform with a button (1) to show anouther userform with a button (2). you click the button on 1 and it shows 2. then when you click the button on 2 and it shows 1. I'm having a problem with the second part.
View 5 Replies View RelatedIs there a method of formatting specific cells to show zeros? Or indeed I could turn on show zeros in options and then hide the ones I don't want. Any tips / advice on how to selectively display (or hide) zeros appreciated.
View 3 Replies View Related