At wits end - please help

[Max1978]

Sorry while I'm fairly good with excel formulas my VBA is rubbish. I am only capable of recording macros and that's it.

I have what I know will be a very simple macro for someone on this forum - but despite my best efforts and hours (litterally) of searching I am unable to get the code right. There's a lot out there, but I can't find anything that matches my scenario and my VBA understanding isn't sufficient enough to edit it to work.

Essentially I want to unhide rows 25 to 52 automatically in one sheet called "Telehealth Model" when a calculated cell value "S23" in the sheet equals 0; and re-hide the same rows if the cell "S23" calculates to equal 1.

The cell S23 contains an "if" formula based on a user selection from a drop-down validation list. I thought using the an "if" in a reference cell (S23) formula would be an easier way to write a macro to unhide rows rather than trying to incorporate a user selection if statements into VBA. Yet, I have still failed.

Please help... I would really appreciate it.

Cheers

Max

 

[L. Howard]

Hi Max,

Try a change event macro. Copy to sheet module.

Responds to 1 or 0 in cell S23 to hide or un-hide.

Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target <> Range("S23") Then Exit Sub

Select Case Range("S23").Value

   Case Is = 1
      Rows("25:52").EntireRow.Hidden = True
      
   Case Is = 0
     Rows("25:52").EntireRow.Hidden = False

   Case Else
     '
End Select

End Sub

 

[Max1978]

Thanks Howard.

I copied as suggested, but nothing happens. I make a selection in the validation list, which in turn updates the value in S23 with no effect. During my search for the right code previously, someone else mentioned that their macro wasn't working as it had a formula in the reference cell... Could this be why it's not working. The workbook is defnitely macro enabled.

Thanks
Max

 

[Max1978]

Apologies - this does work but only if I click on the cell S23 and hit enter. I was hoping the selection for the validation list (which in turn updates the result in s23) would automatically trigger the unhiding of the cells.

 

[pvr928]

Try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

instead

cheers

pvr928

 

[Max1978]

Thanks pvr928 - it works... FINALLY after all these hours of seraching. You guys are awesome!

 

[pvr928]

No worries - glad to help - but L. Howard deserves most of the credit.

Cheers

pvr928

 

[L. Howard]

Apologies - this does work but only if I click on the cell S23 and hit enter. I was hoping the selection for the validation list (which in turn updates the result in s23) would automatically trigger the unhiding of the cells.

Hi Max,

You are correct about the drop down not firing the change if it is relying on a formula. I was using a formula in S23 and changing the formula result by a key stroke entry in another cell the formula was referring to.

Here is an example of a drop down/formula work-around where the drop down alone will fire the change event macro. You do not need to select S23 and hit enter.

https://www.dropbox.com/s/vpy2c774dl92c7w/Change_Event by FORMULA Drop Box.xlsm?dl=0

Howard


With this Change_Event code in the sheet module.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target <> Range("S20") Then Exit Sub '/ Cell the drop down is in

    Dim updatedCell As Range
    Set updatedCell = Range(Target.Dependents.Address)
   
 If Not Intersect(updatedCell, Range("S23")) Is Nothing Then
       
    Select Case Range("S23").Value  '/ Cell the formula is in
    
      Case Is = 1
          Rows("25:52").EntireRow.Hidden = True
      
      Case Is = 0
          Rows("25:52").EntireRow.Hidden = False
      
      Case Else
     '
    End Select
       
 End If

End Sub