I'm looking to make a simple button that would hide a given range of columns.
This is the simplest I could find:
VB:
Sub button1()
Columns("AD:AE").EntireColumn.Hidden = Not Columns("AD:AE").EntireColumn.Hidden
End Sub
Although this works nicely, there was another way to do it (looks more ergonomic and doesn't take up spreadsheet space). Here's a screenshot of what I mean: ColumnHide.gif
Need a macro for each button.
The sheet will be protected with a password (in the future, users will have varied access privileges).
Column A is designated as the "Button" Column.
There are 5 buttons here. Each representing the area on the sheet that needs to be viewed. Once the button is pressed, it takes you to that section of the sheet. At this time, I have designated each column area as:
a-z
aa-az
ba-bz
ca-cz
da-dz
I have tried this formula with opening tabs, but this won't work.
I'm trying to record a macro which will hide and unhide columns K:P of data, but I only want one button. I know how to do this to produce one button for hiding and another for unhiding...but I want one combined button.
How to use vba, how I do this via the macro recorder?
I am trying to create a very large spreadsheet and i have got everything organized, but for easy viewing i want to have a toggle button that hides and un-hides certain sets of the columns (ie. Press down and it hides columns "B-F", "H-J", and "M-O".... then on depress it shows all the information again)
I have got some of the coding down, but am only able to get it to hide one set at a time, not all the sets. Can anyone help me out and let me know how i tell it to run multiple commands from the one press? here is the code i have already: ...
I want to hide & unhide columns using only (plus minus) button which is appearing on top of current sheet. I have couple of files with that options built in, but I dont know how to do it. I tried to see macros but appears that there is no macros at all in that file??
View 3 Replies View RelatedI'm trying to get a Button to hide and unhide rows depending on Column A only which is a pasted link to other sheets. The rows should hide when Cell A is empty or 0.
or 2 buttons one for hiding and the other for unhiding rows.
Here is my code ( Recorded by macro )
Sub Tally_Chck()
'
' Tally_Chck Macro
'
'
Columns("D:E").Select
Range("E1").Activate
Selection.EntireColumn.Hidden = True
Range("B3").Select
End Sub
I want Hide And Undie Column With One Button...
-Once Click Hide
-Again Click Unhide
Using Excel 2007, I figured out how to insert a Toggle Button and program it to hide or unhide my columns F and G (which show cost data for products we sell) depending on the state of the button.
What I'd like to add to the button is text and functionality as follows:
- Columns F and G are shown, Toggle button IS NOT depressed and the button says "Hide Cost"
- Columns F and G are hidden, Toggle Button IS depressed and the button says "Show Cost"
I figured out how to change the text on the button, but can't figure out how to change it based on the button state. I know there has to be a way to do this.
I have a TextBox and a ComboBox on a worksheet, is it possible to hide them with option buttons?. So only one box shows at a time.
Option Button1 show TextBox
Option Button2 show ComboBox
I have a command button that hides rows based on zero value in Col B then a 2nd btn to unhide those rows. The challenge is that as I want this code for several workbooks that will have data of varying row lengths & not all the zero values will be in the exact same place for each workbook or each month. Having done a little research on the Board (& googled) I have not found an answer to my challenge. So I am sure someone will point me in the right direction -
Private Sub CommandButton1_Click()
BeginRow = 3
EndRow = ActiveSheet.UsedRange.Rows.Count
ChkCol = 2
Range("B3:B" & EndRow).EntireRow.Hidden = True
For RowCnt = BeginRow To EndRow
If Cells(RowCnt, ChkCol).Value 0 Then
Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
Range("A2").Activate................................
I am creating a disclaimer in one of my sheets.
Cell I15 contains 'Are you a resident for tax purposes?' Cell N15 has the data validation list of Yes or No.
Then I have a button below which covers rows18, 19 and 20.
Is there a way I can have this button hidden until Cell N15 = Yes?
I am trying to make an excel toggle button and am stumped. I am trying to create a toggle button that hides the entire row if it finds a 0 in a preset range that I am calling "Alpha". I have tried this code but it's not working.
Code:
Private Sub ToggleButton1_Click()
If ToggleButton1.Value = True Then
For Each cell In Range("Alpha")
[Code].....
What the code does is, when the command button is clicked, if any of the validation list in a particular range is equal to"Select_Product.." or "Select Feature", then hide those rows.
Also, is there a way to toggle hide and unhide rows using one command button. I would like the code to not only hide the rows but when clicked again, it'll unhide it. Is this possible?
Private Sub CommandButton1_Click()
Dim c As Range
Dim d As Range
Dim myRange As Range
Dim featureRange As Range
I'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 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 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 once saw an excel sheet where I could hide or unhide a section by some + and - signes above the column-letters.... I have searched for this but I only get the ordinary hide/unhide solutions.
View 4 Replies View RelatedI have a single button I want to use to call a macro to:
1.Hide columns C:AZ if they arent already hidden
2.Unhide columns C:AZ if they are already hidden.
I want to set up a macro that will hide and unhide columns.
View 9 Replies View RelatedThe code below if I run once Hide the Range("H:FV,GG:IV") and shows the Range("A:G, FW:GF") And if I run it again Unhide the Range("H:FV,GG:IV") And Show all columns
Code:
Sub Hide_Unhide()Range("H:FV,GG:IV").Select
If Selection.EntireColumn.Hidden = True Then
Range("H:FV,GG:IV").EntireColumn.Hidden = False
Else
Range("H:FV,GG:IV").Select
If Selection.EntireColumn.Hidden = False Then
Range("H:FV,GG:IV").EntireColumn.Hidden = True
End If
End If
Range("A1").Select
End Sub
Above code is working with 2 cases now is it possible to add 3rd case Hide Or Unhide Range("H:GG,GI:HJ,HO:IV") in the same code, and shows the Range("A:G, GH, HK:HN")
Resume: my request
Step1-if I run macro First time it must Hide Range("H:FV,GG:IV") and shows the Range("A:G, FW:GF")
Step2-If I run macro Second time it must Hide Range("H:GG,GI:HJ,HO:IV") and shows the Range("A:G, GH, HK:HN")
Step3-if I run macro third time it must show all columns
And repeat same cycle all time Step 1 to 2, Step2 to 3 and Step3 to 1
I have a worksheet with a fair amount of data. It is split into two parts: the main part is a table with data in columns D to AR while the other part is simply a list with checkboxes against each item. I have set things up so that when a checkbox is ticked against an item in the list, then a 2 appears in row 2 above the relevant column in the main table; if the checkbox is unticked then a 2 appears above the relevant column.
What I seek is a macro that will hide all columns that do not have the associated item ticked; in other words, I want columns to be hidden if there is a 1 in the relevant cell in Row 2 (and visible if there is a 2 there).
I have used the following code (obtained from this forum), but it doesn't work as expected. When I select the items, the cells in Row 2 react as expected, but the hiding and unhiding only occurs when I go to another Worksheet and then return to the Worksheet where the data is. Obviously I want the macro to work immediately I tick or untick a checkbox. What is wrong with the code I have used?
Private Sub Worksheet_Activate()
Call HideCols
End SubSub
[Code]....
I'd like a macro that can hide/unhide certain columns. At the moment, the columns I want to hide/unhide are F, I, M, P, U and Y.
View 4 Replies View RelatedI have a range of columns ( J:AH) which i have to hide from the user interface.In order to unhide, the editor must enter a password. He/ She can only enter the password thrice. After 3 times, if it is still wrong, the whole workbook must be closed. Is this possible? If not, I'll settle for something less complicated.
View 9 Replies View RelatedI need hide/show some column by using Macro Button. I have attached the excel sheet( name VBA testing.xls). I need to hide column K,L,N,O & visible column G,H by clicking button "Plan A".Similarly i need to hide the column G,H,N,O & unhide the column K,L by clicking the button "Plant 2. Similarly by clicking the Button "Plant 3", hiding the column G,H,K,L are needed whereas column N,O will be unhide.
View 7 Replies View RelatedI have a sheet that upon opening a macro runs to hide a selection of columns based on cell containing certain words (see here).
I now also require the workbook to be protected so that the hidden columns (which Im sure are locked as default when hidden) are locked as are columns N & S. Now, I know I can do this by protecting the sheet and unprotecting the columns I require (ie N & S - with the hidden cells remaining locked anyway).
The issue is, every time the workbook is opened this protection must be in place.
However, I require a button, which could simply reside in A1, that when pressed, will unhide and unlocked all cells. - This is needed as its vital I can easily copy rows to another sheet (and delete from existing sheet) as and when required.
if possible, re-pressing the button would hide and proetct the worksheet however this is not too significant as long as when I close and open the workbook everything is hidden and protected as specified above.
There is no need to password protect anything.
I have a speadsheet that has 27 columns for time entries, however employees seldom need to use more than the first 12 columns. I need to protect the sheet but would like the employees to be able to Unhide the columns when they need to use them, then reHide them. I can't find this as an option in either 2003 or 2007.
View 2 Replies View Relatedworksheet I am working on at the moment, basically if row 5 has a 0 displayed I want that column to hide, but if row 5 has text of any value displayed I want it to unhide, the range is E5 to BA5 across.
I have draft VBA code as follows:
I'm looking for a macro to hide and unhide columns..
Book1 is where I want my macro to sit with 1 cell showing the period number 1-12.
Book2 is where my data is dispalayed (I've got about 44 of these to update)
B4:M4 in book 2 (and all data sheets) has the period number in it.
I would like to be able to update the period in book1, run the macro, and end up with book 2 showing column A, the current period column, column N.
I'm fairly new to writting macro's and think I'll be able to addapt the formula to work for 44 sheets once I have it working for 1!
I need to write a macro that will hide and unhide columns based on a cells value. I know how to write it to hide and unhide rows, but i can figure it out for columns.
For the rows I am using the following:
Sub HideRowsSavings()
Dim LR As Long, i As Long
Application.ScreenUpdating = False
With Sheets("Savings #4")
LR = .Range("A" & Rows.Count).End(xlUp).Row
[Code] ......
I am trying to hide or unhide columns E:BL depending if the the cell value in row 54 contains a value (Note cells in row 54 contain formulas). I have written the following code but get a runtime error on the else statement.
Sub UpdateCashflow()
Application. ScreenUpdating = False
ActiveSheet. Unprotect Password:="LD"
ActiveSheet.Select
Dim c As Integer
For c = 5 To 64
If Cells(54, c) = "" Then
ActiveSheet.Range(Cells(54, c)).EntireColumn.Hidden = True
Else
ActiveSheet.Range(Cells(54, c)).EntireColumn.Hidden = False
End If
Next c
ActiveSheet.Protect Password:="LD", DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.EnableEvents = True
'
End Sub