Alright, this is what I propose:
Freeze the sheet on row 9: Select row 10|Windows|Freeze Panes.
Per my idea you will need to insert blank rows in-between the end of each gray section. These blank rows are what "hide" the five different sections. You will need to insert enough space in-between the sections that only one section can be viewed until you scroll down to the next section, but we will not scroll manually, we will have codes in the combo box (drop down) and the scroll will be instant (as if you
changed value instead of scrolling).
The combo box is linked to the F-row (F1:F5), and F1:F5 is linked from the name of each section (A10 and A20 as per above sheet, but these cell references will be changed in your worksheet to where ever you place these cell refs)).
Note that I have re-merged (A10 & B10 is merged and C10 not) the row excluding the parenthes in row F (as the text will be too long for the combo box).
The combo box is from View|Toolbars|Control Toolbox. Click on Combo Box and size it on the worksheet over cell C9. You might want to make that cell bigger.
I don't write code, I just dabble. I tried to edit the below code, I got from our good friend Erik Van Geit, to work in your sheet, but run into the same error and must ask the board to finish editing.
This code has done the same for me what I believe you ask for, except this code scroll columns to find the target. The code needs to be edited to scroll through the rows. The edit must be reasonable simple, but I'm running out of time to finish.
Code:
Private Sub ComboBox1_Change()
'Erik Van Geit
'find comboboxvalue and jump to there
Dim c As Range
Dim SC As Integer 'Scroll Column
Set c = Me.UsedRange.Find(ComboBox1, LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
MsgBox """" & ComboBox1 & """not found", 48, "ERROR"
Else
SC = c.Column - 2
ActiveWindow.ScrollColumn = SC
End If
End Sub
This combo box has many properties that you can set, even background color to be easy to find.
This will be my last post for tonight and I'm inviting the board to finish of what I believe will work for what is requested per my understanding, and to finish edit the code.
Good luck.
RAM
