Solved

Excel 2003: Simple VBA hide row on cell value

Posted on 2010-09-16
15
321 Views
Last Modified: 2012-05-10
I have been trying for 2 hours to get a row to be hidden based on the content of a cell.  I hve tried a dozen of the solutions on the forum to no avail.  

What am I missing?

this is my code

Sub HideandUnhide()
If Cells(D, 71) = "*Requires 2 signatures*" Then
    Rows(75).EntireRow.Hidden = False
Else
    Rows(75).EntireRow.Hidden = True
End If
End Sub
0
Comment
Question by:YDagenais
  • 6
  • 5
  • 4
15 Comments
 
LVL 12

Expert Comment

by:telyni19
ID: 33695170
Two things:
1. The Cells function requires two numbers as inputs, so I think you'd want Cells(4,71) rather than using the letter D.
2. You're unhiding row 75 if the text is as indicated. Did you intend to hide it instead? If so, swap False and True.
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33695205
Also the worksheet change event handler is triggered when anything changes so could be used to trigger your activity whenever a change occurs to the worksheet.

It can be simpplified and tidied up depending on if the functionality applies to more than one row.

Chris
Private Sub Worksheet_Change(ByVal Target As Range)

    If Range("D" & 71) = "*Requires 2 signatures*" Then

        Rows(75).EntireRow.Hidden = False

    Else

        Rows(75).EntireRow.Hidden = True

    End If



End Sub

Open in new window

0
 

Author Comment

by:YDagenais
ID: 33695243
I have tried both suggestions and the row 75 remains visible .  As additional information, the D71 cell is the result of a formula:=IF(data!C57="Yes","*Requires 2 signatures*"," ").  

Sub HideandUnhide()
If Cells(4, 71) = "*Requires 2 signatures*" Then
    Rows(75).EntireRow.Hidden = True
Else
    Rows(75).EntireRow.Hidden = False
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("D" & 71) = "*Requires 2 signatures*" Then
        Rows(75).EntireRow.Hidden = False
    Else
        Rows(75).EntireRow.Hidden = True
    End If

End Sub

0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33695284
It should work fine in the change event handler.

Did you place it in the specific worksheets code page?

Chris
0
 
LVL 12

Accepted Solution

by:
telyni19 earned 500 total points
ID: 33695288
Sorry, about that, the Cells function is switched relative to the Range function. If you're using Cells, you'd want Cells(71, 4) for cell D71 - or Range("D71").Value.

And the Worksheet_Change example didn't switch the False and True values. So you'd want either of these:

Sub HideandUnhide()
If Cells(71, 4) = "*Requires 2 signatures*" Then
    Rows(75).EntireRow.Hidden = True
Else
    Rows(75).EntireRow.Hidden = False
End If
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("D" & 71) = "*Requires 2 signatures*" Then
        Rows(75).EntireRow.Hidden = True
    Else
        Rows(75).EntireRow.Hidden = False
    End If
End Sub
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33695381
telyni19:

Swapping true and false is easy enough but is not necessarily appropriate as we do not know if it is to be hidden until the condition is met or hidden if the condition is met.

The key comment was "row 75 remains visible" which implies the status does not change and therefore the function isn't working at all.

Chris
0
 
LVL 12

Expert Comment

by:telyni19
ID: 33695453
Chris:
I'll let the author of the question tell me whether the cell should be hidden if the condition is met or not. (You're right, that hasn't actually been specified, which would be useful information.) However, aside from that, your function looks as if it should work, so the first thing I would think to try, given the information that it does not hide the row when it should, is to switch the values so that it should hide the row when the condition is met rather than the reverse. If that also does not do what the author intended, then there is a more subtle problem, and we really need to clarify what the function should be doing to avoid confusion.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:YDagenais
ID: 33695454
Ok I tried several versions.  D71 is =IF(data!C57="Yes","*Requires 2 signatures*"," ")
So if D71 = " " then it should hide row 75, else do not hide.
The code is in the appropriate worksheet change

Private Sub Worksheet_Change(ByVal Target As Range)
    If Cells(71, 4) = " " Then
        Rows(75).EntireRow.Hidden = True
    Else
        Rows(75).EntireRow.Hidden = False
    End If
End Sub
0
 
LVL 12

Expert Comment

by:telyni19
ID: 33695593
Is the code running at all? Can you put a breakpoint on the If statement and make sure the code is getting that far? Then check what it thinks the value of Cells(71,4) is. Spaces are hard to track, so you could try "" in the IF function in D71 instead and then use this line for the If statement instead:
If Len(Cells(71, 4)) = 0
0
 

Author Comment

by:YDagenais
ID: 33695662
I changed D71  to show "" and changed the code to If Len(Cells(71, 4)) = 0

I put breaks on all the lines and now the code works but only if the code is ran manually.  

Is it possible that the code runs every time D71 is changed?
0
 
LVL 12

Expert Comment

by:telyni19
ID: 33695712
Yes, the code will run every time any cell on the worksheet is changed. The Worksheet_Change Sub is automatically triggered. If you don't want that and simply want to trigger it manually or on a button click or something, then you'd want to use a regular named Sub, as you had originally.
0
 

Author Comment

by:YDagenais
ID: 33695852
It does not work unless the code is ran manually.  D71 cell is changed and the row does not unhide.

I have tried these variations.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Len(Cells(71, 4)) = 0 Then
        Rows(75).EntireRow.Hidden = True
    Else
        Rows(75).EntireRow.Hidden = False
    End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    If Len(Range("D" & 71) = 0 Then
        Rows(75).EntireRow.Hidden = True
    Else
        Rows(75).EntireRow.Hidden = False
    End If

End Sub
0
 
LVL 59

Expert Comment

by:Chris Bottomley
ID: 33695869
>>>I put breaks on all the lines and now the code works but only if the code is ran manually.  
It wouldn't do so if defined as the change handler since the parameter Target  wouldn't allow it to.

Can you upload a sample workbook ... delete all the data to make it secure.

Chris
0
 

Author Comment

by:YDagenais
ID: 33695914
Thank you Chris but our internal policy and firewalls prevents us from posting or exporting files.

I will tell the user to hide the row manyally, we have spent enough time on this.

Thank you .
0
 

Author Closing Comment

by:YDagenais
ID: 33695924
The solution should work and the code is accurate however i am unable to make it work due to internal policies.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now