Writing A Cleaner Loop & Toggle Hide/unhide With Button
Oct 12, 2007
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
View 9 Replies
ADVERTISEMENT
Jun 12, 2007
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................................
View 2 Replies
View Related
Nov 14, 2013
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].....
View 3 Replies
View Related
Sep 23, 2009
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: ...
View 6 Replies
View Related
Jan 8, 2007
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
View 9 Replies
View Related
Sep 4, 2013
I have this working VBA to hide Columns based on cell value. Need to be able to Toggle between Hide and Unhide
Sub HideCols()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Dim cl As Range
For Each cl In Sheet16.Range("l$7:$ax$7")
[Code] ..........
View 2 Replies
View Related
Jun 13, 2007
I have a macro that works fine with just numbers as a test run when I use it in the desired workbook/worksheets (where the values are returned via Indedx/match functions) there appears to be a small problem. (Below is the relevant post.)
[url]
Background is that I load raw data into a monthly account sheet which has the appropriate cost centre codes for allocating expenses.
I then use a vlookup in combination with indirect to place the summary data by costcentre code into annual worksheet.
I then have a summary sheet for printing purposes so I can print out a single month expenses by code to staple to the appropiate monthly costcentre statement. This is done by using Index & match functions looking at the annual summary sheet
What seems to be happening is that the code is sometimes recognizing formulas as a value & therefore showing zero values when I want only values 0 (as I sometimes have minus values).
Also sometimes when I go to unhide the all the rows it also is recognising some formulas as having value.
I am using Xl 2003.
View 9 Replies
View Related
Dec 23, 2011
I have a userform with 70 toggle buttons, the buttons are arranged in 7 columns and 10 rows, hence I named each button c1r1 to c7r10.
The button captions will change, so I want to take the captions from cell contents: worsheet 'Buttons' and the range A1:A70
I can do this long hand...
c1r1.Caption = Sheets("Buttons").Range("A1")
c1r2.Caption = Sheets("Buttons").Range("A2")
.....
c1r10.Caption = Sheets("Buttons").Range("A10")
c2r1.Caption = Sheets("Buttons").Range("A11")
c2r2.Caption = Sheets("Buttons").Range("A12")
unitl I get to
c7r10.Caption = Sheets("Buttons").Range("A70")
I guess a loop would be better. Here's my code that does not work..
I was just going to insert word 'works' for now until I get the loop to work.
I'd also have to get a loop inside this loop, ie when the column number is 1, it needs to loop through the rows numbers, before it loops to column 2.
I guess I might be able to do that, but I fall at the first hurdle, I get Error 424, object required. I guess it does not like 'buttonaddress.caption'
Do I need to declare or dim 'buttonadress'?
buttoncoladdress = 1
buttonrowaddress = 1
Do Until buttoncoladdress = 7
buttonaddress = "c" & buttoncoladdress & "r" & buttonrowaddress
buttonaddress.Caption = "works"
buttoncoladdress = buttoncoladdress + 1
Loop
View 3 Replies
View Related
Jul 25, 2012
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
View 2 Replies
View Related
Feb 9, 2009
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 Related
Feb 9, 2010
I'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.
View 6 Replies
View Related
Mar 11, 2014
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
View 4 Replies
View Related
Oct 23, 2008
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.
View 9 Replies
View Related
Sep 24, 2012
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
View 2 Replies
View Related
Nov 28, 2012
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.
View 5 Replies
View Related
Oct 8, 2009
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?
View 10 Replies
View Related
Jan 10, 2012
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?
View 9 Replies
View Related
Nov 11, 2011
I have a togglebutton for an excel sheet in use for scoring psychological tests. When pressed, the button higlights the cells where a raw score is inserted.
What I need is a code to higlight the togglebutton when pressed, eg. green (colorindex 4). The button is white. How to do this?
Below is the code, It has more lines than needed, but it works.
Private Sub ToggleButton1_Click()
'FKV TOGGLE'
ActiveSheet.Unprotect "manisk"
If ToggleButton1 Then
[Code] ........
View 2 Replies
View Related
Aug 13, 2008
I have read several articles saying using a command button as a toggle button can't be done but some articles have said it is possible using the state properties.
.State = msobuttondown
.State = msobuttonup
Is there any way to combine this with the onaction property so that when you press the button, it stays down and activates a macro then when you press it again it pops back up and activates another macro.
View 8 Replies
View Related
Feb 23, 2008
I'm looking for a way to have mutually exclusive toggle buttons (i.e. only one can be selected at a time) embedded in a worksheet. It is possible with radio buttons but they won't serve my needs.
View 5 Replies
View Related
Apr 7, 2014
Is there a way to get a sound toggle button on excel?
What I need is a button to turn the sound alerts on and off on my spreadsheet. Ideally I'd like a button that either changed an image to the speaker image when on and the speaker image with line through it when off. Is that possible?
If not, is it possible to have a button that just says: "Sound:ON" or "Sound:OFF"?
I guess it could be done with tick boxes, or drop down options.
View 4 Replies
View Related
Mar 26, 2014
I have the code below for creating a stopwatch in excel. I was trying to see if there is a way to change this code to assign it to a toggle button so if you hit one part the stopwatch would start and then the other it would stop.
[Code] ....
View 4 Replies
View Related
Feb 11, 2009
I wanted to know how do I keep a toggle button in the on(ture) position after I close the userform? How do I keep the toggle button off(false) positoin after I close the userform? In order words, once I run my macro again, I want it to remain in memory that my toggle button was in the on(true) or off(false)?
For example,I have an application that I am developing which involves some what-if analysis for some projects. There is a command button located on my worksheet. After the command button is clicked, I have include some toggle buttons on a userform for all of the various projects. If I assume that sine projects will not be used, I click the associated toggle buttons. This puts them in the on(true) position. After I close the userform and reclick the command button, my toggle buttons are all back in the off(false) position.
View 5 Replies
View Related
Jul 27, 2009
it's possible to use this macro code in a toggle to perform this action when it's true and when the toggle is click again it can undo it.
This macro does lookups and finds the max on sets of value when it is run and i was wondering if by clicking the button again it can undo what it placed in the cells.
View 4 Replies
View Related
Jan 24, 2008
I need a macro for button1. The name of button one is "on" when clicked I want it to put an "X" in A1 and then change the button name to "off" When clicked again I want it to delete the "X" in A1 and change the name back to "on". When clicked again it repeats the same process.
View 7 Replies
View Related
Sep 23, 2008
I am now trying to hide rows when the data in a column matches that of a cell with a data validation list. Ideally i would like only the rows with matching data to show. When the cell is changed then the visible rows change and when the the entry "total" is selected all the rows are shown. There are several small tables of data on a page so I can't use auto filter. The data areas will also change in size as new entries are added. So the last row in the data tables will increase over time. I will copy and apply the macro to each separate table of data.
As an example cell c19 contains "lwsf"
The data table is in the range c24:q47. the column with matching data is column D so all rows of entries with "lwsf" in column D should be visible and the rest hidden. When c19 is changed to "TF" then the rows visible become those with TF in column D and the rest hidden.
View 14 Replies
View Related
Oct 27, 2006
Having a problem with a survey I'm creating in Excel.
I have a question with a Yes/No response cell. I want to show/hide several rows below it. The problem is that these rows also contain 6 option buttons, which I also need to hide/show depending on the response cell.
Is there a handy way to hide all of these shapes/controls in the defined range, and then "unhide" them if the cell value changes back to "Yes"?
View 7 Replies
View Related
Mar 27, 2014
Is there a way to change the text on a toggle button in the code below? Basically I would like for the toggle button to begin with the word "START" on it and then when clicked changed to the word "STOP". Of course then when the user clicks on "STOP" it changes back to "START".
View 3 Replies
View Related
Sep 11, 2009
I have a sheet in which I am using freeze panes to keep the header section always visible. I would like to be able to add a toggle button with a macro to the sheet to allow the user to turn the existing freeze panes on/off at will, without the need to navigate the menu bar itself. So far I have not been able to find any info on if/how this may be possible. If anyone has any experience controlling freeze panes via macros, etc.
View 3 Replies
View Related
Nov 4, 2011
ToggleButton? I have made this Macro work
Sub group()
'
Dim i As Integer
Sheets("Operating Income Trending (LOC)").Select
For i = 53 To 2 Step -1
If Cells(7, i) = "" Then
Sheets("Operating Income Trending (LOC)").Columns(i).EntireColumn.Hidden = True
End If
Next i
End Sub
And when i tried to put it into a togglebutton like below:
Private Sub ToggleButton1_Click()
Select Case ToggleButton1.Caption
Case "Group"
Dim i As Integer
Sheets("Operating Income Trending (LOC)").Select
[Code] .........
Then instead of hiding all the blank columns, it hides almost all the columns, leaving some that are supposed to hide. i could not figure out why as it worked fine as individual macro.
View 2 Replies
View Related
