How can I validate data input by macros?
 

This may semm easy to a lot of you, but here goes...

I have created macros that transfer data from a list format into 15 cost
centre spreadsheets linked to a total sheet for each accounting period (AP)
of the year.

I have created a form at the front of the workbook so that the user can
select a button relating to the month that they wish to populate data for.
When they click a button, the macro enters (hard code) the corresponding AP
number into a cell on the total sheet to format all sheets from forecast to
actual data.

I want to use Data Validation to prompt the user to confirm their selection
so that after they have selected the AP they wish to run, a warning message
is displayed asking them if they wish to continue before the data upload
takes place. However, data entered by the macros into the cell I wish to
validate does not cause the error message to be displayed.

Does anyone have a solution please...?

How can I validate data input by macros?
 

Couldn't you confirm the input at the time the button is clicked? As part of
the code attached to the button, you could display a message that says "You
have selected AP xxx. Is this correct? If they answer "yes", proceed with
the entry in the summary sheet. If they answer "no". Prompt them to try
again and end the procedure.

-Stan Shoemaker
Palo Alto, CA

"ewan72" wrote:

This may semm easy to a lot of you, but here goes...

I have created macros that transfer data from a list format into 15 cost
centre spreadsheets linked to a total sheet for each accounting period (AP)
of the year.

I have created a form at the front of the workbook so that the user can
select a button relating to the month that they wish to populate data for.
When they click a button, the macro enters (hard code) the corresponding AP
number into a cell on the total sheet to format all sheets from forecast to
actual data.

I want to use Data Validation to prompt the user to confirm their selection
so that after they have selected the AP they wish to run, a warning message
is displayed asking them if they wish to continue before the data upload
takes place. However, data entered by the macros into the cell I wish to
validate does not cause the error message to be displayed.

Does anyone have a solution please...?

How can I validate data input by macros?
 

Hi thanks for the reply.

I reposted this query yesterday under the name 'validation warning for macro
data' as I couldn't find this original (dumbass). I have found a work-around
today using a nested If statement.

Thanks again.

"stanshoe" wrote:

Couldn't you confirm the input at the time the button is clicked? As part of
the code attached to the button, you could display a message that says "You
have selected AP xxx. Is this correct? If they answer "yes", proceed with
the entry in the summary sheet. If they answer "no". Prompt them to try
again and end the procedure.

-Stan Shoemaker
Palo Alto, CA

"ewan72" wrote:

This may semm easy to a lot of you, but here goes...

I have created macros that transfer data from a list format into 15 cost
centre spreadsheets linked to a total sheet for each accounting period (AP)
of the year.

I have created a form at the front of the workbook so that the user can
select a button relating to the month that they wish to populate data for.
When they click a button, the macro enters (hard code) the corresponding AP
number into a cell on the total sheet to format all sheets from forecast to
actual data.

I want to use Data Validation to prompt the user to confirm their selection
so that after they have selected the AP they wish to run, a warning message
is displayed asking them if they wish to continue before the data upload
takes place. However, data entered by the macros into the cell I wish to
validate does not cause the error message to be displayed.

Does anyone have a solution please...?