Automatic table detection

[mariatti]

Hi mate,
I'm making a personalized expense spreadsheet, to put salaries, fixed costs and expenses on the credit card. So, each month, I create a copy of the spreadsheet within the same Excel file. The problem is that with each copy the tables change their name and the VBA code needs to keep up with this and I'm not able to do it. I managed to make a script that does exactly what I need, however, the code changes for all columns with the name VALOR.
Would I be able to add a way to detect the table name of a cell in this code and only make changes to that detected table?
If you have any optimizations for the code, I would also be grateful, I'm very new to VBA
Thanks guys

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim tableName As String

    On Error Resume Next
    tableName = Me.ListObjects(Target.ListObject.Name).Name
    On Error GoTo 0

    If tableName <> "" Then
        Set rng = Intersect(Target, Me.ListObjects(tableName).ListColumns("VALOR").DataBodyRange)

        If Not rng Is Nothing Then
            Application.EnableEvents = False
            For Each cell In rng
                If cell.Value = "" Or cell.Value = 0 Or cell.Value = Empty Then 'Se o valor for apagado
                    cell.Offset(0, -1).Value = "[DATA]" ' Adiciona "[DATA]" na coluna "DATA"
                    cell.Value = "[VALOR]" ' Adiciona "[VALOR]" na coluna "VALOR"
                    cell.Offset(0, 1).Value = "[O QUE COMPROU]" ' Adiciona "[O QUE COMPROU]" na coluna "DESCRIÇÃO"
                Else
                    cell.Offset(0, -1).Value = Now ' Insere a data atual
                End If
            Next cell
            Application.EnableEvents = True
        End If
    End If
End Sub

 

[NoSparks]

Yes, you could upload a copy of your file (without personal info) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that can be posted here. Then we would all be working on the same thing.

I use box.com with no issues, it's free for individual use.

 

[mariatti]

Yes, you could upload a copy of your file (without personal info) to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that can be posted here. Then we would all be working on the same thing.

I use box.com with no issues, it's free for individual use.

Follow the spreadsheet link. The worksheets within the workbook are in (day-month) format. for example, the first spreadsheet is 31-09, that is, the 31st of September

Gastos.xlsx | Powered by Box

app.box.com

 

[NoSparks]

This is a no macros xlsx file, where is your VBA ?

 

[mariatti]

This is a no macros xlsx file, where is your VBA ?

sorry bud! the correct file!

Gastos.xlsm | Powered by Box

app.box.com

 

[NoSparks]

I can verify that the WorkSheet_Change code of your original post does what you want and only in the table of that particular sheet.
So what you're experiencing is caused by something else that I don't have or see in this file.

I see dates in cells I45:I47 on each sheet and don't know where they come from.

In the VBA project explorer I see EstaPastaDeTrabalho and the icon differs from that for the sheets.
Google Translate tells me this says "thiswork folder"
but the properties window seems to indicate a Workbook and it would appear to be password protected.

You also have a connection which doesn't happen on my computer and I suspect there would be other VBA dealing with that.
Connection name: - Consulta - DespesasMensais69156
Description: - Conexão com a consulta 'DespesasMensais69156' na pasta de trabalho.
Google Translation: - Connection to query 'Monthly Expenses 69156' in workbook
Connection type: - OLE DB Query
Command type: - SQL
Command text: - SELECT * FROM [DespesasMensais69156]


Sorry I'm not able to solve this for you.
Hopefully someone else can and I will learn something too.

Good Luck
NoSparks

 

[Georgiboy]

Could I get the table name from a specific cell, instead of the active cell? because I would like this script to only work on one table. In this case, in the red table(credit card table) in answer #4.

You can hardcode the left part of the table name to check if the target cell is in the correct table as below:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim tableName As String

    On Error Resume Next
    tableName = Target.ListObject.Name
    On Error GoTo 0

    If Left(tableName, 8) = "GastosCC" Then
        Set rng = Intersect(Target, Me.ListObjects(tableName).ListColumns("VALOR").DataBodyRange)

        If Not rng Is Nothing Then
            Application.EnableEvents = False
            For Each cell In rng
                If cell.Value = "" Or cell.Value = 0 Then 'Se o valor for apagado
                    cell.Offset(0, 1).Value = "[DATA]" ' Adiciona "[DATA]" na coluna "DATA"
                    cell.Value = "[VALOR]" ' Adiciona "[VALOR]" na coluna "VALOR"
                    cell.Offset(0, -1).Value = "[O QUE COMPROU]" ' Adiciona "[O QUE COMPROU]" na coluna "DESCRIÇÃO"
                Else
                    cell.Offset(0, 1).Value = Now ' Insere a data atual
                End If
            Next cell
            Application.EnableEvents = True
        End If
    End If
End Sub

 

[NoSparks]

@Georgiboy

I don't believe you are addressing the problem, the code the OP posted does exactly what he wants and nothing else.

The issue, as I understand it, is that changes are happening in the VALOR column of the similar table on all the other sheets as a result of the changes being made in the correct table. It is not this code that is making those changes.

It doesn't happen in the file provided and the only thing I see missing is the connection to the DespesasMensais69156 database.
My gut feeling is the connection is somehow tied to the Excel tables on the original sheet and is following the copies of the tables, but that is just a guess.

 

[Georgiboy]

@NoSparks

In the OP it was stated:

So, each month, I create a copy of the spreadsheet within the same Excel file. The problem is that with each copy the tables change their name

So the TS is making a copy of a sheet that contains tables and code, when the sheet is copied, Excel adds numbers onto the end of the table names. This was mentioned in post 4.

The original code was working on every table that contained the column name 'VALOR' as can be seen in the OP, below:

script that does exactly what I need, however, the code changes for all columns with the name VALOR

With the quote above, i don't think the TS is stating that all of the tables update together but instead when any table with a column name of 'VALOR' is amended the code fires, this is unwanted.

In post 9 the TS stated:

Could I get the table name from a specific cell, instead of the active cell? because I would like this script to only work on one table. In this case, in the red table(credit card table) in answer #4

For this reason i have offered code that looks at the left part of the table name to make sure the target cell is inside the correct table regardless of the sheet selected.

IMO I have offered a solution that works for the issues the TS has stated.

If you open the file again, go to the leftmost table and change one cell in the VALOR column you will see that the original code fires and places a timestamp outside of the table, this i believe is unwanted, the TS wants the code to only fire in the rightmost table where there is a place for said timestamp.

This is the way i understand the problem.

 

[mariatti]

I can verify that the WorkSheet_Change code of your original post does what you want and only in the table of that particular sheet.
So what you're experiencing is caused by something else that I don't have or see in this file.

I see dates in cells I45:I47 on each sheet and don't know where they come from.

In the VBA project explorer I see EstaPastaDeTrabalho and the icon differs from that for the sheets.
Google Translate tells me this says "thiswork folder"
but the properties window seems to indicate a Workbook and it would appear to be password protected.

You also have a connection which doesn't happen on my computer and I suspect there would be other VBA dealing with that.
Connection name: - Consulta - DespesasMensais69156
Description: - Conexão com a consulta 'DespesasMensais69156' na pasta de trabalho.
Google Translation: - Connection to query 'Monthly Expenses 69156' in workbook
Connection type: - OLE DB Query
Command type: - SQL
Command text: - SELECT * FROM [DespesasMensais69156]


Sorry I'm not able to solve this for you.
Hopefully someone else can and I will learn something too.

Good Luck
NoSparks

about the connections, I don't understand anything... I can't imagine what you're talking about HAHAHA
Now, as for the dates, it was my mistake during an edit, you can ignore them.

You can hardcode the left part of the table name to check if the target cell is in the correct table as below:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim tableName As String

    On Error Resume Next
    tableName = Target.ListObject.Name
    On Error GoTo 0

    If Left(tableName, 8) = "GastosCC" Then
        Set rng = Intersect(Target, Me.ListObjects(tableName).ListColumns("VALOR").DataBodyRange)

        If Not rng Is Nothing Then
            Application.EnableEvents = False
            For Each cell In rng
                If cell.Value = "" Or cell.Value = 0 Then 'Se o valor for apagado
                    cell.Offset(0, 1).Value = "[DATA]" ' Adiciona "[DATA]" na coluna "DATA"
                    cell.Value = "[VALOR]" ' Adiciona "[VALOR]" na coluna "VALOR"
                    cell.Offset(0, -1).Value = "[O QUE COMPROU]" ' Adiciona "[O QUE COMPROU]" na coluna "DESCRIÇÃO"
                Else
                    cell.Offset(0, 1).Value = Now ' Insere a data atual
                End If
            Next cell
            Application.EnableEvents = True
        End If
    End If
End Sub

@Georgiboy

I don't believe you are addressing the problem, the code the OP posted does exactly what he wants and nothing else.

The issue, as I understand it, is that changes are happening in the VALOR column of the similar table on all the other sheets as a result of the changes being made in the correct table. It is not this code that is making those changes.

It doesn't happen in the file provided and the only thing I see missing is the connection to the DespesasMensais69156 database.
My gut feeling is the connection is somehow tied to the Excel tables on the original sheet and is following the copies of the tables, but that is just a guess.

About the code doing what I want...... It really does, but it edits ANY table that has the name VALUE in the column and that is the first problem. I would like it to do this only in the table named "ExpensesCC". The second problem is that with each copy of the spreadsheet, Excel changes the table names automatically, which makes a fixed table name in the VBA code unfeasible, because with each copy I would have to change its name within the code.
What I would like is for the script to detect the name of this table automatically so that changes are made only to it, even though Excel changes the name of the tables in copies of the spreadsheets.

 

[Georgiboy]

In the file you uploaded, i did not see a table named: ExpensesCC
I thought it was named: GastosCC

However you could try the below for the name: ExpensesCC

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim tableName As String

    On Error Resume Next
    tableName = Target.ListObject.Name
    On Error GoTo 0

    If Left(tableName, 10) = "ExpensesCC" Then
        Set rng = Intersect(Target, Me.ListObjects(tableName).ListColumns("VALOR").DataBodyRange)

        If Not rng Is Nothing Then
            Application.EnableEvents = False
            For Each cell In rng
                If cell.Value = "" Or cell.Value = 0 Then 'Se o valor for apagado
                    cell.Offset(0, 1).Value = "[DATA]" ' Adiciona "[DATA]" na coluna "DATA"
                    cell.Value = "[VALOR]" ' Adiciona "[VALOR]" na coluna "VALOR"
                    cell.Offset(0, -1).Value = "[O QUE COMPROU]" ' Adiciona "[O QUE COMPROU]" na coluna "DESCRIÇÃO"
                Else
                    cell.Offset(0, 1).Value = Now ' Insere a data atual
                End If
            Next cell
            Application.EnableEvents = True
        End If
    End If
End Sub