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"?
First, I realize there are plenty of hide cells threads but I have yet to find one pertaining to my situation and I apologize in advance if I this solution has already been posted.
The Problem: I am looking for macro code to a toggle button that will hide various rows that have no value between multiple sets of rows. The toggle should refresh the format of the rows as the information that was blank could later on have value.
The Setup: For each set of rows, the first row will have the label T and the last will have the label S. In between T and S there can be data. If the cells between T and S are all empty then the rows including T and S should be hidden other wise only the non blank cells between T and S should be visible.
The Reason: I have a master database worksheet, there are four copies of the master database worksheet each entitled phase1, ""2, ""3, & ""4 respectively. These phase sheets are linked to the master sheet and show the entire row's data based upon the beginning cell of each row showing either phase1, ""2, & so on.. The data is broken up into many sub databases and traditional auto filters or advanced auto filters will not be applicable as the title of the data and the empty rows in between need to be hidden if the data is empty.
I need some code that will do the following in a UserForm:
If option1 is selected = textbox1 is visible, textbox2/combo1 is hidden
If option2 is selected = textbox2 is visible, textbox1/combo1 is hidden
If option3 is selected = combobox1 is visible, textbox1/textbox2 is hidden
I'm newer in VBA. I will appreciate for help creating vba procedure. I would like to hide/show range of cells by that condition:
View 11 Replies View RelatedCould 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 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] ..........
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.
I have a workbook wherein I have 7 sheets.Lets say they are called Tom, Peter, John, Sia, Mia, Tia and "Home Page". I have 2 buttons for Report 1 and 2 to which I want to assign the macros.I also have a table wherein I have defined which sheets I want to show. First Column of table has sheet names from A2:A6(Home Page,Tom, Peter, John, Sia, Mia, Tia). Column 2 has report 1 sheets - Home Page, Tom, John, Mia and Column 3 has report 2 sheet names- Home Page, Peter, Sia, Tia
What I want to do is, if I click on "Report 1" button, I only want to show sheets whose names are there in cells under report 1 so for report 1 it will be Home Page, Tom, John, Mia. For Report 2, it will be Home Page, Peter, Sia, Tia. Since I have many reports I want this to be one macro. Stepwise, here is what I want
1. Click on button for Report, macro should check which report I am referring to and select the range on basis of that. Report 1 = column B, if Report 2, Range is column C.
2. Basis the range I want sheets to show or hide.
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 have a workbook with two sheets. i have a command button on sheet 1 that inserts another worksheet from another workbook based on a cell value in sheet 1. i would like this specific command button on sheet 1 to be hidden or disabled until a value is entered into a certain cell on sheet 1 that matches a value in a named range on sheet 2.
View 7 Replies View RelatedI 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 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: ...
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
What's the best way to hide controls on a userform? I have a userfrom with 2 datepicker one for start date and one for end date. I want them hidden until I use checkbox and check it to appear. I am using this code but nothing is work.
View 2 Replies View RelatedI have form controls on my worksheets. How do I hide them? When I hide the row, the controls stay there. These controls don't seem to have that visible property like the VB control.
I'm thinking of using a validation->list instead, but then you don't see the drop down arrow.
I have a spreadsheet that has a userform attached to it with text fields that enter additional data regarding each record. The userform takes these additional 5 fields and inserts them back into the spreadsheet, the final information to be exported back into Access. ( There are of course other ways to do this, but this is how my supervisor wants it done!)
My problem is that now that I can scroll through the spreadsheet without closing the userform, the userform doesnt show the current row's information as I scroll or change rows on the spreadsheet. It will only show the information that was in the row that it was on before i switched back to spreadsheet.
Is there any way you can use VBA to assign a key to toggle between two different cell formats? I want to toggle a cell from white border with black text to black border with white text and visa versa, with the same key or button. I've searched the posts but can't find this mentioned.
View 6 Replies View Relatedget my code to work.
I have a sheet with a dropdown box in cell "J1" which is meant as a 'Show only rows containing this value'.
Column Q, from row 3 downward has a pick box which lists the same values as the "J1" drop down box, but uses code to combine whats chosen with commas. An example of what is in a cell in column Q is "SeaHawk, BlackHawk, Squirrel, MRH", where cell J1 would be either "SeaHawk" or "MRH" or something completely different.
I need to only show rows, with text in column Q containing the smae text as J1.
My Excel>Preferences>View>Comments is set to Comment Indicator Only.
Working from keyboard and mouse, when I click on a cell holding a comment ($B$52) , the comment appears. When I click on a different cell the comment disappears.
When I run the code
...
Range("B52").Select
End Sub
the cell is selected, but no comment appears.
When I run
...
With Range("B52")
.Select
.Comment.Visible = True
End With
End Sub
the comment remains visible even after I click off of B52.
I get the same behaviour when I use Application.Goto rather than Select.
I would like to write a routine that
1) creates a comment for cell B52
2) selects B52
3) shows the comment in B52
< VB routine ends >
4) comment disappears when user clicks on different cell.
Does this require event code?
I have two options in a Cell as Applicable and Not Applicable. This is in a Drop-down menu and what I want is that when I choose Applicable, it should bring up additional 5 or 6 cells for me to complete. And if I choose Not Applicable, the additional cells should not show up. The default would be Not Applicable.
I bet you're getting sick of the "X" questions, and I have done an exhaustive search but the search filter kills searching for (X) or 'X' or "X" .. but nothing I did find is what I am looking to do. I have any # of userforms that when you click the "X" in the top right side, it will hide the userform... once.. Is there a fix to the code below to make it do it every time its clicked?
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode <> 1 Then Cancel = 1
UserForm15.Hide
UserForm11.Show
If CloseMode = 1 Then Cancel = 1
UserForm15.Hide
UserForm11.Show
End Sub
Like I said, I don't want to disable it, I tried that off of one of the posts I found and thats just plain annoying, I just want it to hide the one userform and show the other. This will do it once and then you can click it all you want and it wont do anything.. see the annoying trait above.. this lasts until you exit the program.. so the X button can be hit repeatedly though out the day. The userform 15 is visible, userform 11 isn't shown, IE not behind userform 15.
how to creat a userform with a button (1) to show anouther userform with a button (2). you click the button on 1 and it shows 2. then when you click the button on 2 and it shows 1. I'm having a problem with the second part.
View 5 Replies View RelatedIs there a method of formatting specific cells to show zeros? Or indeed I could turn on show zeros in options and then hide the ones I don't want. Any tips / advice on how to selectively display (or hide) zeros appreciated.
View 3 Replies View RelatedI have a some data in 1st column in which most of the data has decimals. For ex. 209.8, 224.4 like this. I have converted it by roundup function and get the exact result. I want to hide the decimals means not show decimals. Only the result will show like. 210, 224 etc. For your reference I have attached the sheet. I have manually typed this in the C column result area. That I need in A column.
View 9 Replies View RelatedIs it possible to hide the contents of a column that would only show once clicked on?
That way i could have a column called "Keywords - click to open" & the contents would only show once clicked on?
I have uploaded an example excel spreadsheet : example show hide.xls
I have a workbook that I want to show or hide a selection of cells depending on another cell when printing it.
Example
If cell x = A2A then display a certain cell set if anything else then exclude cell set from printing.
I've seen excel sheet view like this [URL].....
View 2 Replies View RelatedI'm trying to only show specific sheets per user using the environ variable and this code seems to work for the single user / sheet but the master user does not function correctly i.e. the code does not show all sheets, this is the code I am using:
[Code] ......
Why the above code does not respect the Master User "Jane" should be able to see all sheets?
Original source for this code was found here:
HTML Code: [URL]....
I'm creating an excel workbook that will allow any business or accounting student to go in and create their four year plan while giving them all of their requirements for their particular degree.
One of the features I wanted to have with this program is that it wouldn't display all the worksheets for each major, but rather the ones you choose that you are associated with. I was going to have a drop down menu on the first page that allows you to choose your majors, those being accounting, or business administration with its 5 focuses.
If I just allow all the worksheets to be shown, there would be 6 in total and I thought it might get confusing for a student who's only taking one of the 6. My hope is that on the first page, when you choose one of the options, it would automatically display the hidden worksheet that goes along with that major.
Basically, I am looking for a macro code to link to an 'Update' button that will do the following;
I will have 2 sheets, one sheet will be referencing data from other sheet with the following
=IF(Log Sheet!A4="Yes",(A1),"0")
This means, if it needs to be 'referred' it will go to the other sheet, and if it doesn't, then it wont and it will show up as 0.
Basically, every row will have this type of formula for 30 columns, and i would like a macro code that will hide values of 0, but even once hidden, will then show them again if in the other spreadsheet i change the value to yes, meaning i want the previously hidden row to show.