Input number of rows

[acaPAWN7]

I want to make a list of numbers which can determine the number of rows from the user. Let say, the list is between 'year 1' to 'year 5'. If the user choose 'year 3', the rows will be added from 'year 1' til 'year 3'. could this be made possible?

or the other way, possibly is there any method to hide the existing row from the user view? so if the user select 3, the 'year 1' til 'year 3' will be unhidden. and the remaining rows ('year 4' and 'year 5') will be hidden from the user view.

I'm not sure whether there are ways to solve this kind of problem. hope any expert can help me. thanks~

 

[Peter_SSs]

acaPAWN7

If I have understood correctly, then this may be what you want.

1. Assuming the layout is as shown below.
2. Right click the sheet name tab and choose 'View Code'
3. Copy and paste the following code in the pane at the right of the VBA screen.<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<> "$D$1"<SPAN style="color:#00007F">Then</SPAN><SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Sub</SPAN>
    ShowRows = Target.Value
    Rows("2:6").EntireRow.Hidden =<SPAN style="color:#00007F">False</SPAN>
    <SPAN style="color:#00007F">If</SPAN> ShowRows = 5<SPAN style="color:#00007F">Then</SPAN><SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Sub</SPAN>
    Rows(ShowRows + 2 & ":6").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
1	Years		Years to show:	5
2	Year 1
3	Year 2
4	Year 3
5	Year 4
6	Year 5
7
Show Years

 

[acaPAWN7]

I'm not sure about the code coz after I tried it, it won't works.. Let me explain a bit detail of my problem.. what I want to show here is just example, not the real table of mine (but quite similar):

this is the table.. it shows from year 1 til year 5 of loan disbursement:

all of the calculations are already been set in the loan disbursement column (for every number of years)..


so if the user selects year 3 from the combo box, it will be like this:

the remaining rows (for year 4 and 5) are hidden from the view.. and if the user select year 4, row of year 5 will be hidden, and so on.. actually can u explain how to create the combo box, including with its number of years (1, 2, 3, 4 & 5)? hope this time u get what I mean here..

 

[Peter_SSs]

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
Show Years

 

[acaPAWN7]

owh, now i'm understand with your explaination here.. by the way, can i make the C3 drop down box at different worksheet? maybe i need to change the code, rite? this would be much better coz i wanted to have the sheet for selecting number of years as the result page only, without any input from the user.. thanks again Peter_SSs

 

[acaPAWN7]

btw, the code seems don't run after i saved the macro.. i already follow the steps i told before.. anyway, how to activate it? huhu -_-"

 

[acaPAWN7]

i'm so sorry.. zillions of appologizes.. coz i didn't aware of the row numbers.. my mistakes, haha.. thanks pal, it really works..

regarding to my previous question:

by the way, can i make the C3 drop down box at different worksheet? maybe i need to change the code, rite? this would be much better coz i wanted to have the sheet for selecting number of years as the result page only, without any input from the user..

hope u get what i mean here.. btw, does this apply to columns? i can replace all the rows code into column, is it? (ShowRows = ShowColumns, etc..) or there are other ways? for example, i wanna hide column F to T (similar to row 7 til 11 for the previous code).. lots of thanks

 

[Peter_SSs]

by the way, can i make the C3 drop down box at different worksheet? maybe i need to change the code, rite? this would be much better coz i wanted to have the sheet for selecting number of years as the result page only, without any input from the user..

If I understand this correctly:
1. You want the drop-down on another sheet. What is the name of that sheet?
2. What is the name of the sheet that shows/hides the year rows?
3. Confirm that when the drop-down in the first sheet is changed, the rows on the second sheet should automatically show/hide?

btw, does this apply to columns?

Yes similar with columns and rows.

 

[acaPAWN7]

Yup, you're getting what I mean here..

1. The drop-down will be placed at Main sheet (F19).
2. The show/hide year rows will be at LD sheet (row 17 - 21).
3. When the user select (change) the dop-down at Main sheet, the year rows at LD sheet should be show/hide automatically.

Can the code be just like this to set the drop-down at other sheet?

If Target.Address <> "Main!$F$19" Then Exit Sub

So, for adjusting the column, can the code be like this? I think there're errors on it:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ShowColumns As Integer
    If Target.Count > 1 Then Exit Sub
    If Target.Address <> "Main!$F$26" Then Exit Sub
    ShowColumns = Target.Value
    Columns("F:T").EntireColumn.Hidden = False
    If ShowColumns = 15 Or ShowColumns = 0 Then Exit Sub
    Columns(ShowColumns + F & ":T").EntireColumn.Hidden = True
End Sub

I changed the 'rows' into 'columns'. Is that it? Or they're different ways? Similar to the previous case, where I wanted to put the drop-down (15 years) at the Main sheet (F26), and the show/hide columns at P&L sheet and also CF sheet (column F - T, same for both sheet).

I need to paste the same code at both sheets, right? Hope this time it is more clearer to you. Thanks

 

[Peter_SSs]

1. The drop-down will be placed at Main sheet (F19).
2. The show/hide year rows will be at LD sheet (row 17 - 21).
3. When the user select (change) the dop-down at Main sheet, the year rows at LD sheet should be show/hide automatically.

This code should be in the Worksheet_Change evnt for sheet "Main"
<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 <> "$D$19" <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
    ShowRows = Target.Value
    Worksheets("LD").Activate
    ActiveSheet.Rows("7:21").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>
    ActiveSheet.Rows(ShowRows + 17 & ":21").EntireRow.Hidden = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

I changed the 'rows' into 'columns'. Is that it? Or they're different ways? Similar to the previous case, where I wanted to put the drop-down (15 years) at the Main sheet (F26), and the show/hide columns at P&L sheet and also CF sheet (column F - T, same for both sheet).

Sorry, I really have no idea what issue you are talking about in relation to columns? I don't know what previous case you are talking about.