Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Adding custom controls to frame to slow?

0

Hello,

When i add custom controls to my Frame1 it takes forever and it slow when i scroll it

any idea how to solve it?

Private Sub CreateFinished(Top As Integer, id As Integer, artnumber As String, titel As String,Price as double, myid As Integer)
Dim pathToPicture As String
pathToPicture = ThisWorkbook.Path & "\emp1.bmp"
Dim idString As String
Dim ccont1000 As MSForms.Frame
Set ccont1000 = Frame1.Controls.Add("Forms.Frame.1")
   idString = myid
With ccont1000
.Width = Frame1.Width - 18
.Height = 60
.Top = Top - 0.1
.Left = 2
.BorderStyle = fmBorderStyleNone
.SpecialEffect = fmSpecialEffectFlat
.Name = idString '"Control" & id
.BackColor = RGB(47, 59, 71)
.MousePointer = fmMousePointerCustom
End With
'=CheckBox
Dim cCont0 As MSForms.CheckBox
Set cCont0 = ccont1000.Controls.Add _
   ("Forms.CheckBox.1")
With cCont0
   .Name = "CheckBox"
   .Left = 10
   .Top = 18
   .BackColor = RGB(255, 255, 255)
   .BackStyle = fmBackStyleTransparent
   .ForeColor = RGB(255, 255, 255)
   .Tag = idString
   .Locked = True
   .MousePointer = fmMousePointerCustom
  End With
'=Artnumber
Dim cCont1 As MSForms.Label
Set cCont1 = ccont1000.Controls.Add _
   ("Forms.Label.1")
With cCont1
   .Name = idString
   .Width = 72
   .Height = 39
   .Left = 34
   .Top = 10
   .Caption = vbCrLf & artnumber
   .TextAlign = fmTextAlignCenter
   .BorderStyle = fmBorderStyleNone
   .BackColor = RGB(255, 255, 255)
   .BackStyle = fmBackStyleTransparent
   .ForeColor = RGB(255, 255, 255)
   .Tag = idString
   .MousePointer = fmMousePointerCustom
  End With
'=photo
Dim fileName As String
Dim cCont2 As MSForms.Image
Set cCont2 = ccont1000.Controls.Add _
   ("Forms.Image.1")
With cCont2
fileName = pathToPicture 
Application.DisplayAlerts = False
   .Picture = LoadPicture(fileName)
   .Name = idString
   .Tag = fileName
   .Width = 48
   .Height = 48
   .Left = 125
   .Top = 7
   .MousePointer = fmMousePointerCustom
   .PictureSizeMode = fmPictureSizeModeStretch
End With
Dim cCont100 As MSForms.Image
Set cCont100 = ccont1000.Controls.Add _
   ("Forms.Image.1")
With cCont100
   .Name = idString
   .Tag = fileName
   .Picture = circuitButtonsForm.Label540.Picture
   .PictureSizeMode = fmPictureSizeModeStretch
   .BackStyle = fmBackStyleTransparent
   .Width = 48
   .Height = 52
   .Left = 125
   .Top = 7
   .ControlTipText = titel
   .MousePointer = fmMousePointerCustom
   End With
'=Price
Dim cCont4 As MSForms.Label
Set cCont4 = ccont1000.Controls.Add _
   ("Forms.Label.1")
With cCont4
   .Name = idString
   .Width = 60
   .Height = 39
   .Left = 185
   .Top = 10
   .Caption = vbCrLf & FormatCurrency(price, 2)
   .TextAlign = fmTextAlignCenter
   .BorderStyle = fmBorderStyleNone
   .BackColor = RGB(255, 255, 255)
   .BackStyle = fmBackStyleTransparent
   .ForeColor = RGB(255, 255, 255)
   .Tag = idString
   .MousePointer = fmMousePointerCustom
  End With
End Sub

Frame1.ScrollTop = 0
For index = 0 To ListBoxHistory.ListCount - 1
      Call CreateFinished(4 + (65 * index), index + 1, ListBoxHistory.List(index, 1), ListBoxHistory.List(index, 8),  Format(ListBoxHistory.List(index, 2)), ListBoxHistory.List(index, 0)) 
Next index

Thank you.

excel
MS Office Version: 2017
posted by: GhostofWanted (rep: 46) on Mon Aug 24, 2020 at 2:11 pm
Answer
Discuss

Discussion

Where are the frames? In a user form? On a worksheet? How big is ListBoxHistory.ListCount? Frame1.ScrollTop = 0 indicates that at least one frame already exists before the start. How many frames do already exist?
Variatus (rep: 4889) Aug 24, '20 at 9:23 pm
Frame1 are in a userform.
the listcount count now 15
just 1 frame is used in the userform
and 1 frame is just inside the frame
Dim ccont1000 As MSForms.Frame
Set ccont1000 = Frame1.Controls.Add("Forms.Frame.1")
GhostofWanted (rep: 46) Aug 24, '20 at 10:04 pm
It's true that sub CreateFinished cretes only one Frame. But it's also true that CreateFinished is called 15 times. Doesn't that add up?
Variatus (rep: 4889) Aug 25, '20 at 3:37 am
Yes thats true
Because it read the listbox rows till the end. And we create createfinished everytime because those are the controls we create.
GhostofWanted (rep: 46) Aug 25, '20 at 4:34 am
But then you have 15 or 16 frames, each with a number of controls, all in the same position so that you can't see any but the last one, and that would drastically slow down operations. Why not create a frame once and re-use the same controls to display different things? If this isn't the solution to your problem please post your workbook so that we can get a better understanding of what you're wanting to do.
Variatus (rep: 4889) Aug 25, '20 at 8:06 pm
Hello Variatus,

I added the workbook for you all
hope someone can find the solution.
thank you

'Updated the workbook'
GhostofWanted (rep: 46) Aug 25, '20 at 10:16 pm
Ghost, you give me a devolved and "simplified" copy of a messy project that doesn't even load. It requires a complete review which I can't do because I don't know what the final picture should look like. Your expectations of my ability are too high. Thank you. I could show you how to structure your code but I fear that isn't what you want because a better structure with properly named variables will just make your code reviewable but does nothing to solve your immediate problem. I think I have to give up on this. Sorry.
Variatus (rep: 4889) Aug 26, '20 at 8:24 pm
Hello Variatus,
Please tell me what i can do.
So you still can helpme out.
Because i like to solved it.

I have added an updated workbook.
And made it run when you start the workbook.
Also i added some descriptions in the UserForm1 Code.
And added some previews how it should looks after running.
On the Sample Image Preview Sheet.

Hope this will be better.
GhostofWanted (rep: 46) Aug 26, '20 at 10:16 pm
Yes. That's better. Please add a picture showing the listbox. Note that a ListBox shows all list items. Therefore it must be of variable size in this case. A ComboBox would have the same content but takes only the space of one item because the list is in a drop-down. I wonder whether you want the listbox at all or just use it to get data from Sheet3 into the user form. There are better ways for that.
Variatus (rep: 4889) Aug 27, '20 at 7:59 am
Hello,
'updated' the file again.

I have added the listbox but it won't show all because screen is to small
but the important parts are visible. i don't use any sizes on the listbox
it just load with default sizes i suppose. sorry.

Listbox is also On the Sample Image Preview Sheet.

I just need to get the data from sheet3
if you can teach me the better way instead of using a listbox, please will you?
if everything can be better also i love to know how
so it will be faster running and all sort of things
Thank you for your time.
GhostofWanted (rep: 46) Aug 27, '20 at 11:25 am
Add to Discussion

Answers

0
Selected Answer

Let's try the attached workbook for size. I have done away with your listbox and radically changed the project's layout but preserved most of your properties. Functionality should be the way you wanted. Try it.

The problem with your original code was naming. Your code just kept on creating controls by the same names. It's a miracle that it didn't crash. Anyway, I didn't bother to check the exact reasons and just wrote it new.

An associated matter is that you assigned control names to some controls' Tag property. I didn't follow because I don't know what exactly you assigned or what you intended with those tags. My code is very transparent - once you get the hang of it - and you should find it easy to insert the assignments back where and how you need them.

posted by: Variatus (rep: 4889) on Thu Aug 27, 2020 at 12:39 pm
Discuss

Discussion

Hello,

First and foremost WOW, this is completely different from my code. This will take a while before we master this new structure. and indeed it works much faster so no more listbox And to answer your question the TAG is used when clicking the frame so I can remove it if necessary but we had not inserted that code yet because the refresh of the frame was not easy to renew. because then got holes where the selected frames were.   But really wow, thank you for this brand new way of working. So thank you again very much really great. then just play around with the code. if there is anything else I will come back to this if necessary.  
Question: Do you know how we can remove selected control and not have empty places? this was my code

Dim Ctrl As Control
    For Each Ctrl InFrame1.Controls
        If EndsWith(ObjectName, Ctrl.Name) Then
            If Ctrl.Name = ObjectName Then
Frame1.Controls.Remove Ctrl.name
                Exit For
            End If
        End If
    Next
GhostofWanted (rep: 46) Aug 27, '20 at 12:59 pm
I'm glad you found your way in my code so quickly. The frames are numbered from 1 and up. So you can always find a missing one or know everyone's position if you know the name. If you remove #03 Frame04 and up must each be moved up by 66 pt and the FmMain size reduced by the same measure if the remaining number is >4. The more difficult part will be to trigger the procedure. You may need another class module for that. I suggest you ask another question for that since the "discussion" in this one is already over limits :-)
Variatus (rep: 4889) Aug 27, '20 at 8:10 pm
Hello Variatus,

hahha yeah, you will be right. And probably just start a new topic. But indeed it is searching and puzzling for the right subs to make, but it going already pretty good I think of course I have to add some other subs but in any case it is already much faster thank you again for teaching me this new way of code.
GhostofWanted (rep: 46) Aug 28, '20 at 12:44 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login