Manual entries in a dynamic table.

[meetarnav]

I have created a dynamic table.

The inputs are Start Date, End Date each in different cells.

Then I calculate the number of 30-day intervals between the two dates.

I have managed to create a dynamic table that spits out two columns depending on the number of intervals -> namely Serial Number and Date.


So if the start date is 22 Aug. 2010 and end date is 30 Nov. 2010 the number of 30 day intervals is 4.27 (However, I have inelegantly convinced excel to treat it as 4).


BUT THE PROBLEM STARTS HERE.


Now I want the user to enter values manually into 2 columns ahead of these two columns. BUT I want to control the ability of the user to manually add ONLY DATA for the given number of rows as the table generates and not any more.


SO if the table generates only TWO rows then...


S.No________Date________Expenses_________Earnings

1.__________22 july_____400______________500

2.__________22 august___300______________500

________________________XXXXX_____________XXXXX


XXXX = The user should not be able to enter these...


Is there a way to sort of dynamically lock all cells after the end of the dynamic table?


Thanks in advance!

 

[vijaySharma]

Hi there,


1. You would need to calculate how many rows of data you will be writing and asking the end user to fill.


2. Considering that the data input table structure of the above 4 columns would remain unchanged, and assuimg "Expenses" is Column C, you input range starts from C2:C (maxRows)


Same would apply for Column D as well.


You can then through VBA code using Lock and Unlock and get the desired result.


HTH


~Vijay