| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
Hiding cells on condition
I am trying to hide a range of cells when the value in another cell is FALSE
and show the range of cells when the value is TRUE. My code is as follows but it doesn't work Private Sub Workbook_Open() If Range("C4").Value = False Then Range("E4:M40").Hide = True End If If Range("C4").Value = True Then Range("E4:M40").Unhide = False End Sub How do I make this work? |
|
#2
|
|||
|
|||
|
Apart from syntax errors, you can only hide an entirecolumn or row.
-- HTH RP (remove nothere from the email address if mailing direct) "Pennington" wrote in message ... I am trying to hide a range of cells when the value in another cell is FALSE and show the range of cells when the value is TRUE. My code is as follows but it doesn't work Private Sub Workbook_Open() If Range("C4").Value = False Then Range("E4:M40").Hide = True End If If Range("C4").Value = True Then Range("E4:M40").Unhide = False End Sub How do I make this work? |
|
#3
|
|||
|
|||
|
Hi..
Private Sub Workbook_Open() With Sheets("Sheet1") ..Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ ..Range("E3:M3").EntireColumn.Hidden = True End With End Sub ----- XL2003 Regards William "Pennington" wrote in message ... I am trying to hide a range of cells when the value in another cell is FALSE and show the range of cells when the value is TRUE. My code is as follows but it doesn't work Private Sub Workbook_Open() If Range("C4").Value = False Then Range("E4:M40").Hide = True End If If Range("C4").Value = True Then Range("E4:M40").Unhide = False End Sub How do I make this work? |
|
#4
|
|||
|
|||
|
simpler
Private Sub Workbook_Open() Sheets("Sheet1").Range("E3:M3").EntireColumn.Hidde n = Not .Range("C4").Value End Sub -- HTH RP (remove nothere from the email address if mailing direct) "William" wrote in message ... Hi.. Private Sub Workbook_Open() With Sheets("Sheet1") .Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ .Range("E3:M3").EntireColumn.Hidden = True End With End Sub ----- XL2003 Regards William "Pennington" wrote in message ... I am trying to hide a range of cells when the value in another cell is FALSE and show the range of cells when the value is TRUE. My code is as follows but it doesn't work Private Sub Workbook_Open() If Range("C4").Value = False Then Range("E4:M40").Hide = True End If If Range("C4").Value = True Then Range("E4:M40").Unhide = False End Sub How do I make this work? |
|
#6
|
|||
|
|||
|
Thank William but I can't get it to work. I have placed this code in Module 1
of the workbook Private Sub Workbook_Open() With Sheets("ProcessA") Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ Range("E3:M3").EntireColumn.Hidden = True End With End Sub Saved the file closed and opening it but all the columns are still showing "William" wrote: Hi.. Private Sub Workbook_Open() With Sheets("Sheet1") ..Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ ..Range("E3:M3").EntireColumn.Hidden = True End With End Sub ----- XL2003 Regards William "Pennington" wrote in message ... I am trying to hide a range of cells when the value in another cell is FALSE and show the range of cells when the value is TRUE. My code is as follows but it doesn't work Private Sub Workbook_Open() If Range("C4").Value = False Then Range("E4:M40").Hide = True End If If Range("C4").Value = True Then Range("E4:M40").Unhide = False End Sub How do I make this work? |
|
#7
|
|||
|
|||
|
Pennington
Place the code in the "ThisWorkbook" module, NOT a general module. ----- XL2003 Regards William "Pennington" wrote in message ... Thank William but I can't get it to work. I have placed this code in Module 1 of the workbook Private Sub Workbook_Open() With Sheets("ProcessA") Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ Range("E3:M3").EntireColumn.Hidden = True End With End Sub Saved the file closed and opening it but all the columns are still showing "William" wrote: Hi.. Private Sub Workbook_Open() With Sheets("Sheet1") ..Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ ..Range("E3:M3").EntireColumn.Hidden = True End With End Sub ----- XL2003 Regards William "Pennington" wrote in message ... I am trying to hide a range of cells when the value in another cell is FALSE and show the range of cells when the value is TRUE. My code is as follows but it doesn't work Private Sub Workbook_Open() If Range("C4").Value = False Then Range("E4:M40").Hide = True End If If Range("C4").Value = True Then Range("E4:M40").Unhide = False End Sub How do I make this work? |
|
#8
|
|||
|
|||
|
Willaim
I inserted Private Sub Workbook_Open() With Sheets("ProcessA").Range("E3:M3").EntireColumn.Hid den = Not ..Range("C4").Value = False End With But VBA does not like .Range I am using Excel 2000. Also if one can only hide complete rows or columns why is the range ("E3:M3")? "William" wrote: Very nice Bob! For the benefit of the OP, I think you intended.... With Sheets("Sheet1") ..Range("E3:M3").EntireColumn.Hidden = Not .Range("C4").Value End With Note: C4 must contain either TRUE or FLASE (or be blank). -- ----- XL2003 Regards William "Bob Phillips" wrote in message ... simpler Private Sub Workbook_Open() Sheets("Sheet1").Range("E3:M3").EntireColumn.Hidde n = Not .Range("C4").Value End Sub -- HTH RP (remove nothere from the email address if mailing direct) "William" wrote in message ... Hi.. Private Sub Workbook_Open() With Sheets("Sheet1") .Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ .Range("E3:M3").EntireColumn.Hidden = True End With End Sub ----- XL2003 Regards William "Pennington" wrote in message ... I am trying to hide a range of cells when the value in another cell is FALSE and show the range of cells when the value is TRUE. My code is as follows but it doesn't work Private Sub Workbook_Open() If Range("C4").Value = False Then Range("E4:M40").Hide = True End If If Range("C4").Value = True Then Range("E4:M40").Unhide = False End Sub How do I make this work? |
|
#9
|
|||
|
|||
|
Williiam
This code now works on opening the workbook. Private Sub Workbook_Open() With Sheets("ProcessA") Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ Range("E3:M3").EntireColumn.Hidden = True End With End Sub But what I need is for the columns to be hidden when the value in cell C4 is FALSE and when the value in C4 is changed to TRUE, the columns are revealed with the workbook open. I have tried selecting the command SheetChange instead of Open but it doesn not work - what other options are there. "William" wrote: Pennington Place the code in the "ThisWorkbook" module, NOT a general module. ----- XL2003 Regards William "Pennington" wrote in message ... Thank William but I can't get it to work. I have placed this code in Module 1 of the workbook Private Sub Workbook_Open() With Sheets("ProcessA") Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ Range("E3:M3").EntireColumn.Hidden = True End With End Sub Saved the file closed and opening it but all the columns are still showing "William" wrote: Hi.. Private Sub Workbook_Open() With Sheets("Sheet1") ..Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ ..Range("E3:M3").EntireColumn.Hidden = True End With End Sub ----- XL2003 Regards William "Pennington" wrote in message ... I am trying to hide a range of cells when the value in another cell is FALSE and show the range of cells when the value is TRUE. My code is as follows but it doesn't work Private Sub Workbook_Open() If Range("C4").Value = False Then Range("E4:M40").Hide = True End If If Range("C4").Value = True Then Range("E4:M40").Unhide = False End Sub How do I make this work? |
|
#10
|
|||
|
|||
|
William
I have now got it to work using the sheetcalculate command and have decided to hide rows rather than columns but I have got another problem. The True and False values are produced from check boxes but when the rows are hidden the check boxes are not - they all move down onto the unhidden rows. How can I hide the check boxes or is there another way of selecting a value by the click of a mouse? "William" wrote: Pennington Place the code in the "ThisWorkbook" module, NOT a general module. ----- XL2003 Regards William "Pennington" wrote in message ... Thank William but I can't get it to work. I have placed this code in Module 1 of the workbook Private Sub Workbook_Open() With Sheets("ProcessA") Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ Range("E3:M3").EntireColumn.Hidden = True End With End Sub Saved the file closed and opening it but all the columns are still showing "William" wrote: Hi.. Private Sub Workbook_Open() With Sheets("Sheet1") ..Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ ..Range("E3:M3").EntireColumn.Hidden = True End With End Sub ----- XL2003 Regards William "Pennington" wrote in message ... I am trying to hide a range of cells when the value in another cell is FALSE and show the range of cells when the value is TRUE. My code is as follows but it doesn't work Private Sub Workbook_Open() If Range("C4").Value = False Then Range("E4:M40").Hide = True End If If Range("C4").Value = True Then Range("E4:M40").Unhide = False End Sub How do I make this work? |
|
#11
|
|||
|
|||
|
Place this code in the "ProcessA" worksheet module.
Private Sub Worksheet_Change(ByVal Target As Range) With Sheets("ProcessA") If Not Target(1).Address = .Range("C4").Address Then Exit Sub ..Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ ..Range("E3:M3").EntireColumn.Hidden = True End With End Sub The code assumes that C4 is not a formula and can only be changed by selecting cell C4. -- ----- XL2003 Regards William "Pennington" wrote in message ... Williiam This code now works on opening the workbook. Private Sub Workbook_Open() With Sheets("ProcessA") Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ Range("E3:M3").EntireColumn.Hidden = True End With End Sub But what I need is for the columns to be hidden when the value in cell C4 is FALSE and when the value in C4 is changed to TRUE, the columns are revealed with the workbook open. I have tried selecting the command SheetChange instead of Open but it doesn not work - what other options are there. "William" wrote: Pennington Place the code in the "ThisWorkbook" module, NOT a general module. ----- XL2003 Regards William "Pennington" wrote in message ... Thank William but I can't get it to work. I have placed this code in Module 1 of the workbook Private Sub Workbook_Open() With Sheets("ProcessA") Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ Range("E3:M3").EntireColumn.Hidden = True End With End Sub Saved the file closed and opening it but all the columns are still showing "William" wrote: Hi.. Private Sub Workbook_Open() With Sheets("Sheet1") ..Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ ..Range("E3:M3").EntireColumn.Hidden = True End With End Sub ----- XL2003 Regards William "Pennington" wrote in message ... I am trying to hide a range of cells when the value in another cell is FALSE and show the range of cells when the value is TRUE. My code is as follows but it doesn't work Private Sub Workbook_Open() If Range("C4").Value = False Then Range("E4:M40").Hide = True End If If Range("C4").Value = True Then Range("E4:M40").Unhide = False End Sub How do I make this work? |
|
#12
|
|||
|
|||
|
Pennington
Firstly, dont use the "Calculate" event, use the "Change" event to execute your code. In answer to your post, select all the checkboxes that you want to be hidden / visible when you execute your code to hide / unhide rows and give a name to that selection of checkboxes - In my example I have used "cbgroup". Private Sub Worksheet_Change(ByVal Target As Range) With Sheets("ProcessA") If Not Target(1).Address = .Range("C4").Address Then Exit Sub ..Range("C7:M19").EntireRow.Hidden = False ..Shapes("cbgroup").Visible = True If .Range("C4").Value = False Then ..Range("C7:M19").EntireRow.Hidden = True ..Shapes("cbgroup").Visible = False End If End With End Sub -- ----- XL2003 Regards William "Pennington" wrote in message ... William I have now got it to work using the sheetcalculate command and have decided to hide rows rather than columns but I have got another problem. The True and False values are produced from check boxes but when the rows are hidden the check boxes are not - they all move down onto the unhidden rows. How can I hide the check boxes or is there another way of selecting a value by the click of a mouse? "William" wrote: Pennington Place the code in the "ThisWorkbook" module, NOT a general module. ----- XL2003 Regards William "Pennington" wrote in message ... Thank William but I can't get it to work. I have placed this code in Module 1 of the workbook Private Sub Workbook_Open() With Sheets("ProcessA") Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ Range("E3:M3").EntireColumn.Hidden = True End With End Sub Saved the file closed and opening it but all the columns are still showing "William" wrote: Hi.. Private Sub Workbook_Open() With Sheets("Sheet1") ..Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ ..Range("E3:M3").EntireColumn.Hidden = True End With End Sub ----- XL2003 Regards William "Pennington" wrote in message ... I am trying to hide a range of cells when the value in another cell is FALSE and show the range of cells when the value is TRUE. My code is as follows but it doesn't work Private Sub Workbook_Open() If Range("C4").Value = False Then Range("E4:M40").Hide = True End If If Range("C4").Value = True Then Range("E4:M40").Unhide = False End Sub How do I make this work? |
|
#13
|
|||
|
|||
|
LOL. I took that With out at the last moment and pre-prended the first Range
with it. Bob "William" wrote in message ... Very nice Bob! For the benefit of the OP, I think you intended.... With Sheets("Sheet1") .Range("E3:M3").EntireColumn.Hidden = Not .Range("C4").Value End With Note: C4 must contain either TRUE or FLASE (or be blank). -- ----- XL2003 Regards William "Bob Phillips" wrote in message ... simpler Private Sub Workbook_Open() Sheets("Sheet1").Range("E3:M3").EntireColumn.Hidde n = Not .Range("C4").Value End Sub -- HTH RP (remove nothere from the email address if mailing direct) "William" wrote in message ... Hi.. Private Sub Workbook_Open() With Sheets("Sheet1") .Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ .Range("E3:M3").EntireColumn.Hidden = True End With End Sub ----- XL2003 Regards William "Pennington" wrote in message ... I am trying to hide a range of cells when the value in another cell is FALSE and show the range of cells when the value is TRUE. My code is as follows but it doesn't work Private Sub Workbook_Open() If Range("C4").Value = False Then Range("E4:M40").Hide = True End If If Range("C4").Value = True Then Range("E4:M40").Unhide = False End Sub How do I make this work? |
|
#14
|
|||
|
|||
|
The lines have got mixed up.
Try Private Sub Workbook_Open() With Sheets("ProcessA") .Range("E3:M3").EntireColumn.Hidden = _ Not .Range("C4").Value = False End With End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Pennington" wrote in message ... Willaim I inserted Private Sub Workbook_Open() With Sheets("ProcessA").Range("E3:M3").EntireColumn.Hid den = Not .Range("C4").Value = False End With But VBA does not like .Range I am using Excel 2000. Also if one can only hide complete rows or columns why is the range ("E3:M3")? "William" wrote: Very nice Bob! For the benefit of the OP, I think you intended.... With Sheets("Sheet1") ..Range("E3:M3").EntireColumn.Hidden = Not .Range("C4").Value End With Note: C4 must contain either TRUE or FLASE (or be blank). -- ----- XL2003 Regards William "Bob Phillips" wrote in message ... simpler Private Sub Workbook_Open() Sheets("Sheet1").Range("E3:M3").EntireColumn.Hidde n = Not .Range("C4").Value End Sub -- HTH RP (remove nothere from the email address if mailing direct) "William" wrote in message ... Hi.. Private Sub Workbook_Open() With Sheets("Sheet1") .Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ .Range("E3:M3").EntireColumn.Hidden = True End With End Sub ----- XL2003 Regards William "Pennington" wrote in message ... I am trying to hide a range of cells when the value in another cell is FALSE and show the range of cells when the value is TRUE. My code is as follows but it doesn't work Private Sub Workbook_Open() If Range("C4").Value = False Then Range("E4:M40").Hide = True End If If Range("C4").Value = True Then Range("E4:M40").Unhide = False End Sub How do I make this work? |
|
#15
|
|||
|
|||
|
William
It won't work with sheet change event. It only works with sheetcalculate event. I ma running Excel 2000 so I don't know if that makes any difference. In your post below you suggest I give a name to a selection of checkboxes - how do I do that? All I have are checkbox numbers in the and there is no provision on the checkbox properties. I am certainly making progress and it seems I am just a step away. Your help is very much appreciated. "William" wrote: Pennington Firstly, dont use the "Calculate" event, use the "Change" event to execute your code. In answer to your post, select all the checkboxes that you want to be hidden / visible when you execute your code to hide / unhide rows and give a name to that selection of checkboxes - In my example I have used "cbgroup". Private Sub Worksheet_Change(ByVal Target As Range) With Sheets("ProcessA") If Not Target(1).Address = .Range("C4").Address Then Exit Sub ..Range("C7:M19").EntireRow.Hidden = False ..Shapes("cbgroup").Visible = True If .Range("C4").Value = False Then ..Range("C7:M19").EntireRow.Hidden = True ..Shapes("cbgroup").Visible = False End If End With End Sub -- ----- XL2003 Regards William "Pennington" wrote in message ... William I have now got it to work using the sheetcalculate command and have decided to hide rows rather than columns but I have got another problem. The True and False values are produced from check boxes but when the rows are hidden the check boxes are not - they all move down onto the unhidden rows. How can I hide the check boxes or is there another way of selecting a value by the click of a mouse? "William" wrote: Pennington Place the code in the "ThisWorkbook" module, NOT a general module. ----- XL2003 Regards William "Pennington" wrote in message ... Thank William but I can't get it to work. I have placed this code in Module 1 of the workbook Private Sub Workbook_Open() With Sheets("ProcessA") Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ Range("E3:M3").EntireColumn.Hidden = True End With End Sub Saved the file closed and opening it but all the columns are still showing "William" wrote: Hi.. Private Sub Workbook_Open() With Sheets("Sheet1") ..Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ ..Range("E3:M3").EntireColumn.Hidden = True End With End Sub ----- XL2003 Regards William "Pennington" wrote in message ... I am trying to hide a range of cells when the value in another cell is FALSE and show the range of cells when the value is TRUE. My code is as follows but it doesn't work Private Sub Workbook_Open() If Range("C4").Value = False Then Range("E4:M40").Hide = True End If If Range("C4").Value = True Then Range("E4:M40").Unhide = False End Sub How do I make this work? |
|
#16
|
|||
|
|||
|
1. Use your mouse to hover over the toolbars, right-click and select
"Customise". Select the Command tab and then select "Drawing" from the "Categories" section. From the "Commands" section, select "Drawing Objrcts" and drag the icon to one of your toolbars. Click "Close" to return to the Excel sheet. 2. Select the "Drawing Objects" icon from your toolbar and then select the check-boxes which will be effected by your code. Hover over one of the selected checkboxes and select "GroupingGroup". Either accept the name given to the group in the "Name" box or create your own name for the group. 3. Since cell C4 is being changed via a check box, use the "Calculate" event.... Private Sub Worksheet_Calculate() With Sheets("Sheet1") If .Range("C4").Value = True Then ..Range("C7:M19").EntireRow.Hidden = False ..Shapes("cbgroup").Visible = True Else ..Range("C7:M19").EntireRow.Hidden = True ..Shapes("cbgroup").Visible = False End If End With End Sub But if it is the check boxes that cause C4 to change, how are you going to change the check box if it is hidden? ----- XL2003 Regards William "William" wrote in message ... Pennington Firstly, dont use the "Calculate" event, use the "Change" event to execute your code. In answer to your post, select all the checkboxes that you want to be hidden / visible when you execute your code to hide / unhide rows and give a name to that selection of checkboxes - In my example I have used "cbgroup". Private Sub Worksheet_Change(ByVal Target As Range) With Sheets("ProcessA") If Not Target(1).Address = .Range("C4").Address Then Exit Sub .Range("C7:M19").EntireRow.Hidden = False .Shapes("cbgroup").Visible = True If .Range("C4").Value = False Then .Range("C7:M19").EntireRow.Hidden = True .Shapes("cbgroup").Visible = False End If End With End Sub -- ----- XL2003 Regards William "Pennington" wrote in message ... William I have now got it to work using the sheetcalculate command and have decided to hide rows rather than columns but I have got another problem. The True and False values are produced from check boxes but when the rows are hidden the check boxes are not - they all move down onto the unhidden rows. How can I hide the check boxes or is there another way of selecting a value by the click of a mouse? "William" wrote: Pennington Place the code in the "ThisWorkbook" module, NOT a general module. ----- XL2003 Regards William "Pennington" wrote in message ... Thank William but I can't get it to work. I have placed this code in Module 1 of the workbook Private Sub Workbook_Open() With Sheets("ProcessA") Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ Range("E3:M3").EntireColumn.Hidden = True End With End Sub Saved the file closed and opening it but all the columns are still showing "William" wrote: Hi.. Private Sub Workbook_Open() With Sheets("Sheet1") ..Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ ..Range("E3:M3").EntireColumn.Hidden = True End With End Sub ----- XL2003 Regards William "Pennington" wrote in message ... I am trying to hide a range of cells when the value in another cell is FALSE and show the range of cells when the value is TRUE. My code is as follows but it doesn't work Private Sub Workbook_Open() If Range("C4").Value = False Then Range("E4:M40").Hide = True End If If Range("C4").Value = True Then Range("E4:M40").Unhide = False End Sub How do I make this work? |
|
#17
|
|||
|
|||
|
William
Many thanks I have made progress but I am still not done. Firstly the check box that causes C4 to change is not hidden. I think its complicated because I have 4 items of data that I am trying to display gradually in response to 4 questions. When the first question is answered correctly the 2nd item of data appears and so on. I need to prevent users answering Q 2 before they have given a correct answer to Q1. The box grouping did work but the boxes didn't stay in the cells so I chose to select one check box and for some reason it works. However, I now realize I have another issue. Providing the user goes sequentialy through the questions it works fine but if he unchecks lets say Q1 having answered Q 2, 3 & 4 correctly, the score will be invalid. The values are added and displayed as percentages so that if each Question scores say 25%, so answering Q1 correctly = 25% and then Q2 correctly = 50%, then Q3 correctly= 75% and finally Q4 correctly = 100% but if he then goes and changes Q1 to produce a FALSE value the score will be 75% not 0%. So how can I cause the other values to change to FALSE if previous values to have been set to TRUE from the checkboxes? Maybe is requires some deft programming, which I am unable to do but if there is a simple solution I would be most grateful "William" wrote: 1. Use your mouse to hover over the toolbars, right-click and select "Customise". Select the Command tab and then select "Drawing" from the "Categories" section. From the "Commands" section, select "Drawing Objrcts" and drag the icon to one of your toolbars. Click "Close" to return to the Excel sheet. 2. Select the "Drawing Objects" icon from your toolbar and then select the check-boxes which will be effected by your code. Hover over one of the selected checkboxes and select "GroupingGroup". Either accept the name given to the group in the "Name" box or create your own name for the group. 3. Since cell C4 is being changed via a check box, use the "Calculate" event.... Private Sub Worksheet_Calculate() With Sheets("Sheet1") If .Range("C4").Value = True Then ..Range("C7:M19").EntireRow.Hidden = False ..Shapes("cbgroup").Visible = True Else ..Range("C7:M19").EntireRow.Hidden = True ..Shapes("cbgroup").Visible = False End If End With End Sub But if it is the check boxes that cause C4 to change, how are you going to change the check box if it is hidden? ----- XL2003 Regards William "William" wrote in message ... Pennington Firstly, dont use the "Calculate" event, use the "Change" event to execute your code. In answer to your post, select all the checkboxes that you want to be hidden / visible when you execute your code to hide / unhide rows and give a name to that selection of checkboxes - In my example I have used "cbgroup". Private Sub Worksheet_Change(ByVal Target As Range) With Sheets("ProcessA") If Not Target(1).Address = .Range("C4").Address Then Exit Sub .Range("C7:M19").EntireRow.Hidden = False .Shapes("cbgroup").Visible = True If .Range("C4").Value = False Then .Range("C7:M19").EntireRow.Hidden = True .Shapes("cbgroup").Visible = False End If End With End Sub -- ----- XL2003 Regards William "Pennington" wrote in message ... William I have now got it to work using the sheetcalculate command and have decided to hide rows rather than columns but I have got another problem. The True and False values are produced from check boxes but when the rows are hidden the check boxes are not - they all move down onto the unhidden rows. How can I hide the check boxes or is there another way of selecting a value by the click of a mouse? "William" wrote: Pennington Place the code in the "ThisWorkbook" module, NOT a general module. ----- XL2003 Regards William "Pennington" wrote in message ... Thank William but I can't get it to work. I have placed this code in Module 1 of the workbook Private Sub Workbook_Open() With Sheets("ProcessA") Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ Range("E3:M3").EntireColumn.Hidden = True End With End Sub Saved the file closed and opening it but all the columns are still showing "William" wrote: Hi.. Private Sub Workbook_Open() With Sheets("Sheet1") ..Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ ..Range("E3:M3").EntireColumn.Hidden = True End With End Sub ----- XL2003 Regards William "Pennington" wrote in message ... I am trying to hide a range of cells when the value in another cell is FALSE and show the range of cells when the value is TRUE. My code is as follows but it doesn't work Private Sub Workbook_Open() If Range("C4").Value = False Then Range("E4:M40").Hide = True End If If Range("C4").Value = True Then Range("E4:M40").Unhide = False End Sub How do I make this work? |
|
#18
|
|||
|
|||
|
Hi Pennington
Have a look at your original and subsequent posts - each time a suggested solution is put forward you are "changing the goalposts". I think perhaps you need to start again and clearly set out the problem you are having and what you want to achieve. ----- XL2003 Regards William "Pennington" wrote in message ... William Many thanks I have made progress but I am still not done. Firstly the check box that causes C4 to change is not hidden. I think its complicated because I have 4 items of data that I am trying to display gradually in response to 4 questions. When the first question is answered correctly the 2nd item of data appears and so on. I need to prevent users answering Q 2 before they have given a correct answer to Q1. The box grouping did work but the boxes didn't stay in the cells so I chose to select one check box and for some reason it works. However, I now realize I have another issue. Providing the user goes sequentialy through the questions it works fine but if he unchecks lets say Q1 having answered Q 2, 3 & 4 correctly, the score will be invalid. The values are added and displayed as percentages so that if each Question scores say 25%, so answering Q1 correctly = 25% and then Q2 correctly = 50%, then Q3 correctly= 75% and finally Q4 correctly = 100% but if he then goes and changes Q1 to produce a FALSE value the score will be 75% not 0%. So how can I cause the other values to change to FALSE if previous values to have been set to TRUE from the checkboxes? Maybe is requires some deft programming, which I am unable to do but if there is a simple solution I would be most grateful "William" wrote: 1. Use your mouse to hover over the toolbars, right-click and select "Customise". Select the Command tab and then select "Drawing" from the "Categories" section. From the "Commands" section, select "Drawing Objrcts" and drag the icon to one of your toolbars. Click "Close" to return to the Excel sheet. 2. Select the "Drawing Objects" icon from your toolbar and then select the check-boxes which will be effected by your code. Hover over one of the selected checkboxes and select "GroupingGroup". Either accept the name given to the group in the "Name" box or create your own name for the group. 3. Since cell C4 is being changed via a check box, use the "Calculate" event.... Private Sub Worksheet_Calculate() With Sheets("Sheet1") If .Range("C4").Value = True Then ..Range("C7:M19").EntireRow.Hidden = False ..Shapes("cbgroup").Visible = True Else ..Range("C7:M19").EntireRow.Hidden = True ..Shapes("cbgroup").Visible = False End If End With End Sub But if it is the check boxes that cause C4 to change, how are you going to change the check box if it is hidden? ----- XL2003 Regards William "William" wrote in message ... Pennington Firstly, dont use the "Calculate" event, use the "Change" event to execute your code. In answer to your post, select all the checkboxes that you want to be hidden / visible when you execute your code to hide / unhide rows and give a name to that selection of checkboxes - In my example I have used "cbgroup". Private Sub Worksheet_Change(ByVal Target As Range) With Sheets("ProcessA") If Not Target(1).Address = .Range("C4").Address Then Exit Sub .Range("C7:M19").EntireRow.Hidden = False .Shapes("cbgroup").Visible = True If .Range("C4").Value = False Then .Range("C7:M19").EntireRow.Hidden = True .Shapes("cbgroup").Visible = False End If End With End Sub -- ----- XL2003 Regards William "Pennington" wrote in message ... William I have now got it to work using the sheetcalculate command and have decided to hide rows rather than columns but I have got another problem. The True and False values are produced from check boxes but when the rows are hidden the check boxes are not - they all move down onto the unhidden rows. How can I hide the check boxes or is there another way of selecting a value by the click of a mouse? "William" wrote: Pennington Place the code in the "ThisWorkbook" module, NOT a general module. ----- XL2003 Regards William "Pennington" wrote in message ... Thank William but I can't get it to work. I have placed this code in Module 1 of the workbook Private Sub Workbook_Open() With Sheets("ProcessA") Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ Range("E3:M3").EntireColumn.Hidden = True End With End Sub Saved the file closed and opening it but all the columns are still showing "William" wrote: Hi.. Private Sub Workbook_Open() With Sheets("Sheet1") ..Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ ..Range("E3:M3").EntireColumn.Hidden = True End With End Sub ----- XL2003 Regards William "Pennington" wrote in message ... I am trying to hide a range of cells when the value in another cell is FALSE and show the range of cells when the value is TRUE. My code is as follows but it doesn't work Private Sub Workbook_Open() If Range("C4").Value = False Then Range("E4:M40").Hide = True End If If Range("C4").Value = True Then Range("E4:M40").Unhide = False End Sub How do I make this work? |
|
#19
|
|||
|
|||
|
Hi William
You are correct but I guess its because I am building the spec as I go along not knowing what I can do in Excel. The original problem of hiding rows/columns when a value is FALSE is now resolved and I finally selected Bob's solution as amended by you but this didn't hide the checkboxes when a value is FALSE which is also now resolved thanks to the code you kindly provided. I now want the action of changing a value from TRUE to FALSE on say ROW 4 to change previously selected TRUE values to FALSE on ROWS 5, 6 & 7. The values are inserted when a check box is clicked so presumambly suitable code in a sheet calculate event would change these values. If it would help I could send you a sample file. "William" wrote: Hi Pennington Have a look at your original and subsequent posts - each time a suggested solution is put forward you are "changing the goalposts". I think perhaps you need to start again and clearly set out the problem you are having and what you want to achieve. ----- XL2003 Regards William "Pennington" wrote in message ... William Many thanks I have made progress but I am still not done. Firstly the check box that causes C4 to change is not hidden. I think its complicated because I have 4 items of data that I am trying to display gradually in response to 4 questions. When the first question is answered correctly the 2nd item of data appears and so on. I need to prevent users answering Q 2 before they have given a correct answer to Q1. The box grouping did work but the boxes didn't stay in the cells so I chose to select one check box and for some reason it works. However, I now realize I have another issue. Providing the user goes sequentialy through the questions it works fine but if he unchecks lets say Q1 having answered Q 2, 3 & 4 correctly, the score will be invalid. The values are added and displayed as percentages so that if each Question scores say 25%, so answering Q1 correctly = 25% and then Q2 correctly = 50%, then Q3 correctly= 75% and finally Q4 correctly = 100% but if he then goes and changes Q1 to produce a FALSE value the score will be 75% not 0%. So how can I cause the other values to change to FALSE if previous values to have been set to TRUE from the checkboxes? Maybe is requires some deft programming, which I am unable to do but if there is a simple solution I would be most grateful "William" wrote: 1. Use your mouse to hover over the toolbars, right-click and select "Customise". Select the Command tab and then select "Drawing" from the "Categories" section. From the "Commands" section, select "Drawing Objrcts" and drag the icon to one of your toolbars. Click "Close" to return to the Excel sheet. 2. Select the "Drawing Objects" icon from your toolbar and then select the check-boxes which will be effected by your code. Hover over one of the selected checkboxes and select "GroupingGroup". Either accept the name given to the group in the "Name" box or create your own name for the group. 3. Since cell C4 is being changed via a check box, use the "Calculate" event.... Private Sub Worksheet_Calculate() With Sheets("Sheet1") If .Range("C4").Value = True Then ..Range("C7:M19").EntireRow.Hidden = False ..Shapes("cbgroup").Visible = True Else ..Range("C7:M19").EntireRow.Hidden = True ..Shapes("cbgroup").Visible = False End If End With End Sub But if it is the check boxes that cause C4 to change, how are you going to change the check box if it is hidden? ----- XL2003 Regards William "William" wrote in message ... Pennington Firstly, dont use the "Calculate" event, use the "Change" event to execute your code. In answer to your post, select all the checkboxes that you want to be hidden / visible when you execute your code to hide / unhide rows and give a name to that selection of checkboxes - In my example I have used "cbgroup". Private Sub Worksheet_Change(ByVal Target As Range) With Sheets("ProcessA") If Not Target(1).Address = .Range("C4").Address Then Exit Sub .Range("C7:M19").EntireRow.Hidden = False .Shapes("cbgroup").Visible = True If .Range("C4").Value = False Then .Range("C7:M19").EntireRow.Hidden = True .Shapes("cbgroup").Visible = False End If End With End Sub -- ----- XL2003 Regards William "Pennington" wrote in message ... William I have now got it to work using the sheetcalculate command and have decided to hide rows rather than columns but I have got another problem. The True and False values are produced from check boxes but when the rows are hidden the check boxes are not - they all move down onto the unhidden rows. How can I hide the check boxes or is there another way of selecting a value by the click of a mouse? "William" wrote: Pennington Place the code in the "ThisWorkbook" module, NOT a general module. ----- XL2003 Regards William "Pennington" wrote in message ... Thank William but I can't get it to work. I have placed this code in Module 1 of the workbook Private Sub Workbook_Open() With Sheets("ProcessA") Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ Range("E3:M3").EntireColumn.Hidden = True End With End Sub Saved the file closed and opening it but all the columns are still showing "William" wrote: Hi.. Private Sub Workbook_Open() With Sheets("Sheet1") ..Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ ..Range("E3:M3").EntireColumn.Hidden = True End With End Sub ----- XL2003 Regards William "Pennington" wrote in message ... I am trying to hide a range of cells when the value in another cell is FALSE and show the range of cells when the value is TRUE. My code is as follows but it doesn't work Private Sub Workbook_Open() If Range("C4").Value = False Then Range("E4:M40").Hide = True End If If Range("C4").Value = True Then Range("E4:M40").Unhide = False End Sub How do I make this work? |
|
#20
|
|||
|
|||
|
Hi Pennington
Please send me a sample file and I'll have a look at it - when sending the email please enter "Excel" in the subject line or I will not see the email. ----- XL2003 Regards William "Pennington" wrote in message ... Hi William You are correct but I guess its because I am building the spec as I go along not knowing what I can do in Excel. The original problem of hiding rows/columns when a value is FALSE is now resolved and I finally selected Bob's solution as amended by you but this didn't hide the checkboxes when a value is FALSE which is also now resolved thanks to the code you kindly provided. I now want the action of changing a value from TRUE to FALSE on say ROW 4 to change previously selected TRUE values to FALSE on ROWS 5, 6 & 7. The values are inserted when a check box is clicked so presumambly suitable code in a sheet calculate event would change these values. If it would help I could send you a sample file. "William" wrote: Hi Pennington Have a look at your original and subsequent posts - each time a suggested solution is put forward you are "changing the goalposts". I think perhaps you need to start again and clearly set out the problem you are having and what you want to achieve. ----- XL2003 Regards William "Pennington" wrote in message ... William Many thanks I have made progress but I am still not done. Firstly the check box that causes C4 to change is not hidden. I think its complicated because I have 4 items of data that I am trying to display gradually in response to 4 questions. When the first question is answered correctly the 2nd item of data appears and so on. I need to prevent users answering Q 2 before they have given a correct answer to Q1. The box grouping did work but the boxes didn't stay in the cells so I chose to select one check box and for some reason it works. However, I now realize I have another issue. Providing the user goes sequentialy through the questions it works fine but if he unchecks lets say Q1 having answered Q 2, 3 & 4 correctly, the score will be invalid. The values are added and displayed as percentages so that if each Question scores say 25%, so answering Q1 correctly = 25% and then Q2 correctly = 50%, then Q3 correctly= 75% and finally Q4 correctly = 100% but if he then goes and changes Q1 to produce a FALSE value the score will be 75% not 0%. So how can I cause the other values to change to FALSE if previous values to have been set to TRUE from the checkboxes? Maybe is requires some deft programming, which I am unable to do but if there is a simple solution I would be most grateful "William" wrote: 1. Use your mouse to hover over the toolbars, right-click and select "Customise". Select the Command tab and then select "Drawing" from the "Categories" section. From the "Commands" section, select "Drawing Objrcts" and drag the icon to one of your toolbars. Click "Close" to return to the Excel sheet. 2. Select the "Drawing Objects" icon from your toolbar and then select the check-boxes which will be effected by your code. Hover over one of the selected checkboxes and select "GroupingGroup". Either accept the name given to the group in the "Name" box or create your own name for the group. 3. Since cell C4 is being changed via a check box, use the "Calculate" event.... Private Sub Worksheet_Calculate() With Sheets("Sheet1") If .Range("C4").Value = True Then ..Range("C7:M19").EntireRow.Hidden = False ..Shapes("cbgroup").Visible = True Else ..Range("C7:M19").EntireRow.Hidden = True ..Shapes("cbgroup").Visible = False End If End With End Sub But if it is the check boxes that cause C4 to change, how are you going to change the check box if it is hidden? ----- XL2003 Regards William "William" wrote in message ... Pennington Firstly, dont use the "Calculate" event, use the "Change" event to execute your code. In answer to your post, select all the checkboxes that you want to be hidden / visible when you execute your code to hide / unhide rows and give a name to that selection of checkboxes - In my example I have used "cbgroup". Private Sub Worksheet_Change(ByVal Target As Range) With Sheets("ProcessA") If Not Target(1).Address = .Range("C4").Address Then Exit Sub .Range("C7:M19").EntireRow.Hidden = False .Shapes("cbgroup").Visible = True If .Range("C4").Value = False Then .Range("C7:M19").EntireRow.Hidden = True .Shapes("cbgroup").Visible = False End If End With End Sub -- ----- XL2003 Regards William "Pennington" wrote in message ... William I have now got it to work using the sheetcalculate command and have decided to hide rows rather than columns but I have got another problem. The True and False values are produced from check boxes but when the rows are hidden the check boxes are not - they all move down onto the unhidden rows. How can I hide the check boxes or is there another way of selecting a value by the click of a mouse? "William" wrote: Pennington Place the code in the "ThisWorkbook" module, NOT a general module. ----- XL2003 Regards William "Pennington" wrote in message ... Thank William but I can't get it to work. I have placed this code in Module 1 of the workbook Private Sub Workbook_Open() With Sheets("ProcessA") Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ Range("E3:M3").EntireColumn.Hidden = True End With End Sub Saved the file closed and opening it but all the columns are still showing "William" wrote: Hi.. Private Sub Workbook_Open() With Sheets("Sheet1") ..Range("E3:M3").EntireColumn.Hidden = False If .Range("C4").Value = False Then _ ..Range("E3:M3").EntireColumn.Hidden = True End With End Sub ----- XL2003 Regards William "Pennington" wrote in message ... I am trying to hide a range of cells when the value in another cell is FALSE and show the range of cells when the value is TRUE. My code is as follows but it doesn't work Private Sub Workbook_Open() If Range("C4").Value = False Then Range("E4:M40").Hide = True End If If Range("C4").Value = True Then Range("E4:M40").Unhide = False End Sub How do I make this work? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| sum of a cell if 2 cells meet a condition | Excel Worksheet Functions | |||
| Heps to design Locked/Unlocked cells in protected worksheet | Excel Discussion (Misc queries) | |||
| Hiding Formula in cells | Excel Discussion (Misc queries) | |||
| How to add cells wiht the condition of the former cell. | Excel Discussion (Misc queries) | |||
| condition format for surrounding cells | Excel Discussion (Misc queries) | |||
Linear Mode
