Looping through visible cells for excel to access insert

Question

I have a project I am working on where my team uses an excel front end to manipulate the data which in turn updates an access database back-end to hold the database. (there are good reasons for this)

The current version works by if a user changes data in a cell and wants to update the database they highlight the cell(s) and hit an update button. (this becomes annoying doing multiple updates). So I started playing with the worksheet_changed function.

In order for the worksheet_changed function to work the user has to move off of the 'updated' cell in order for excel to notice the change and update the code. (In my case hitting enter or down arrow after data entry). I have gotten this to work well using the offset property to look at the row above and enter that line into the database - however - when the spreadsheet is filtered as it always is...if the row above happens to be hidden it will update that row when actually I need the visible cell to update....so I am stuck - below is a small chunk of the code used to update the database.

Private Sub Worksheet_Change(ByVal Target As Range)

Refreshbuttons

Dim KeyCells As Range
Dim aCell As Range

Const TARGET_DB = "MKT DB1.accdb"

Dim VErrors(4) As String
VErrors(0) = "Y"
VErrors(1) = "YES"
VErrors(2) = "1"
VErrors(3) = "TRUE"

Dim NVErrors(5) As String
NVErrors(0) = "N"
NVErrors(1) = "NO"
NVErrors(2) = ""
NVErrors(3) = "0"
NVErrors(4) = "FALSE"

Set srch = Range("A4:Z4").Find("PROJECTID", , xlValues, xlWhole)
PRO = Chr(srch.Column + 64)
Set srch = Range("A4:Z4").Find("PROJECTDES", , xlValues, xlWhole)
PD = Chr(srch.Column + 64)
Set srch = Range("A4:Z4").Find("ECAT", , xlValues, xlWhole)
EC = Chr(srch.Column + 64)
Set srch = Range("A4:Z4").Find("SALEMODEL", , xlValues, xlWhole)
SM = Chr(srch.Column + 64)
Set srch = Range("A4:Z4").Find("MKDBROSOURCE", , xlValues, xlWhole)
MDR = Chr(srch.Column + 64)
Set srch = Range("A4:Z4").Find("SOLREVIEWED", , xlValues, xlWhole)
SRD = Chr(srch.Column + 64)
Set srch = Range("A4:Z4").Find("DBSUPPORTEDDUEDATE", , xlValues, xlWhole)
DSDD = Chr(srch.Column + 64)
Set srch = Range("A4:Z4").Find("CATEGORY", , xlValues, xlWhole)
CT = Chr(srch.Column + 64)
Set srch = Range("A4:Z4").Find("COMPLETE", , xlValues, xlWhole)
CMP = Chr(srch.Column + 64)
Set srch = Range("A4:Z4").Find("STYLECOUNT", , xlValues, xlWhole)
SC = Chr(srch.Column + 64)
Set srch = Range("A4:Z4").Find("ECATREADY", , xlValues, xlWhole)
ECR = Chr(srch.Column + 64)
Set srch = Range("A4:Z4").Find("ESTHRS", , xlValues, xlWhole)
EST = Chr(srch.Column + 64)
Set srch = Range("A4:Z4").Find("ACTUALHRS", , xlValues, xlWhole)
AH = Chr(srch.Column + 64)



 Set cnn = New ADODB.Connection
MyConn = ThisWorkbook.Path & Application.PathSeparator & TARGET_DB

With cnn
    .Provider = "Microsoft.ACE.OLEDB.12.0"
    .Open MyConn
End With

projectCount = 0

**For Each C In Selection.Offset(-1,0).Rows
    tmp = C.Address**  // THIS IS WHERE MY ISSUE IS - IT LOOKS TO THE ROW ABOVE AND NOT THE  VISIBLE ROW

    ChangeFields = ""
    ChangeValuesOld = ""
    ChangeValuesNew = ""

If Range("A" & C.Row).EntireRow.Hidden = False Then
        'create the recordset
        Set rst = New ADODB.Recordset
        rst.CursorLocation = adUseServer

        'On Error GoTo Err1:
        strSQL = "SELECT * FROM Projects WHERE Projectid = " & Range(PRO & C.Row).Value & ""

        rst.Open Source:=strSQL, _
                ActiveConnection:=cnn
        If rst.EOF = False Then
            'Start = GetTickCount()

            If rst("Projectid") <> Range(PRO & C.Row).Value Or (IsNull(rst("Projectid")) And Range(PRO & C.Row).Value <> "") Then
                If IsNull(rst("projectid")) Then
                    ChangeValuesOld = ChangeValuesOld & "NULL "
                Else
                    ChangeValuesOld = ChangeValuesOld & rst("projectid") & " "
                End If

                If IsEmpty(Range(PRO & C.Row).Value) Then
                    ChangeValuesNew = ChangeValuesNew & "NULL "
                Else
                    ChangeValuesNew = ChangeValuesNew & Range(PRO & C.Row).Value & " "
                End If

                ChangeFields = ChangeFields & "PROJECTID "

            End If
             If rst("ProjectDes") <> Range(PD & C.Row).Value Or (IsNull(rst("ProjectDes")) And Range(PD & C.Row).Value <> "") Then
                If IsNull(rst("ProjectDes")) Then
                    ChangeValuesOld = ChangeValuesOld & "NULL "
                Else
                    ChangeValuesOld = ChangeValuesOld & rst("ProjectDes") & " "
                End If

                If IsEmpty(Range(PD & C.Row).Value) Then
                    ChangeValuesNew = ChangeValuesNew & "NULL "
                Else
                    ChangeValuesNew = ChangeValuesNew & Range(PD & C.Row).Value & " "
                End If

                ChangeFields = ChangeFields & "ProjectDes "
End If
             If rst("ECAT") <> Range(EC & C.Row).Value Or (IsNull(rst("ECAT")) And Range(EC & C.Row).Value <> "") Then
                If IsNull(rst("ECAT")) Then
                    ChangeValuesOld = ChangeValuesOld & "NULL "
                Else
                    ChangeValuesOld = ChangeValuesOld & rst("ECAT") & " "
                End If

                If IsEmpty(Range(EC & C.Row).Value) Then
                    ChangeValuesNew = ChangeValuesNew & "NULL "
                Else
                    ChangeValuesNew = ChangeValuesNew & Range(EC & C.Row).Value & " "
                End If

                ChangeFields = ChangeFields & "ECAT "

Any help is GREATLY appreciated - thank you

`In order for the worksheet_changed function to work the user has to move off of the 'updated' cell in order for excel to notice the change and update the code.` Why? It is not required. You can use the `Intersect` to check if a particular cell was updated or not... — Siddharth Rout, Oct 06 '14 at 16:53
An example of `Intersect` is `If Not Intersect(target, Rows(2)) Is Nothing Then` This will check if the change happened in row 2. Change the row rumber to the relevant row. Also since you are using `Worksheet_Change`, I would recommend reading [THIS](http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640) link as well. — Siddharth Rout, Oct 06 '14 at 16:59
In this event, the Target is not the current cell, but the cell that was changed. You don't need the offset here. — Mr. Mascaro, Oct 06 '14 at 16:59
Thank you, I appreciate your comments - I was reading of the intersect route however there is the possibility that any cell in columns A:N could be changed in the rows 5-100+ due to new entries being added weekly. So I couldn't have it reference a specific row, rather the row that has a cell selected so it is dynamic. In that case would I just set a variable to handle this? — Ethan V, Oct 06 '14 at 18:28

score 0 · Accepted Answer · answered Oct 06 '14 at 18:55

0

Target.address

this should reference the cell address of the changed cell, so unless you change a hidden cell a hidden cell should not be referenced

if you just need the row you should be able to do Target.Row

answered Oct 06 '14 at 18:55

Tbaker

197
1
1
10

Thanks Tbizzness! This actually led me to the solution. I Changed For Each C In Selection.Offset(-1,0).Rows to For Each C In Target and it works beautifully now. Thanks for all the help! – Ethan V Oct 06 '14 at 19:52

Looping through visible cells for excel to access insert

1 Answers1