Active Cell Issues

[GingerCoder]

I am building an application form in excel, whereas the applicant will only be able to edit unprotected cells. I have included coding so that the number of applicants, number of facilities, and number of collateral accounts can be populated to the correct spacing in the form. For example, I have included space for up to 5 applicants using the below code. If there is only one applicant, the cells allotted for the other four applicant are automatically hidden:

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("C7").Value = 1 Then
Rows("24:95").Select
Selection.EntireRow.Hidden = True
Range("C7").Select
ElseIf Range("C7").Value = 2 Then
Rows("24:41").Select
Selection.EntireRow.Hidden = False
Range("C7").Select
ElseIf Range("C7").Value = 3 Then
Rows("24:59").Select
Selection.EntireRow.Hidden = False
Range("C7").Select
ElseIf Range("C7").Value = 4 Then
Rows("24:77").Select
Selection.EntireRow.Hidden = False
Range("C7").Select
ElseIf Range("C7").Value = 5 Then
Rows("24:95").Select
Selection.EntireRow.Hidden = False
Range("C7").Select
End If

Within the subset of one applicant's inputs, there are multiple data validation lists; Yes/No, type of applicant (individual, business) etc. My issue is this...No matter what cell I am in, the cursor will always jump back to C7. I have tried changing the Range("7").Select to a multitude of options to see if that fixes the issue, but to no avail. I've tried the obvious Options>Advanced>After Pressing Enter. I've tried an activecell.offset, an activecell.select, keycells as values and nothing has worked. Maybe I just don't have the right format. I can either get my structural coding or the formatting coding, but not simultaneously. What am I doing wrong? Can someone give me a code...and be specific...that will simply let the cursor go to the next active cell in the worksheet after an input and not be directed back to the one single cell. I know my issue is either with that Range("C7").Select piece or there is a code that would superceded that and be applicable to all the coding throughout the worksheet and/or workbook. I have Googled this to death and am tired. Someone help!

 

[My Aswer Is This]

You may consider writing your script like this:
I wrote two lines of code for you. If you like you can finish it.

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("C7").Value = 1 Then Rows("24:95").Hidden = True
If Range("C7").Value = 2 Then Rows("24:95").Hidden = False
End Sub

 

[GingerCoder]

Thanks for your suggestion. Unfortunately, that lands my cursor in one of the hidden cells, so same issue.

 

[My Aswer Is This]

Just add this line of code to last line of your script:

Range("C7").Select

If Range("C7") is hidden then change select to what ever you want.
My script never moves the active cell.

 

[Fluff]

Try adding these 2 lines to the top of your code

   If Target.CountLarge > 1 Then Exit Sub
   If Not Target.Address = "$C$7" Then Exit Sub

 

[My Aswer Is This]

Your script need to start like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 3 And Target.Row = 7 Then

This way the script will only run if you make a change to Range("C7")
And you will have to add a:
End if at bottom of script.

You current script runs any time Range("C7") has a value of 1 or 2 or 3 or 4 or 5 in Range("C7")

 

[GingerCoder]

Thanks all. None of those helped in my situation. I ended up re-writing my code. It makes it more simple but pretty monotonous. Oh, well. It works! Thanks!


If Range("C7").Value = "1" Then
Rows("25:100").EntireRow.Hidden = True
ElseIf Range("C7").Value = "2" Then
Rows("25:43").EntireRow.Hidden = False


End If


If Range("C7").Value = "1" Then
Rows("25:100").EntireRow.Hidden = True
ElseIf Range("C7").Value = "3" Then
Rows("25:62").EntireRow.Hidden = False


End If


If Range("C7").Value = "1" Then
Rows("25:100").EntireRow.Hidden = True
ElseIf Range("C7").Value = "4" Then
Rows("25:81").EntireRow.Hidden = False


End If


If Range("C7").Value = "1" Then
Rows("25:100").EntireRow.Hidden = True
ElseIf Range("C7").Value = "5" Then
Rows("25:100").EntireRow.Hidden = False


End If