• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

Andere code voor "Rows("79:106").EntireRow.Hidden = True"

Status
Niet open voor verdere reacties.

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

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
 
Rows("79:106").EntireRow.Hidden = True is sowieso een beetje teveel van het goed; dit volstaat:

Code:
Rows("79:106").Hidden = True
 
Bedankt SNB,

ja inderdaad, deze gaat al tijdje mee bij mij :rolleyes:

Heb deze korte echter ook al geprobeerd maar werkt ook niet
 
Goede morgen gebruikers,

kan iemand me verder helpen? ik heb deze code:

Code:
ActiveSheet.Shapes("Group Box 158").Top = ActiveWorkbook.Names("Roller_conveyor_specification").RefersToRange.Cells(10, 1).Top + 1.5

maar ik zou die group box graag naar een andere sheet verplaatsen en probeerde volgende code:

Code:
ActiveSheet.Shapes("Group Box 158").Top = Sheets("Design Summary Data").Names("Roller_conveyor_specification_Data").RefersToRange.Cells(10, 1).Top 1.5

echter deze werkt niet.

kan iemand me zeggen waar ik verkeerd ben in de tweede code?

alvast bedankt
 
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan