Hide/Unhide Rows Problem

J

jm485

New Member
Joined
Mar 7, 2011
Messages
12
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:office:office" /><o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
Here is the code for the category Misc day 25 that works fine<o:p></o:p>
<o:p></o:p>
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:p></o:p>
<o:p></o:p>
Here are two codes for transportation day 1 and day 2 which are not working like the <o:p></o:p>
others do. <o:p></o:p>
<o:p></o:p>
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>
<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>
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Sort by date Sort by votes
I eventually figured this out. It took forever! I changed the Expense Report P-1 to say Sheet1 and Expense Report P-2 to say Sheet2. That seemed to help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Adding to an existing MACRO
Replies
2
Views
115
Northern NY Design
N
itsgrady
  • Solved
I would like to unhide the row after printing
Replies
4
Views
454
itsgrady
itsgrady
M
  • Question Question
Deleting unuse row before transfering into pdf
Replies
1
Views
285
NoSparks
N
R
  • Solved
Unhide next row in multiple column
2
Replies
12
Views
646
kevin9999
K
K
  • Question Question
Hide/Unhide Rows based on cell values
Replies
2
Views
1K
Alex Blakenburg
A

Forum statistics

Threads
1,224,514
Messages
6,179,220
Members
452,895
Latest member
BILLING GUY

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top