Deleting with a referance...kinda...not sure what to call this!!
 

Hi folks,

Well I purchased 4 books, aan I've been learning more about EXcel and VBA.
Its a hard and fast learning curve. Excel is offering more capabilities than
I ever thought! Anyway...I have a problem as below:

I have two workbooks - Workbook 1 and Workbook 2

In Workbook 1 the user can enter data such as Age, Name Address.

I have a submit button linked to a macro which passes this info to Workbook
2 and saves it. Each time the button is clicked, the data moves down 1 row
and is saved, building up a long list of entries. I'm sure you know the
principle....anyway...

So then all the data saved in WorkBook 2 is linked back to be displayed in
Workbook 1 with a simple '='
The reason for this is that I can update the program by changing Workbook 1,
and the user can upgrade by replacing Workbook 1 without losing their data in
Workbook 2.....anyway....

My problem is thus:
The user can now see their saved item lists in Workbook 1 which is basically
the user interface. How can I let the user select any item from their
displayed lists and delete the selected item in Workbook 2 where the list is
saved?

How can I get the program to find the correct item in Workbook 2 and delete
that specific row. I can number each row entry, but still don't know how to
get that row of data deleted?

Does this make sense? I'm thinking this might require a section of VBA
code.....what do you think?

Thanks for any ideas...

Deleting with a referance...kinda...not sure what to call this!!
 

Yes, you will need a macro if you want to delete a row (a formula
cannot do this), but you say that you already have a macro to copy
data into the second workbook. If you have a column with unique values
in them, then you could use the MATCH function to give you the
(relative) row that matches your data item. However, if you delete
that row, then the formulae in Workbook 1 may return #REF, so these
might need to be re-instated as well.

Hope this helps.

Pete

On Nov 27, 8:24 pm, dim wrote:
Hi folks,

Well I purchased 4 books, aan I've been learning more about EXcel and VBA.
Its a hard and fast learning curve. Excel is offering more capabilities than
I ever thought! Anyway...I have a problem as below:

I have two workbooks - Workbook 1 and Workbook 2

In Workbook 1 the user can enter data such as Age, Name Address.

I have a submit button linked to a macro which passes this info to Workbook
2 and saves it. Each time the button is clicked, the data moves down 1 row
and is saved, building up a long list of entries. I'm sure you know the
principle....anyway...

So then all the data saved in WorkBook 2 is linked back to be displayed in
Workbook 1 with a simple '='
The reason for this is that I can update the program by changing Workbook 1,
and the user can upgrade by replacing Workbook 1 without losing their data in
Workbook 2.....anyway....

My problem is thus:
The user can now see their saved item lists in Workbook 1 which is basically
the user interface. How can I let the user select any item from their
displayed lists and delete the selected item in Workbook 2 where the list is
saved?

How can I get the program to find the correct item in Workbook 2 and delete
that specific row. I can number each row entry, but still don't know how to
get that row of data deleted?

Does this make sense? I'm thinking this might require a section of VBA
code.....what do you think?

Thanks for any ideas...

Deleting with a referance...kinda...not sure what to call this
 

Thanks Pete, I'll give it a go.

"Pete_UK" wrote:

Yes, you will need a macro if you want to delete a row (a formula
cannot do this), but you say that you already have a macro to copy
data into the second workbook. If you have a column with unique values
in them, then you could use the MATCH function to give you the
(relative) row that matches your data item. However, if you delete
that row, then the formulae in Workbook 1 may return #REF, so these
might need to be re-instated as well.

Hope this helps.

Pete

On Nov 27, 8:24 pm, dim wrote:
Hi folks,

Well I purchased 4 books, aan I've been learning more about EXcel and VBA.
Its a hard and fast learning curve. Excel is offering more capabilities than
I ever thought! Anyway...I have a problem as below:

I have two workbooks - Workbook 1 and Workbook 2

In Workbook 1 the user can enter data such as Age, Name Address.

I have a submit button linked to a macro which passes this info to Workbook
2 and saves it. Each time the button is clicked, the data moves down 1 row
and is saved, building up a long list of entries. I'm sure you know the
principle....anyway...

So then all the data saved in WorkBook 2 is linked back to be displayed in
Workbook 1 with a simple '='
The reason for this is that I can update the program by changing Workbook 1,
and the user can upgrade by replacing Workbook 1 without losing their data in
Workbook 2.....anyway....

My problem is thus:
The user can now see their saved item lists in Workbook 1 which is basically
the user interface. How can I let the user select any item from their
displayed lists and delete the selected item in Workbook 2 where the list is
saved?

How can I get the program to find the correct item in Workbook 2 and delete
that specific row. I can number each row entry, but still don't know how to
get that row of data deleted?

Does this make sense? I'm thinking this might require a section of VBA
code.....what do you think?

Thanks for any ideas...

Deleting with a referance...kinda...not sure what to call this
 

Okay, thanks for feeding back.

Pete

On Nov 29, 10:02 am, dim wrote:
Thanks Pete, I'll give it a go.



"Pete_UK" wrote:
Yes, you will need a macro if you want to delete a row (a formula
cannot do this), but you say that you already have a macro to copy
data into the second workbook. If you have a column with unique values
in them, then you could use the MATCH function to give you the
(relative) row that matches your data item. However, if you delete
that row, then the formulae in Workbook 1 may return #REF, so these
might need to be re-instated as well.

Hope this helps.

Pete

On Nov 27, 8:24 pm, dim wrote:
Hi folks,

Well I purchased 4 books, aan I've been learning more about EXcel and VBA.
Its a hard and fast learning curve. Excel is offering more capabilities than
I ever thought! Anyway...I have a problem as below:

I have two workbooks - Workbook 1 and Workbook 2

In Workbook 1 the user can enter data such as Age, Name Address.

I have a submit button linked to a macro which passes this info to Workbook
2 and saves it. Each time the button is clicked, the data moves down 1 row
and is saved, building up a long list of entries. I'm sure you know the
principle....anyway...

So then all the data saved in WorkBook 2 is linked back to be displayed in
Workbook 1 with a simple '='
The reason for this is that I can update the program by changing Workbook 1,
and the user can upgrade by replacing Workbook 1 without losing their data in
Workbook 2.....anyway....

My problem is thus:
The user can now see their saved item lists in Workbook 1 which is basically
the user interface. How can I let the user select any item from their
displayed lists and delete the selected item in Workbook 2 where the list is
saved?

How can I get the program to find the correct item in Workbook 2 and delete
that specific row. I can number each row entry, but still don't know how to
get that row of data deleted?

Does this make sense? I'm thinking this might require a section of VBA
code.....what do you think?

Thanks for any ideas...- Hide quoted text -

- Show quoted text -