acaPAWN7
This used Data Validation in cell C3, not a combo box. There are a number of different ways to get the list of years to appear in the C3 drop-down. This is just one of them.
1. Enter the list of years you want to appear in the C3 drop-down list in J2:J6 as shown.
2. Select C3
3. Data|Validation...|Allow: List|Source: =$J$2:$J$6|OK
4. Right click the sheet name tab and choose 'View Code'
5. Copy and paste the following code in the pane at the right of the VBA screen. (Ensure that you have the correct sheet selected in the left pane of the VBA window and that 'Worksheet' is appearing in the top left drop-down and 'Change' in the top right drop-down of the right part of the VBA screen. Also ensure that after pasting you do not have the first and last line of the code below appearing twice)
6. Now go back to your Excel sheet and try selecting different values from the C3 drop-down.<font face=Courier New><SPAN style="color:#00007F">Private</SPAN><SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target<SPAN style="color:#00007F">As</SPAN> Range)
<SPAN style="color:#00007F">Dim</SPAN> ShowRows<SPAN style="color:#00007F">As</SPAN><SPAN style="color:#00007F">Integer</SPAN>
<SPAN style="color:#00007F">If</SPAN> Target.Count > 1<SPAN style="color:#00007F">Then</SPAN><SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Sub</SPAN>
<SPAN style="color:#00007F">If</SPAN> Target.Address<> "$C$3"<SPAN style="color:#00007F">Then</SPAN><SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Sub</SPAN>
ShowRows = Target.Value
Rows("7:11").EntireRow.Hidden =<SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">If</SPAN> ShowRows = 5<SPAN style="color:#00007F">Or</SPAN> ShowRows = 0<SPAN style="color:#00007F">Then</SPAN><SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Sub</SPAN>
Rows(ShowRows + 7 & ":11").EntireRow.Hidden =<SPAN style="color:#00007F">True</SPAN><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>
Mr Excel.xls |
---|
|
---|
| A | B | C | D | E | F | G | H | I | J | K |
---|
1 | | | | | | | | | | Years List | |
---|
2 | | | | | | | | | | 1 | |
---|
3 | | Number of Years | 5 | | | | | | | 2 | |
---|
4 | | | | | | | | | | 3 | |
---|
5 | | | | | | | | | | 4 | |
---|
6 | | Years | Loan Disbursement | | 5 | |
---|
7 | | Year 1 | | | | |
---|
8 | | Year 2 | | | | |
---|
9 | | Year 3 | | | | |
---|
10 | | Year 4 | | | | |
---|
11 | | Year 5 | | | | |
---|
12 | | | | | | | | | | | |
---|
|
---|