Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I automatically hide "FALSE" rows?
I have created the following "IF" formula =IF('officer
data'!Z4="fse",'officer data'!A4) to pull employees that have a status of "fse" from another worksheet in the same workbook. I only want to show the rows that are "TRUE" and automatically hide rows that are "FALSE" |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I automatically hide "FALSE" rows?
You could apply a filter (Data, Filter, AutoFilter) and then have your filter
show everything that does not equal FALSE. (under custom) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Kiley" wrote: I have created the following "IF" formula =IF('officer data'!Z4="fse",'officer data'!A4) to pull employees that have a status of "fse" from another worksheet in the same workbook. I only want to show the rows that are "TRUE" and automatically hide rows that are "FALSE" |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I automatically hide "FALSE" rows?
That works but I was hoping that there was a formula to help do this because
I am using this workbook as a template and the employees may change. If I appy the filter and change the employees, the new employees are not automatically visible. "Luke M" wrote: You could apply a filter (Data, Filter, AutoFilter) and then have your filter show everything that does not equal FALSE. (under custom) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Kiley" wrote: I have created the following "IF" formula =IF('officer data'!Z4="fse",'officer data'!A4) to pull employees that have a status of "fse" from another worksheet in the same workbook. I only want to show the rows that are "TRUE" and automatically hide rows that are "FALSE" |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I automatically hide "FALSE" rows?
There's no formula that will hide rows.
You'll either need a macro or teach the users to show the data and filter it again. (If you teach them, they'll be able to use filters in lots of workbooks!) Kiley wrote: That works but I was hoping that there was a formula to help do this because I am using this workbook as a template and the employees may change. If I appy the filter and change the employees, the new employees are not automatically visible. "Luke M" wrote: You could apply a filter (Data, Filter, AutoFilter) and then have your filter show everything that does not equal FALSE. (under custom) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Kiley" wrote: I have created the following "IF" formula =IF('officer data'!Z4="fse",'officer data'!A4) to pull employees that have a status of "fse" from another worksheet in the same workbook. I only want to show the rows that are "TRUE" and automatically hide rows that are "FALSE" -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I automatically hide "FALSE" rows?
I did create a macro but it will not work if I protect the worksheet. I need
to protect the worksheet so that the formulas cannot be changed. Is there a way around this? "Dave Peterson" wrote: There's no formula that will hide rows. You'll either need a macro or teach the users to show the data and filter it again. (If you teach them, they'll be able to use filters in lots of workbooks!) Kiley wrote: That works but I was hoping that there was a formula to help do this because I am using this workbook as a template and the employees may change. If I appy the filter and change the employees, the new employees are not automatically visible. "Luke M" wrote: You could apply a filter (Data, Filter, AutoFilter) and then have your filter show everything that does not equal FALSE. (under custom) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Kiley" wrote: I have created the following "IF" formula =IF('officer data'!Z4="fse",'officer data'!A4) to pull employees that have a status of "fse" from another worksheet in the same workbook. I only want to show the rows that are "TRUE" and automatically hide rows that are "FALSE" -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I automatically hide "FALSE" rows?
Your macro could unprotect the worksheet, do its work and reprotect the
worksheet. Or you could protect the worksheet in code using the "userinterfaceonly" parm. Saved from a previous post. If you already have the outline/subtotals/autofilter applied, you can protect the worksheet in code (auto_open/workbook_open??). Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True '.EnableOutlining = True .EnableAutoFilter = True If .FilterMode Then .ShowAllData End If End With End Sub It needs to be reset each time you open the workbook. (Earlier versions of excel don't remember it after closing the workbook. IIRC, xl2002+ will remember the allow autofilter setting under tools|Protection|protect sheet, but that won't help when you're filtering via code.) Kiley wrote: I did create a macro but it will not work if I protect the worksheet. I need to protect the worksheet so that the formulas cannot be changed. Is there a way around this? "Dave Peterson" wrote: There's no formula that will hide rows. You'll either need a macro or teach the users to show the data and filter it again. (If you teach them, they'll be able to use filters in lots of workbooks!) Kiley wrote: That works but I was hoping that there was a formula to help do this because I am using this workbook as a template and the employees may change. If I appy the filter and change the employees, the new employees are not automatically visible. "Luke M" wrote: You could apply a filter (Data, Filter, AutoFilter) and then have your filter show everything that does not equal FALSE. (under custom) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Kiley" wrote: I have created the following "IF" formula =IF('officer data'!Z4="fse",'officer data'!A4) to pull employees that have a status of "fse" from another worksheet in the same workbook. I only want to show the rows that are "TRUE" and automatically hide rows that are "FALSE" -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I automatically hide "FALSE" rows?
I don't know anything about code. So, I didn't understand the code part of
your response. I did add the "unprotect" and "protect" to my macro and that worked. However, once I closed the workbook and came back in and tried the macro, it prompted me to unprotect the worksheet. Did I miss something? "Dave Peterson" wrote: Your macro could unprotect the worksheet, do its work and reprotect the worksheet. Or you could protect the worksheet in code using the "userinterfaceonly" parm. Saved from a previous post. If you already have the outline/subtotals/autofilter applied, you can protect the worksheet in code (auto_open/workbook_open??). Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True '.EnableOutlining = True .EnableAutoFilter = True If .FilterMode Then .ShowAllData End If End With End Sub It needs to be reset each time you open the workbook. (Earlier versions of excel don't remember it after closing the workbook. IIRC, xl2002+ will remember the allow autofilter setting under tools|Protection|protect sheet, but that won't help when you're filtering via code.) Kiley wrote: I did create a macro but it will not work if I protect the worksheet. I need to protect the worksheet so that the formulas cannot be changed. Is there a way around this? "Dave Peterson" wrote: There's no formula that will hide rows. You'll either need a macro or teach the users to show the data and filter it again. (If you teach them, they'll be able to use filters in lots of workbooks!) Kiley wrote: That works but I was hoping that there was a formula to help do this because I am using this workbook as a template and the employees may change. If I appy the filter and change the employees, the new employees are not automatically visible. "Luke M" wrote: You could apply a filter (Data, Filter, AutoFilter) and then have your filter show everything that does not equal FALSE. (under custom) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Kiley" wrote: I have created the following "IF" formula =IF('officer data'!Z4="fse",'officer data'!A4) to pull employees that have a status of "fse" from another worksheet in the same workbook. I only want to show the rows that are "TRUE" and automatically hide rows that are "FALSE" -- Dave Peterson -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I automatically hide "FALSE" rows?
It sounds like it.
I'm not sure how you run your macro, but it should look something like: Sub YourMacro() activesheet.unprotect password:="hithere" 'your code to do the work activesheet.protect password:="hithere" End sub Depending on where/how you run the code, it could vary. But you'll have to share more. Kiley wrote: I don't know anything about code. So, I didn't understand the code part of your response. I did add the "unprotect" and "protect" to my macro and that worked. However, once I closed the workbook and came back in and tried the macro, it prompted me to unprotect the worksheet. Did I miss something? "Dave Peterson" wrote: Your macro could unprotect the worksheet, do its work and reprotect the worksheet. Or you could protect the worksheet in code using the "userinterfaceonly" parm. Saved from a previous post. If you already have the outline/subtotals/autofilter applied, you can protect the worksheet in code (auto_open/workbook_open??). Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True '.EnableOutlining = True .EnableAutoFilter = True If .FilterMode Then .ShowAllData End If End With End Sub It needs to be reset each time you open the workbook. (Earlier versions of excel don't remember it after closing the workbook. IIRC, xl2002+ will remember the allow autofilter setting under tools|Protection|protect sheet, but that won't help when you're filtering via code.) Kiley wrote: I did create a macro but it will not work if I protect the worksheet. I need to protect the worksheet so that the formulas cannot be changed. Is there a way around this? "Dave Peterson" wrote: There's no formula that will hide rows. You'll either need a macro or teach the users to show the data and filter it again. (If you teach them, they'll be able to use filters in lots of workbooks!) Kiley wrote: That works but I was hoping that there was a formula to help do this because I am using this workbook as a template and the employees may change. If I appy the filter and change the employees, the new employees are not automatically visible. "Luke M" wrote: You could apply a filter (Data, Filter, AutoFilter) and then have your filter show everything that does not equal FALSE. (under custom) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Kiley" wrote: I have created the following "IF" formula =IF('officer data'!Z4="fse",'officer data'!A4) to pull employees that have a status of "fse" from another worksheet in the same workbook. I only want to show the rows that are "TRUE" and automatically hide rows that are "FALSE" -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I automatically hide "FALSE" rows?
this is what the code looks like after I set up the macro by creating the
steps involved. ActiveSheet.Unprotect Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="<FALSE", Operator:=xlAnd, _ Criteria2:="<#VALUE!" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub "Dave Peterson" wrote: It sounds like it. I'm not sure how you run your macro, but it should look something like: Sub YourMacro() activesheet.unprotect password:="hithere" 'your code to do the work activesheet.protect password:="hithere" End sub Depending on where/how you run the code, it could vary. But you'll have to share more. Kiley wrote: I don't know anything about code. So, I didn't understand the code part of your response. I did add the "unprotect" and "protect" to my macro and that worked. However, once I closed the workbook and came back in and tried the macro, it prompted me to unprotect the worksheet. Did I miss something? "Dave Peterson" wrote: Your macro could unprotect the worksheet, do its work and reprotect the worksheet. Or you could protect the worksheet in code using the "userinterfaceonly" parm. Saved from a previous post. If you already have the outline/subtotals/autofilter applied, you can protect the worksheet in code (auto_open/workbook_open??). Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True '.EnableOutlining = True .EnableAutoFilter = True If .FilterMode Then .ShowAllData End If End With End Sub It needs to be reset each time you open the workbook. (Earlier versions of excel don't remember it after closing the workbook. IIRC, xl2002+ will remember the allow autofilter setting under tools|Protection|protect sheet, but that won't help when you're filtering via code.) Kiley wrote: I did create a macro but it will not work if I protect the worksheet. I need to protect the worksheet so that the formulas cannot be changed. Is there a way around this? "Dave Peterson" wrote: There's no formula that will hide rows. You'll either need a macro or teach the users to show the data and filter it again. (If you teach them, they'll be able to use filters in lots of workbooks!) Kiley wrote: That works but I was hoping that there was a formula to help do this because I am using this workbook as a template and the employees may change. If I appy the filter and change the employees, the new employees are not automatically visible. "Luke M" wrote: You could apply a filter (Data, Filter, AutoFilter) and then have your filter show everything that does not equal FALSE. (under custom) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Kiley" wrote: I have created the following "IF" formula =IF('officer data'!Z4="fse",'officer data'!A4) to pull employees that have a status of "fse" from another worksheet in the same workbook. I only want to show the rows that are "TRUE" and automatically hide rows that are "FALSE" -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I automatically hide "FALSE" rows?
And it works ok for you or you have trouble?
Kiley wrote: this is what the code looks like after I set up the macro by creating the steps involved. ActiveSheet.Unprotect Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="<FALSE", Operator:=xlAnd, _ Criteria2:="<#VALUE!" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub "Dave Peterson" wrote: It sounds like it. I'm not sure how you run your macro, but it should look something like: Sub YourMacro() activesheet.unprotect password:="hithere" 'your code to do the work activesheet.protect password:="hithere" End sub Depending on where/how you run the code, it could vary. But you'll have to share more. Kiley wrote: I don't know anything about code. So, I didn't understand the code part of your response. I did add the "unprotect" and "protect" to my macro and that worked. However, once I closed the workbook and came back in and tried the macro, it prompted me to unprotect the worksheet. Did I miss something? "Dave Peterson" wrote: Your macro could unprotect the worksheet, do its work and reprotect the worksheet. Or you could protect the worksheet in code using the "userinterfaceonly" parm. Saved from a previous post. If you already have the outline/subtotals/autofilter applied, you can protect the worksheet in code (auto_open/workbook_open??). Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True '.EnableOutlining = True .EnableAutoFilter = True If .FilterMode Then .ShowAllData End If End With End Sub It needs to be reset each time you open the workbook. (Earlier versions of excel don't remember it after closing the workbook. IIRC, xl2002+ will remember the allow autofilter setting under tools|Protection|protect sheet, but that won't help when you're filtering via code.) Kiley wrote: I did create a macro but it will not work if I protect the worksheet. I need to protect the worksheet so that the formulas cannot be changed. Is there a way around this? "Dave Peterson" wrote: There's no formula that will hide rows. You'll either need a macro or teach the users to show the data and filter it again. (If you teach them, they'll be able to use filters in lots of workbooks!) Kiley wrote: That works but I was hoping that there was a formula to help do this because I am using this workbook as a template and the employees may change. If I appy the filter and change the employees, the new employees are not automatically visible. "Luke M" wrote: You could apply a filter (Data, Filter, AutoFilter) and then have your filter show everything that does not equal FALSE. (under custom) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Kiley" wrote: I have created the following "IF" formula =IF('officer data'!Z4="fse",'officer data'!A4) to pull employees that have a status of "fse" from another worksheet in the same workbook. I only want to show the rows that are "TRUE" and automatically hide rows that are "FALSE" -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I automatically hide "FALSE" rows?
I am having problems when I close the workbook and come back in, it will ask
for a password before I can perform the macro. The worksheet is protected and I set up the macro to unprotect the sheet do the filter and then proctect the sheet again. This worksheet is a template and I want someone to be able to go into the sheet and perform the macro without having to unprotect it first. "Dave Peterson" wrote: And it works ok for you or you have trouble? Kiley wrote: this is what the code looks like after I set up the macro by creating the steps involved. ActiveSheet.Unprotect Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="<FALSE", Operator:=xlAnd, _ Criteria2:="<#VALUE!" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub "Dave Peterson" wrote: It sounds like it. I'm not sure how you run your macro, but it should look something like: Sub YourMacro() activesheet.unprotect password:="hithere" 'your code to do the work activesheet.protect password:="hithere" End sub Depending on where/how you run the code, it could vary. But you'll have to share more. Kiley wrote: I don't know anything about code. So, I didn't understand the code part of your response. I did add the "unprotect" and "protect" to my macro and that worked. However, once I closed the workbook and came back in and tried the macro, it prompted me to unprotect the worksheet. Did I miss something? "Dave Peterson" wrote: Your macro could unprotect the worksheet, do its work and reprotect the worksheet. Or you could protect the worksheet in code using the "userinterfaceonly" parm. Saved from a previous post. If you already have the outline/subtotals/autofilter applied, you can protect the worksheet in code (auto_open/workbook_open??). Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True '.EnableOutlining = True .EnableAutoFilter = True If .FilterMode Then .ShowAllData End If End With End Sub It needs to be reset each time you open the workbook. (Earlier versions of excel don't remember it after closing the workbook. IIRC, xl2002+ will remember the allow autofilter setting under tools|Protection|protect sheet, but that won't help when you're filtering via code.) Kiley wrote: I did create a macro but it will not work if I protect the worksheet. I need to protect the worksheet so that the formulas cannot be changed. Is there a way around this? "Dave Peterson" wrote: There's no formula that will hide rows. You'll either need a macro or teach the users to show the data and filter it again. (If you teach them, they'll be able to use filters in lots of workbooks!) Kiley wrote: That works but I was hoping that there was a formula to help do this because I am using this workbook as a template and the employees may change. If I appy the filter and change the employees, the new employees are not automatically visible. "Luke M" wrote: You could apply a filter (Data, Filter, AutoFilter) and then have your filter show everything that does not equal FALSE. (under custom) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Kiley" wrote: I have created the following "IF" formula =IF('officer data'!Z4="fse",'officer data'!A4) to pull employees that have a status of "fse" from another worksheet in the same workbook. I only want to show the rows that are "TRUE" and automatically hide rows that are "FALSE" -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I automatically hide "FALSE" rows?
You need to specify the password on both the .protect and unprotect lines:
ActiveSheet.Unprotect password:="hithere" ... ActiveSheet.protect password:="hithere" Kiley wrote: I am having problems when I close the workbook and come back in, it will ask for a password before I can perform the macro. The worksheet is protected and I set up the macro to unprotect the sheet do the filter and then proctect the sheet again. This worksheet is a template and I want someone to be able to go into the sheet and perform the macro without having to unprotect it first. "Dave Peterson" wrote: And it works ok for you or you have trouble? Kiley wrote: this is what the code looks like after I set up the macro by creating the steps involved. ActiveSheet.Unprotect Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="<FALSE", Operator:=xlAnd, _ Criteria2:="<#VALUE!" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub "Dave Peterson" wrote: It sounds like it. I'm not sure how you run your macro, but it should look something like: Sub YourMacro() activesheet.unprotect password:="hithere" 'your code to do the work activesheet.protect password:="hithere" End sub Depending on where/how you run the code, it could vary. But you'll have to share more. Kiley wrote: I don't know anything about code. So, I didn't understand the code part of your response. I did add the "unprotect" and "protect" to my macro and that worked. However, once I closed the workbook and came back in and tried the macro, it prompted me to unprotect the worksheet. Did I miss something? "Dave Peterson" wrote: Your macro could unprotect the worksheet, do its work and reprotect the worksheet. Or you could protect the worksheet in code using the "userinterfaceonly" parm. Saved from a previous post. If you already have the outline/subtotals/autofilter applied, you can protect the worksheet in code (auto_open/workbook_open??). Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True '.EnableOutlining = True .EnableAutoFilter = True If .FilterMode Then .ShowAllData End If End With End Sub It needs to be reset each time you open the workbook. (Earlier versions of excel don't remember it after closing the workbook. IIRC, xl2002+ will remember the allow autofilter setting under tools|Protection|protect sheet, but that won't help when you're filtering via code.) Kiley wrote: I did create a macro but it will not work if I protect the worksheet. I need to protect the worksheet so that the formulas cannot be changed. Is there a way around this? "Dave Peterson" wrote: There's no formula that will hide rows. You'll either need a macro or teach the users to show the data and filter it again. (If you teach them, they'll be able to use filters in lots of workbooks!) Kiley wrote: That works but I was hoping that there was a formula to help do this because I am using this workbook as a template and the employees may change. If I appy the filter and change the employees, the new employees are not automatically visible. "Luke M" wrote: You could apply a filter (Data, Filter, AutoFilter) and then have your filter show everything that does not equal FALSE. (under custom) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Kiley" wrote: I have created the following "IF" formula =IF('officer data'!Z4="fse",'officer data'!A4) to pull employees that have a status of "fse" from another worksheet in the same workbook. I only want to show the rows that are "TRUE" and automatically hide rows that are "FALSE" -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I automatically hide "FALSE" rows?
I added the password and not it says I have an error in the last line?
ActiveSheet.Unprotect Password:="tarcrune" Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="<FALSE", Operator:=xlAnd, _ Criteria2:="<#VALUE!" ActiveSheet.Protect Password:="tarcrune" DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub "Dave Peterson" wrote: You need to specify the password on both the .protect and unprotect lines: ActiveSheet.Unprotect password:="hithere" ... ActiveSheet.protect password:="hithere" Kiley wrote: I am having problems when I close the workbook and come back in, it will ask for a password before I can perform the macro. The worksheet is protected and I set up the macro to unprotect the sheet do the filter and then proctect the sheet again. This worksheet is a template and I want someone to be able to go into the sheet and perform the macro without having to unprotect it first. "Dave Peterson" wrote: And it works ok for you or you have trouble? Kiley wrote: this is what the code looks like after I set up the macro by creating the steps involved. ActiveSheet.Unprotect Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="<FALSE", Operator:=xlAnd, _ Criteria2:="<#VALUE!" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub "Dave Peterson" wrote: It sounds like it. I'm not sure how you run your macro, but it should look something like: Sub YourMacro() activesheet.unprotect password:="hithere" 'your code to do the work activesheet.protect password:="hithere" End sub Depending on where/how you run the code, it could vary. But you'll have to share more. Kiley wrote: I don't know anything about code. So, I didn't understand the code part of your response. I did add the "unprotect" and "protect" to my macro and that worked. However, once I closed the workbook and came back in and tried the macro, it prompted me to unprotect the worksheet. Did I miss something? "Dave Peterson" wrote: Your macro could unprotect the worksheet, do its work and reprotect the worksheet. Or you could protect the worksheet in code using the "userinterfaceonly" parm. Saved from a previous post. If you already have the outline/subtotals/autofilter applied, you can protect the worksheet in code (auto_open/workbook_open??). Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True '.EnableOutlining = True .EnableAutoFilter = True If .FilterMode Then .ShowAllData End If End With End Sub It needs to be reset each time you open the workbook. (Earlier versions of excel don't remember it after closing the workbook. IIRC, xl2002+ will remember the allow autofilter setting under tools|Protection|protect sheet, but that won't help when you're filtering via code.) Kiley wrote: I did create a macro but it will not work if I protect the worksheet. I need to protect the worksheet so that the formulas cannot be changed. Is there a way around this? "Dave Peterson" wrote: There's no formula that will hide rows. You'll either need a macro or teach the users to show the data and filter it again. (If you teach them, they'll be able to use filters in lots of workbooks!) Kiley wrote: That works but I was hoping that there was a formula to help do this because I am using this workbook as a template and the employees may change. If I appy the filter and change the employees, the new employees are not automatically visible. "Luke M" wrote: You could apply a filter (Data, Filter, AutoFilter) and then have your filter show everything that does not equal FALSE. (under custom) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Kiley" wrote: I have created the following "IF" formula =IF('officer data'!Z4="fse",'officer data'!A4) to pull employees that have a status of "fse" from another worksheet in the same workbook. I only want to show the rows that are "TRUE" and automatically hide rows that are "FALSE" -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I automatically hide "FALSE" rows?
change last two lines to:
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="tarcrune" Note that this needs to be one continuous line in VBA (don't have part of it on a second line) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Kiley" wrote: I added the password and not it says I have an error in the last line? ActiveSheet.Unprotect Password:="tarcrune" Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="<FALSE", Operator:=xlAnd, _ Criteria2:="<#VALUE!" ActiveSheet.Protect Password:="tarcrune" DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub "Dave Peterson" wrote: You need to specify the password on both the .protect and unprotect lines: ActiveSheet.Unprotect password:="hithere" ... ActiveSheet.protect password:="hithere" Kiley wrote: I am having problems when I close the workbook and come back in, it will ask for a password before I can perform the macro. The worksheet is protected and I set up the macro to unprotect the sheet do the filter and then proctect the sheet again. This worksheet is a template and I want someone to be able to go into the sheet and perform the macro without having to unprotect it first. "Dave Peterson" wrote: And it works ok for you or you have trouble? Kiley wrote: this is what the code looks like after I set up the macro by creating the steps involved. ActiveSheet.Unprotect Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="<FALSE", Operator:=xlAnd, _ Criteria2:="<#VALUE!" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub "Dave Peterson" wrote: It sounds like it. I'm not sure how you run your macro, but it should look something like: Sub YourMacro() activesheet.unprotect password:="hithere" 'your code to do the work activesheet.protect password:="hithere" End sub Depending on where/how you run the code, it could vary. But you'll have to share more. Kiley wrote: I don't know anything about code. So, I didn't understand the code part of your response. I did add the "unprotect" and "protect" to my macro and that worked. However, once I closed the workbook and came back in and tried the macro, it prompted me to unprotect the worksheet. Did I miss something? "Dave Peterson" wrote: Your macro could unprotect the worksheet, do its work and reprotect the worksheet. Or you could protect the worksheet in code using the "userinterfaceonly" parm. Saved from a previous post. If you already have the outline/subtotals/autofilter applied, you can protect the worksheet in code (auto_open/workbook_open??). Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True '.EnableOutlining = True .EnableAutoFilter = True If .FilterMode Then .ShowAllData End If End With End Sub It needs to be reset each time you open the workbook. (Earlier versions of excel don't remember it after closing the workbook. IIRC, xl2002+ will remember the allow autofilter setting under tools|Protection|protect sheet, but that won't help when you're filtering via code.) Kiley wrote: I did create a macro but it will not work if I protect the worksheet. I need to protect the worksheet so that the formulas cannot be changed. Is there a way around this? "Dave Peterson" wrote: There's no formula that will hide rows. You'll either need a macro or teach the users to show the data and filter it again. (If you teach them, they'll be able to use filters in lots of workbooks!) Kiley wrote: That works but I was hoping that there was a formula to help do this because I am using this workbook as a template and the employees may change. If I appy the filter and change the employees, the new employees are not automatically visible. "Luke M" wrote: You could apply a filter (Data, Filter, AutoFilter) and then have your filter show everything that does not equal FALSE. (under custom) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Kiley" wrote: I have created the following "IF" formula =IF('officer data'!Z4="fse",'officer data'!A4) to pull employees that have a status of "fse" from another worksheet in the same workbook. I only want to show the rows that are "TRUE" and automatically hide rows that are "FALSE" -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I automatically hide "FALSE" rows?
That worked! Thank you Luke and Dave! Much appreciated!
"Luke M" wrote: change last two lines to: ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, Password:="tarcrune" Note that this needs to be one continuous line in VBA (don't have part of it on a second line) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Kiley" wrote: I added the password and not it says I have an error in the last line? ActiveSheet.Unprotect Password:="tarcrune" Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="<FALSE", Operator:=xlAnd, _ Criteria2:="<#VALUE!" ActiveSheet.Protect Password:="tarcrune" DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub "Dave Peterson" wrote: You need to specify the password on both the .protect and unprotect lines: ActiveSheet.Unprotect password:="hithere" ... ActiveSheet.protect password:="hithere" Kiley wrote: I am having problems when I close the workbook and come back in, it will ask for a password before I can perform the macro. The worksheet is protected and I set up the macro to unprotect the sheet do the filter and then proctect the sheet again. This worksheet is a template and I want someone to be able to go into the sheet and perform the macro without having to unprotect it first. "Dave Peterson" wrote: And it works ok for you or you have trouble? Kiley wrote: this is what the code looks like after I set up the macro by creating the steps involved. ActiveSheet.Unprotect Selection.AutoFilter Selection.AutoFilter Field:=1, Criteria1:="<FALSE", Operator:=xlAnd, _ Criteria2:="<#VALUE!" ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True End Sub "Dave Peterson" wrote: It sounds like it. I'm not sure how you run your macro, but it should look something like: Sub YourMacro() activesheet.unprotect password:="hithere" 'your code to do the work activesheet.protect password:="hithere" End sub Depending on where/how you run the code, it could vary. But you'll have to share more. Kiley wrote: I don't know anything about code. So, I didn't understand the code part of your response. I did add the "unprotect" and "protect" to my macro and that worked. However, once I closed the workbook and came back in and tried the macro, it prompted me to unprotect the worksheet. Did I miss something? "Dave Peterson" wrote: Your macro could unprotect the worksheet, do its work and reprotect the worksheet. Or you could protect the worksheet in code using the "userinterfaceonly" parm. Saved from a previous post. If you already have the outline/subtotals/autofilter applied, you can protect the worksheet in code (auto_open/workbook_open??). Option Explicit Sub auto_open() With Worksheets("sheet1") .Protect Password:="hi", userinterfaceonly:=True '.EnableOutlining = True .EnableAutoFilter = True If .FilterMode Then .ShowAllData End If End With End Sub It needs to be reset each time you open the workbook. (Earlier versions of excel don't remember it after closing the workbook. IIRC, xl2002+ will remember the allow autofilter setting under tools|Protection|protect sheet, but that won't help when you're filtering via code.) Kiley wrote: I did create a macro but it will not work if I protect the worksheet. I need to protect the worksheet so that the formulas cannot be changed. Is there a way around this? "Dave Peterson" wrote: There's no formula that will hide rows. You'll either need a macro or teach the users to show the data and filter it again. (If you teach them, they'll be able to use filters in lots of workbooks!) Kiley wrote: That works but I was hoping that there was a formula to help do this because I am using this workbook as a template and the employees may change. If I appy the filter and change the employees, the new employees are not automatically visible. "Luke M" wrote: You could apply a filter (Data, Filter, AutoFilter) and then have your filter show everything that does not equal FALSE. (under custom) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Kiley" wrote: I have created the following "IF" formula =IF('officer data'!Z4="fse",'officer data'!A4) to pull employees that have a status of "fse" from another worksheet in the same workbook. I only want to show the rows that are "TRUE" and automatically hide rows that are "FALSE" -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Disable autoformat of "true" and "false" text | Excel Discussion (Misc queries) | |||
=IF(VLOOKUP(C11,Group,2,FALSE)=D11,"True","Not Valid") and =IF(D1 | Excel Worksheet Functions | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
If "x" Hide / Unhide Rows | Excel Discussion (Misc queries) |