Show/Hide Fields Based on Cell Values Triggered by Button

[hanakon]

Hello,

I'm new to VB programming, and I'm basically learning as I go along while doing some HR projects for a summer job. Right now I'm working on a gas check payout report for a mine, and I'm trying to make a macro where when you click on this button, all rows are hidden where the "total" cell for that row contains "$0" while all other values are left alone. Conversely, when the button is unclicked, all rows should appear regardless of value.

The Total column is AF, and the rows applicable are 9 through 69. Below is what I've managed so far, and it doesn't work, and I have no idea where I went wrong, or if I'm even on the right track. Any help would be much appreciated.

Sub ToggleButton1()
For RowCnt = 9 To 69
If Cells(RowCnt, AF).Value = "$0" Then
Cells(RowCnt, AF).EntireRow.Hidden = True
Else
Cells(RowCnt, AF).EntireRow.Hidden = False
End If
Next RowCnt
End Sub

Thanks!
Cory

 

[VoG]

Try this

Sub ToggleButton1()
Dim RowCnt As Long
For RowCnt = 9 To 69
    Rows(RowCnt).Hidden = Cells(RowCnt, AF).Value = 0
Next RowCnt
End Sub

 

[hanakon]

It looks solid, but when I run it the following row gets highlighted in yellow:

Rows(RowCnt).Hidden = Cells(RowCnt, AF).Value = 0

And I get the following error codes:

"Run-time error '1004': Application-defined or object-defined error"

And when I click "definition" from the context menu of the little yellow arrow pointing to the error line:

"Identifier under cursor is not recognized" (not sure if that one's relevant).

If it helps any to know this, I'm working with Excel 2007 at my workplace. The only other detail I imagine is worth noting is that the "Total" values that are determining whether or not to hide the row are all in currency format with no decimals; I'm not sure if $0 can still be treated as a value = 0 or if it has to be treated like a string, as in value = "$0", to be sure, neither way has worked yet.

At any rate, thanks again for the help thus far. Any other ideas?

 

[VoG]

Try

Sub ToggleButton1()
Dim RowCnt As Long
For RowCnt = 9 To 69
    Rows(RowCnt).Hidden = Cells(RowCnt, "AF").Value = 0
Next RowCnt
End Sub

 

[hanakon]

That's excellent, it worked beautifully, thanks so much! Since you obviously have a much better idea of what's going on here than I do, do you know what I would add to make the button toggle the hiding on/off? Ie, one click hides all 0 fields, the next click shows them again, etc.

 

[VoG]

Try

Sub ToggleButton1()
Dim RowCnt As Long
Static hid As Boolean
hid = Not hid
For RowCnt = 9 To 69
    If hid Then
        Rows(RowCnt).Hidden = Cells(RowCnt, "AF").Value = 0
    Else
        Rows(RowCnt).Hidden = Cells(RowCnt, "AF").Value <> 0
End If
Next RowCnt
End Sub

 

[hanakon]

This script is running fine, and initially behaves the same way, but when I toggle the button a second time to "unhide" the 0 rows, the existing rows are instead replaced with all of the rows that were previously hidden. Ie, in a simplified version of my spreadsheet:

Person1: $60
Person2: $0
Person3: $120
Person4: $0

On first button press becomes:

Person1: $60
Person3: $120

And on second button press becomes:

Person2: $0
Person4: $0

And alternates between those last two states each additional time the button is pressed.

 

[VoG]

Sorry, I misunderstood. Try

Sub ToggleButton1()
Dim RowCnt As Long
Static hid As Boolean
hid = Not hid
For RowCnt = 9 To 69
    If hid Then
        Rows(RowCnt).Hidden = Cells(RowCnt, "AF").Value = 0
    Else
        Rows(RowCnt).Hidden = False
End If
Next RowCnt
End Sub

 

[hanakon]

Not at all, looking back at the post I can see that I wasn't very clear about what I was trying to do. That said, this version of the script works perfectly and is doing exactly what I was hoping for. Thanks again, I doubt I'd have ever figured this out without the help. I'll be sure to pick up a copy of Visual Basic for Dummies in the near future, in case I'm tasked with more macro work like this.

Much obliged,
Cory