MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Cell Date: Combo Box

  Asked By: Reginheraht    Date: Mar 16    Category: MS Office    Views: 961

I've used VBA in Access before, but never in Excel. Here's what I'm
trying to do.

I've got a bill payment spreadsheet I've laid out and I want to make
the date next to the payment cell change when the payment cell is
updated, which would be when I make a payment.

The way I update the payment cell is complicated(to me at least). What
I want to do is take the amount that is in the cell and then add the
payment amount to it through a form. This form would, preferably, have
a combo or list box so I can choose which institution I am making a
payment to.

If anyone could help or point me in the right direction it would be
much appreciated.



1 Answer Found

Answer #1    Answered By: Ralph Murray     Answered On: Mar 16

You need to use the worksheet_Change event. As luck would have it I just
uploaded an example of this to the Files section of this group which you
could easily amend. Download 'Change event demo' from the Files section
and make the following changes:

1. In the line beginning 'Set rg=' change  it to reflect the column you
enter a new payment in. I'm assuming it's a column we're talking about,
rather than just a cell.
2. The line that starts 'rg.Offset' inserts a formula in Column E on the
same row as the new entry. If you want the date  to be updated in a
single cell, you'll need to change this line to:
range("A1").FormulaR1C1 = "=TODAY()"

I have concerns about what you seem to be suggesting here. I think
you're sayng that a single cell  should be incremented direct from a
form. Whilst this is certainly possible, the potential for data loss is
huge. What most of us prefer to do is to keep a list  of all the items
which contribute to such a total, and then have the 'total' cell
calculate the total of all the others. Then if there is a problem, a
glance at the source list can tell us what recent data has gone astray.

In the source list, the institution could eaily be selected from a
pulldown using data validation.

Can you say more about what you're trying to do here?

Didn't find what you were looking for? Find more on Cell Date: Combo Box Or get search suggestion and latest updates.