VBA Hide Rows

[MelissaM]

Hi

I used the following code to hide rows based on a cell value:

Private Sub Worksheet_Calculate()
Dim MyResult As String

Application.EnableEvents = False

Rows("11:" & Worksheets("Vouchers").UsedRange.Rows.Count).EntireRow.Hidden = False

MyResult = Worksheets("Vouchers").Cells(11, 3).Value

Select Case MyResult

Case "", "None"
Rows("3:37").EntireRow.Hidden = True

Case Else
Rows("3:37").EntireRow.Hidden = False
End Select

MyResult2 = Worksheets("Vouchers").Cells(65, 3).Value

Select Case MyResult2

Case "", "None"
Rows("58:72").EntireRow.Hidden = True

Case Else
Rows("58:72").EntireRow.Hidden = False
End Select

Application.EnableEvents = True
End Sub

It worked fine when I tested it, but once I closed the workbook and reopened it it seemed to stop working. I thought it might have been because I had protected the worksheet so I unprotected it and re-entered the code but it is still not working. The workbook is a macro enabled workbook and all the cell references have been double checked. Any ideas?

 

[dmt32]

Hi,
your code works ok but will only trigger each time sheet calculates - if this is what is required, check if calculation been set to manual? If though, you expect it to operate when YOU make a cell change then you need a different event.

you should be able to shorten code a little:

Private Sub Worksheet_Calculate()
    Application.EnableEvents = False
    With Worksheets("Vouchers")
        .Rows("11:" & .UsedRange.Rows.Count).EntireRow.Hidden = False
        .Rows("3:37").EntireRow.Hidden = .Cells(11, 3).Value = "None"
        .Rows("58:72").EntireRow.Hidden = .Cells(65, 3).Value = "None"
    End With
    Application.EnableEvents = True
End Sub

Dave

 

[MelissaM]

I have checked the calculation and it is set to Automatic. I have tried pasting in the code above and that didn't seem to want to work for me either. I repasted the original code and that didn't fix the problem.

What I have done is created some travel vouchers for clients to give to suppliers and, depending on what options are on their trip, I want to hide the rows containing the vouchers that are not required as I save them as a PDF and email them out. The information for the vouchers is coming from their confirmation (in a seperate tab), and that information is coming from the bookings spreadsheet via IF statements and VLOOKUP's. When I change the booking reference on the confirmation the trip information pulls through to the confirmation and filters through to the vouchers. If there is no ferry operator or accommodation provider pulled through (cells 11,3 & 65,3) I want to be able to hide the vouchers relating to those services. It was working perfectly yesterday when I set it up but 10 minutes later, after having closed the spreadsheet and logged off the external server, it didn't want to work again.

 

[dmt32]

Hi,
where have you pasted the code?

The code should be placed in your worksheets code page that performs the calculation - Right Click Tab > View Code


Dave

 

[MelissaM]

Hi Dave
Code was pasted in the code page.
Melissa

 

[dmt32]

Then should work

Just a thought, try running ths bit of code & then see if your event works for you.

Sub reset()
Application.EnableEvents = True
End Sub

if still no joy, are you able to place sample copy of your workbook in a dropbox?

Dave