I have two forms. One form has all the userform pop ups asking for details of expenses made (Expense Report P-1) Another form has all the details listed so it can be printed out (Expense Report P-2)
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
></o>
<o></o>
On P-2 report I created a macro that would hid the rows if a certain cell on the page was < = 0 and would unhide if the same cell was > 0 it will also lock the cells so changes can't be made when there is a value >0 in the certain cell and will unlock when the certain cell's value is <0
<o></o>
<o></o>
The codes I have created work fine for 6 categories each referencing 25 days but the last two categories and their 25 days only allow one row to show at a time. So if for transportation day 1 details are filted in from a userform on P-1 and the cell G278 on P-2 shows a value then it the row (278:278) will unhide. And will hide again when the cell G278's value is 0.
However, if details are also filtered in from a form for transportation day 2 and the cell G279 shows a value it will unhide but then the row for day 1 will hide. If I delete the value from cell G278 or G279 they both will hide even though when I manually unhide the rows the value for the one I didn't delete is still showing.
<o></o>
<o></o>
I checked the codes for transportation with those that works and it all looks the same. I don't understand why this is happening.
(I am using Excel 2003)
<o></o>
<o></o>
Here is the code for the category Misc day 25 that works fine<o></o>
<o></o>
<o></o>
<o></o>
Here are two codes for transportation day 1 and day 2 which are not working like the <o></o>
others do. <o></o>
<o></o>
<o></o>
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o></o><o
></o>On P-2 report I created a macro that would hid the rows if a certain cell on the page was < = 0 and would unhide if the same cell was > 0 it will also lock the cells so changes can't be made when there is a value >0 in the certain cell and will unlock when the certain cell's value is <0
<o
></o><o
></o>The codes I have created work fine for 6 categories each referencing 25 days but the last two categories and their 25 days only allow one row to show at a time. So if for transportation day 1 details are filted in from a userform on P-1 and the cell G278 on P-2 shows a value then it the row (278:278) will unhide. And will hide again when the cell G278's value is 0.
However, if details are also filtered in from a form for transportation day 2 and the cell G279 shows a value it will unhide but then the row for day 1 will hide. If I delete the value from cell G278 or G279 they both will hide even though when I manually unhide the rows the value for the one I didn't delete is still showing.
<o
></o><o
></o>I checked the codes for transportation with those that works and it all looks the same. I don't understand why this is happening.
(I am using Excel 2003)
<o
></o><o
></o>Here is the code for the category Misc day 25 that works fine<o
></o><o
></o>
Rich (BB code):
Sub HideRow_Misc25()<o:p></o:p>
Application.ScreenUpdating = False<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
Sheets("Expense Report P-2").Activate<o:p></o:p>
If Range("G277").Value > 0 Then<o:p></o:p>
<o:p></o:p>
ActiveSheet.Unprotect Password:="secret"<o:p></o:p>
Range("misc25,misc25a").Select<o:p></o:p>
Selection.Locked = True<o:p></o:p>
ActiveSheet.Protect Password:="secret"<o:p></o:p>
<o:p></o:p>
Rows("277:277").Select<o:p></o:p>
Range("B277").Activate<o:p></o:p>
ActiveSheet.Unprotect Password:="secret"<o:p></o:p>
Selection.EntireRow.Hidden = False<o:p></o:p>
ActiveSheet.Protect Password:="secret"<o:p></o:p>
<o:p></o:p>
Sheets("Expense Report P-1 ").Activate<o:p></o:p>
Range("misc25b").Select<o:p></o:p>
<o:p></o:p>
<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:place w:st="on"><st1:PlaceName w:st="on">ElseIf</st1:PlaceName> <st1:PlaceType w:st="on">Range</st1:PlaceType></st1:place>("G277").Value <= 0 Then<o:p></o:p>
<o:p></o:p>
ActiveSheet.Unprotect Password:="secret"<o:p></o:p>
Range("misc25,misc25a").Select<o:p></o:p>
Selection.Locked = False<o:p></o:p>
ActiveSheet.Protect Password:="secret"<o:p></o:p>
<o:p></o:p>
Rows("277:277").Select<o:p></o:p>
Range("B277").Activate<o:p></o:p>
ActiveSheet.Unprotect Password:="secret"<o:p></o:p>
Selection.EntireRow.Hidden = True<o:p></o:p>
ActiveSheet.Protect Password:="secret"<o:p></o:p>
<o:p></o:p>
Sheets("Expense Report P-1 ").Activate<o:p></o:p>
Range("misc25b").Select<o:p></o:p>
<o:p></o:p>
End If<o:p></o:p>
End Sub<o:p></o:p><o
></o>Here are two codes for transportation day 1 and day 2 which are not working like the <o
></o>others do. <o
></o><o
></o>
Rich (BB code):
Sub HideRow_Transportation1()<o:p></o:p>
Application.ScreenUpdating = False<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
Sheets("Expense Report P-2").Activate<o:p></o:p>
If Range("G278").Value > 0 Then<o:p></o:p>
<o:p></o:p>
ActiveSheet.Unprotect Password:="secret"<o:p></o:p>
Range("Cab1,Rail1,Toll1,Gas1,Parking1,transport1a").Select<o:p></o:p>
Selection.Locked = True<o:p></o:p>
ActiveSheet.Protect Password:="secret"<o:p></o:p>
<o:p></o:p>
Rows("278:278").Select<o:p></o:p>
Range("B278").Activate<o:p></o:p>
ActiveSheet.Unprotect Password:="secret"<o:p></o:p>
Selection.EntireRow.Hidden = False<o:p></o:p>
ActiveSheet.Protect Password:="secret"<o:p></o:p>
<o:p></o:p>
Sheets("Expense Report P-1 ").Activate<o:p></o:p>
Range("transportation1").Select<o:p></o:p>
<o:p></o:p>
<st1:place w:st="on"><st1:PlaceName w:st="on">ElseIf</st1:PlaceName> <st1:PlaceType w:st="on">Range</st1:PlaceType></st1:place>("G278").Value <= 0 Then<o:p></o:p>
<o:p></o:p>
ActiveSheet.Unprotect Password:="secret"<o:p></o:p>
Range("Cab1,Rail1,Toll1,Gas1,Parking1,transport1a").Select<o:p></o:p>
Selection.Locked = False<o:p></o:p>
ActiveSheet.Protect Password:="secret"<o:p></o:p>
<o:p></o:p>
Rows("278:278").Select<o:p></o:p>
Range("B278").Activate<o:p></o:p>
ActiveSheet.Unprotect Password:="secret"<o:p></o:p>
Selection.EntireRow.Hidden = True<o:p></o:p>
ActiveSheet.Protect Password:="secret"<o:p></o:p>
<o:p></o:p>
Sheets("Expense Report P-1 ").Activate<o:p></o:p>
Range("transportation1").Select<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
End If<o:p></o:p>
End Sub<o:p></o:p>
Rich (BB code):
Sub HideRow_Transportation2()<o:p></o:p>
Application.ScreenUpdating = False<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
Sheets("Expense Report P-2").Activate<o:p></o:p>
If Range("G279").Value > 0 Then<o:p></o:p>
<o:p></o:p>
ActiveSheet.Unprotect Password:="secret"<o:p></o:p>
Range("Cab2,Rail2,Toll2,Gas2,Parking2,transport2a").Select<o:p></o:p>
Selection.Locked = True<o:p></o:p>
ActiveSheet.Protect Password:="secret"<o:p></o:p>
<o:p></o:p>
Rows("279:279").Select<o:p></o:p>
Range("B279").Activate<o:p></o:p>
ActiveSheet.Unprotect Password:="secret"<o:p></o:p>
Selection.EntireRow.Hidden = False<o:p></o:p>
ActiveSheet.Protect Password:="secret"<o:p></o:p>
<o:p></o:p>
Sheets("Expense Report P-1 ").Activate<o:p></o:p>
Range("transportation2").Select<o:p></o:p>
<o:p></o:p>
<st1:place w:st="on"><st1:PlaceName w:st="on">ElseIf</st1:PlaceName> <st1:PlaceType w:st="on">Range</st1:PlaceType></st1:place>("G279").Value <= 0 Then<o:p></o:p>
<o:p></o:p>
ActiveSheet.Unprotect Password:="secret"<o:p></o:p>
Range("Cab2,Rail2,Toll2,Gas2,Parking2,transport2a").Select<o:p></o:p>
Selection.Locked = False<o:p></o:p>
ActiveSheet.Protect Password:="secret"<o:p></o:p>
<o:p></o:p>
Rows("279:279").Select<o:p></o:p>
Range("B279").Activate<o:p></o:p>
ActiveSheet.Unprotect Password:="secret"<o:p></o:p>
Selection.EntireRow.Hidden = True<o:p></o:p>
ActiveSheet.Protect Password:="secret"<o:p></o:p>
<o:p></o:p>
Sheets("Expense Report P-1 ").Activate<o:p></o:p>
Range("transportation2").Select<o:p></o:p>
<o:p></o:p>
End If<o:p></o:p>
End Sub<o:p></o:p>
