Frame1 scroll

Nirmalesh

New Member
Joined
Mar 28, 2022
Messages
20
Office Version
  1. 2007
Platform
  1. Windows
I have 1Frame control on user form.and
1label control and six textboxes under label. I want to do that when I scroll frame control then label control stay on their exact position as a header.
Actually I would like to make textbox grid. In this grid I use label control as header. How can I do this. Please vcçv
 

Nirmalesh

New Member
Joined
Mar 28, 2022
Messages
20
Office Version
  1. 2007
Platform
  1. Windows
Hi Nirmalesh, thanks for the screenshots.
Question: Why don't you have the labels outside the frame, Just above the frame. Then they won't move when you scroll. That is by far the simplest solution.
Yes it will be easy.but label also dynamic.and second thing that It showing awkward.i want to it show as gridview control.i will find out the code for my question.
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
4,005
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I still don't understand why you want to reinvent the wheel, while VBA gives you ready made solutions.

You mention the labels are 'dynamic', but at the same time you want them to stay in the same spot. So what do you mean with them being dynamic?
 

Nirmalesh

New Member
Joined
Mar 28, 2022
Messages
20
Office Version
  1. 2007
Platform
  1. Windows
I still don't understand why you want to reinvent the wheel, while VBA gives you ready made solutions.

You mention the labels are 'dynamic', but at the same time you want them to stay in the same spot. So what do you mean with them being dynamic?
Because Excel VBA have only one control that is listbox but in listbox there are no gridline.and Excel VBA provide second thing that is ActiveX control listview, but I do not want to use ActiveX control, because any ActiveX control does not exist and work when I install my project in other computer.so I want to make my own datagridview control
 

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
4,005
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
This is a decent way of achieving what you want.
it uses a 6 row grid of textboxes with header textboxes above the grid.

then a scrollbar control is added next to the grid.

on starting the userform, the grid is filled with the details for the first 6 rows from the spreadsheet .
When you click on the scroll control, the boxes are first cleared then filled again with the details starting from the 2nd row of the sheet.
1650114372738.png

That way you give the user the impression that the grid is scrolling, whereas in reality the textboxes are static, only the contents move up or down one row.

In my example userform the first (top left) textbox of the grid is TextBox7, with the numbering increasing left to right and then the next row.

You may ned to edit the code to suit your numbering.

VBA Code:
Option Explicit
    Dim vIn, UB As Long

Private Sub ScrollBar1_Change()
    LoadGrid ScrollBar1.Value
End Sub

Private Sub UserForm_Activate()

    vIn = Sheets("Sheet1").Range("B4").CurrentRegion    '<<<<< use the sheetname and starting cell of your database
    UB = UBound(vIn, 1)
    
    With ScrollBar1
        .Min = 2        'skip headerrow
        .Max = UB
        .LargeChange = 6
        .SmallChange = 1
    End With
End Sub

Sub LoadGrid(iStartRow As Long)
    Dim lR As Long, lC As Long, i As Integer, iGridR As Integer
    Dim tbTB As Control
    
    i = 7   'start number of first textbox in grid
    'Clear grid boxes
    For lR = i To i + 35
        For Each tbTB In Me.Controls
            If tbTB.Name = "TextBox" & lR Then Exit For
        Next tbTB
        tbTB.Value = ""
    Next lR
    
    lR = Application.WorksheetFunction.Max(2, iStartRow)
    
    'maximum 6 rows to be displayed
    iGridR = Application.WorksheetFunction.Min(5 + lR, UB)
    
    For lR = lR To iGridR
        For lC = 1 To 6
            For Each tbTB In Me.Controls
                If tbTB.Name = "TextBox" & i Then Exit For
            Next tbTB
            tbTB.Value = vIn(lR, lC)
            i = i + 1
        Next lC
    Next lR

End Sub
 

Nirmalesh

New Member
Joined
Mar 28, 2022
Messages
20
Office Version
  1. 2007
Platform
  1. Windows
This is a decent way of achieving what you want.
it uses a 6 row grid of textboxes with header textboxes above the grid.

then a scrollbar control is added next to the grid.

on starting the userform, the grid is filled with the details for the first 6 rows from the spreadsheet .
When you click on the scroll control, the boxes are first cleared then filled again with the details starting from the 2nd row of the sheet.
View attachment 62638
That way you give the user the impression that the grid is scrolling, whereas in reality the textboxes are static, only the contents move up or down one row.

In my example userform the first (top left) textbox of the grid is TextBox7, with the numbering increasing left to right and then the next row.

You may ned to edit the code to suit your numbering.

VBA Code:
Option Explicit
    Dim vIn, UB As Long

Private Sub ScrollBar1_Change()
    LoadGrid ScrollBar1.Value
End Sub

Private Sub UserForm_Activate()

    vIn = Sheets("Sheet1").Range("B4").CurrentRegion    '<<<<< use the sheetname and starting cell of your database
    UB = UBound(vIn, 1)
   
    With ScrollBar1
        .Min = 2        'skip headerrow
        .Max = UB
        .LargeChange = 6
        .SmallChange = 1
    End With
End Sub

Sub LoadGrid(iStartRow As Long)
    Dim lR As Long, lC As Long, i As Integer, iGridR As Integer
    Dim tbTB As Control
   
    i = 7   'start number of first textbox in grid
    'Clear grid boxes
    For lR = i To i + 35
        For Each tbTB In Me.Controls
            If tbTB.Name = "TextBox" & lR Then Exit For
        Next tbTB
        tbTB.Value = ""
    Next lR
   
    lR = Application.WorksheetFunction.Max(2, iStartRow)
   
    'maximum 6 rows to be displayed
    iGridR = Application.WorksheetFunction.Min(5 + lR, UB)
   
    For lR = lR To iGridR
        For lC = 1 To 6
            For Each tbTB In Me.Controls
                If tbTB.Name = "TextBox" & i Then Exit For
            Next tbTB
            tbTB.Value = vIn(lR, lC)
            i = i + 1
        Next lC
    Next lR

End Sub
Thankyou so much sir.please provide ful code.if I click on textbox then grid data show in userform text box .with update save button also
 

Nirmalesh

New Member
Joined
Mar 28, 2022
Messages
20
Office Version
  1. 2007
Platform
  1. Windows
Thankyou so much sir.please provide ful code.if I click on textbox then grid data show in userform text box .with update save button also. And sir good news is that I got solution for frame control scroll before few days.if I scroll frame scroll bar then lable control do not move or leave their position.but thanks for help me.please sir provide me full code .
 

Nirmalesh

New Member
Joined
Mar 28, 2022
Messages
20
Office Version
  1. 2007
Platform
  1. Windows
Dear sir I do not want only six texbox row I want unlimited rows
 

Nirmalesh

New Member
Joined
Mar 28, 2022
Messages
20
Office Version
  1. 2007
Platform
  1. Windows
Sir I found for my frame control scroll bar is very simple and ony one line code sir.after hard work I found that
 

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
4,005
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Glad you solved your problem
 

Forum statistics

Threads
1,176,511
Messages
5,903,470
Members
435,031
Latest member
traceson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top