in my work sheet I need to Hide & Unhide some text box By checking a checkbox it means if user mark the check box the text box will be appear & if uncheck it the text box will be hidden
View 8 Repliesin my work sheet I need to Hide & Unhide some text box By checking a checkbox it means if user mark the check box the text box will be appear & if uncheck it the text box will be hidden
View 11 Replies View RelatedPrivate Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 13 Then Exit Sub
If InStr(Target.Value, "Other (specify in next column)") Then
Columns("N").Hidden = False
ElseIf WorksheetFunction. CountIf(Columns("M"), "Other (specify in next column)") = 0 Then
Columns("N").Hidden = True
End If
End Sub
but I have a lot of columns that I need to perform as above and I have put the code together as below
Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Column = 13 And InStr(Target.Value, "Other (specify in next column)") Then
Columns("N").Hidden = False
ElseIf WorksheetFunction.CountIf(Columns("M"), "Other (specify in next column)") = 0 Then.................
Using the above code, when I selected more than one cell anywhere in my workbook and pressed delete I was bugging out with a runtime error 13 message. You can see from the above code that I inserted "On Error Resume Next" - this got rid of the runtime error 13 message, but now when I select more than one cell and press delete, hidden columns are incorrectly revealed in my worksheet. how I can extend the working code at the top of this posting so that it works for a number of different columns in my Worksheet i.e. without the runtime error 13 occurring and without columns being incorrectly revealed.
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 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 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.
I 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 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 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 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
Could someone provide me with the VBA to 'toggle' between HIDING and UNHIDING columns within one macro
e.g. toggle between this hiding and unhiding the following
Sub HideColumns()
Range("G:G,I:I,AB:AV").Activate
Selection.EntireColumn.Hidden = True
End Sub
I am using the code below on my spreadsheet. What is does is hides Row #1 until Row 41 is reached. When 41 is reached Row 1 appears. Unfortunately (for me) I need to alter this code and was wondering if anyone could tell me if it is possible. First the
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Rows(1).Hidden (Target.Row < 41) Then
ActiveSheet.Unprotect
Application.ScreenUpdating = False
Rows(1).Hidden = Target.Row < 41
ActiveSheet.Protect
Application.ScreenUpdating = True
End If
End Sub
What I would now like to happen is that Row 1 stays hidden UNTIL Row 16 is OFF Screen. Can that be done? What has happened is that my row 16 contains the heads for my input table. I have now had to set the rows to resize to accommodate data input. If no rows are resized then Row 16 stays visible until I reach row 40. When row 41 is reached then Row 1 appears which contains my headers as well. I hope this makes sense to someone...
SO, IF I can get a code that would keep row one hidden until Row 16 is off screen then it will not matter what rows expand.
I made this simple little macro to hide/unhide a row on a different sheet based on a check box. The row hides fine. The problem I have is unhide. I uncheck the box, but the row stays hidden. What am I missing to make this little gem come to life? This is for tracking popcorn sales for scouting and I'm trying to make it as easy to use as possible.
Sub Patrol1_Scout1()
If True Then
Sheets("Show_n_Deliver_Sold").Rows("7:7").EntireRow.Hidden = True
End If
If False Then
Sheets("Show_n_Deliver_Sold").Rows("7:7").EntireRow.Hidden = False
End If
End Sub
It seems there may be an event or something that happens when a row is hidden/unhidden.
I have this simple function that gives TRUE / FALSE if the Cell's Row or Column is Hidden.
Public Function IsVisible(MyRange As Range)
IsVisible = ((Rows(MyRange.Row).Hidden = False) And _
(Columns(MyRange.Column).Hidden = False))
End Function
And a formula in a cell
=IsVisible(A1)
When I hide/unhide the ROW - the formula recalculates automatically without having to do the F2 Enter thing..
But when I hide/unhide the COLUMN, it does not recalculate automatically. I have to do the F2 Enter thing to get it to recalculate.
Even putting Application.Volatile in there doesn't do it.
I'm just curious why it recalculates when the row is hidden/unhidden but not when the column is hidden/unhidden? Is there a hidden event that could be used? I often see posts of people trying to prevent hiding rows.
I 'd like to open a xls where a combo appears only when i select a specific cell.
I created a combo in excel 97 (combobox1) and also i wrote some code for its behavior but it doesnt work.
Private Sub workbook_load()
ComboBox1.AddItem ("YES")
ComboBox1.AddItem ("NO")
End Sub
Private Sub ComboBox1_Change()
If ActiveCell.Activate = E4 Then
ComboBox1.Activate
End If
End Sub
I want to create a check box which will hide/unhide a given row depending on if selected or not.
I can create a macro to hide or unhide the row, but I can't figure out how to do both depending on whether the check box is selected or not.
I am trying to create a spreadsheet which will open with sheet1 showing 'Terms and conditions' with 2 check box's. Once the check box's are checked I then want subsequence sheets to unhide. E.g check box 1 ticked - will unhide sheet 2 - (containing the report) check box 2 ticked - will unhide sheet 3 - (containing the data)
Basically, by the use of check box's I am trying to get the user to read and agree to terms/disclaimer of the use of data. Therefore can the workbook also always open with the one sheet 'terms and conditions' showing only.
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
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.
When team number is entered, excel must show or hide the the team e.g when you enter 5 as the required team number then only 5 teams appear and the rest of the teams are hidden.
I have attached excel file that has the layout. Teams.xlsx
This is what I have been using but it has become very difficult since my Rows are always moving down when I insert new data into my pivot data source. So instead of having to constantly update my codes, I would like to just hide the entire PivotTable with my toggle button
[Code].....
I have a button that I want to hide/unhide a section of rows.
This is the code a have so far:
[Code] .....
So I've named a a section of rows "rngHideRows". I've tried to use the command .Range("rngHideRows").EntireRow.Hidden = True (and viariants of this) but nothing I try works.
code:
Private Sub Worksheet_Change(ByVal Target As Range)
With Sheets("Sheet2")
Select Case Target.Address(False, False)
Case "B12"[code].....
I need to change it so that it hides a range of rows, say (6:26) instead of adding a line of code for each of the rows from 6 to 26 that I want to hide
Also how can I go about hiding the same rows on 5 more sheets, can I add more sheetnames after "Sheet2"?
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