I have a worksheet that contains over 15k rows, each row, in column AK has an input of X-123, x123, 123, ENVEL, ROL, WPL-503, etc. there are a total of 20 different inputs. These inputs are found on another file and inputted from with a Vlookup formula.
I tried advanced filter, but it is limited and thought, if I have a command button that opens a userform then I can select which ones I want to be hidden.
I don't know exactly if I should use a checkbox, listbox, combo box ect.
I got a worksheet here. I'd like to lock all the cell height and width using protected sheets function. I realized from time to time I have the need to hide them. How do I enable hiding sheets while maintaining cell integrity?
I have a worksheet "Feature Segments" that has a value in cell B40 and C40, if the value in these is "Off (Default)" then I need rows 22 and 23 on Worksheet "Summary" to hide.
I have put this code on the features segment tab but it is not working.
Code: Private Sub Worksheet_Change(ByVal Target As Range) Application.ScreenUpdating = False If Target.Address = "B40" Then
This code will hide row 2 across Sheet1, Sheet2 and Sheet3 while recording in the macro record mode but when played back after recording will only hide row 2 on Sheet1. How do I get this code to work?
Sub MultiplePageHideRows() Rows("2:2").Select Sheets( Array("Sheet1", "Sheet2", "Sheet3")).Select Sheets("Sheet1").Activate Selection.EntireRow.Hidden = True Range("A1").Select Sheets("Sheet1").Select End Sub
Getting 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.
Say I have two worksheets, "Sheet1" and "Sheet2". Let's also say A1 in Sheet1 could have one of four values: cat, dog, rabbit, mouse. Now, based on which value cell A1 on Sheet1 is, I need different rows hidden in Sheet 2. For argument sake let's say if cat is chosen rows 1-5 are hidden, if dog, 6-10 and so on.
The workbook has multiple sheets, and a cover sheet. Each project has a sheet and the status, costs, updates ect. are updated to the cover sheet.
Problem: What happens is there is a cell which has conditional formatting on it, e.g. If text is Green then colour cell green, if cell text is amber then colour amber and if cell text is Red then colour Red. This works fine.
If the cell text is green then I need a number of rows hidden, if it is amber or red then show the rows.
I have found the following Sub If Target.Value = 1 Then Rows(Target.Row).RowHeight=0 Else End If End Sub
Which I have changed to:
Sub If F4.Value = Green Then Rows("5,6,7,8,9,10.Row).RowHeight=0 Else End If End Sub
So this does not work, need to know why and how to make this work for each sheet I am on e.g. ActiveWorksheet?
I have a macro in which i can enter the rows i want to hide.
If i want to hide "position 32" i have to enter the number 8 of the row. This works fine. But now if i want to hide the "position 32" from Sheet1 it also should hide the rows 4-8 from Sheet2 [Data with 32].
Or if i hide "position 34" in Sheet1 [row 10] it also should hide the rows 14-18 in Sheet2.
If Sheet1.Range("A34:A94") = "HIDE" Then For Each cell In Range("A27:A94") If UCase(cell.Value) = "HIDE" Then cell.EntireRow.Hidden = True End If End Sub
I have a worksheet used for inventory. In Column A is the quantity (to be entered manually). In Column B is the product description. In Column C is the price of the product, and Column D the total price (column C price x the quantity entered in Column A). At the bottom of the worksheet is a grand total. Also, Column B (products) is grouped into subheadings by the supplier each product came from (for example, row 6 has the title PPG, and then rows 7-137 list every product from PPG).
The calculations in this worksheet work fine. What I am trying to do is, using a macro once all of the appropriate quantities are entered in column A, automatically hide every row of product that does not have a quantity. The tricky part is, if no products under a given supplier subheader are entered, the subheader also hides, and if a quantity is entered, that subheader shows. For example, if I have no quantities under any products for PPG, then the PPG subheader hides, but if just one quantity is added, PPG shows. Also, this list will be constantly updated, new products will be put in and taken out all of the time, so I cannot base the macro on a specific number of rows.
Count all the true statements in column A (Work) of sheet1 (Checklist), once counted insert that many rows on sheet2 in a specific location, I found a count formula just don't know how to do the insert rows part
Code: Sub CountRows() Dim Rng As Range, CountTrue As Long Set Rng = Sheets("Checklist").Range("Work") CountTrue = Application.WorksheetFunction.CountIf(Rng, "True") End Sub
I want to know how do I hide my "Raw Data" worksheets? I have about 10 worksheets in a workbook and just need to hide the first one - where all the raw data is store...
We're trying to create an invoicing sheet with MS excel for our sub contractors but we also want the information they input to be transfered onto a second work sheet within the work book that has our mark up added.
Problem is that we don't want our sub contractors to see the sheet with the mark up. Is there any way to hide a work sheet or to limit access to the work sheets some one can veiw with in a workbook?
I have an excel workbook that has multiple worksheets that is linked to our in-house system via ODBC and it refreshes every 5 minutes with up to date sales data.
I am looking to output just one of the work sheets onto a large LCD display / wallboard.
I need to just display the summary worksheet without the excel toolbars / gui
I put the correct cell in place $K$23, now I want to hide a worksheet called "DutyCode" when info!$k$23 is blank or has "xx" or "XX" in the cell, and then unhide "DutyCode" when the number 27 is inputed in info!$k$23
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address "$k$23" Then Exit Sub If Target.Value = 1234 Then Worksheets("Sheet2").Visible = True Else Worksheets("Sheet2").Visible = False End If End Sub
In excel I would like to create a macro which wil be able to hide all lines were a text (to be defined in a cell for example) is not present Here is an example:
linesdata:X#1#2a#3b#4b#5a#6a#7c Pending X value lines #2 to #7 will be hidden or not
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).
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.
Is there a way to either hide or disable the worksheet menu bar on ONE SHEET ONLY, so that a right-click customize doesn't even work on that sheet. I want the user to be able to do nothing but what my customized toolbar allows on that sheet.
I am trying to write a code that would hide when certain cells in the worksheet are empty. Also the sheet name should start with a -.
This is the code i have so far. The thing is that the sheet will always hide. The criteria on the cells doesnt work.
Sub Hide_all_filled_Templates() Dim ws As Worksheet Application.DisplayAlerts = False For Each ws In Worksheets If Left(ws.Name, 1) = "-" Then If Not Range("I9").Value = "" Or Range("K9").Value = "" Or Range("M9").Value = "" Or Range("O9").Value = "" Then ws.Visible = False End If Next Application.DisplayAlerts = True
End Sub The code now hides all shees starting with "-". It does not take the cell criteria into account. Why?
Other point is that these cell references are just a few of what it should really be. How do I make this easier for myself to write the code. Point is that these cells come in row 9 (like the example) then in row 11 then in row 15, 17, 21,23, etc. Also the columns jump with uneven steps.
I've been trying to make use of 'Run "doit", but the macro stops when it gets to my sheets called 'RST' and 'RST Pivot'. What I'm I doing wrong?
Sub DoIt() Application. ScreenUpdating = True With Sheet1.Shapes("Rectangle1") .Visible = msoTrue = (Not Sheet1.Shapes("Rectangle1").Visible) End With 'Toggling sheets Forces Rectangle 1to show while code is running Sheets("RST").Select Sheets("RST Pivot").Select End Sub
Also, do I need to change 'With Sheet1.Shapes' to reflect the actual sheet name?
I got two worksheets visible for user to enter information with vba codes behind the sheets(i.e. Private Sub Worksheet_SelectionChange(ByVal Target As Range). Most cells of the worksheets are protected if the vba is executed. However, it was found that if user chooses not to run the macro when prompted, the worksheet can be edited whatever user want. So, how can the worksheet whole worksheet is protected if macro is not run?
I need to be able to save a copy of my spreadsheet but protect the structure so that the hidden rows cannot be opened by the person that I e-mail it to. At the moment I am using the current
Private Sub CommandButton2_Click() Do fName = Application.GetSaveAsFilename Loop Until fName <> False ActiveWorkbook.SaveAs Filename:=fName Call ProtectRobin End Sub Private Sub ProtectRobin() ActiveWorkbook.Protect ("Robin") End Sub
The saving part of the macro is working perfectly, however I cannot get the protection to work. It must have a password - I do not want the receipient to be able to go 'Tools' > 'Unprotect Sheet'.
