I need a VBA code that will when used with a Form Control "Check Box" will unhide / hide a row. To be more exact, I'm needind the code to "Hide" row 34 when unchecked and "Unhide" the same row when checked. I'm using Excel 2010.
View 9 RepliesHow can i hide and unhide column and rows using checkbox in the excel.
detail price qty amount
chair 1000 1 1000
some time i just want to see only the amount or some time i want to see the qty, how can i hide and unhide row or Column
I have an Excel 2010 file with 10 worksheets. I would like to set up two VBA codes to attached to a two button; one to hide specific worksheets, the other to unhide. The specific worksheets are:
Calculation Sheet
GL Receipt
Sheet2
How can i hide and unhide one checkbox using another one? Can it be done using IF formula?
And also i am using this checkbox to function something else as TRUE/FALSE.
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 need to hide / unhide some rows in a sheet with the help of a activex checkbox... I want remove, let's say row 20:24 and row 34 with one checkbox.
View 6 Replies View RelatedI am having difficulties with this one. I have a button on my worksheet (sheet1) that runs a macro.
View 4 Replies View Relatedi'm newbie here. i need to hide & unhide rows using checkbox in excel but i stuck. i write these simple code and it doesn't work at all. my bad..
View 2 Replies View RelatedI've had luck with hiding rows with a checkbox, but I cannot get them to unhide when I uncheck the box.
Here's my scenario - (using Excel 2000) In an estimating spreadsheet I have a print range of A1:N74. What I'd like to do is add a checkbox or button so I can hide and unhide rows based on a null or zero in column range b5:b62. This is for printing purposes, but I can't get a 'beforeprint' event to work either. All I need is box checked and cells hide, box unchecked and cells show (unhide).
Sub CheckBox1_Click()
Dim Rng As Range
Dim MyCell As Range
Set Rng = Range("B5:B62")
For Each MyCell In Rng
If MyCell.Value = "" Then
MyCell.EntireRow.Hidden = True
End If
Next MyCell
End Sub
I want the option of printing my form with and without a certain column. I need to hide a column when CheckBox.value=True and unhide a column when CheckBox.value=False
I keep getting errors:
Block If without End IF
End If without Block If
Else without If, when I use an else for the second If statement
Private Sub CheckBox1_Click()
Dim DescriptionCell As Range
Set DescriptionCell = ActiveSheet.Range("B:B")
If CheckBox1.Value = True Then
With DescriptionCell
.EntireColumn.Hidden = True
If CheckBox1.Value = False Then
With DescriptionCell
.EntireColumn.Hidden = False
End With
End Sub
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 11 Replies View RelatedI would like to keep the current macros that I have and add the following:
A macros that can link the check boxes with the corresponding categories and rows in the other two sheets. If the box is checked the corresponding rows in the other sheets should appear.
I have color matched the check boxes with the rows on the two other sheets I would like to be linked up with.
I need to work with the run times for some equipment. This is just hours and minutes and I get the raw data as like "Pump # 1 - 35:30". This is not date/time data per se, just a raw number of hours. I thought I would format the cells as hh:mm and for 15:00 I would just enter 15 to save time. No go, that becomes 1/15/00 00:00 and displays as 00:00. I discovered that if I just leave the formatting as general and enter 15:00 it works fine and displays right but the formula bar says 3:00 PM (Excel figured to format it hh:mm). When I enter 30:00 it again seems fine but the formula bar says Excel stores it as 1/1/00 6:00:00 AM (Excel figured to format it [h]:mm:ss but I deleted the seconds).
I can just set the formatting as [h]:mm to be consistent and enter the full 15:00 but I guess that after working that out I am just curious - can Excel store hours:minutes as just hours:minutes without turning it into a meaningless date? And without converting it to a decimal number? I don't want to total 1:30 + 1:45 = 3.25; I want the total to be 3:15. Again, it appears that [h]:mm will do what I need but it just seems unelegant to me that it is stored as some weird date and I wonder if it will cause a problem at any point.
I have a 2010 excel workbook with several locked worksheet (to which I manage the PW). I and another staff member manage different section of the macro but the other staff member doesn't have access to the locked areas.
Is there a way I can encrypt the password within VBA so it's not visible to the other staff member?
Locking the VBAProject doesn't work as the other staff member has to be able to edit his VBA section.
Many staff run the macro (via a button) and don't need to access the Macro and don't have access to the protected sheets.
I understand excel isn't ideal with PW protection for people wanting to bypass the protection and this isn't an issue.
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 have a file which contains many worksheets with many buttons on them for doing administrative work when updating data. I want to be able to click on a button on my "admin" worksheet to hide all the other buttons on the other worksheets. This is hide the buttons from the view of 'readers' so that they will not be tempted to click on a button to see what it might do. I have tried several different variation of coding, but can not seem to get the "right" object to work with so as to set the code to be xxxx.visible=false. I seen references to Shapes, Shape, Control Toolbox buttons objects, CommandButton, etc.
I would really appreciate if someone could give some pointers on how to designate in VB code, which will lend itself of future updates, the following:
in a toogle button, loop through the series of worksheets making each command button on that sheet set visible = false. Then for the other portion of the toogle repeat the process, but set visible = true.
Within a worksheet "Page_2" I would like VBA to perform following calculation: IF(COUNTIF(R:R;1);1;2)
Basically, look into column R of worksheet "Page_2" and look if there is in the column at least one number with the value 1, if there isn't show me value 2.
Depending on the value coming out of this function, VBA needs to hide worksheet "Page_3" if the value is 2 and unhide worksheet "Page_3" if the value is 1.
Here is the code which I'm using and that is not working.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Application.WorksheetFunction.CountIf(Range("R1:R5000"), 1) = 1 Then
Worksheets("Page_3").Visible = True
[Code]...
I am trying to create an input sheet with a specified number of rows. My plan was to hide all the empty rows in the table and ask how many rows were required in the table. A button would then be pressed to unhide all the relevant rows thus giving a table of the correct size. My best effort so far looks something like this:
Sub UnhideRows()
Dim i As Integer
Dim myRow As Integer
myRow = Range("A1") + 2
Application. ScreenUpdating = False
For i = Selection.Rows.Count To 1 Step -1
If WorksheetFunction. Sum(Selection.Rows(i)) > myRow Then
Selection.Rows(i).EntireRow.Hidden = True
End If
Next i
Application.ScreenUpdating = True
End Sub
the cell to give the size of the table is A1 and the table starts in A3. The first cell in each row has an index number (=row() - 2), all other cells would be empty to begin with. My attempt did not work.
Code:
Sheets(Array("Sheet 1", "Sheet 2")).Visible = False
How do I convert the above to using Sheet Codes Names, Sheet1 and Sheet2?
Want to ensure my code will work if the user changes the sheet name.
I am trying to hide unhide excel rows based on the answer in "A1". Row 2 to 10 shall be unhide if answer is "Yes" and it shall be hidden if answer is no.....
[Code] ......
i want to Hide & unhide ribbon of excel 2013 using shortcut key
View 3 Replies View RelatedUsing Excel 2007, I have a workbook with 7 sheets. The first one is a Navigation Page where I have checkboxes (form controls, not active X) with the names of the other 6 sheets. When the box(es) are checked, the sheet(s) become visible. I have accomplished this by assigning macros I recorded.
I need to now add the opposite: When the box(es) are unchecked, the sheets become hidden. From googling and looking for other threads/forums here, I gather that I need to add code/ VBA, but I know nothing about these at all.
i want to hide and unhide headings from all sheets at once (Excel 2013)
View 8 Replies View RelatedExcel 2007.
I created an excel Calendar that has columns for months and weeks, and rows for hours of the day.
I have two drop lists one contains months, and one contains weeks.
Basically if you choose august from the first drop down list, all the rest of the columns that are not August disappear, and the sheet only shows august.
The second dropdown selects the week. If you choose week 1, it shows week 1 of this particular month. This second drop down is what I do not know how to make work.
This is the VBA code i used.
Private Sub ComboBox2_Change()
Select Case ComboBox2.Text
Case "May"
Range("All").EntireColumn.Hidden = True
Range("May").EntireColumn.Hidden = False
[Code] ......
I do not know how to make the code or choose the categories so that It only shows the week of the month chosen in the first column. Will I have to name each and every group of columns for each and every month as May - Week 1, May - Week 2...... June - Week 1, June - Week 2, etc... or is this a way around it?
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].....
I have an input spreadsheet to capture site addess details using Excel 2010
The Site Name is in Column A starting at row 8, each row is formatted with validation rules etc. but want only expose rows with data and one blank row to add next site.
So I needed code to unhide the next row without data - found what I needed in this thread
Have adjusted to my needs
[Code].....
It works by adding the next row but if I then delete the last rows Site Name I get a run time error with "Unable to set the Hidden property of the Range class" at follow code line.
[Code] .....
I currently have the following Macro for one of my many checkboxes in 2007 Excel:
[Code] .....
It works perfectly until additional rows are added/deleted before the indicated rows in the code (It changes the number sequence in the workbook). The number sequence stays the same in the code which means I am now hiding rows either before (delete rows) or after (insert rows) the intended rows I want to be hidden. Is there a way to change the above code to remain with the assigned rows regardless of the adding/deleting of rows before it?
How do you changethe font size in acheckbox in excel 2010?
View 1 Replies View RelatedI am copying a large table of data from a report generated in Firefox and pasting it into Excel 2010. The data has several columns of html checkboxes. I need to do two things with the checkboxes and would like to do a third:
1: Count how many checkboxes are ticked in each of the columns.
2: Compare a column A of checkboxes to a column B containing numbers, and then both count and highlight any row where the checkbox is ticked but column B is a 0.
3: (optional) I would like to erase the html checkboxes and, if the box was checked, replace it with a regular x in the underlying cell.
I found some code on another forum that generates a list of values for each checkbox (vba - Obtain the value of an HTML Checkbox inserted in Excel worksheet - Stack Overflow).
Based on that, I recorded a macro to extract the html Name of a single checkbox and then set up a Vlookup for the True/False value. However, I can't figure out how to automate a vlookup for every individual checkbox and put the data in the appropriate underlying cell.
I have Columns A to C which are hidden. I have tried to unhide these bey selecting the entire worksheet but to no avail. I am using Office 2010.
View 4 Replies View Related