User Data form and populating a worksheet

[l]

Hi all,
i apologize in advance if i'll not be able to explain what i need.

I'll try to let you know what i would like to have. My VBA skills are
.... let's say they are not...

Let's say the user has simply to input in cell A1 how many tiers he
wants.
Then i would like the user to be presented with an interface, asking
him to write TWO variables (5 numbers) for each tier he selected PLUS
1. (so if he selected 3 in A1, he will be asked 4*2=8 numbers)
At the same time, I would like one of the two variables in the first
tier, and the other variable in the last tier to be preset to zero,
but the user has to be able to change it.

i would like the user to be allowed to populate the cell A1 with
numbers ranging from 1 to 9, and the Input interface to change
accordingly.

NOW it comes the difficult part....
for each tier, there are several calculations that have to be
performed.
What i have done so far in excel, is creating all these calculations
for all 10 tiers. (for each tier there are 13 rows each time)
My dream would be to ADD dinamically these 13 row everytime i change
the number of tiers in cell A1.

Is that possible? what should i start to read? could you plz suggest
some issue/argument/webpage/previous post that i could read?

thanks a lot in advance

 

[l]

to be presented with an interface, asking
him to write TWO variables (5 numbers) for each tier he selected PLUS
1. (so if he selected 3 in A1, he will be asked 4*2=8 numbers)

i meant "% numbers", not "5 numbers", i apologize

 

[Guest]

What you need is a worksheet change that is triggered every time an entry is
made into cell A1. Enter the code into a VBA sheet (not module) by clicking
on the tab on the bottom of the worksheet (normally sheet1) and selecting
view code. Then paste code on sheet.



Sub worksheet_change(ByVal Target As Range)

EnableEvents = False

If (Target.Row = 1) And (Target.Column = 1) Then

Columns("$C:$D").ClearContents
Columns("$C:$D").Interior.ColorIndex = None
Range("C1" & (Target + 1)).Interior.ColorIndex = 6
Range("C11").Value = 0
Range("C" & (Target + 1) & "" & (Target + 1)) = 0
MsgBox ("Enter numbers into colored cells")
Else
If Not IsEmpty(Cells(1, "A")) Then
'enter your code here
End If
End If

EnableEvents = True

End Sub

 

[l]

Thanks a lot

but what about the user form for data input?

As far as i understood, the Form has always the same shape/dimension,
while in my head the form has to present just the number of fields
needed once the user select the triggering number in A1. The form
could also have all the fields visible, but it's important that only
the needed ones are editable.
thanks

 

[Guest]

I think the form size is just the way the form area (number of cells) gets
displays on the screen and the print area. I have taken forms and made the
large by copying row and adding to end of the form and then change the print
area. I don't think you should be worrying about the size.