• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hide cells wih VBA

B

begcar

New Member
Hello, I need your help. I am new in VBA, but I need to create one.
I have a workbook, to create a letter for the employees, I have 2 sheets in one of them I made a Master Data, where we have to put if the employee is elegible or not for some benefits the answer could be Yes or No. In the other spreadsheet is the letter, in Cell F1, they have to put the numer of their case (this number is in the master data sheet), so they just have to copy and paste this number. Depending on the number the benefits will be display (If they put Yes in the master data, the information about this benefit will be displayed in the letter sheet; if they put NO in the master data, in the letter sheet the cell where the information of this benefit will be displayed: FALSE. What I need is these cells that has False should be hidden, but if another employee use this workbook and put another case number in cell "F1", all the information should be displayed again and only the False should be hidden; these cells can be totally different freom the previous case, so all the benefits hsould be displayed and just hide the False in all the cases. The range of the cells that can contain the "False" are from B40:K80. Thanks so much!!
 
Hi, begcar!
Why not changing those formulas to?
=IF(<condition>,"all the benefit information","")
And eventually you could add conditional formatting conditions based on these cell contents (testing against "") to control fonts, colors or other things.
Regards!
 
Thanks SirJB7,
What I need is to hide them in order to do not show those cells, so the letter will only contain, the required fields
Thanks again!
 
What I have now is:
Code: 
Private Sub Worksheet_Calculate()
With Range("B40:K80")
.EntireRow.Hidden = False
On Error Resume Next
.SpecialCells(xlFormulas, 4).EntireRow.Hidden = True
End With
End Sub
But twith this all the rows are hidden, if I change the number in F1, the rows does not appear again, regardless they do not have a Fail.
 
Is it possible to put osmehting like: If F1, change unhide from B40 to K80, and hidden just if is False?
 
Hi ,

I am not so sure I have understood your requirement , but try this :
Code: 
Private Sub Worksheet_Calculate()
            For Each cell In Range("b40:B80")
                cell.EntireRow.Hidden = (cell.Value = False)
            Next
End Sub
Narayan
 
Thanks Narayan, that will hide the cells, but manually have to be unhidded again, and manually run the macro again, is it possible to unhide them, as soon as they change the cell F1?
 
The Worksheet_Calculate macro needs to be in the Worksheet module, not a regular Module. Otherwise it won't get triggered.
begcar said:
What I have now is:
Code: 
Private Sub Worksheet_Calculate()
With Range("B40:K80")
.EntireRow.Hidden = False
On Error Resume Next
.SpecialCells(xlFormulas, 4).EntireRow.Hidden = True
End With
End Sub
But twith this all the rows are hidden, if I change the number in F1, the rows does not appear again, regardless they do not have a Fail.
Click to expand...
 
Can ypu let me know how can I modify this? and how can I put on this: If F1 change unhide the cells and just hide the ones that has a False?
 
Hi ,

No issues ; the only change I have made is to use the Worksheet_Change event procedure instead of the Worksheet_Calculate event procedure ; you can find this procedure in the Sheet1 section.

When ever a change is made to cell F1 , or any other cell in the worksheet for that matter , this event is triggered ; within the code , we are checking to see whether the change is in F1 or any other cell ; if it is in any other cell , we don't do anything ; if it is in F1 , then the Hide / Unhide rows action is taken.

Narayan
 
You must log in or register to reply here.
Back
Top