Argthtjtr

I use below code to display calculation results in separate window to follow changes in live time. This is only short part of the code it continues down below for many rows down. I have long code and would like to make my UserForm to display summary reports in tabbed view (MultiPage). I can only create one tab with following code. When I try to add second "Tab" I get "Procedure is too large".

My code:

Controls("Label841").Caption = ThisWorkbook.Sheets("Price calculation").Range("A109").Value

Controls("Label842").Caption = ThisWorkbook.Sheets("Price calculation").Range("A110").Value

Controls("Label843").Caption = ThisWorkbook.Sheets("Price calculation").Range("A111").Value

Controls("Label844").Caption = ThisWorkbook.Sheets("Price calculation").Range("A112").Value

Controls("Label845").Caption = ThisWorkbook.Sheets("Price calculation").Range("A113").Value

Controls("Label846").Caption = ThisWorkbook.Sheets("Price calculation").Range("A114").Value

Controls("Label847").Caption = ThisWorkbook.Sheets("Price calculation").Range("A115").Value

Controls("Label848").Caption = ThisWorkbook.Sheets("Price calculation").Range("A116").Value

Controls("Label849").Caption = ThisWorkbook.Sheets("Price calculation").Range("A117").Value

Controls("Label850").Caption = ThisWorkbook.Sheets("Price calculation").Range("A118").Value

Controls("Label851").Caption = ThisWorkbook.Sheets("Price calculation").Range("A119").Value

Controls("Label852").Caption = ThisWorkbook.Sheets("Price calculation").Range("A120").Value

Controls("Label853").Caption = ThisWorkbook.Sheets("Price calculation").Range("A121").Value

Controls("Label854").Caption = ThisWorkbook.Sheets("Price calculation").Range("A122").Value

Controls("Label855").Caption = ThisWorkbook.Sheets("Price calculation").Range("A123").Value

Controls("Label856").Caption = ThisWorkbook.Sheets("Price calculation").Range("A124").Value



Controls("Label875").Caption = ThisWorkbook.Sheets("Price calculation").Range("D109").Value

Controls("Label876").Caption = ThisWorkbook.Sheets("Price calculation").Range("D110").Value

Controls("Label877").Caption = ThisWorkbook.Sheets("Price calculation").Range("D111").Value

Controls("Label878").Caption = ThisWorkbook.Sheets("Price calculation").Range("D112").Value

Controls("Label879").Caption = ThisWorkbook.Sheets("Price calculation").Range("D113").Value

Controls("Label880").Caption = ThisWorkbook.Sheets("Price calculation").Range("D114").Value

Controls("Label881").Caption = ThisWorkbook.Sheets("Price calculation").Range("D115").Value

Controls("Label882").Caption = ThisWorkbook.Sheets("Price calculation").Range("D116").Value

Controls("Label883").Caption = ThisWorkbook.Sheets("Price calculation").Range("D117").Value

Controls("Label884").Caption = ThisWorkbook.Sheets("Price calculation").Range("D118").Value

Controls("Label885").Caption = ThisWorkbook.Sheets("Price calculation").Range("D119").Value

Controls("Label886").Caption = ThisWorkbook.Sheets("Price calculation").Range("D120").Value

Controls("Label887").Caption = ThisWorkbook.Sheets("Price calculation").Range("D121").Value

Controls("Label888").Caption = ThisWorkbook.Sheets("Price calculation").Range("D122").Value

Controls("Label889").Caption = ThisWorkbook.Sheets("Price calculation").Range("D123").Value

Controls("Label890").Caption = ThisWorkbook.Sheets("Price calculation").Range("D124").Value

Controls("Label891").Caption = ThisWorkbook.Sheets("Price calculation").Range("D125").Value



Controls("Label911").Caption = ThisWorkbook.Sheets("Price calculation").Range("E109").Value

Controls("Label912").Caption = ThisWorkbook.Sheets("Price calculation").Range("E110").Value

Controls("Label913").Caption = ThisWorkbook.Sheets("Price calculation").Range("E111").Value

Controls("Label914").Caption = ThisWorkbook.Sheets("Price calculation").Range("E112").Value

Controls("Label915").Caption = ThisWorkbook.Sheets("Price calculation").Range("E113").Value

Controls("Label916").Caption = ThisWorkbook.Sheets("Price calculation").Range("E114").Value

Controls("Label917").Caption = ThisWorkbook.Sheets("Price calculation").Range("E115").Value

Controls("Label918").Caption = ThisWorkbook.Sheets("Price calculation").Range("E116").Value

Controls("Label919").Caption = ThisWorkbook.Sheets("Price calculation").Range("E117").Value

Controls("Label920").Caption = ThisWorkbook.Sheets("Price calculation").Range("E118").Value

Controls("Label921").Caption = ThisWorkbook.Sheets("Price calculation").Range("E119").Value

Controls("Label922").Caption = ThisWorkbook.Sheets("Price calculation").Range("E120").Value

Controls("Label923").Caption = ThisWorkbook.Sheets("Price calculation").Range("E121").Value

Controls("Label924").Caption = ThisWorkbook.Sheets("Price calculation").Range("E122").Value

Controls("Label925").Caption = ThisWorkbook.Sheets("Price calculation").Range("E123").Value

Controls("Label926").Caption = ThisWorkbook.Sheets("Price calculation").Range("E124").Value

Controls("Label927").Caption = ThisWorkbook.Sheets("Price calculation").Range("E125").Value

Try

Dim vDB As Variant, a As Variant, c As Variant

Dim Ws As Worksheet

Dim i As Integer, j As Integer, n As Integer

Set Ws = ThisWorkbook.Sheets("Price calculation")



a = Array("a", "d", "e") 'column characters

c = Array(841, 875, 911) 'label numbers



For i = LBound(a) To UBound(a)

    vDB = Ws.Range(a(i) & 109).Resize(16)

    n = 0

    For j = c(i) To c(i) + 15

        n = n + 1

        Me.Controls("Label" & j).Caption = vDB(n, 1)

    Next j

Next i

If the size of the data is different, one variable k will be given.

Dim vDB As Variant, a As Variant, c As Variant

Dim Ws As Worksheet

Dim i As Integer, j As Integer, n As Integer

Dim k As Integer



Set Ws = ThisWorkbook.Sheets("Price calculation")



a = Array("a", "d", "e") 'column characters

c = Array(841, 875, 911) 'label numbers



For i = LBound(a) To UBound(a)

    If i = 0 Then

        k = 16

    Else

        k = 17

    End If

    vDB = Ws.Range(a(i) & 109).Resize(k)

    n = 0

    For j = c(i) To c(i) + k - 1

        n = n + 1

        Me.Controls("Label" & j).Caption = vDB(n, 1)

    Next j

Next i

You could make your procedure a little bit smaller (and faster) by doing the follow:

With ThisWorkbook.Sheets("Price calculation")

    Controls("Label841").Caption = .Range("A109").Value

    Controls("Label842").Caption = .Range("A110").Value

    Controls("Label843").Caption = .Range("A111").Value

    ....



End With

Besides that, you can write the equivalent thing with a for... nextstatement. For instance, the first 16 lines of your code could be replaced with the following routine:

With ThisWorkbook.Sheets("Price calculation")

    For x = 841 to 856

        Controls("Label" & x).Caption = .Range("A" & x - 732).Value

    Next x

End With

or:

With ThisWorkbook.Sheets("Price calculation")

    For x = 841 to 856

        Controls("Label" & x).Caption = .Cells(x - 732, 1).Value

    Next x

End With

This kind of practice reduces A LOT your code size and makes your procedures A LOT faster.

Start with a blank form. Add a multipage control with a single page. Put this code into the UserForm Initialize event

Private Sub UserForm_Initialize()





Dim x As Integer: Dim y As Integer: Dim counter As Integer

Dim SourceRange As Range

Set SourceRange = ThisWorkbook.Sheets("Price calculation").Range("A109:A124")



Dim p As Control

Dim lab As Control

Const rowoffset = 20 'height of each row

Const startpoint = 60  'position of top row in tab

Const columnoffset = 3 'where next columns for captions are on spreadhseet

y = 12 'indent from left of form

Dim r As Range

For counter = 0 To 2

Set p = Me.MyMultiPage.Pages(counter)

x = startpoint

For Each r In SourceRange

Set lab = p.Controls.Add("Forms.Label.1")

lab.Left = y

lab.Top = x

lab.Width = 100

lab.Caption = r.Text

x = x + rowoffset

Next r

Set SourceRange = SourceRange.Offset(0, columnoffset)

If counter = Me.MyMultiPage.Pages.Count - 1 Then

    Me.MyMultiPage.Pages.Add "Page" & counter + 1, "Page" & counter + 1, counter + 1

End If



Next counter

End Sub

play with the constants till it looks pretty/fits in the form