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:

Please Login or Register  to view this content.

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 :-)

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?

Originally Posted by Norie

Try flipping the calculation.

Please Login or Register  to view this content.

Unfortunately it doesn't helped.

Originally Posted by romperstomper

How is genForms declared?

Please Login or Register  to view this content.

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.

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:

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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

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 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:

Please Login or Register  to view this content.

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.

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.

Originally Posted by Norie

This might be the problem.

Please Login or Register  to view this content.

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):

Please Login or Register  to view this content.

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:

Please Login or Register  to view this content.

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.

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.