'This code is for the Worksheet Module ONLY
'In the Sheet Code Module for the sheet you want this to work on paste:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim Rng1 As Range
'Assign the range to work with
Set Rng1 = Range("J1:J100,L1:L100")
'Only work on assigned range
If Intersect(Target, Rng1) Is Nothing Then Exit Sub
'Cancel cell editing that would normally trigger when you double click
Cancel = True
'Call the userform
UserForm1.Show
End Sub
'End worksheet module code
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Application.Intersect(Target, Range("C10:C100")) Is Nothing Then
If Target.Value = "Landelijk" Then
Sheets("Landelijk").Select
ActiveWorkbook.Names("keuze2").Delete
ActiveWorkbook.Names.Add Name:="keuze2", RefersToR1C1:="=Landelijk!R2C28:R2C28"
Sheets("Landelijk").Select
Else
If Target.Value = "Regio" Then
Sheets("Landelijk").Select
ActiveWorkbook.Names("keuze2").Delete
ActiveWorkbook.Names.Add Name:="keuze2", RefersToR1C1:="=Landelijk!R2C29:R6C29"
Sheets("Landelijk").Select
End If
End If
End If
If Not Application.Intersect(Target, Range("D10:D100")) Is Nothing Then
If Target.Value = "Alle Regio's" Then
Sheets("Landelijk").Select
ActiveWorkbook.Names("keuze3").Delete
ActiveWorkbook.Names.Add Name:="keuze3", RefersToR1C1:="=Landelijk!R2C31:R2C31"
Sheets("Landelijk").Select
Else
If Target.Value = "Zuid West" Then
Sheets("Landelijk").Select
ActiveWorkbook.Names("keuze3").Delete
ActiveWorkbook.Names.Add Name:="keuze3", RefersToR1C1:="=Landelijk!R2C32:R15C32"
Sheets("Landelijk").Select
Else
If Target.Value = "Zuid Oost" Then
Sheets("Landelijk").Select
ActiveWorkbook.Names("keuze3").Delete
ActiveWorkbook.Names.Add Name:="keuze3", RefersToR1C1:="=Landelijk!R2C33:R12C33"
Sheets("Landelijk").Select
Else
If Target.Value = "West" Then
Sheets("Landelijk").Select
ActiveWorkbook.Names("keuze3").Delete
ActiveWorkbook.Names.Add Name:="keuze3", RefersToR1C1:="=Landelijk!R2C34:R10C34"
Sheets("Landelijk").Select
Else
If Target.Value = "Noord Oost" Then
Sheets("Landelijk").Select
ActiveWorkbook.Names("keuze3").Delete
ActiveWorkbook.Names.Add Name:="keuze3", RefersToR1C1:="=Landelijk!R2C35:R11C35"
Sheets("Landelijk").Select
End If
If Target.Value = "LVO" Then
Sheets("Landelijk").Select
ActiveWorkbook.Names("keuze3").Delete
ActiveWorkbook.Names.Add Name:="keuze3", RefersToR1C1:="=Landelijk!R2C36:R3C36"
Sheets("Landelijk").Select
End If
End If
End If
End If
End If
End If
If Not Application.Intersect(Target, Range("F10:F100")) Is Nothing Then
If Target.Value = "Bedrijfsapplicatie" Then
Sheets("Landelijk").Select
ActiveWorkbook.Names("keuze5").Delete
ActiveWorkbook.Names.Add Name:="keuze5", RefersToR1C1:="=Landelijk!R2C37:R4C37"
Sheets("Landelijk").Select
Else
If Target.Value = "Infrastructuur" Then
Sheets("Landelijk").Select
ActiveWorkbook.Names("keuze5").Delete
ActiveWorkbook.Names.Add Name:="keuze5", RefersToR1C1:="=Landelijk!R2C38:R4C38"
Sheets("Landelijk").Select
Else
If Target.Value = "Kantoorapplicatie" Then
Sheets("Landelijk").Select
ActiveWorkbook.Names("keuze5").Delete
ActiveWorkbook.Names.Add Name:="keuze5", RefersToR1C1:="=Landelijk!R2C39:R4C39"
Sheets("Landelijk").Select
End If
End If
End If
End If
End Sub
Private Sub CommandButton1_Click()
Dim lRij As Long
Dim c As Range
Dim iWS As Integer
Dim sBedr As String
Dim sKntr As String
sBedr = "7:15"
sKntr = "17:25"
Application.ScreenUpdating = False
For iWS = 2 To Worksheets.Count
Worksheets(iWS).Range(sBedr).ClearContents
Worksheets(iWS).Range(sKntr).ClearContents
Next
For Each c In Sheets(1).[D11:D100]
On Error Resume Next
If Range("F" & c.Row).Value = "Bedrijfsapplicatie" Then lRij = Sheets(c.Value).[A16].End(xlUp).Row + 1
If Range("F" & c.Row).Value = "Kantoorapplicatie" Then lRij = Sheets(c.Value).[A25].End(xlUp).Row + 1
If Range("C" & c.Row).Value = "Landelijk" Then
For iWS = 2 To Worksheets.Count
Range("A" & c.Row & ":Z" & c.Row).Copy Sheets(iWS).Range("A" & lRij)
Next
Else
Range("A" & c.Row & ":Z" & c.Row).Copy Sheets(c.Value).Range("A" & lRij)
End If
Next
Application.ScreenUpdating = True
End Sub