Combine two Worksheet_Change in one Makro - VBA

[Nadine1988]

Hello,

I'm really new to the VBA world and I'm currently trying to create a form which can be filled out. I got pretty far already but now I'm stuck. the problem is that I don't know how to combine two makro's under one Worksheet_Change.
So i do have this existing makro which is working perfectly fine:

'Mehrfachselektion mit Löschfunktion

Option Explicit
Private Sub Worksheet_Change(ByVal Destination As Range)
Dim rngDropdown As Range
Dim oldValue As String
Dim newValue As String
Dim DelimiterType As String
DelimiterType = vbCrLf
Dim DelimiterCount As Integer
Dim TargetType As Integer
Dim i As Integer
Dim arr() As String

If Destination.Count > 1 Then Exit Sub
On Error Resume Next

Set rngDropdown = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitError

If rngDropdown Is Nothing Then GoTo exitError
If Destination.Address <> "$C$41" And Destination.Address <> "$D$41" And Destination.Address <> "$C$51" And Destination.Address <> "$D$51" Then GoTo exitError

TargetType = 0
TargetType = Destination.Validation.Type
If TargetType = 3 Then ' is validation type is "list"
Application.ScreenUpdating = False
Application.EnableEvents = False
newValue = Destination.Value
Application.Undo
oldValue = Destination.Value
Destination.Value = newValue
If oldValue <> "" Then
If newValue <> "" Then
If oldValue = newValue Or oldValue = newValue & Replace(DelimiterType, " ", "") Or oldValue = newValue & DelimiterType Then ' leave the value if there is only one in the list
oldValue = Replace(oldValue, DelimiterType, "")
oldValue = Replace(oldValue, Replace(DelimiterType, " ", ""), "")
Destination.Value = oldValue
ElseIf InStr(1, oldValue, DelimiterType & newValue) Then
arr = Split(oldValue, DelimiterType)
If Not IsError(Application.Match(newValue, arr, 0)) = 0 Then
Destination.Value = oldValue & DelimiterType & newValue
Else:
Destination.Value = ""
For i = 0 To UBound(arr)
If arr(i) <> newValue Then
Destination.Value = Destination.Value & arr(i) & DelimiterType
End If
Next i
Destination.Value = Left(Destination.Value, Len(Destination.Value) - Len(DelimiterType))
End If
ElseIf InStr(1, oldValue, newValue & Replace(DelimiterType, " ", "")) Then
oldValue = Replace(oldValue, newValue, "")
Destination.Value = oldValue
Else
Destination.Value = oldValue & DelimiterType & newValue
End If
Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", "") & Replace(DelimiterType, " ", ""), Replace(DelimiterType, " ", "")) ' remove extra commas and spaces
Destination.Value = Replace(Destination.Value, DelimiterType & Replace(DelimiterType, " ", ""), Replace(DelimiterType, " ", ""))
If Destination.Value <> "" Then
If Right(Destination.Value, 2) = DelimiterType Then ' remove delimiter at the end
Destination.Value = Left(Destination.Value, Len(Destination.Value) - 2)
End If
End If
If InStr(1, Destination.Value, DelimiterType) = 1 Then ' remove delimiter as first characters
Destination.Value = Replace(Destination.Value, DelimiterType, "", 1, 1)
End If
If InStr(1, Destination.Value, Replace(DelimiterType, " ", "")) = 1 Then
Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", ""), "", 1, 1)
End If
DelimiterCount = 0
For i = 1 To Len(Destination.Value)
If InStr(i, Destination.Value, Replace(DelimiterType, " ", "")) Then
DelimiterCount = DelimiterCount + 1
End If
Next i
If DelimiterCount = 1 Then ' remove delimiter if last character
Destination.Value = Replace(Destination.Value, DelimiterType, "")
Destination.Value = Replace(Destination.Value, Replace(DelimiterType, " ", ""), "")
End If
End If
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End If

exitError:
Application.EnableEvents = True


End Sub


What I now need is to add a makro which does the following: When "No" is choosen in the dorpdown in cell D43, rows 45 - 55 needs to be hidden. If "Yes" is choosen in the dropdown the rows needs to be shown. I don't know where to add the makro in above code... Looking forward to your help

Thanks
Nadin

 

[Nadine1988]

code is working when sheet is not protected but there's an error message when the sheet is protected ... see above. any suggestions on this?

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Code not working (hide rows) - two change worksheet codes in one
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Herakles]

View attachment 93475

code is working when sheet is not protected but there's an error message when the sheet is protected ... see above. any suggestions on this?

Can you unprotect the sheet before you start to make changes?

 

[Nadine1988]

Problem solved - next one occured

I would like to hide an other row - one which is related to an option field - Yes or No - this field is realted to Cell E13 so I've set up the code just like the others before (In Cell E13 it says 1 if it's Yes and 2 if it's no...

'Hide rows

            
         Cells.EntireRow.Hidden = False
         
        If Range("E13").Value = "1" Then
            Rows("16:17").EntireRow.Hidden = True
        ElseIf Range("E13").Value = "2" Then
            Rows("16:17").EntireRow.Hidden = False
        End If
     
        
        If Range("D43").Value = "No" Then
            Rows("44:52").EntireRow.Hidden = True
        ElseIf Range("D43").Value = "Yes" Then
            Rows("44:52").EntireRow.Hidden = False
        End If
                
        If Range("D55").Value = "No" Then
            Rows("57:65").EntireRow.Hidden = True
        ElseIf Range("D55").Value = "Yes" Then
            Rows("57:65").EntireRow.Hidden = False
        End If

but out of no reason it's not working ...

 

[Herakles]

There is always a reason.

Try this as the value in E13 is a number. I've not tested this.

If Range("E13").Value = 1 Then
            Rows("16:17").EntireRow.Hidden = True
        ElseIf Range("E13").Value = 2 Then
            Rows("16:17").EntireRow.Hidden = False
        End If

 

[Nadine1988]

haha - yes, I guess so

hmm... still not working - the strange thing is that it doesn't do anything when I change the option buttons but when I do change cell D43 to no the rows 16 and 17 disappear.

But I'm currently also checking an option to create a code directly related to the optionbutton...