Tabella con condizioni

Hai un problema con Excel?

Nuova discussione

Tabella con condizioni
di Pierof (utente non iscritto) data: 10/07/2012
In alcuni fogli di una cartella è collocato un set tabella a cinque colonne e n righe con dati alfa/numerici che cambia al verificarsi di queste condizioni:
-se in uan qualsiasi cella , del set, successiva alla prima riga di intestazioni si inserisce un dato deve contornarmi il bordo di tutte le celle della riga che ha subito la variazione;
-se nella terza colonna in cui esiste un collegamento a una formula mi compare il valore numerico zero mi deve nascondere l'intera riga;
-per ultimo le righe del set che rimarranno a vista devono formattarsi nella classica tabella a un colore ( bianco + colore );
in attesa di vs. info saluti pierof
di Patel (utente non iscritto) data: 10/07/2012
Fai vedere ad un amico questa descrizione e chiedigli se ci capisce qualcosa

di Harrybosch data: 10/07/2012

da quanto mi sembra di aver capito, ho preparato un codice di questo tipo:

- la tabella (dove opera il codice) va dalla colonna a alla colonna e.
- ogni volta che si compila la tabella, in automatico le righe saranno evidenziate alternativamente
- se nella colonna c il valore è 0, allora tutta la riga viene nascosta (anche l'evidenziazione delle righe viene aggiornata)
- ogni volta che si cambia un qualsiasi valore all'interno della tabella, la relativa riga viene contornata

in ogni caso ti allego anche il file dove ho testato le macro...

se chiedevi una cosa diversa allega un esempio

ciao vanni

da inserire nel FOGLIO dove risiede la tabella:


Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Columns("A:E")) Is Nothing Then
Exit Sub

Else
Application.ScreenUpdating = False

elimina_contorni

r = Mid(Target.Address(0, 0), 2)

On Error Resume Next
  With Range(Cells(r, 1), Cells(r, 5)).Borders
     .LineStyle = xlContinuos
     .ColorIndex = xlAutomatic
  End With
    Target.Offset(1, 0).Select
End If

Nascondi_e_evidenzia_alternate

Application.ScreenUpdating = True
End Sub



da inserire in un modulo:
Sub elimina_contorni()

With Columns("A:E")
    .Borders(xlEdgeBottom).LineStyle = xlNone
    .Borders(xlEdgeLeft).LineStyle = xlNone
    .Borders(xlEdgeRight).LineStyle = xlNone
    .Borders(xlInsideVertical).LineStyle = xlNone
    .Borders(xlInsideHorizontal).LineStyle = xlNone
    .Interior.ColorIndex = 0
End With

End Sub


Sub Nascondi_e_evidenzia_alternate()
Dim r As Long
Dim righe
Dim ur
Dim Colore

Cells.EntireRow.Hidden = False
ur = ""
For r = 2 To Cells(65536, 1).End(xlUp).Row
    
  If Cells(r, 3).Value = "0" Then
    Rows(r).EntireRow.Hidden = True
  Else
    
    righe = Cells(r, 1).Value
    If r > 1 Then ur = Cells(r - 1, 1).Value
   
        If righe <> ur Or r = 1 Then
             If Colore = 6 Then
                Colore = 0
        Else
            Colore = 6
        End If
    
    End If
    
    Range(Cells(r, 1), Cells(r, 5)).Interior.ColorIndex = Colore

End If
Next r

End Sub

Chiarimenti
di Pierof (utente non iscritto) data: 11/07/2012

chiarimenti
obbiettivo 1: la routine deve rispettare questo ordine di condizioni
a)-celle con dati contorno delle celle della riga target;
b)-se il valore della terza colonna è zero mi deve nascondere l'intera riga;
c)-solo alla fine mi deve formattare a colore alterno le righe rimaste a video,quidi escludendo dalla colorazione quelle nascoste

obbiettivo 2 : nella tabella devo poter inseire dati in tutte le celle delle cinque colonne

obbiettivo 3 : se la cella attiva o la cella target corrisponde a quella dell'ultima colonna si può con il successivo invio attivare la cella della riga successiva??

obbiettivo 4: nella tabella con dati inseriti voglio eliminare una riga intermedia di conseguenza mi deve resettare la formattazione delle righe succcessive sempre a colore alternato e celle con contorno marcato

obbiettivo 5: queste condizioni devono essere valide per alcuni fogli a scelta della cartella di lavoro e quindi il codice credo vada inserito nel thiswoorkbook e non in moduli che devo richiamare per formattare la tabella come mi hai postato , se poi sbaglio in qualche passaggio chiariscimi meglio le idee

per ultimo ma non meno importante, almeno per me, neofita di vba se puoi commentare il codice sarei molto contento e trarrei maggiore insegnamento……..grazie pierof
di Harrybosch data: 11/07/2012
Ciao pierof
vedo di commentarti il codice appena ho un attimo di tempo; tra l'altro devo modificare leggermente la macro per l'evidenziazione delle righe perché mi sono accorto di un difetto che produce.

ciao, vanni

di Harrybosch data: 12/07/2012
Ciao pierof
ho avuto un pò di tempo per migliorare e commentarti il codice, che esegue tutti i passaggi richiesti; quindi, riassumendo:
- se nella colonna c il valore è zero nasconde la riga
- quindi evidenzia solo le righe scoperte in modo alternato e viene inserito il bordo alla riga dell'ultima cella modificata
- puoi eliminare qualsiasi riga intermedia e le condizioni saranno sempre valide
- ci devono essere dati in almeno una delle cinque celle della riga
- premendo il tasto invio passi alla cella successiva alla modifica e quando ti trovi sull'ultima, dopo la modifica, passa alla riga successiva
- per quanto riguarda il tuo "obiettivo 5": l'obiettivo del target (ovvero dell'ultima cella modificata), quindi il "private sub worksheet_change" va inserito nel foglio dove hai la tabella (altrimenti non riesci a raggiungerlo); basta copiare questo foglio per averne uno nuovo con le stesse caratteristiche, con le prime 5 colonne che funzionano allo stesso modo. le due macro invece vanno inserite in un modulo perchè possono venir richiamate da qualsiasi foglio (dove abbiamo l'evento target) e sono sempre le stesse. nel thiswoorkook non va nulla, tu stesso dici che le condizioni devono essere valide solo per alcuni fogli (e quindi non per tutti!).

il commento è anche sul foglio che ti allego (digita f8 continuamente per verificare ogni passaggio del codice)
ciao, vanni

Private Sub Worksheet_Change(ByVal Target As Range)

'se non intercetto le colonne a:f allora non fare nulla...
If Intersect(Target, Columns("A:E")) Is Nothing Then
Exit Sub
'altrimenti...
Else
'con questa apertura evito di far "scrollare" il video
Application.ScreenUpdating = False
'lancio la macro per pulire tutti i contorni precedenti
elimina_contorni

'dall'ultima cella modificata mi ricavo la riga e la colonna
r = Mid(Target.Address(0, 0), 2)
C = Mid(Target.Address(0, 0), 1, 1)

On Error Resume Next
'dispongo il contorno dalla prima alla quinta cella della riga sopra trovata
  With Range(Cells(r, 1), Cells(r, 5)).Borders
     .LineStyle = xlContinuos
     .ColorIndex = xlAutomatic
  End With

End If
'lancio la macro per evidenziare le righe
Nascondi_e_evidenzia_alternate

'quindi mi sposto con il cursore: se l'utlima cella modificata era nella
'colonna E allora seleziono la prima riga successiva, altrimenti la cella dopo
If C = "E" Then
Target.Offset(1, -4).Select
Else
Target.Offset(0, 1).Select
End If

Application.ScreenUpdating = True
End Sub




Sub elimina_contorni()
'dalla colonna A alla E elimino tutti i contorni (sopra, sotto, ecc...)
With Columns("A:E")
    .Borders(xlEdgeBottom).LineStyle = xlNone
    .Borders(xlEdgeLeft).LineStyle = xlNone
    .Borders(xlEdgeRight).LineStyle = xlNone
    .Borders(xlInsideVertical).LineStyle = xlNone
    .Borders(xlInsideHorizontal).LineStyle = xlNone
    .Interior.ColorIndex = 0
End With

End Sub


Sub Nascondi_e_evidenzia_alternate()
Dim x As Integer
Dim Rng As Range
'con Rng determino la tabella (dinamica) dei dati...
Cells(1, 1).Select
Set Rng = ActiveCell.CurrentRegion
'... e prima di evidenziare cancello le evidenziature precedenti (=0)
Rng.Offset(1, 0).Resize(Rng.Rows.Count - 1, Rng.Columns.Count).Interior.ColorIndex = 0
'determino il numero dell'ultima riga occupata
fine = Rng.Rows.Count

'prima del ciclo assegno 1 a una variabile
x = 1
'parto dalla seconda riga (salto le intestazioni)
For r = 2 To fine
'se nella colonna C il valore è zero...
    If Cells(r, 3).Value = "0" Then
    '...allora nascondo la colonna
       Rows(r).EntireRow.Hidden = True
       'e non la considero nel ciclo
    End If
    
        'se la riga non è nascosta allora proseguo il ciclo
            If Rows(r).Hidden = False Then
                'se la mia variabile è pari assumo il colore giallo ( =6 )
                If x Mod 2 = 0 Then
                    Colore = 6
                'altrimenti (quindi numero dispari)
                Else
                'assumo il colore bianco ( =2 )
                    Colore = 2
                End If
     'finite le condizioni aggiungo 1 alla mia varibile
     x = x + 1
     'assegno il colore appena individuato alla riga
    Range(Cells(r, 1), Cells(r, 5)).Interior.ColorIndex = Colore
    End If
    
Next r

End Sub

Chiarimenti del 12-7-12
di Pierof. (utente non iscritto) data: 12/07/2012
Vanni grazie per l'aiuto che mi stai dando che mi ha aperto la mente su diverse istruzioni del codice sempre se possibile vorrei che mi adattassi il codice alle seguenti condizioni:

1)-tutte le righe del set che hanno anche una sola cella diversa da vuota devono avere e rimanere con il contorno marcato e l'evidenzazione delle righe anche quando si attiva la macro o si chiude/riapre la cartella di lavoro;

2)-se la cella dell'ultima colonna non è quella target ma anche solo quella attiva, con la successiva pressione del tasto invio deve passarmi alla prima cella della riga successiva;

poi ho visto uno strano comportamento di evidenzazione delle due righe succcessive alla riga in cui sto inserendo i dati....è capitato solo a me ???.....se hai modo testa su più righe il codice......grazie di tutto aspetto con ansia le tue rettifiche...pierof.
di Harrybosch data: 13/07/2012
Ciao pierof
per quanto riguarda il primo punto che hai chiesto, si desume che la riga potrebbe anche restare vuota; questo non va bene per il codice che ti ho scritto, in quanto la tabella di riferimento viene adottata tramite il metodo rng che presuppone di comprendere una rettangolo dove le celle "occupate" devono essere contigue. pertanto, se salti una riga, o appunto rimane vuota, salta anche la tabella e la successiva evidenziazione delle righe.
bisognerebbe cambiare il metodo di ricerca: invece dell'used range, fare semplicemente un ciclo for da 2 all'ultima riga: ma in questo caso per trovare l'ultima riga occupata bisognerebbe fare prima un controllo su ogni colonna visto che tutte le celle possono restare libere!
oppure consideri come ultima riga un numero elevato, tipo for i = 2 to 5000, con l'inconveniente che in questo caso ti evidenzia tutte le righe fino a quel punto, a patto di non inserire una condizione che blocchi il ciclo se si verifica (ma ciò risulta difficile se dici che ogni cella può restare vuota!).
pertanto meglio la prima strada, dove analizzi le cinque colonne e l'ultima riga sarà quella relativa all'ultimo dato trovato; magari questa volta ti consiglio di provare a scrivere il codice in questi termini ed eventualmente te lo sistemo se non funziona; l'input l'hai appena ricevuto.

il secondo punto mi sembra che esuli un pò dal reale meccanismo del vba: vorresti andare a capo semplicemente con la pressione del tasto invio, senza che ci sia una condizione specifica o un evento particolare. ok, ti trovi sull'ultima cella della tua tabella, ma non ci sono "aggangi" per fargli dire di andare a capo... mi sembra che stai chiedendo una funzione del tipo"ordine spostamento" come quella che ha integrato access nelle proprietà di ogni casella, dove puoi indicargli esattamente le casella dove spostarsi di volta in volta. capisco andare a capo dopo la modifica dell'ultima cella attiva della riga (e così infatti era il codice che ti ho scritto), ma così mi risulta difficile e, sinceramente, nemmeno di molta utilità su foglio excel dove non hai delle celle fisse (come nelle maschere di access) ma un foglio tabellare compilabile in ogni sua parte...

lo "strano comportamento" probabilmente è dovuto proprio al fatto che hai saltato delle righe; quindi il codice continuava nel segnare i bordi (in quanto l'evento target si basava sulle cinque colonne per intero) mentre smetteva di evidenziare e di nascondere le righe con lo 0 nella terza colonna (perchè appunto la macro è legata all'usedrange)

fammi sapere,
ciao vanni
Chiarimenti del 13-7-12
di Pierof. (utente non iscritto) data: 13/07/2012

ciaovanni,
prendendo spunto dalle tue considerazioni è fattibile la seguente soluzione

-nelle righe della tabella se inserirò un dato nella prima,seconda,... cella il codice interverrà ad applicarmi il contorno marcato a tutte le cinque celle della riga ed eventualmente anche la sua colorazione a righe alterne , condizone che deve permanere finquando nella riga esista un dato, magari si potrebbe inserire un msgbox di avviso se nella riga non ci sono dati ;

quindi domanda , non si potrebbe legare l'evento di marcatura del bordo dell'ultima cella della riga per attivare il ritorno a capo con la successiva pressione del tasto invio , quando la cella attiva corrisponderà all'ultima cella della riga??

inoltre dammi delle delucidazioni sulle successive istruzioni del codice:

-perchè se ho già lavorato sulla tabella e quindi ci sono con dati con le relative formattazioni impostate dalla routine.....cmq usi la macro ""elimina_contorni"" ;

-ma quando e in quale posizione và inserita nella routine l'istruzione ""on error resume next""

p.s. per quanto riguarda l'input a scrivere in autonomia del codice ci provo spesso e all'occorenza voglio postare una routine che lavora su una tabella dati che ha bisogno di qualche ritocco nel codice .......e non sò se va bene postarla in questa sezione del forum.

grazie come sempre per le preziose infovanni;...pierof.
di Pierof. (utente non iscritto) data: 13/07/2012
Ciao vanni un dettaglio importante che avevo menzionato nel mio primo report riguarda la terza colonna della tabella le cui celle contengono un collegamento posto in un altro foglio che riporta il risultato di una formula. quessto valore numerico è variabile nel tempo se è uguale a zero la routine deve nascondere la riga e viceversa scoprirla se torna ad essre diverso da zero, ma sembra che la routine così impostata non effettui in automatico la variazione, ma ha bisogno del target di una cella della tabella per attivarsi ...o sbaglio

domanda non è possibile collegare la macro "nascondi_e_evidenzia_alternate" a qualche evento tipo cambio ora del sistema o al worksheet_selectionchange per attivarla in automatico??
di Harrybosch data: 31/07/2012
Ciao pierof.
ho trovato un pò di tempo per continuare la discussione.
intanto un suggerimento: ti assicuro che puoi, ma direi meglio devi, postare il tuo codice quando apri una discussione; il motivo è semplice: invece di trovarti un codice "nuovo", dove magari è più difficile seguirne tutto il ragionamento, viene corretto il tuo aiutandoti a capire l'eventuale errore. oltre che a "facilitare" il compito a chi cerca di aiutarti a risolvere la questione, è un'ottima forma di apprendimento.

per quanto riguarda le delucidazioni che chiedevi:
-nelle righe della tabella se inserirò un dato nella prima,seconda,... cella il codice interverrà ad applicarmi il contorno marcato a tutte le cinque celle della riga ed eventualmente anche la sua colorazione a righe alterne, condizione che deve permanere finquando nella riga esista un dato, magari si potrebbe inserire un msgbox di avviso se nella riga non ci sono dati

finchè ci sono dati sulla riga il codice esegue già questa operazione; se salti la riga vorresti un messaggio che ti avvisi ma mi sembra più una operazione da form. servirebbe un ciclo per sapere quali sono le righe vuote; ma così il messaggio apparirebbe ogni volta...

-quindi domanda, non si potrebbe legare l'evento di marcatura del bordo dell'ultima cella della riga per attivare il ritorno a capo con la successiva pressione del tasto invio , quando la cella attiva corrisponderà all'ultima cella della riga??

il bordo si attiva su tutte le celle della riga quindi non riguarda solo l'ultima cella; con l'evento change già hai il codice che ti porta a capo se modifichi l'ultima cella (la quinta) della riga

-perchè se ho già lavorato sulla tabella e quindi ci sono con dati con le relative formattazioni impostate dalla routine.....cmq usi la macro ""elimina_contorni"" ;

uso la macro "elimina_contorni" perchè altrimenti resterebbero i contorni sulla riga precedentemente modificata; prima di bordare la riga attiva bisogna "pulire" il precedente passaggio.

-ma quando e in quale posizione và inserita nella routine l'istruzione ""on error resume next""

questa istruzione serve per far "saltare" il codice alla istruzione successiva nel caso di verificasse un errore, evitando così il debug (per esempio l'errore ci sarebbe nel caso si tentasse di cancellare l'intera tabella)

- infine, per quanto riguarda l'ultima questione che hai posto:
se nella terza colonna il valore è legato al risultato di una formula, quando diventa 0 in effettti la ruotine non viene eseguita in automatico se non si interviene direttamente sulla cella. quindi si, si potrebbe legare la macro al cambio ora o meglio ancora farla eseguire ogni tot tempo; se ti interessa questa soluzione potresti aprire una nuova discussione, visto che la richiesta iniziale di colorazione delle righe in modo alternato e di bordatura mi sembra risolto, e visto che la cosa potrebbe interessare anche ad altri utenti. inoltre, oltre alla mia, potresti trovare altre risposte interessanti. quindi potresti applicare successivamente le soluzioni al tuo caso specifico.

ciao, vanni
di Pierof. (utente non iscritto) data: 03/08/2012
Grazie,vanni per le ultime e delucidazioni e scusatemi se rispondo solo adesso, per il poco tempo che ho avuto.
sicuramente questo thread per ora va bene così ma quando avrò un p'ò più di tempo posterò il codice per apporre qualche sicura correzione e inserire nuove istruzioni che ho in mente di eseguire nella tabella......a presto saluti a tutti pierof.

di Vecchio Frac data: 07/08/2012 11:11:18
Mi sono divertito anch'io con questo thread.
Ecco il mio contributo... da incollare nel codice di un foglio in cui da A1 a E1 si trovano le cinque colonne di intestazione della costruenda tabella dati.
Credo di aver rispettato tutte le condizioni di piero e di aver implementato qualche meccanismo interessante anche se si potrebbe limare ancora :)

Option Explicit

Private previous_row As Integer
'

Private Sub Worksheet_Change(ByVal Target As Range)
Static previous_row
Dim activeRange As Range

    If Target.Column > 5 Then Exit Sub
    
    If Target.Row <> previous_row And previous_row <> 0 Then
        Rows(previous_row).Borders.LineStyle = xlNone
    End If
    
    If Not (Intersect(Target, [a:e]) Is Nothing) Then
        If Target.Row = 1 Then Exit Sub
       
        Set activeRange = Range("A" & Target.Row, "E" & Target.Row)
        
        'Call set_borders:
        'per evidenziare le cinque celle della riga modificata ognuna con
        'il suo bordino di cella, passare il parametro "single cell"
        'per incorniciare la riga di tabella modificata con il solo bordo
        'esterno, passare il parametro "entire range"
        'per togliere qualunque bordino, non passare alcun parametro
        Call set_borders(used_range)
        Call set_borders(activeRange, "single cell")
    
    End If
    
    previous_row = Target.Row
    
    If WorksheetFunction.CountBlank(activeRange) = 5 Then
        Call set_borders(activeRange, "none")
    End If
    
    Application.ScreenUpdating = False
    
    Call hide_rows(used_range)
    
    Call alternate_rows(used_range)
    
    If Target.Rows.Hidden Then
        Target.Offset(1, 0).Select
    Else
        Target.Select
    End If
    
    If Target.Column = 5 And Target <> "" Then
        Target.Offset(1, -4).Select
    End If

    Application.ScreenUpdating = True

End Sub


Private Sub set_borders(rng As Range, Optional mode As String)
Dim myBorders() As Variant, item As Variant
    
    Application.ScreenUpdating = False
    
    rng.EntireRow.Borders.LineStyle = xlNone
    
    Select Case LCase(mode)
    Case "single cell"
        rng.Borders.LineStyle = xlContinuous
        rng.Borders.Weight = xlMedium
        
    Case "entire range"
         myBorders = Array(xlEdgeLeft, xlEdgeTop, xlEdgeBottom, xlEdgeRight)
        
        For Each item In myBorders
            With rng.Borders(item)
                .LineStyle = xlContinuous
                .Weight = xlMedium
            End With
        Next
    End Select
    
    Application.ScreenUpdating = True
    
End Sub


Private Sub alternate_rows(rng As Range)
Const GIALLINO = 36, VERDINO = 35
Dim riga As Range, c As Integer, previous_color As Integer
    
    Application.ScreenUpdating = False
    
    'coloro alternativamente di giallino (36) e verdino (35) a partire dalla seconda riga del range,
    'evitando le righe già nascoste
    previous_color = VERDINO
    For Each riga In rng.Rows
        riga.Interior.ColorIndex = IIf(previous_color = VERDINO, GIALLINO, VERDINO)
        previous_color = riga.Interior.ColorIndex
        If riga.Rows.Hidden Then previous_color = IIf(riga.Interior.ColorIndex = VERDINO, GIALLINO, VERDINO)
    Next
    
    Application.ScreenUpdating = True

End Sub

Private Sub hide_rows(rng As Range)
Dim riga As Range

    Application.ScreenUpdating = False
    
    'nascondo le righe in cui la colonna C è zero o vuota
    For Each riga In rng.Rows
        riga.EntireRow.Hidden = (riga.Columns(3) = 0) Or (riga.Columns(3) = "") Or (WorksheetFunction.CountBlank(riga) = 5)
    Next
    
    Application.ScreenUpdating = True

End Sub


Private Function used_range() As Range
Dim ac As Range, r As Long
    
    Application.ScreenUpdating = False
    
    'determina l'ultima riga contenente almeno un dato
    r = 1
    For Each ac In [a65536:e65536]
        ac.End(xlUp).Select: If ActiveCell.Row > r Then r = ActiveCell.Row
    Next
    
    If r < 2 Then r = 2
    Set used_range = Range("A2:E" & r)
    
    Application.ScreenUpdating = True
    
End Function

Vuoi Approfondire?