Converting VBA code from a Button to a Change Event

[Hainesy]

Hi all,

I have some code attributed to a button currently and I want to change it so that it runs automatically based on the value of a cell.

I believe it needs to be a change event? But I cant seem to get it to work.

Below is the code that I currently have running off a button.

Sub QuestionOne()


Application.ScreenUpdating = False


ActiveSheet.Unprotect "Password"


If [m34] = "Yes" Then
    Rows("35:38").Hidden = False


ElseIf [m34] = "No" Then
    Rows("35:38").Hidden = True
    
    Range("M35:M38").Select
    Selection.ClearContents
    
    Range("M34").Select
    
End If


ActiveSheet.Protect "Password"


Application.ScreenUpdating = True


End Sub

Any help/advice would be much appreciated.

Thank you

 

[FormR]

HI, welcome to the forum!

Is cell M34 being changing manually or does it contain a formula?

 

[Hainesy]

Thanks!

M34 is being changed manually by the user. It contains a 'Yes', 'No' validation list.

 

[FormR]

Hi, in that case you can try this. Right click the sheet in question and choose "View Code" and paste the code in the sheets code module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("M34"), Target) Is Nothing Then
  Me.Unprotect "Password"
  Select Case UCase(Range("M34").Value)
    Case "YES"
      Rows("35:38").Hidden = False
    Case "NO"
      Rows("35:38").Hidden = True
      Application.EnableEvents = False
      Range("M35:M38").ClearContents
      Application.EnableEvents = True
  End Select
  Me.Protect "Password"
End If
End Sub

 

[Hainesy]

Hi, in that case you can try this. Right click the sheet in question and choose "View Code" and paste the code in the sheets code module.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("M34"), Target) Is Nothing Then
  Me.Unprotect "Password"
  Select Case UCase(Range("M34").Value)
    Case "YES"
      Rows("35:38").Hidden = False
    Case "NO"
      Rows("35:38").Hidden = True
      Application.EnableEvents = False
      Range("M35:M38").ClearContents
      Application.EnableEvents = True
  End Select
  Me.Protect "Password"
End If
End Sub

Thank you for coming back to me.

If I use that code in a blank workbook, it seems to work fine. But for some reason it doesn't seem to work in my actual workbook.

Could there be some reason for this?

 

[FormR]

it doesn't seem to work in my actual workbook.

Hi, what does "doesn't seem to work" mean exactly? What actually happens or doesn't happen?

 

[Hainesy]

Apologies, it was my fault! Its now working fine (I had pasted it into the wrong sheet!)

That's exactly what I needed, thank you!

 

[FormR]

Great, glad you got it working