+ Reply to Thread
Results 1 to 2 of 2

VBA Ok Click() If Statement Procedure Too Long

Hybrid View

  1. 08-22-2014, 06:38 AM #1
    LukeAM
    LukeAM is offline
    Registered User
    Join Date
    08-22-2014
    Location
    Sheffield, England
    MS-Off Ver
    Windows 7 Professional
    Posts
    20

    VBA Ok Click() If Statement Procedure Too Long

    Hi,

    I'm trying to create a workbook with a userform including tick boxes. And depending on what tick boxes are 'clicked' a new worksheet opens with information on the options clicked.

    However, there are to be at least 40 tick boxes and there is lots of information. I got so far before hitting the 'Procedure too large' error. I cannot figure out (even from looking at other forums on various websites including this one) how to overcome this problem. All the information for the IF statements for the tick boxes are based in the Private_Sub OK_Click(). Some code is below, but this thread can only be 15000 characters long. Before asking the text that I am wanting to input into the cells is not finished yet which is why there are only 2-3 words. I haven't used VBA for long and any answers would be very helpful and much appreciated. I hThanks


    Private Sub OK_Click()
 
Unload UserForm1
 
 Worksheets("Data").Cells(1, 1).Select
 'Worksheets("Data").Cells(2, 1). = ActiveCell
 Do While ActiveCell.Value <> Empty
 rowNr = rowNr + 1
 ActiveCell.Offset(1, 0).Select
 Loop
 'MsgBox rowNr
 rowNr = rowNr + 1
  
  
Worksheets("Data").Cells(2, 3) = CheckBox1.Value
Worksheets("Data").Cells(3, 3) = CheckBox2.Value
Worksheets("Data").Cells(4, 3) = CheckBox3.Value
Worksheets("Data").Cells(5, 3) = CheckBox4.Value
Worksheets("Data").Cells(6, 3) = CheckBox5.Value
Worksheets("Data").Cells(7, 3) = CheckBox6.Value
Worksheets("Data").Cells(8, 3) = CheckBox7.Value
Worksheets("Data").Cells(9, 3) = CheckBox8.Value
Worksheets("Data").Cells(10, 3) = CheckBox9.Value
Worksheets("Data").Cells(11, 3) = CheckBox10.Value
 
If CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then
Sheets.Add.Name = "TC1-40"
Worksheets("TC1-40").Cells(1, 1) = "No"
Worksheets("TC1-40").Cells(1, 2) = "T&C"
Worksheets("TC1-40").Range("A1:B1").Font.Bold = True
Worksheets("TC1-40").Range("A1:B1").Interior.Color = RGB(59, 179, 73)
Worksheets("TC1-40").Range("A1:A100").VerticalAlignment = xlCenter
Worksheets("TC1-40").Range("A1:A100").HorizontalAlignment = xlCenter
Worksheets("TC1-40").Range("B2:B52").WrapText = True
Worksheets("TC1-40").Range("A2:A3").MergeCells = True
Worksheets("TC1-40").Range("B1:J1").MergeCells = True
Worksheets("TC1-40").Range("B2:J2").MergeCells = True
Worksheets("TC1-40").Range("B3:J3").MergeCells = True
Worksheets("TC1-40").Range("B4:J4").MergeCells = True
Worksheets("TC1-40").Range("B5:J5").MergeCells = True
Worksheets("TC1-40").Range("B6:J6").MergeCells = True
Worksheets("TC1-40").Range("B7:J7").MergeCells = True
Worksheets("TC1-40").Range("B8:J8").MergeCells = True
Worksheets("TC1-40").Range("B9:J9").MergeCells = True
Worksheets("TC1-40").Range("B10:J10").MergeCells = True
Worksheets("TC1-40").Range("B11:J11").MergeCells = True
Worksheets("TC1-40").Range("B12:J12").MergeCells = True
Worksheets("TC1-40").Range("B13:J13").MergeCells = True
Worksheets("TC1-40").Range("B14:J14").MergeCells = True
Worksheets("TC1-40").Range("B15:J15").MergeCells = True
Worksheets("TC1-40").Range("B16:J16").MergeCells = True
Worksheets("TC1-40").Range("B17:J17").MergeCells = True
Worksheets("TC1-40").Range("B18:J18").MergeCells = True
Worksheets("TC1-40").Range("B19:J19").MergeCells = True
Worksheets("TC1-40").Range("B20:J20").MergeCells = True
Worksheets("TC1-40").Range("B21:J21").MergeCells = True
Worksheets("TC1-40").Range("B22:J22").MergeCells = True
Worksheets("TC1-40").Range("B23:J23").MergeCells = True
Worksheets("TC1-40").Range("B24:J24").MergeCells = True
Worksheets("TC1-40").Range("B25:J25").MergeCells = True
Worksheets("TC1-40").Range("B26:J26").MergeCells = True
Worksheets("TC1-40").Range("B27:J27").MergeCells = True
Worksheets("TC1-40").Range("B28:J28").MergeCells = True
Worksheets("TC1-40").Range("B29:J29").MergeCells = True
Worksheets("TC1-40").Range("B30:J30").MergeCells = True
Worksheets("TC1-40").Range("B31:J31").MergeCells = True
Worksheets("TC1-40").Range("B32:J32").MergeCells = True
Worksheets("TC1-40").Range("B33:J33").MergeCells = True
Worksheets("TC1-40").Range("B34:J34").MergeCells = True
Worksheets("TC1-40").Range("B35:J35").MergeCells = True
Worksheets("TC1-40").Range("B36:J36").MergeCells = True
Worksheets("TC1-40").Range("B37:J37").MergeCells = True
Worksheets("TC1-40").Range("B38:J38").MergeCells = True
Worksheets("TC1-40").Range("B39:J39").MergeCells = True
Worksheets("TC1-40").Range("B40:J40").MergeCells = True
Worksheets("TC1-40").Range("B41:J41").MergeCells = True
Worksheets("TC1-40").Range("B42:J42").MergeCells = True
Worksheets("TC1-40").Range("B43:J43").MergeCells = True
Worksheets("TC1-40").Range("B44:J44").MergeCells = True
Worksheets("TC1-40").Range("B45:J45").MergeCells = True
Worksheets("TC1-40").Range("B46:J46").MergeCells = True
Worksheets("TC1-40").Range("B47:J47").MergeCells = True
Worksheets("TC1-40").Range("B48:J48").MergeCells = True
Worksheets("TC1-40").Range("B49:J49").MergeCells = True
Worksheets("TC1-40").Range("B50:J50").MergeCells = True
Worksheets("TC1-40").Range("B51:J51").MergeCells = True
Worksheets("TC1-40").Range("B52:J52").MergeCells = True
Worksheets("TC1-40").Cells(2, 1) = "1"
Worksheets("TC1-40").Cells(2, 2) = "For the purposes of this subcontract..."
Worksheets("TC1-40").Range("B2:J2").RowHeight = 138.25
Worksheets("TC1-40").Range("B3") = "In accordance with..."
Worksheets("TC1-40").Range("B3:J3").RowHeight = 108.75
Worksheets("TC1-40").Cells(4, 1) = "2"
Worksheets("TC1-40").Range("B4") = "The subcontract..."
Worksheets("TC1-40").Range("B4:J4").RowHeight = 93
Worksheets("TC1-40").Cells(5, 1) = "3"
Worksheets("TC1-40").Range("B5") = "The subcontractor shall..."
Worksheets("TC1-40").Range("B5:J5").RowHeight = 76.5
Worksheets("TC1-40").Cells(6, 1) = "4"
Worksheets("TC1-40").Range("B6") = "The subcontractor shall..."
Worksheets("TC1-40").Range("B6:J6").RowHeight = 60.75
Worksheets("TC1-40").Cells(7, 1) = "5"
Worksheets("TC1-40").Range("B7") = "The subcontractor..."
Worksheets("TC1-40").Range("B7:J7").RowHeight = 60.75
Worksheets("TC1-40").Cells(8, 1) = "6"
Worksheets("TC1-40").Range("B8") = "The subcontract..."
Worksheets("TC1-40").Range("B8:J8").RowHeight = 94.5
Worksheets("TC1-40").Cells(9, 1) = "7"
Worksheets("TC1-40").Range("B9") = "You shall..."
Worksheets("TC1-40").Range("B9:J9").RowHeight = 30
Worksheets("TC1-40").Cells(10, 1) = "8"
Worksheets("TC1-40").Range("B10") = "It is our..."
Worksheets("TC1-40").Range("B10:J10").RowHeight = 60.75
Worksheets("TC1-40").Cells(11, 1) = "9"
Worksheets("TC1-40").Range("B11") = "Working hours shall..."
Worksheets("TC1-40").Range("B11:J11").RowHeight = 60.75
Worksheets("TC1-40").Cells(12, 1) = "10"
Worksheets("TC1-40").Range("B12") = "You are..."
Worksheets("TC1-40").Range("B12:J12").RowHeight = 29.25
Worksheets("TC1-40").Cells(13, 1) = "11"
Worksheets("TC1-40").Range("B13") = "During the execution..."
Worksheets("TC1-40").Range("B13:J13").RowHeight = 45
Worksheets("TC1-40").Cells(14, 1) = "12"
Worksheets("TC1-40").Range("B14") = "The subcontract rates..."
Worksheets("TC1-40").Range("B14:J14").RowHeight = 59.25
Worksheets("TC1-40").Cells(15, 1) = "13"
Worksheets("TC1-40").Range("B15") = "The subcotnractor shall..."
Worksheets("TC1-40").Range("B15:J15").RowHeight = 74.25
Worksheets("TC1-40").Cells(16, 1) = "14"
Worksheets("TC1-40").Range("B16") = "The subcontract rates..."
Worksheets("TC1-40").Range("B16:J16").RowHeight = 29.25
Worksheets("TC1-40").Cells(17, 1) = "15"
Worksheets("TC1-40").Range("B17") = "Except where..."
Worksheets("TC1-40").Range("B17:J17").RowHeight = 132.75
Worksheets("TC1-40").Cells(18, 1) = "16"
Worksheets("TC1-40").Range("B18") = "The subcontractor..."
Worksheets("TC1-40").Range("B18:J18").RowHeight = 60
Worksheets("TC1-40").Cells(19, 1) = "17"
Worksheets("TC1-40").Range("B19") = "The subcontractor..."
Worksheets("TC1-40").Range("B19:J19").RowHeight = 30
Worksheets("TC1-40").Cells(20, 1) = "18"
Worksheets("TC1-40").Range("B20") = "All documents..."
Worksheets("TC1-40").Range("B20:J20").RowHeight = 44.25
Worksheets("TC1-40").Cells(21, 1) = "19"
Worksheets("TC1-40").Range("B21") = "The subcontractor..."
Worksheets("TC1-40").Range("B21:J21").RowHeight = 29.25
Worksheets("TC1-40").Cells(22, 1) = "20"
Worksheets("TC1-40").Range("A22:A32").MergeCells = True
Worksheets("TC1-40").Range("B22") = "In line 1 of..."
Worksheets("TC1-40").Range("B22:J22").RowHeight = 77.25
Worksheets("TC1-40").Range("B23") = "Insert New Clause..."
Worksheets("TC1-40").Range("B23:J23").RowHeight = 96
Worksheets("TC1-40").Range("B24") = "At the beginning..."
Worksheets("TC1-40").Range("B24:J24").RowHeight = 21
Worksheets("TC1-40").Range("B25") = "At the end..."
Worksheets("TC1-40").Range("B25:J25").RowHeight = 36.75
Worksheets("TC1-40").Range("B26") = "Insert new..."
Worksheets("TC1-40").Range("B26:J26").RowHeight = 66.75
Worksheets("TC1-40").Range("B27") = "50.6.1 the..."
Worksheets("TC1-40").Range("B27:J27").RowHeight = 32.25
Worksheets("TC1-40").Range("B28") = "50.6.2 the..."
Worksheets("TC1-40").Range("B28:J28").RowHeight = 50.25
Worksheets("TC1-40").Range("B29") = "50.6.3 no..."
Worksheets("TC1-40").Range("B29:J29").RowHeight = 84
Worksheets("TC1-40").Range("B30") = "Insert New Clause..."
Worksheets("TC1-40").Range("B30:J30").RowHeight = 175.5
Worksheets("TC1-40").Range("B31") = "In line 3..."
Worksheets("TC1-40").Range("B31:J31").RowHeight = 69.75
Worksheets("TC1-40").Range("B32") = "In line 1..."
Worksheets("TC1-40").Range("B32:J32").RowHeight = 36.75
Worksheets("TC1-40").Cells(33, 1) = "21"
Worksheets("TC1-40").Range("B33") = "It is a..."
Worksheets("TC1-40").Range("B33:J33").RowHeight = 48
Worksheets("TC1-40").Cells(34, 1) = "22"
Worksheets("TC1-40").Range("B34") = "Any charges due..."
Worksheets("TC1-40").Range("B34:J34").RowHeight = 67.5
Worksheets("TC1-40").Cells(35, 1) = "23"
Worksheets("TC1-40").Range("B35") = "The Subcontractor..."
Worksheets("TC1-40").Range("B35:J35").RowHeight = 54
Worksheets("TC1-40").Cells(36, 1) = "24"
Worksheets("TC1-40").Range("B36") = "The Contracto..."
Worksheets("TC1-40").Range("B36:J36").RowHeight = 43.5
Worksheets("TC1-40").Cells(37, 1) = "25"
Worksheets("TC1-40").Range("B37") = "The subcontract..."
Worksheets("TC1-40").Range("B37:J37").RowHeight = 56.25
Worksheets("TC1-40").Cells(38, 1) = "26"
Worksheets("TC1-40").Range("B38") = "The Subcontract..."
Worksheets("TC1-40").Range("B38:J38").RowHeight = 69.75
Worksheets("TC1-40").Cells(39, 1) = "27"
Worksheets("TC1-40").Range("B39") = "The Subcontractor..."
Worksheets("TC1-40").Range("B39:J39").RowHeight = 37.5
Worksheets("TC1-40").Cells(40, 1) = "28"
Worksheets("TC1-40").Range("B40") = "The Subcontractor..."
Worksheets("TC1-40").Range("B40:J40").RowHeight = 33.75
Worksheets("TC1-40").Cells(41, 1) = "29"
Worksheets("TC1-40").Range("B41") = "You shall..."
Worksheets("TC1-40").Range("B41:J41").RowHeight = 35.25
Worksheets("TC1-40").Cells(42, 1) = "30"
Worksheets("TC1-40").Range("B42") = "We will provide..."
Worksheets("TC1-40").Range("B42:J42").RowHeight = 52.5
Worksheets("TC1-40").Cells(43, 1) = "31"
Worksheets("TC1-40").Range("B43") = "If the..."
Worksheets("TC1-40").Range("B43:J43").RowHeight = 57.75
Worksheets("TC1-40").Cells(44, 1) = "32"
Worksheets("TC1-40").Range("B44") = "All of your..."
Worksheets("TC1-40").Range("B44:J44").RowHeight = 51.75
Worksheets("TC1-40").Cells(45, 1) = "33"
Worksheets("TC1-40").Range("B45") = "You will be..."
Worksheets("TC1-40").Range("B45:J45").RowHeight = 55.5
Worksheets("TC1-40").Cells(46, 1) = "34"
Worksheets("TC1-40").Range("B46") = "In the event..."
Worksheets("TC1-40").Range("B46:J46").RowHeight = 113.25
Worksheets("TC1-40").Cells(47, 1) = "35"
Worksheets("TC1-40").Range("B47") = "Daily measurement..."
Worksheets("TC1-40").Range("B47:J47").RowHeight = 33
Worksheets("TC1-40").Cells(48, 1) = "36"
Worksheets("TC1-40").Range("B48") = "Your rates..."
Worksheets("TC1-40").Range("B48:J48").RowHeight = 40.5
Worksheets("TC1-40").Cells(49, 1) = "37"
Worksheets("TC1-40").Range("B49") = "We will not..."
Worksheets("TC1-40").Range("B49:J49").RowHeight = 84
Worksheets("TC1-40").Cells(50, 1) = "38"
Worksheets("TC1-40").Range("B50") = "Notwithstanding anything..."
Worksheets("TC1-40").Range("B50:J50").RowHeight = 100.5
Worksheets("TC1-40").Cells(51, 1) = "39"
Worksheets("TC1-40").Range("B51") = "Your rates..."
Worksheets("TC1-40").Range("B51:J51").RowHeight = 36
Worksheets("TC1-40").Cells(52, 1) = "40"
Worksheets("TC1-40").Range("B52") = "You shall..."
Worksheets("TC1-40").Range("B52:J52").RowHeight = 82.5

Else If
    Last edited by LukeAM; 08-22-2014 at 06:58 AM.
    Reply With QuoteRegister To Reply
  2. 08-22-2014, 06:59 AM #2
    LukeAM
    LukeAM is offline
    Registered User
    Join Date
    08-22-2014
    Location
    Sheffield, England
    MS-Off Ver
    Windows 7 Professional
    Posts
    20

    Re: VBA Ok Click() If Statement Procedure Too Long

    Sorry about the length
    Last edited by LukeAM; 08-22-2014 at 07:46 AM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Help needed simplifying code as procedure to long
    By johnny_p in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 02-28-2014, 10:55 AM
  2. Dbl Click Event Procedure
    By vbastruggles in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-22-2013, 10:35 PM
  3. Excel 2010: Macro Error 'Procedure Too Long'
    By marthadanielle in forum Excel General
    Replies: 4
    Last Post: 04-13-2012, 07:15 AM
  4. Procedure Too Long
    By Nick3535 in forum Excel Programming / VBA / Macros
    Replies: 28
    Last Post: 10-08-2010, 09:02 PM
  5. Double click procedure in an add-in
    By james.rees02 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-02-2009, 03:23 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1