Automation Error VBA from Userform

Question

Keep getting an automation error when my userform uses the below code when it initializes. I dont get an error when i take it out. The userform is being called from a shape using a module. the text from the userform is stored under a sheet called "Compliance". My userform is also called compliance. Here is my code below, any help would be much appreciated:

Private Sub UserForm_Initialize()
Dim ws As Worksheet
Set ws = Sheets("compliance")
On Error Resume Next
With page1

  employee1.Value = ws.Range("B2")
  employee2.Value = ws.Range("B3")
  employee3.Value = ws.Range("B4")
  employee4.Value = ws.Range("B5")
  employee5.Value = ws.Range("B5")
  employee6.Value = ws.Range("B6")

  '//// setting ops grade

 grade1.Value = ws.Range("D2")
 grade2.Value = ws.Range("D3")
 grade3.Value = ws.Range("D4")
 grade4.Value = ws.Range("D5")
 grade5.Value = ws.Range("D6")
grade6.Value = ws.Range("D7")

  '//// setting employee compliance recap

 recap1.Value = ws.Range("F2")
 recap2.Value = ws.Range("F3")
 recap3.Value = ws.Range("F4")
 recap4.Value = ws.Range("F5")
 recap5.Value = ws.Range("F6")
 recap6.Value = ws.Range("F7")




  '////////////////////////////////////////// febraury
'//// setting employees interviewed
With page2

 febemp1.Value = ws.Range("B14")
 febemp2.Value = ws.Range("B15")
 febemp3.Value = ws.Range("B16")
 febemp4.Value = ws.Range("B17")
 febemp5.Value = ws.Range("B18")
 febemp6.Value = ws.Range("B19")


febgrd1.Value = ws.Range("D14")
febgrd2.Value = ws.Range("D15")
febgrd3.Value = ws.Range("D16")
febgrd4.Value = ws.Range("D17")
febgrd5.Value = ws.Range("D18")
febgrd6.Value = ws.Range("D19")


 febrecap1.Value = ws.Range("F14")
 febrecap2.Value = ws.Range("F15")
 febrecap3.Value = ws.Range("F16")
 febrecap4.Value = ws.Range("F17")
 febrecap5.Value = ws.Range("F18")
 febrecap6.Value = ws.Range("F19")



With page3

 marchemp1.Value = ws.Range("B26")
 marchemp2.Value = ws.Range("B27")
 marchemp3.Value = ws.Range("B28")
 marchemp4.Value = ws.Range("B29")
 marchemp5.Value = ws.Range("B30")
 marchemp6.Value = ws.Range("B31")


marchgrd1.Value = ws.Range("D26")
marchgrd2.Value = ws.Range("D27")
marchgrd3.Value = ws.Range("D28")
marchgrd4.Value = ws.Range("D29")
marchgrd5.Value = ws.Range("D30")
marchgrd6.Value = ws.Range("D31")


marchrecap1.Value = ws.Range("F26")
marchrecap2.Value = ws.Range("F27")
marchrecap3.Value = ws.Range("F28")
marchrecap4.Value = ws.Range("F29")
marchrecap5.Value = ws.Range("F30")
marchrecap6.Value = ws.Range("F31")


  With page4

apremp1.Value = ws.Range("B38")
apremp2.Value = ws.Range("B39")
apremp3.Value = ws.Range("B40")
apremp4.Value = ws.Range("B41")
apremp5.Value = ws.Range("B42")
apremp6.Value = ws.Range("B43")


 aprgrd1.Value = ws.Range("D38")
 aprgrd2.Value = ws.Range("D39")
 aprgrd3.Value = ws.Range("D40")
 aprgrd4.Value = ws.Range("D41")
 aprgrd5.Value = ws.Range("D42")
 aprgrd6.Value = ws.Range("D43")


aprrecap1.Value = ws.Range("F38")
aprrecap2.Value = ws.Range("F39")
aprrecap3.Value = ws.Range("F40")
aprrecap4.Value = ws.Range("F41")
aprrecap5.Value = ws.Range("F42")
aprrecap6.Value = ws.Range("F43")

  With page5

mayemp1.Value = ws.Range("B50")
mayemp2.Value = ws.Range("B51")
mayemp3.Value = ws.Range("B52")
mayemp4.Value = ws.Range("B53")
mayemp5.Value = ws.Range("B54")
mayemp6.Value = ws.Range("B55")


 maygrd1.Value = ws.Range("D50")
 maygrd2.Value = ws.Range("D51")
 maygrd3.Value = ws.Range("D52")
 maygrd4.Value = ws.Range("D53")
 maygrd5.Value = ws.Range("D54")
 maygrd6.Value = ws.Range("D55")


mayrecap1.Value = ws.Range("F50")
mayrecap2.Value = ws.Range("F51")
mayrecap3.Value = ws.Range("F52")
mayrecap4.Value = ws.Range("F53")
mayrecap5.Value = ws.Range("F54")
mayrecap6.Value = ws.Range("F55")


  With page6

junemp1.Value = ws.Range("B62")
junemp2.Value = ws.Range("B63")
junemp3.Value = ws.Range("B64")
junemp4.Value = ws.Range("B65")
junemp5.Value = ws.Range("B66")
junemp6.Value = ws.Range("B67")


jungrd1.Value = ws.Range("D62")
jungrd2.Value = ws.Range("D63")
jungrd3.Value = ws.Range("D64")
jungrd4.Value = ws.Range("D65")
jungrd5.Value = ws.Range("D66")
jungrd6.Value = ws.Range("D67")


junrecap1.Value = ws.Range("F62")
junrecap2.Value = ws.Range("F63")
junrecap3.Value = ws.Range("F64")
junrecap4.Value = ws.Range("F65")
junrecap5.Value = ws.Range("F66")
junrecap6.Value = ws.Range("F67")

  With page7

julemp1.Value = ws.Range("B74")
julemp2.Value = ws.Range("B75")
julemp3.Value = ws.Range("B76")
julemp4.Value = ws.Range("B77")
julemp5.Value = ws.Range("B78")
julemp6.Value = ws.Range("B79")


julgrd1.Value = ws.Range("D74")
julgrd2.Value = ws.Range("D75")
julgrd3.Value = ws.Range("D76")
julgrd4.Value = ws.Range("D77")
julgrd5.Value = ws.Range("D78")
julgrd6.Value = ws.Range("D79")


julrecap1.Value = ws.Range("F74")
julrecap2.Value = ws.Range("F75")
julrecap3.Value = ws.Range("F76")
julrecap4.Value = ws.Range("F77")
julrecap5.Value = ws.Range("F78")
julrecap6.Value = ws.Range("F79")


  With page8

augemp1.Value = ws.Range("B86")
augemp2.Value = ws.Range("B87")
augemp3.Value = ws.Range("B88")
augemp4.Value = ws.Range("B89")
augemp5.Value = ws.Range("B90")
augemp6.Value = ws.Range("B91")


auggrd1.Value = ws.Range("D86")
auggrd2.Value = ws.Range("D87")
auggrd3.Value = ws.Range("D88")
auggrd4.Value = ws.Range("D89")
auggrd5.Value = ws.Range("D90")
auggrd6.Value = ws.Range("D91")


augrecap1.Value = ws.Range("F86")
augrecap2.Value = ws.Range("F87")
augrecap3.Value = ws.Range("F88")
augrecap4.Value = ws.Range("F89")
augrecap5.Value = ws.Range("F90")
augrecap6.Value = ws.Range("F91")

  With page9

sepemp1.Value = ws.Range("B98")
sepemp2.Value = ws.Range("B99")
sepemp3.Value = ws.Range("B100")
sepemp4.Value = ws.Range("B101")
sepemp5.Value = ws.Range("B102")
sepemp6.Value = ws.Range("B103")

sepgrd1.Value = ws.Range("D98")
sepgrd2.Value = ws.Range("D99")
sepgrd3.Value = ws.Range("D100")
 sepgrd4.Value = ws.Range("D101")
 sepgrd5.Value = ws.Range("D102")
 sepgrd6.Value = ws.Range("D103")



seprecap1.Value = ws.Range("F98")
seprecap2.Value = ws.Range("F99")
seprecap3.Value = ws.Range("F100")
seprecap4.Value = ws.Range("F101")
seprecap5.Value = ws.Range("F102")
seprecap6.Value = ws.Range("F103")

  With page10

  octemp1.Value = ws.Range("B110")
  octemp2.Value = ws.Range("B111")
 octemp3.Value = ws.Range("B112")
  octemp4.Value = ws.Range("B113")
  octemp5.Value = ws.Range("B114")
 octemp6.Value = ws.Range("B115")



  octgrd1.Value = ws.Range("D110")
   octgrd2.Value = ws.Range("D111")
  octgrd3.Value = ws.Range("D112")
  octgrd4.Value = ws.Range("D113")
  octgrd5.Value = ws.Range("D114")
  octgrd6.Value = ws.Range("D115")


  octrecap1.Value = ws.Range("F110")
  octrecap2.Value = ws.Range("F111")
  octrecap3.Value = ws.Range("F112")
  octrecap4.Value = ws.Range("F113")
  octrecap5.Value = ws.Range("F114")
  octrecap6.Value = ws.Range("F115")

  With page11

  novemp1.Value = ws.Range("B122")
  novemp2.Value = ws.Range("B123")
  novemp3.Value = ws.Range("B124")
  novemp4.Value = ws.Range("B125")
  novemp5.Value = ws.Range("B126")
  novemp6.Value = ws.Range("B127")


  novgrd1.Value = ws.Range("D122")
  novgrd2.Value = ws.Range("D123")
  novgrd3.Value = ws.Range("D124")
  novgrd4.Value = ws.Range("D125")
  novgrd5.Value = ws.Range("D126")
  novgrd6.Value = ws.Range("D127")

novrecap1.Value = ws.Range("F122")
novrecap2.Value = ws.Range("F123")
novrecap3.Value = ws.Range("F124")
novrecap4.Value = ws.Range("F125")
novrecap5.Value = ws.Range("F126")
novrecap6.Value = ws.Range("F127")


  With page12

decemp1.Value = ws.Range("B134")
decemp2.Value = ws.Range("B135")
decemp3.Value = ws.Range("B136")
decemp4.Value = ws.Range("B137")
decemp5.Value = ws.Range("B138")
decemp6.Value = ws.Range("B139")


 decgrd1.Value = ws.Range("D134")
 decgrd2.Value = ws.Range("D135")
 decgrd3.Value = ws.Range("D136")
 decgrd4.Value = ws.Range("D137")
 decgrd5.Value = ws.Range("D138")
 decgrd6.Value = ws.Range("D139")


decrecap1.Value = ws.Range("F134")
decrecap2.Value = ws.Range("F135")
decrecap3.Value = ws.Range("F136")
decrecap4.Value = ws.Range("F137")
decrecap5.Value = ws.Range("F138")
decrecap6.Value = ws.Range("F139")


End With
    End With
        End With
            End With
                End With
            End With
        End With
    End With
End With
End With
    End With
End With
End Sub

A) End your End With blocks when you are done with it. Not all at once at the end. B) limit your code you share here to relevant code. C) Remove 'On Error Resume Next' and re-run — urdearboy
– urdearboy, Commented Aug 16, 2018 at 3:37
For your first With Block you need to use End With before With page2 — urdearboy
– urdearboy, Commented Aug 16, 2018 at 3:37
Remove the On Error Resume Next. Do you get an error on a specific line? Also, Sheets is implicitly referencing the ActiveWorkbook is the correct workbook active? What is page1 and where is that set? — Comintern
– Comintern, Commented Aug 16, 2018 at 3:41
@Comintern i am not getting an error on a specific line, just a pop up dialog when my userform tries to open from a Sub that is linked to a shape. Page1 - Page 12 are multipages on my userform — Jose Cortez
– Jose Cortez, Commented Aug 16, 2018 at 3:47
This code could be a fraction of the size (and easier to debug/maintain) with a few loops and use of Me.Controls(controlNameHere) — Tim Williams
– Tim Williams, Commented Aug 16, 2018 at 4:32

Tim Williams · Accepted Answer · 2018-08-17 01:10:56Z

1

Untested - too many controls to make...

You'd need to harmonize your control naming a bit, but something like this should work:

Private Sub UserForm_Initialize()

    Dim ws As Worksheet, mNum As Long, months, i As Long, m
    Dim c As Range

    Set ws = Sheets("compliance")
    Set c = ws.Range("B1")

    months = Array("jan", "feb", "mar", "apr", "may", "jun", _
                   "jul", "aug", "sep", "oct", "nov", "dec")

    For mNum = 1 To 12
        m = months(m - 1)
        With Me.MultiPage1.Pages("page" & mNum)
            For i = 1 To 6
                .Controls(m & "emp" & i).Value = c.Offset(i, 0).Value
                .Controls(m & "grd" & i).Value = c.Offset(i, 2).Value
                .Controls(m & "recap" & i).Value = c.Offset(i, 4).Value
            Next i
        End With
        Set c = c.Offset(12, 0)
    Next mNum

End Sub

edited Aug 17, 2018 at 1:10

answered Aug 16, 2018 at 5:08

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

T.M. Over a year ago

Additional hint: Though there is no fixed limit of controls per se that a userform can hold, there seems to be a a long standing limitation on the number of controls that can be addressed directly by their name - c.f. Not enough memory - crash when loading VBA userform. Therefore I'd recommend not only to minimize the absolute number of value controls, but to address such big control sets preferrably via the (Multipage.Pages') Controls collection.

Tim Williams Over a year ago

You don't really even need 12 pages - each month could be loaded "on demand" and displayed in a single page.

T.M. Over a year ago

I can only agree with Tim's words in comment (BTW a fine answer) - so you could consider the use of a register control and only one set of dynamically linked textboxes instead of 12 multipages multiplied by 18 textboxes.

Collectives™ on Stack Overflow

Automation Error VBA from Userform

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related