Hi All,
I am looking for some sample VB Macro Code I could apply to an Update form I am trying to build. I apologize if I over extend my explanations.
A Basic example of what I have so far is enclosed.
The process considered is:
- A User enters data in the Data Entry Form (sheet=NewEntry) and updates an excel “Table” (Sheet “TableData” - Table1).
- Since some “Invoice Information” could be changed after record creation, I need a way to allow users to Update records.
In the example provided, I am setting up an “Update” form (see sheet=Update) that searches the Invoice Table (Table1) for the “Client” and all his/her “Invoices” (InvId), showing the User the required record´s current information.
Once the user has made the necessary changes to the ‘updatable’ fields of the Invoice, a full version of the “updated” fields of the Invoice record should be over-written on the respective Invoice’s row within the Invoice Table (Table1).
Not having much expertise in writing Macros, and not understanding how to “isolate” a specific row in the Table (other than using a “filter”) the logic for the macro I understand could be used is to:
1. Filter the table based on the unique keys I have (in the example, two fields of the table: “Client and InvId”, which make up the “Client-InvId” key pair). Doing so, the Filter will return one record of the Table.
2. Copy the “load_UpdatedInvoice” range (shown in sheet=Update!$C$20:$K$20) and over-write the Table´s filtered row. (By construct, Table1 has only one row for any “Client-InvId” key pair).
3. Once copied, reset Table1 (Filter off and sort by “Client” and “InvId” fields.)
4. Clear the “Update” form's fields and reset it for a new update process.
The confusion I have is how to specify in the Macro the ‘variable’ I need (the “Client-InvId” key) that is used to filter the Table.
From recording the key-strokes of the “filter”, I have the following code snip-it for the Filter I need:
How should I "define/declare" and “set” these Filter Criteria to the values in cells Update!$C$4 (“Client” ie. "ACME") and Update!$C$5 (“InvId” ie. "2035") respectively?
Is there another more expedient and secure way to achieve my purpose?
Many thanks for your interest and support.
Kind regards.
Daniel Murray
DMurray3
I am looking for some sample VB Macro Code I could apply to an Update form I am trying to build. I apologize if I over extend my explanations.
A Basic example of what I have so far is enclosed.
The process considered is:
- A User enters data in the Data Entry Form (sheet=NewEntry) and updates an excel “Table” (Sheet “TableData” - Table1).
- Since some “Invoice Information” could be changed after record creation, I need a way to allow users to Update records.
In the example provided, I am setting up an “Update” form (see sheet=Update) that searches the Invoice Table (Table1) for the “Client” and all his/her “Invoices” (InvId), showing the User the required record´s current information.
Once the user has made the necessary changes to the ‘updatable’ fields of the Invoice, a full version of the “updated” fields of the Invoice record should be over-written on the respective Invoice’s row within the Invoice Table (Table1).
Not having much expertise in writing Macros, and not understanding how to “isolate” a specific row in the Table (other than using a “filter”) the logic for the macro I understand could be used is to:
1. Filter the table based on the unique keys I have (in the example, two fields of the table: “Client and InvId”, which make up the “Client-InvId” key pair). Doing so, the Filter will return one record of the Table.
2. Copy the “load_UpdatedInvoice” range (shown in sheet=Update!$C$20:$K$20) and over-write the Table´s filtered row. (By construct, Table1 has only one row for any “Client-InvId” key pair).
3. Once copied, reset Table1 (Filter off and sort by “Client” and “InvId” fields.)
4. Clear the “Update” form's fields and reset it for a new update process.
The confusion I have is how to specify in the Macro the ‘variable’ I need (the “Client-InvId” key) that is used to filter the Table.
From recording the key-strokes of the “filter”, I have the following code snip-it for the Filter I need:
Code:
Sub Macro1()
' Macro to filter Table1 based on “Client-InvId”
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _
"ACME" ‘ this corresponds to the “Client” of the “Client-InvId” key
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2, Criteria1:= _
"2035" ‘ this corresponds to the “InvId” of the “Client-InvId” key
End SubHow should I "define/declare" and “set” these Filter Criteria to the values in cells Update!$C$4 (“Client” ie. "ACME") and Update!$C$5 (“InvId” ie. "2035") respectively?
Is there another more expedient and secure way to achieve my purpose?
Many thanks for your interest and support.
Kind regards.
Daniel Murray
DMurray3