userForm -> Frame 'top' value limited. Dynamically created controls,

Yeah.... so basically I thought about it, but there are too many data I would like to show without need of clicking the client's name just for first sight. The details will be shown after clicking on the frame (there are so much data about one client).
Listbox is too....... simple. I can't do formatting like e.g.

Client number: 000123 Name: John Smith Status: active
Address: Paris Phone number: 00011122233 Payments: on-time

And so on. By the way - the application window is 1200px wide, so there are much more data shown. I thought that 40px of frame highness (?) is enought to enter two lines of data and it will be readable, because each frame is distinguishing from others.

Can't you put a search box at the top to filter the number of results? No-one in their right mind would want to scroll through that many records

I'm really grateful that everyone is concerned about my application and want to help to prepare the GUI and stuff, but trust me - everything is well designed and it has all of searching features and everything needed for easy, intuitive and as-less-clicking-as-possible excel app.

In this thread I'd like to understand what I'm doing wrong and how to solve this issue.

romperstomper - when I first read your post I jumped on my chair - it must me that. Indeed in most of my application I user Integer as iterator. But I looked in my code and this is what I see:

'generate new controls - frames, labels, buttons and so on.
For genForms = 1 To NumberOfClients 'NumberOfClients
    Set genFrame = FrameList.Controls.Add("Forms.CommandButton.1")
With genFrame
.Name = "claim" & genForms
.Caption = "nr " & genForms
.Top = (40 * genForms) - 40
.Left = 0
.Height = 40
.Width = 988
End With
Next genForms
FrameList.ScrollHeight = genForms * 40

Of course in my app I have "Option Explicit" so there's no possibility of having any variable undeclared. But how I might declare the value of ".Top" as Long or the last line: "ScreollHeight". What I found on some microsoft site is, that those values should be set as an integer (-32767 to 32767), but I tried to create some button with it's "TOP" value set as 50'000 (manually, in PROPERTIES window, not with VBA code) and it works correctly. So the problem is that I can't change the type of the "top" parameter.

I hope you have some idea :-)

Originally Posted by romperstomper

How is genForms declared?

Dim genForms As Integer
Dim genFrame As Object

but 'genForms' stands for "generate forms" and this number is nearly 1200, so there's no way to be even close to Integer limit. The problem is with ".top" value and "scrollHeight" which generate "overflow" error when the value reaches 32767.

Originally Posted by Norie

Try flipping the calculation.

.Top = (genForms * 40) - 40

Unfortunately it doesn't helped.

Originally Posted by Norie

This might be the problem.

Dim genForms As Integer

Try changing Integer to Long.

PS Sorry for another question, where's the code that adds the frames?

Good question :-)
This will be probably the topis of my next problem, but I'm trying to find it by myself since 5 days and I hope to find it. The general problem is, that the below code (already quoted above):

'generate new controls - frames, labels, buttons and so on.
For genForms = 1 To NumberOfClients 'NumberOfClients
    Set genFrame = FrameList.Controls.Add("Forms.CommandButton.1")
With genFrame
.Name = "claim" & genForms
.Caption = "nr " & genForms
.Top = (40 * genForms) - 40
.Left = 0
.Height = 40
.Width = 988
End With
Next genForms
FrameList.ScrollHeight = genForms * 40

should generate frames. Because I couldn't find how to get rid of another error (excel hangs out on the line with creating frames) I change it to create buttons and it works:

Set genFrame = FrameList.Controls.Add("Forms.Frame.1")

if I change "FrameList.Controls.Add" (which should create those client frames in the main frame named: "FrameList") to "Me.Controls.Add" it works fine, but it draws those frames on the main user form, not inside this frame I created.

Dear Norie,
Like I said - thank you for your commitment and contribution in my project, but I should be the one in this topic to ask questions. But for clarification:
1. Variable declarations are on the top of the code, which is quite long (both file and the list of declarations),
2. Yes, there is. I know how to prepare user form in EXCEL, but unfortunately I couldn't start to prepare forms because of lack of the tools to pick up labels, textboxes and so.
3. Not everyone in my company has access installed. But this is the second reason why I do it in excel. The first one is the above one.
4. I don't care about access search tools. They are easy to do in excel which I already know. As I said in point '2'.
5. some other stuff ......

Is any of my above explanations helpful for solving my problem?

I find your tone off, and doesn't really help your cause - everyone helps for free here and Norie's questions were relevant; I suspect your issue is one of implicit variable casting, try the below:

.Top = (clng(40) * clng(genForms)) - 40

Originally Posted by Kyle123

I find your tone off, and doesn't really help your cause - everyone helps for free here and Norie's questions were relevant; I suspect your issue is one of implicit variable casting, try the below:

.Top = (clng(40) * clng(genForms)) - 40

THIS IS IT!! It helped.
I didn't know this function cLng() for changing it to Long type. I also implemented it to the "scrollHeight" and now it work like expected.
Sorry about my tone, but I didn't fine questions about "why in excel" helpful. Therefore I know the 'hidden reason' of those questions and I appreciate for your help. I hope I didn't offended anyone and thank you for spending your time on my issue.

I can even give you the whole piece of code for this one window, but it's irrelevant for this solution, and there are many comments in strange (polish) language. It includes one time run option for importing data from excel to access. And ones again - it couldn't be done with access automate option because of different formats, improper values and so on, so there was much to do with those data:

Option Explicit

Dim computerName As String
Dim userName As String
Dim databaseLocation As String
Dim fileWithDatabase As String
Dim currentWorkbook As String
Dim dataSheet As String
Dim rs As New ADODB.Recordset
Dim connDB As New ADODB.Connection
Dim strSQL As String
Dim NumberOfClaims As Integer
Dim LiczbaWierszyWPliku As Integer 'import
Dim importName As String 'import
Dim xlSourceApp As New Excel.Application 'import
Dim sourceWb As Workbook 'import

Dim doClip As DataObject
Dim zakladka As String 'import
Dim daneDoImportu() As String 'import
Dim iImport As Integer 'import
Dim iKolumna As Integer 'import
Dim iEksport As Integer 'import
Dim importSQL As String 'import
Dim ubezpString() As String 'import
Dim poszkString() As String 'import
Dim adresSzkodyString() As String 'import
Dim i As Integer ' import
Dim adresZeStringa As String 'import
Dim azs As Integer 'import (do powyższego stringa)

Dim tempDate As String 'import
Dim nrSzkody As String 'import
Dim nrFufi As String 'import
Dim nrPolisy As String 'import
Dim ochrOd As Variant 'import
Dim ochrDo As Variant 'import
Dim agentNr As String 'import
Dim agentNazw As String 'import
Dim koordNr As String 'import
Dim koordNazw As String 'import
Dim ubezpNazw As String 'import
Dim ubezpAdres As String 'import
Dim ubezpTel As String 'import
Dim poszkNazw As String 'import
Dim poszkAdres As String 'import
Dim poszkTel As String 'import
Dim zglaszajacySzkode As String 'import
Dim szkodaPowst As Variant 'import
Dim szkodaUjaw As Variant 'import
Dim szkodaZglo As Variant 'import
Dim szkodaAdres As String 'import
Dim przedmUbezp As String 'import
Dim klauzula As String 'import
Dim ryzyko As String 'import
Dim klasaRach As String 'import
Dim grupaUbezp As String 'import
Dim opisSzkody As String 'import
Dim przyczSzkody As String 'import
Dim rezerwa As Double 'import
Dim rezerwaTechn As Double 'import
Dim likwProw As String 'import
Dim dataWyplOdm As Variant 'import
Dim status As String 'import
Dim kwotaWypl As Double 'import
Dim rezerwaAkt As Double 'import
Dim rozwiazaneRezerwy As Double 'import
Dim kosztyTechnDataWypl As Variant 'import
Dim kosztyTechnKwota As Double 'import
Dim regres As String 'import
Dim uwagi As String 'import
Dim rejestrujacy As String 'import
Dim dataRejestracji As Variant 'import
Dim IPkomputera As String 'import

Dim genForms As Integer
Dim genFrame As Object

Private Sub btnNewClaim_Click()
FormRegisterClaim.Show
End Sub

Private Sub CommandButton1_Click()

'liczenie ilości szkód w istniejącym rejestrze szkód
importName = Application.GetOpenFilename("Excel Workbooks (*.xls*), *.xls*")
zakladka = "REJESTR SZKÓD"
Set xlSourceApp = GetObject(, "Excel.Application")
Set sourceWb = xlSourceApp.Workbooks.Open(importName)
LiczbaWierszyWPliku = sourceWb.Worksheets(zakladka).Range("C1", Range("C1").End(xlDown)).Count 'liczenie liczby szkód (odliczony nagłówek)

'sczytywanie danych z excela do tablicy, a potem eksport do accessa
ReDim daneDoImportu(LiczbaWierszyWPliku - 1, 33)

iImport = 0
iKolumna = 0
'przeniesienie danych do tablicy
For iImport = 2 To LiczbaWierszyWPliku
    For iKolumna = 1 To 33
        daneDoImportu(iImport - 1, iKolumna) = sourceWb.Worksheets(zakladka).Cells(iImport, iKolumna)
    Next iKolumna
Next iImport

sourceWb.Close SaveChanges:=False
Set xlSourceApp = Nothing

iEksport = 0
iKolumna = 0
'przeniesienie danych do accessa

[cut]
MsgBox ("export done")

End Sub

Private Sub UserForm_Initialize() 'inicjalizacja formularza - ustawienie i sprawdzenie danych
GetEnvData
If userName = "john" Or userName = "mark" Or userName = "ana" Then 'funkcja dostępna tylko dla "adminów"
    MultiPage.Page2.Visible = True
    GenerateClaimList
    Else
    MultiPage.Page2.Visible = False
End If

textbox_database.Text = databaseLocation ' pole z linkiem do pliku bazodanowego

CheckDatabaseConnection (databaseLocation)

End Sub

Private Sub GenerateClaimList()
strSQL = "SELECT COUNT(*) as NoClaims FROM SZKODY"
ConnectDB (databaseLocation)
Set rs = connDB.Execute(strSQL)
NumberOfClaims = rs!NoClaims
DisconnectDB

'MsgBox (NumberOfClaims)

'generate new controls - frames, labels, buttons and so on.
For genForms = 1 To 2000 'NumberOfClaims
    Set genFrame = FrameListaSzkod.Controls.Add("Forms.CommandButton.1")
With genFrame
.Name = "claim" & genForms
.Caption = "nr " & genForms
.Top = (genForms * 40) - 40
.Left = 0
.Height = 40
.Width = 988
End With
Next genForms
FrameListaSzkod.ScrollHeight = genForms * 40

End Sub

Private Sub UserForm_Terminate()
DisconnectDB
End Sub


Sub GetEnvData() 'pobranie (ustawienie) zmiennych środowiskowych wymaganych do pracy programu
computerName = Environ$("computername")
userName = Environ$("username")
currentWorkbook = ThisWorkbook.Path & "\" & ThisWorkbook.Name
dataSheet = "envData"
databaseLocation = Sheets(dataSheet).Cells(2, 1)
End Sub


Private Sub ConnectDB(dbLocation)
connDB.Open ConnectionString:="Provider = Microsoft.ACE.OLEDB.12.0;  Persist Security Info = False; data source=" & dbLocation & ";"
Set rs = New ADODB.Recordset
'rs.Open Source:="szkody", ActiveConnection:=connDB, CursorType:=adOpenStatic, LockType:=adLockOptimistic


End Sub

Private Sub DisconnectDB()
'close the objects (jeśli istnieją)
If rs.State = 1 Then
    rs.Close
    connDB.Close
End If
'destroy the variables
Set rs = Nothing
Set connDB = Nothing

End Sub

Private Sub btn_acceptDatabase_Click() 'potwierdzenie (zapisanie) lokalizacji pliku bazodanowego
Worksheets(dataSheet).Range("A2").Value = textbox_database.Text
ThisWorkbook.Save 'SaveChanges:=True
CheckDatabaseConnection (databaseLocation)
End Sub

Private Sub btnSelectDatabase_Click() 'wybranie pliku bazodanowego z eksploratora windows
fileWithDatabase = Application.GetOpenFilename("Excel Workbooks (*.accdb*), *.accdb*")
textbox_database.Text = fileWithDatabase
If textbox_database.Text <> "False" Then
btn_acceptDatabase.Visible = True
Else
btn_acceptDatabase.Visible = False
End If
databaseLocation = fileWithDatabase
End Sub

Private Sub CheckDatabaseConnection(dbLoc) ' sprawdzenie, czy plik bazodanowy ACCESS istnieje.
If Dir(dbLoc) <> "" Then
    lblDbStatus.Caption = "OK"
    lblDbStatus.BackColor = RGB(0, 200, 0)
    lblDbStatus.ForeColor = RGB(0, 0, 0)
    frameClaims.Visible = True
    ConnectDB (dbLoc)
Else
    lblDbStatus.Caption = "BŁĄD!"
    lblDbStatus.BackColor = RGB(255, 0, 0)
    lblDbStatus.ForeColor = RGB(255, 255, 255)
    frameClaims.Visible = False
End If
End Sub

because of too many characters in my code I had to cut a big part of it, which is irrelevant to the problem.

Originally Posted by Norie

Change every declaration of Integer to Long.

Already did. Unfortunately (for me) I knew that I'm multiplicating integers, but I didn't know that two multiplicated integers gives also an integer. I hoped that the value will be automatically changed into 'Long', because I counld't find how I may change the type of '.top' variable.

Thank you once again for your commitment :-) :-) . I got me very angry I couldn't find this answer by myself.

Did you read the suggestions? Top isn't the problem, the problem is that you are multiplying 2 integers, the result of which is out of bounds for an integer so you get an overflow error. To resolve it at least one number in the multiplication must be a long

Originally Posted by Norie

So when you change Forms.CommandButton.1 to Forms.Frame.1 it adds to the form, not the frame FrameList?

No.
changing "FrameList.Controls.Add("Forms.CommandButton.1")" to "FrameList.Controls.Add("Forms.Frame.1")" should generate frames in the bigger, 'main' frame called "FrameList", but instead of this it hangs the excel and it restarts (with the message, that it stoped working and it is lookding for solution).
But if I change "FrameList.Controls.Add("Forms.Frame.1")" to "Me.Controls.Add("Forms.Frame.1")" it creates frames but in the main user form.

Wow...
It really works! I can see what's going on, but I even didn't think that way! :-)

Thank yoU!!

Now when I see how slow is my solution, I have to re-design my app and get rid of few of my ideas. Thank you for you help - it really show me the weak side of my idea.