Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm struggling with the following little problem that I do not seem to be able to solve.

    I think it looks complex; I believe the functionality I'm looking for however is quite simple: one way to look at this is that I'd like a "moving window" that always shows all visible lines (if there are less than 20) or the last 20 only. On top there's always one fixed line above, and two rows below, there's always a button to add a new line.

    In more detail:
    I have a simple sheet in which a user can add rows with information. Sometimes, a row gets 'completed' and rows will then be automatically hidden (I do that in VBA, easy).
    Adding a row is done by clicking a button that should always be (1) [on screen] and two rows below the last used row (if the last or last few rows are hidden, this is still true)

    So; suppose I have 60 rows and rows 1-10, 25-30 and 55-60 are hidden. The button should be on screen (two rows high) in row 62 and 63 (I can do that). On screen it appears as if the button is two rows below row 54 (as 55-60 were hidden). Still with me?

    With ActiveSheet
    .Shapes("ButtonAdd").Left = .Columns("A").Left
    .Shapes("ButtonAdd").Width = (.Columns("C").Left - .Columns("A").Left)
    .Shapes("ButtonAdd").Top = .Rows(ButtonTop).Top
    .Shapes("ButtonAdd").Height = (.Rows(ButtonTop + 2).Top - .Rows(ButtonTop).Top)
    End With


    OK, now I always want (2) to display row 1 on top (it's fixed and will never be hidden); that seems possible.

    Now here's the complex part... (3) as I indicated, I always want the button on the screen. So if too many rows are visible; only the last 20 rows should be on the screen (the rest may be reached by scrolling up manually). I use this:

    Application.Goto Range("A1").Offset(Something), True

    Now this last one however I'm having problems with as the last 20 rows I want to see on the screen might actually be more than 20 'real rows' because some are hidden; in my example the top row visible would be 35: displaying 35 - 60 shows 35 - 54 (which is 20 rows) as 55-60 are hidden.

    I'm sure this is all possible but here I start to disconnect as to how to accomplish this ass the "Offset" property doesn't 'know' about hidden rows....

    Once the above is solved; it should also work if only 5 rows are in the whole list of course... or if there are only 5 visible rows in a larger list... In that case I should see all visible lines (as 5 is smaller than the max 20) and the button should still be two rows below the last on screen.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Thinking out of the box, why not create a custom toolbar button instead of a command button on the sheet itself? You wouldn't have to worry about visibility.

  3. #3
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='766404' date='20-Mar-2009 15:18']Thinking out of the box, why not create a custom toolbar button instead of a command button on the sheet itself? You wouldn't have to worry about visibility.[/quote]


    That's possible and would address the button placement; I believe (with the location of the 'real last row' through your tip in another post

    Real_Last_Row = Range("B:B").Find(What:="*", SearchDirection:=xlPrevious).Row

    ) that is more or less solved now

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='ErikJan' post='766405' date='20-Mar-2009 15:22']That's possible and would address the button placement; I believe (with the location of the 'real last row' through your tip in another post

    Real_Last_Row = Range("B:B").Find(What:="*", SearchDirection:=xlPrevious).Row

    ) that is more or less solved now[/quote]

    I think I got some inspiration yesterday... it's in my head now and I'll try to develop some short code to do this. If it works, I'll post it here later...

  5. #5
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    Terneuzen, Netherlands
    Posts
    895
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='ErikJan' post='766614' date='21-Mar-2009 19:59']I think I got some inspiration yesterday... it's in my head now and I'll try to develop some short code to do this. If it works, I'll post it here later...[/quote]

    OK, got it... this code works. It needs a button with the name "ButtonAdd". I'm attaching an example sheet too:

    Option Explicit

    Sub Add_Line()
    Dim NewRow As Integer
    NewRow = Real_Last_Row
    Range("A1").Offset(NewRow, 0) = "Line " & NewRow + 1
    Scroll_Screen
    Range("A1").Offset(NewRow, 0).Select
    End Sub

    Private Sub Scroll_Screen()
    Dim ButtonTop As Integer, ViewPort As Integer, FirstRow As Integer
    Application.ScreenUpdating = False
    'Set the viewport
    ViewPort = 20
    FirstRow = First_Visible_in_ViewPort(ViewPort + 1, Real_Last_Row)
    Application.Goto Range("A1").Offset(FirstRow - 1), True
    'Place button two rows below last row
    ButtonTop = Real_Last_Row + 3
    With ActiveSheet
    .Shapes("ButtonAdd").Left = .Columns("A").Left
    .Shapes("ButtonAdd").Width = (.Columns("C").Left - .Columns("A").Left)
    .Shapes("ButtonAdd").Top = .Rows(ButtonTop).Top
    .Shapes("ButtonAdd").Height = (.Rows(ButtonTop + 2).Top - .Rows(ButtonTop).Top)
    End With
    End Sub

    Function First_Visible_in_ViewPort(VP As Integer, Rw As Integer) As Integer
    'Count VP visible lines back from row Rw and return that row
    Dim i As Integer, Cnt As Integer, TopRow As Integer
    '
    If Rw < VP Then 'Check if all rows 'fit' in viewport
    TopRow = 1
    Else
    Cnt = 1
    For i = Rw To 2 Step -1
    If Not Rows(i & ":" & i).EntireRow.Hidden Then Cnt = Cnt + 1: TopRow = i
    If Cnt >= VP Then Exit For 'Stop when ViewPort size reached
    Next
    End If
    '
    First_Visible_in_ViewPort = TopRow
    End Function

    Function Real_Last_Row() As Long
    'Next line works...
    ' intNextEntry_Row = Range("B64000").End(xlUp).Row
    ' but not if there are hidden rows!
    'Thanks to HansV on Woody's Lounge for advising the next code.
    Real_Last_Row = Range("A:A").Find(What:="*", SearchDirection:=xlPrevious).Row 'Find real last row (ignores hidden rows)
    End Function
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •