stuartgb100
04-25-2015, 09:57 AM
Hi,
I pass workbooks to colleagues for them to work on and return to me.
I wish to find a way of helping a user with a visual check that they are happy they have finished their data input, and if not, to remind them of the fact.
Their input results in totals being placed in col K, with a final total at the end of the range in K.
Take the range K6:K499.
Some, but not all of the rows between 6 and 499 require user data.
where it does, the data they enter results in a sumproduct in the corresponding K cell.
The total is dynamically returned in K500.
If a user misses data input, then K500 is incorrect.
Their data input is quite complex, so they might enter 4 parts of data, leaving out the fifth, meaning to return to that dataset later. But if they forget, there will be a problem.
So I'm thinking as follows:
I pass the workbook to a colleague, having turned the cell fill colour to red in the range K6:K499
I put code in their machine's personal.xlsb which places a button on the toolbar.
This button is available to all workbooks/sheets that they open.
User opens their workbook and selects the sheet, then clicks the button.
The code then does as follows:
startcell = k6; endofrange = k499; lastcell = k500
1. establish the range being worked on in Col K (lastcell will be the first cell found sampling up col K that has a top border set); first cell range k6.
2. test the range (k6 and lastcell-1)
3. if any cell in the range has a fill colour value > 0 (ie there are red cells)
Then .rng lastcell.value= "ERROR"
Else .rng.value=sum(K6:K499)
So as long as at least one cell in col K is red, there'll be no total, simply ERROR, prompting user to check their data input.
Two further questions, please:
1.The user is free to input data rows and to delete (it is their data after all).
This means they can either delete or insert rows at will.
Will this cause a problem ?
2. How best to fire the procedure - an Event Change in col K ?
Regards and thanks,
Stuart.
I pass workbooks to colleagues for them to work on and return to me.
I wish to find a way of helping a user with a visual check that they are happy they have finished their data input, and if not, to remind them of the fact.
Their input results in totals being placed in col K, with a final total at the end of the range in K.
Take the range K6:K499.
Some, but not all of the rows between 6 and 499 require user data.
where it does, the data they enter results in a sumproduct in the corresponding K cell.
The total is dynamically returned in K500.
If a user misses data input, then K500 is incorrect.
Their data input is quite complex, so they might enter 4 parts of data, leaving out the fifth, meaning to return to that dataset later. But if they forget, there will be a problem.
So I'm thinking as follows:
I pass the workbook to a colleague, having turned the cell fill colour to red in the range K6:K499
I put code in their machine's personal.xlsb which places a button on the toolbar.
This button is available to all workbooks/sheets that they open.
User opens their workbook and selects the sheet, then clicks the button.
The code then does as follows:
startcell = k6; endofrange = k499; lastcell = k500
1. establish the range being worked on in Col K (lastcell will be the first cell found sampling up col K that has a top border set); first cell range k6.
2. test the range (k6 and lastcell-1)
3. if any cell in the range has a fill colour value > 0 (ie there are red cells)
Then .rng lastcell.value= "ERROR"
Else .rng.value=sum(K6:K499)
So as long as at least one cell in col K is red, there'll be no total, simply ERROR, prompting user to check their data input.
Two further questions, please:
1.The user is free to input data rows and to delete (it is their data after all).
This means they can either delete or insert rows at will.
Will this cause a problem ?
2. How best to fire the procedure - an Event Change in col K ?
Regards and thanks,
Stuart.