Hello,
I am hoping someone can help me with this issue. Its very frustrating to say the least and I don't understand why I am experiencing this problem. I have a few different shapes on my worksheet used as buttons. When a button is clicked it brings the user to a certain row on the same worksheet. Depending on the shape clicked also varies rows are being hidden. During each button click I only want the user to scroll within the section I want them too. There is a code example below. In order to achieve what I am after, I clear the scroll area and reset it at the bottom of each button. As shown below the user will not be able to go past row 220. For some reason, excel will only let me scroll to row 182!! Why? My scroll area is set to go past this to row 220?!!! Any help would be appreciated!
I am hoping someone can help me with this issue. Its very frustrating to say the least and I don't understand why I am experiencing this problem. I have a few different shapes on my worksheet used as buttons. When a button is clicked it brings the user to a certain row on the same worksheet. Depending on the shape clicked also varies rows are being hidden. During each button click I only want the user to scroll within the section I want them too. There is a code example below. In order to achieve what I am after, I clear the scroll area and reset it at the bottom of each button. As shown below the user will not be able to go past row 220. For some reason, excel will only let me scroll to row 182!! Why? My scroll area is set to go past this to row 220?!!! Any help would be appreciated!
VBA Code:
Sub Next_buttn()
' the above button brings the user to the basic info section
Dim wb As Workbook, ws As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Worksheets("Refi Path")
ActiveSheet.ScrollArea = ""
If ws.Range("S57").Value = 1 Then
ws.Rows("9:12").EntireRow.Hidden = False
ws.Rows("13:15").EntireRow.Hidden = True
ws.Rows("16:79").EntireRow.Hidden = True
ws.Rows("80:82").EntireRow.Hidden = False
ws.Rows("83:161").EntireRow.Hidden = True
ws.Shapes.Range("Chart 356").Visible = False
ws.Shapes.Range("Chart 358").Visible = False
ElseIf ws.Range("S57").Value >= 2 Then
ws.Rows("9:16").EntireRow.Hidden = False
ws.Rows("17:79").EntireRow.Hidden = True
ws.Rows("80:82").EntireRow.Hidden = False
ws.Rows("83:161").EntireRow.Hidden = True
ws.Shapes.Range("Chart 356").Visible = True
ws.Shapes.Range("Chart 358").Visible = True
Else
ws.Rows("9:15").EntireRow.Hidden = True
ws.Rows("16:21").EntireRow.Hidden = False
ws.Rows("22:161").EntireRow.Hidden = True
End If
'ws.Shapes.Item("Rectangle 23").OLEFormat.Object.Value = 1
ws.Shapes("Rectangle 23").TextFrame.Characters.Text = ws.Range("R34").Value
'applicant one name
ws.Shapes("Rectangle 67").TextFrame.Characters.Text = ws.Range("S60").Value
' borrower 2
ws.Shapes("Rectangle 66").TextFrame.Characters.Text = ws.Range("S64").Value
' third applicant
ws.Shapes("Rectangle 68").TextFrame.Characters.Text = ws.Range("S67").Value
ws.Shapes.Range("Rectangle 4").Visible = False
ws.Rows("162:179").EntireRow.Hidden = False
ws.Rows("180:182").EntireRow.Hidden = True
ws.Rows("183:186").EntireRow.Hidden = False
ws.Rows("187:195").EntireRow.Hidden = True
ActiveWindow.ScrollRow = 5
ActiveSheet.ScrollArea = "A1:X220" ' THIS IS WHERE I AM HAVING A PROBLEM. It runs with no errors, but I can only scroll to row 182
End Sub