tonissteiner
Gebruiker
- Lid geworden
- 17 sep 2008
- Berichten
- 337
Dag Forum bezoekers,
Ik gebruik in een document onderstaande code. Probleem is dat als ik verborgen rijen weer visible zet ik problemen heb met group boxen die plots niet meer afzonderlijk reageren maar als een geheel. Ik heb het probleem kunnen oplossen zoals in onderstaande code door de rijen niet meer te verbergen (achter de "Else" is deze geblokkeerd)
nu vroeg ik me af of er een andere code, of beter een andere truc is om dit te kunnen oplossen.
alvast bedankt voor jullie moeite
Ik gebruik in een document onderstaande code. Probleem is dat als ik verborgen rijen weer visible zet ik problemen heb met group boxen die plots niet meer afzonderlijk reageren maar als een geheel. Ik heb het probleem kunnen oplossen zoals in onderstaande code door de rijen niet meer te verbergen (achter de "Else" is deze geblokkeerd)
nu vroeg ik me af of er een andere code, of beter een andere truc is om dit te kunnen oplossen.
alvast bedankt voor jullie moeite
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
ActiveSheet.Unprotect
If [F28].Value = "Rn" Then
Rows("79:106").EntireRow.Hidden = False
ActiveSheet.Shapes("Group Box 158").Top = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(10, 1).Top + 1.5
ActiveSheet.Shapes("Group Box 158").Left = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(9, 2).Left + 10
ActiveSheet.Shapes.Range(Array("Group Box 158")).Visible = True
ActiveSheet.Shapes("Option Button 159").Top = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(10, 1).Top + 3.5
ActiveSheet.Shapes("Option Button 159").Left = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(9, 2).Left + 15
ActiveSheet.Shapes.Range(Array("Option Button 159")).Visible = True
ActiveSheet.Shapes("Option Button 160").Top = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(10, 1).Top + 3.5
ActiveSheet.Shapes("Option Button 160").Left = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(9, 2).Left + 65
ActiveSheet.Shapes.Range(Array("Option Button 160")).Visible = True
ActiveSheet.Shapes("Group Box 164").Top = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(18, 1).Top + 2
ActiveSheet.Shapes("Group Box 164").Left = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(17, 2).Left + 10
ActiveSheet.Shapes.Range(Array("Group Box 164")).Visible = True
ActiveSheet.Shapes("Option Button 166").Top = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(18, 1).Top + 3.5
ActiveSheet.Shapes("Option Button 166").Left = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(17, 2).Left + 15
ActiveSheet.Shapes.Range(Array("Option Button 166")).Visible = True
ActiveSheet.Shapes("Option Button 165").Top = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(18, 1).Top + 3.5
ActiveSheet.Shapes("Option Button 165").Left = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(17, 2).Left + 65
ActiveSheet.Shapes.Range(Array("Option Button 165")).Visible = True
ActiveSheet.Shapes("Group Box 171").Top = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(20, 1).Top + 2
ActiveSheet.Shapes("Group Box 171").Left = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(19, 2).Left + 10
ActiveSheet.Shapes.Range(Array("Group Box 171")).Visible = True
ActiveSheet.Shapes("Option Button 173").Top = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(20, 1).Top + 3.5
ActiveSheet.Shapes("Option Button 173").Left = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(19, 2).Left + 15
ActiveSheet.Shapes.Range(Array("Option Button 173")).Visible = True
ActiveSheet.Shapes("Option Button 172").Top = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(20, 1).Top + 3.5
ActiveSheet.Shapes("Option Button 172").Left = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(19, 2).Left + 65
ActiveSheet.Shapes.Range(Array("Option Button 172")).Visible = True
ActiveSheet.Shapes("Group Box 174").Top = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(22, 1).Top + 2
ActiveSheet.Shapes("Group Box 174").Left = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(21, 2).Left + 10
ActiveSheet.Shapes.Range(Array("Group Box 174")).Visible = True
ActiveSheet.Shapes("Option Button 176").Top = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(22, 1).Top + 3.5
ActiveSheet.Shapes("Option Button 176").Left = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(21, 2).Left + 15
ActiveSheet.Shapes.Range(Array("Option Button 176")).Visible = True
ActiveSheet.Shapes("Option Button 175").Top = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(22, 1).Top + 3.5
ActiveSheet.Shapes("Option Button 175").Left = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(21, 2).Left + 65
ActiveSheet.Shapes.Range(Array("Option Button 175")).Visible = True
ActiveSheet.Shapes("Group Box 179").Top = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(24, 1).Top + 2
ActiveSheet.Shapes("Group Box 179").Left = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(23, 2).Left + 10
ActiveSheet.Shapes.Range(Array("Group Box 179")).Visible = True
ActiveSheet.Shapes("Option Button 181").Top = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(24, 1).Top + 3.5
ActiveSheet.Shapes("Option Button 181").Left = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(23, 2).Left + 15
ActiveSheet.Shapes.Range(Array("Option Button 181")).Visible = True
ActiveSheet.Shapes("Option Button 180").Top = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(24, 1).Top + 3.5
ActiveSheet.Shapes("Option Button 180").Left = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(23, 2).Left + 65
ActiveSheet.Shapes.Range(Array("Option Button 180")).Visible = True
ActiveSheet.Shapes("Group Box 184").Top = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(26, 1).Top + 2
ActiveSheet.Shapes("Group Box 184").Left = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(25, 2).Left + 10
ActiveSheet.Shapes.Range(Array("Group Box 184")).Visible = True
ActiveSheet.Shapes("Option Button 183").Top = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(26, 1).Top + 3.5
ActiveSheet.Shapes("Option Button 183").Left = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(25, 2).Left + 15
ActiveSheet.Shapes.Range(Array("Option Button 183")).Visible = True
ActiveSheet.Shapes("Option Button 182").Top = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(26, 1).Top + 3.5
ActiveSheet.Shapes("Option Button 182").Left = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(25, 2).Left + 65
ActiveSheet.Shapes.Range(Array("Option Button 182")).Visible = True
ActiveSheet.Shapes.Range(Array("Option Button 159")).Select
With Selection
.Value = xlOff
.LinkedCell = ""
End With
ActiveSheet.Shapes.Range(Array("Option Button 160")).Select
With Selection
.Value = xlOff
.LinkedCell = ""
End With
ActiveSheet.Shapes.Range(Array("Option Button 166")).Select
With Selection
.Value = xlOff
.LinkedCell = ""
End With
ActiveSheet.Shapes.Range(Array("Option Button 165")).Select
With Selection
.Value = xlOff
.LinkedCell = ""
End With
ActiveSheet.Shapes.Range(Array("Option Button 173")).Select
With Selection
.Value = xlOff
.LinkedCell = ""
End With
ActiveSheet.Shapes.Range(Array("Option Button 172")).Select
With Selection
.Value = xlOff
.LinkedCell = ""
End With
ActiveSheet.Shapes.Range(Array("Option Button 176")).Select
With Selection
.Value = xlOff
.LinkedCell = ""
End With
ActiveSheet.Shapes.Range(Array("Option Button 175")).Select
With Selection
.Value = xlOff
.LinkedCell = ""
End With
ActiveSheet.Shapes.Range(Array("Option Button 181")).Select
With Selection
.Value = xlOff
.LinkedCell = ""
End With
ActiveSheet.Shapes.Range(Array("Option Button 180")).Select
With Selection
.Value = xlOff
.LinkedCell = ""
End With
ActiveSheet.Shapes.Range(Array("Option Button 183")).Select
With Selection
.Value = xlOff
.LinkedCell = ""
End With
ActiveSheet.Shapes.Range(Array("Option Button 182")).Select
With Selection
.Value = xlOff
.LinkedCell = ""
End With
Else:
'Rows("79:106").EntireRow.Hidden = True
ActiveSheet.Shapes.Range(Array("Group Box 158")).Visible = False
ActiveSheet.Shapes.Range(Array("Option Button 159")).Visible = False
ActiveSheet.Shapes.Range(Array("Option Button 160")).Visible = False
ActiveSheet.Shapes.Range(Array("Group Box 164")).Visible = False
ActiveSheet.Shapes.Range(Array("Option Button 165")).Visible = False
ActiveSheet.Shapes.Range(Array("Option Button 166")).Visible = False
ActiveSheet.Shapes.Range(Array("Group Box 171")).Visible = False
ActiveSheet.Shapes.Range(Array("Option Button 172")).Visible = False
ActiveSheet.Shapes.Range(Array("Option Button 173")).Visible = False
ActiveSheet.Shapes.Range(Array("Group Box 174")).Visible = False
ActiveSheet.Shapes.Range(Array("Option Button 175")).Visible = False
ActiveSheet.Shapes.Range(Array("Option Button 176")).Visible = False
ActiveSheet.Shapes.Range(Array("Group Box 179")).Visible = False
ActiveSheet.Shapes.Range(Array("Option Button 180")).Visible = False
ActiveSheet.Shapes.Range(Array("Option Button 181")).Visible = False
ActiveSheet.Shapes.Range(Array("Group Box 184")).Visible = False
ActiveSheet.Shapes.Range(Array("Option Button 182")).Visible = False
ActiveSheet.Shapes.Range(Array("Option Button 183")).Visible = False
End If
If [F28].Value = "Rn" Then
ActiveSheet.Shapes.Range(Array("Option Button 165")).Select
With Selection
.Value = xlOff
.LinkedCell = "E97"
End With
End If
Range("D30").Select
ActiveSheet.Protect
Application.ScreenUpdating = True
End Sub