Helpmij.nl
Helpmij.nl
Helpmij.nl
Steun Helpmij.nl! Klik hier     Computerprobleem? Klik hier!

Quote

Weergeven resultaten 1 tot 4 van 4

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

  • Vraag is opgelost
  1. #1

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

    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

  2. #2
    Rows("79:106").EntireRow.Hidden = True is sowieso een beetje teveel van het goed; dit volstaat:

    Code:
    Rows("79:106").Hidden = True
    VBA voor smarties

    VBA is een taal die je moet leren met een grammatica- en een woordenboek.
    Plaats svp geen bestanden op andere sites; nadat het bestand daar verwijderd is wordt een forumdraad onbegrijpelijk voor anderen. De bijlagen-faciliteit zit niet voor niets in dit forum !

  3. #3
    Bedankt SNB,

    ja inderdaad, deze gaat al tijdje mee bij mij

    Heb deze korte echter ook al geprobeerd maar werkt ook niet

  4. #4
    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

Berichtenregels

  • U mag geen nieuwe vragen starten.
  • U mag niet reageren op berichten.
  • U mag geen bijlagen versturen.
  • U mag uw berichten niet bewerken.
  •  
Helpmij.nl
Helpmij.nl

Helpmij.nl en business

Partners
Sponsoren
Linkpartners
Aanbiedingen