VBA Excel Worksheet_Change Problem

Question

VBA Excel Worksheet_Change Problem

Navigation

#1 by (2 votes)

3

I have a problem when coding with Private Sub Worksheet_Change(ByVal Target As Range)

Because there are so many cells that make changes and that in turn are triggers of each other, that the time has come when excel tells me "the procedure is too large" ,

Can someone help me?

This is a single example of the code but at least 1000 cells activate something, and the condition is that it can not be done in different sheets, everything must happen in it.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
ActiveSheet.DisplayPageBreaks = False

If Target.Address = "$C$3" Then
    If Range("C3") = "Persona Moral" Then
    Range("6:14,293:323,519:536").EntireRow.Hidden = True
    Range("4:5,15:45,144:152,478:494").EntireRow.Hidden = False
    Worksheets("Representantes Legales").Visible = True
    Worksheets("Fideicomisarios-Fideicomitentes").Visible = False
    Else
    If Range("C3") = "Persona Física" Then
    Range("6:10,293:294,297:297,300:323,330:330,338:339,519:532").EntireRow.Hidden = False
    Range("4:5,11:45,144:152,478:494").EntireRow.Hidden = True
    Worksheets("Representantes Legales").Visible = False
    Worksheets("Fideicomisarios-Fideicomitentes").Visible = False
    Else
    If Range("C3") = "Fideicomiso" Then
    Range("11:14,533:534,536:536").EntireRow.Hidden = False
    Range("4:10,15:532,535:535").EntireRow.Hidden = True
    Worksheets("Representantes Legales").Visible = True
    Worksheets("Fideicomisarios-Fideicomitentes").Visible = True
        Else
    If Range("C3") = "" Then
    Rows("4:599").Hidden = True
    Worksheets("Representantes Legales").Visible = False
    Worksheets("Fideicomisarios-Fideicomitentes").Visible = False
    End If
    End If
    End If
    End If
End If

vba excel

asked by Dantek 23.02.2017 в 03:37

source

1 answer

POST request error with JSON with accents in Android Laravel and the pivot tables

score 2 · Accepted Answer

Your procedure is so long that vba can not process it.

what you have to do is reduce the code by removing everything that is repetitive or does not make sense (if it was automatically armed)

as an example:

If Range("C3") = "Persona Moral" Then
    Range("6:14,293:323,519:536").EntireRow.Hidden = True
    Range("4:5,15:45,144:152,478:494").EntireRow.Hidden = False
    Worksheets("Representantes Legales").Visible = True
    Worksheets("Fideicomisarios-Fideicomitentes").Visible = False
    Else
    If Range("C3") = "Persona Física" Then
    Range("6:10,293:294,297:297,300:323,330:330,338:339,519:532").EntireRow.Hidden = False
    Range("4:5,11:45,144:152,478:494").EntireRow.Hidden = True
    Worksheets("Representantes Legales").Visible = False
    Worksheets("Fideicomisarios-Fideicomitentes").Visible = False
    Else

could be translated to something like this:

If Range("C3") = "Persona Moral" Then
   //ocultar cosas
   Ocultar("6:14,293:323,519:536",true,"4:5,15:45,144:152,478:494",false,"Representantes Legales",true,"Fideicomisarios-Fideicomitentes",false)
Else
   If Range("C3") = "Persona Física" Then
      Ocultar("6:10,293:294,297:297,300:323,330:330,338:339,519:532",false,"4:5,11:45,144:152,478:494",true,"Representantes Legales",false,"Fideicomisarios-Fideicomitentes",true)

Sub Ocultar(a,b,c,d, etc...)
   Range(a).EntireRow.Hidden = b
   Range(c).EntireRow.Hidden = d
   ...
end sub

I would also replace the if km with a select case