Excel VBA Procedure is too large UserForm












1















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









share|improve this question


















  • 1





    Use loops? Your label numbers and ranges appear to go in sequence.

    – SJR
    Nov 23 '18 at 11:54











  • Any the most efficient LOOP example for my situation?

    – user7202022
    Nov 23 '18 at 12:08











  • + Instead of using a lot of labels on different Tabs on your multipage, you could re-use your Labels.

    – EvR
    Nov 23 '18 at 12:13











  • I had in mind @pspl's answer.

    – SJR
    Nov 23 '18 at 13:22











  • See some hints to reduce KB limitation Getting error .. Too Large in VBA macros Excel

    – T.M.
    Nov 24 '18 at 17:42
















1















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









share|improve this question


















  • 1





    Use loops? Your label numbers and ranges appear to go in sequence.

    – SJR
    Nov 23 '18 at 11:54











  • Any the most efficient LOOP example for my situation?

    – user7202022
    Nov 23 '18 at 12:08











  • + Instead of using a lot of labels on different Tabs on your multipage, you could re-use your Labels.

    – EvR
    Nov 23 '18 at 12:13











  • I had in mind @pspl's answer.

    – SJR
    Nov 23 '18 at 13:22











  • See some hints to reduce KB limitation Getting error .. Too Large in VBA macros Excel

    – T.M.
    Nov 24 '18 at 17:42














1












1








1








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









share|improve this question














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






excel vba excel-vba






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Nov 23 '18 at 11:48









user7202022user7202022

361111




361111








  • 1





    Use loops? Your label numbers and ranges appear to go in sequence.

    – SJR
    Nov 23 '18 at 11:54











  • Any the most efficient LOOP example for my situation?

    – user7202022
    Nov 23 '18 at 12:08











  • + Instead of using a lot of labels on different Tabs on your multipage, you could re-use your Labels.

    – EvR
    Nov 23 '18 at 12:13











  • I had in mind @pspl's answer.

    – SJR
    Nov 23 '18 at 13:22











  • See some hints to reduce KB limitation Getting error .. Too Large in VBA macros Excel

    – T.M.
    Nov 24 '18 at 17:42














  • 1





    Use loops? Your label numbers and ranges appear to go in sequence.

    – SJR
    Nov 23 '18 at 11:54











  • Any the most efficient LOOP example for my situation?

    – user7202022
    Nov 23 '18 at 12:08











  • + Instead of using a lot of labels on different Tabs on your multipage, you could re-use your Labels.

    – EvR
    Nov 23 '18 at 12:13











  • I had in mind @pspl's answer.

    – SJR
    Nov 23 '18 at 13:22











  • See some hints to reduce KB limitation Getting error .. Too Large in VBA macros Excel

    – T.M.
    Nov 24 '18 at 17:42








1




1





Use loops? Your label numbers and ranges appear to go in sequence.

– SJR
Nov 23 '18 at 11:54





Use loops? Your label numbers and ranges appear to go in sequence.

– SJR
Nov 23 '18 at 11:54













Any the most efficient LOOP example for my situation?

– user7202022
Nov 23 '18 at 12:08





Any the most efficient LOOP example for my situation?

– user7202022
Nov 23 '18 at 12:08













+ Instead of using a lot of labels on different Tabs on your multipage, you could re-use your Labels.

– EvR
Nov 23 '18 at 12:13





+ Instead of using a lot of labels on different Tabs on your multipage, you could re-use your Labels.

– EvR
Nov 23 '18 at 12:13













I had in mind @pspl's answer.

– SJR
Nov 23 '18 at 13:22





I had in mind @pspl's answer.

– SJR
Nov 23 '18 at 13:22













See some hints to reduce KB limitation Getting error .. Too Large in VBA macros Excel

– T.M.
Nov 24 '18 at 17:42





See some hints to reduce KB limitation Getting error .. Too Large in VBA macros Excel

– T.M.
Nov 24 '18 at 17:42












3 Answers
3






active

oldest

votes


















2














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





share|improve this answer


























  • Thank you! However NOTE that in the first column A there are 16 items and all the rest 17.

    – user7202022
    Nov 26 '18 at 13:45











  • @user7202022, Answers were added.

    – Dy.Lee
    Nov 26 '18 at 14:00













  • Your code looks the most interesting implementation. For some reason I can't get it work. P.S. There is "i" outside of "code" tag in your last edit. I have added it in my macro but for some reason it still not working. Getting an error all the time

    – user7202022
    Nov 26 '18 at 15:04











  • @user7202022, I mistyped " a & (i) & 109 ". This to be a(i) & 109.

    – Dy.Lee
    Nov 26 '18 at 15:43



















2














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.






share|improve this answer


























  • Thank you for your answer! Can you please add example for two blocks like Label841-Label856 and Label875-Label891? Thank you!

    – user7202022
    Nov 23 '18 at 13:53



















0














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






share|improve this answer
























    Your Answer






    StackExchange.ifUsing("editor", function () {
    StackExchange.using("externalEditor", function () {
    StackExchange.using("snippets", function () {
    StackExchange.snippets.init();
    });
    });
    }, "code-snippets");

    StackExchange.ready(function() {
    var channelOptions = {
    tags: "".split(" "),
    id: "1"
    };
    initTagRenderer("".split(" "), "".split(" "), channelOptions);

    StackExchange.using("externalEditor", function() {
    // Have to fire editor after snippets, if snippets enabled
    if (StackExchange.settings.snippets.snippetsEnabled) {
    StackExchange.using("snippets", function() {
    createEditor();
    });
    }
    else {
    createEditor();
    }
    });

    function createEditor() {
    StackExchange.prepareEditor({
    heartbeatType: 'answer',
    autoActivateHeartbeat: false,
    convertImagesToLinks: true,
    noModals: true,
    showLowRepImageUploadWarning: true,
    reputationToPostImages: 10,
    bindNavPrevention: true,
    postfix: "",
    imageUploader: {
    brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
    contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
    allowUrls: true
    },
    onDemand: true,
    discardSelector: ".discard-answer"
    ,immediatelyShowMarkdownHelp:true
    });


    }
    });














    draft saved

    draft discarded


















    StackExchange.ready(
    function () {
    StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53446159%2fexcel-vba-procedure-is-too-large-userform%23new-answer', 'question_page');
    }
    );

    Post as a guest















    Required, but never shown

























    3 Answers
    3






    active

    oldest

    votes








    3 Answers
    3






    active

    oldest

    votes









    active

    oldest

    votes






    active

    oldest

    votes









    2














    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





    share|improve this answer


























    • Thank you! However NOTE that in the first column A there are 16 items and all the rest 17.

      – user7202022
      Nov 26 '18 at 13:45











    • @user7202022, Answers were added.

      – Dy.Lee
      Nov 26 '18 at 14:00













    • Your code looks the most interesting implementation. For some reason I can't get it work. P.S. There is "i" outside of "code" tag in your last edit. I have added it in my macro but for some reason it still not working. Getting an error all the time

      – user7202022
      Nov 26 '18 at 15:04











    • @user7202022, I mistyped " a & (i) & 109 ". This to be a(i) & 109.

      – Dy.Lee
      Nov 26 '18 at 15:43
















    2














    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





    share|improve this answer


























    • Thank you! However NOTE that in the first column A there are 16 items and all the rest 17.

      – user7202022
      Nov 26 '18 at 13:45











    • @user7202022, Answers were added.

      – Dy.Lee
      Nov 26 '18 at 14:00













    • Your code looks the most interesting implementation. For some reason I can't get it work. P.S. There is "i" outside of "code" tag in your last edit. I have added it in my macro but for some reason it still not working. Getting an error all the time

      – user7202022
      Nov 26 '18 at 15:04











    • @user7202022, I mistyped " a & (i) & 109 ". This to be a(i) & 109.

      – Dy.Lee
      Nov 26 '18 at 15:43














    2












    2








    2







    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





    share|improve this answer















    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






    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Nov 26 '18 at 15:42

























    answered Nov 23 '18 at 15:12









    Dy.LeeDy.Lee

    3,6721511




    3,6721511













    • Thank you! However NOTE that in the first column A there are 16 items and all the rest 17.

      – user7202022
      Nov 26 '18 at 13:45











    • @user7202022, Answers were added.

      – Dy.Lee
      Nov 26 '18 at 14:00













    • Your code looks the most interesting implementation. For some reason I can't get it work. P.S. There is "i" outside of "code" tag in your last edit. I have added it in my macro but for some reason it still not working. Getting an error all the time

      – user7202022
      Nov 26 '18 at 15:04











    • @user7202022, I mistyped " a & (i) & 109 ". This to be a(i) & 109.

      – Dy.Lee
      Nov 26 '18 at 15:43



















    • Thank you! However NOTE that in the first column A there are 16 items and all the rest 17.

      – user7202022
      Nov 26 '18 at 13:45











    • @user7202022, Answers were added.

      – Dy.Lee
      Nov 26 '18 at 14:00













    • Your code looks the most interesting implementation. For some reason I can't get it work. P.S. There is "i" outside of "code" tag in your last edit. I have added it in my macro but for some reason it still not working. Getting an error all the time

      – user7202022
      Nov 26 '18 at 15:04











    • @user7202022, I mistyped " a & (i) & 109 ". This to be a(i) & 109.

      – Dy.Lee
      Nov 26 '18 at 15:43

















    Thank you! However NOTE that in the first column A there are 16 items and all the rest 17.

    – user7202022
    Nov 26 '18 at 13:45





    Thank you! However NOTE that in the first column A there are 16 items and all the rest 17.

    – user7202022
    Nov 26 '18 at 13:45













    @user7202022, Answers were added.

    – Dy.Lee
    Nov 26 '18 at 14:00







    @user7202022, Answers were added.

    – Dy.Lee
    Nov 26 '18 at 14:00















    Your code looks the most interesting implementation. For some reason I can't get it work. P.S. There is "i" outside of "code" tag in your last edit. I have added it in my macro but for some reason it still not working. Getting an error all the time

    – user7202022
    Nov 26 '18 at 15:04





    Your code looks the most interesting implementation. For some reason I can't get it work. P.S. There is "i" outside of "code" tag in your last edit. I have added it in my macro but for some reason it still not working. Getting an error all the time

    – user7202022
    Nov 26 '18 at 15:04













    @user7202022, I mistyped " a & (i) & 109 ". This to be a(i) & 109.

    – Dy.Lee
    Nov 26 '18 at 15:43





    @user7202022, I mistyped " a & (i) & 109 ". This to be a(i) & 109.

    – Dy.Lee
    Nov 26 '18 at 15:43













    2














    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.






    share|improve this answer


























    • Thank you for your answer! Can you please add example for two blocks like Label841-Label856 and Label875-Label891? Thank you!

      – user7202022
      Nov 23 '18 at 13:53
















    2














    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.






    share|improve this answer


























    • Thank you for your answer! Can you please add example for two blocks like Label841-Label856 and Label875-Label891? Thank you!

      – user7202022
      Nov 23 '18 at 13:53














    2












    2








    2







    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.






    share|improve this answer















    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.







    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Nov 23 '18 at 12:07

























    answered Nov 23 '18 at 11:56









    PsplPspl

    936417




    936417













    • Thank you for your answer! Can you please add example for two blocks like Label841-Label856 and Label875-Label891? Thank you!

      – user7202022
      Nov 23 '18 at 13:53



















    • Thank you for your answer! Can you please add example for two blocks like Label841-Label856 and Label875-Label891? Thank you!

      – user7202022
      Nov 23 '18 at 13:53

















    Thank you for your answer! Can you please add example for two blocks like Label841-Label856 and Label875-Label891? Thank you!

    – user7202022
    Nov 23 '18 at 13:53





    Thank you for your answer! Can you please add example for two blocks like Label841-Label856 and Label875-Label891? Thank you!

    – user7202022
    Nov 23 '18 at 13:53











    0














    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






    share|improve this answer




























      0














      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






      share|improve this answer


























        0












        0








        0







        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






        share|improve this answer













        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







        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 23 '18 at 12:38









        Harassed DadHarassed Dad

        3,3341612




        3,3341612






























            draft saved

            draft discarded




















































            Thanks for contributing an answer to Stack Overflow!


            • Please be sure to answer the question. Provide details and share your research!

            But avoid



            • Asking for help, clarification, or responding to other answers.

            • Making statements based on opinion; back them up with references or personal experience.


            To learn more, see our tips on writing great answers.




            draft saved


            draft discarded














            StackExchange.ready(
            function () {
            StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53446159%2fexcel-vba-procedure-is-too-large-userform%23new-answer', 'question_page');
            }
            );

            Post as a guest















            Required, but never shown





















































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown

































            Required, but never shown














            Required, but never shown












            Required, but never shown







            Required, but never shown







            這個網誌中的熱門文章

            Xamarin.form Move up view when keyboard appear

            Post-Redirect-Get with Spring WebFlux and Thymeleaf

            Anylogic : not able to use stopDelay()