Automation Error in UserForm

vijayendra.shukla · Nov 17, 2010

hi all,

I have created a userform and during runtime i am adding few controls (basically labels and multipages)to the userform. While i am running the code i am getting the
"RunTime Error '-2147217848(80010108)'
Automation error
The object invoked has disconnected from its clients."

When i run in debug mode I do not see any problem in my UserForm_Initialize procedure. I am totally lost.
if you want i can paste the code here.
Anything would be of great help. Thanks a lot.

krishhi · Nov 17, 2010

A google search takes me to these links:

http://support.microsoft.com/kb/186063

http://support.microsoft.com/kb/186063

http://support.microsoft.com/kb/186063

http://computergeardirect.com/computers/a-closer-look-at-runtime-error-80010108/

http://computergeardirect.com/computers/a-closer-look-at-runtime-error-80010108/

http://computergeardirect.com/computers/a-closer-look-at-runtime-error-80010108/

vijayendra.shukla · Nov 17, 2010

Thanks a lot Krishhi for replying.

I will look into the links provided by you but let just tell me why i am so confused here.

I wrote the below shown code

Code:

[FONT=Arial Narrow][SIZE=1]  iUsedRowCount = ThisWorkbook.Sheets("PF").UsedRange.Rows.Count[/SIZE][/FONT]
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
[FONT=Arial Narrow][SIZE=1]  'Below given block of code handles the situation when UsedRange of rows is more than what exactly is.[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]  For iR = iUsedRowCount To 0 Step -1[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]      If Trim(ThisWorkbook.Worksheets("PF").Range("B" & iR).Value) <> "" Then[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]          iUsedRowCount = iR[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]          Exit For[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]      End If[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]  Next[/SIZE][/FONT]
<o:p></o:p>
[FONT=Arial Narrow][SIZE=1]  iUsedRowCount = 6[/SIZE][/FONT]
<o:p></o:p>
[FONT=Arial Narrow][SIZE=1]''''    'Calculating the number of pages required in the multipage. As per the original design, done manually _[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]''''    it was agreed upon that 38 rows with font Arial-Regular-8 are good enough for one page.[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]''''    iUserFormCount = iRowCount / 39[/SIZE][/FONT]
<o:p></o:p>
[FONT=Arial Narrow][SIZE=1]  With ThisWorkbook.Worksheets("PF")[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]      .Columns("A:A").EntireColumn.AutoFit[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]      .Columns("B:B").EntireColumn.AutoFit[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]      .Rows("5:" & iUsedRowCount).AutoFit[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]  End With[/SIZE][/FONT]
<o:p></o:p>
[FONT=Arial Narrow][SIZE=1]  iWidth = ThisWorkbook.Worksheets("PF").Columns("B:B").ColumnWidth[/SIZE][/FONT]
 
[FONT=Arial Narrow][SIZE=1]  'Checking the row values to decide enabling/disabling the checkboxes[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]  sRetail = Trim(ThisWorkbook.Sheets("PF").Range("AI5").FormulaR1C1)[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]  sBroker = Trim(ThisWorkbook.Sheets("PF").Range("AI6").FormulaR1C1)[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]  sCorr = Trim(ThisWorkbook.Sheets("PF").Range("AI7").FormulaR1C1)[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]  iCounter = 0    'This variable will count the pages in multipage[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]  For iUsedRows = 5 To iUsedRowCount[/SIZE][/FONT]
 
[FONT=Arial Narrow][SIZE=1]      If iCounter > 1 Then    'Create a new page in the MultiPage[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]          Me.MultiPage1.Pages.Add[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]'            Me.MultiPage1.Pages.Add ("")[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]      End If[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]'    Me.MultiPage1(0).KeepScrollBarsVisible = fmScrollBarsVertical[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]      bLoopFlag = False[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]      iTransparentLabelHeight = 0[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]      i = 0[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]      iInnerLoopCounter = 0[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]      For i = iUsedRows To (iUsedRows + (38 - 1)) 'This will ensure that 38 rows are covered from the starting of the row for this loop[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]          'Exiting the loop if the row number is more than iUsedRowCount[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]          If i > iUsedRowCount Then[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              Exit For[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]          End If[/SIZE][/FONT]
<o:p></o:p>
[FONT=Arial Narrow][SIZE=1]          If iInnerLoopCounter > 40 Then[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              sColAText = Trim(ThisWorkbook.Worksheets("PF").Range("A" & i))[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              sColATextNext = Trim(ThisWorkbook.Worksheets("PF").Range("A" & (i + 1)))[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              sColATextNext2 = Trim(ThisWorkbook.Worksheets("PF").Range("A" & (i + 2)))[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              If sColAText <> "" And sColAText <> "Checking the module name" And iInnerLoopCounter = 41 Then[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  If sColATextNext2 = "" Then[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      iTotalRowsCovered = i - 1[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      bLoopFlag = True[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      Exit For[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  End If[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              Else[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  If sColAText <> "" And sColAText <> "Checking the module name" And iInnerLoopCounter = 42 Then[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      iTotalRowsCovered = i - 1[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      bLoopFlag = True[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      Exit For[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  End If[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              End If[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]          End If[/SIZE][/FONT]
<o:p></o:p>
[FONT=Arial Narrow][SIZE=1]'            iTop = 24 + 13 * (i - 5)[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]          iTop = 24 + 13 * iInnerLoopCounter[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]          sText = " " & Trim(ThisWorkbook.Worksheets("PF").Range("B" & i))[/SIZE][/FONT]
<o:p></o:p>
[FONT=Arial Narrow][SIZE=1]          If Len(sText) < 77 Then 'Len(sText) > 68 And Len(sText) < 136 Then[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              iHeight = 13[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]          ElseIf Len(sText) > 77 And Len(sText) < 154 Then 'Len(sText) > 136 Then[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              iHeight = 26[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]          Else[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              iHeight = 39[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]          End If[/SIZE][/FONT]
<o:p></o:p>
[FONT=Arial Narrow][SIZE=1]          sColAText = Trim(ThisWorkbook.Worksheets("PF").Range("A" & i))[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]'            If sColAText = "" And iCounter > 0 Then[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]'                bMergeFlag = ThisWorkbook.Worksheets("PF").Range("A" & i).MergeCells[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]'            End If[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]          bTransparentLabelFlag = False   'This flag indicates whether transparent label has to be created or not[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]          bFlag = False   'this flag indicates whether the line is other than "check all that required" or not[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]          If iInnerLoopCounter = 0 And sColAText = "" Then[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              k = 0[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              Do Until sColAText <> ""[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  sColAText = Trim(ThisWorkbook.Worksheets("PF").Range("A" & (i + k)))[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  k = k + 1[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  If k > 38 Then  'Since one page cannot contain more than 38 rows therefore putting this limit[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      Exit Do[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  End If[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              Loop[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              iTransparentLabelHeight = 13 * (k - 1)[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              iTopTransparent = iTop[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              bTransparentLabelFlag = True[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]          ElseIf sColAText <> "" Then[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              If sColAText <> "Checking the module name" Then[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  iHeightColA = 13[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  iTransparentLabelHeight = iHeightColA + iTransparentLabelHeight[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  bTransparentLabelFlag = False[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  bFlag = False[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              Else[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  iMergedRows = ThisWorkbook.Worksheets("PF").Range("A" & i).MergeArea.Rows.Count[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  iRowReached = i + (iMergedRows - 1)[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  If iRowReached > (iUsedRows + (38 - 1)) Then[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      iReduceRows = iRowReached - (iUsedRows + (38 - 1))[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      iMergedRows = iMergedRows - iReduceRows[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  End If[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  iHeightColA = iMergedRows * 13[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  iTransparentLabelHeight = iHeightColA + iTransparentLabelHeight[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  iTopTransparent = iTop - 13[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  bTransparentLabelFlag = True[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  bFlag = True[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              End If[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              Set NewLabel = Me.MultiPage1(iCounter).Controls.Add("Forms.Label.1")[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              With NewLabel[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  .Name = "LbColA" & i & "Page" & iCounter[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  .Caption = sColAText[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  .Font.Name = "Verdana"[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  If bFlag = False Then[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      .Font.Size = 9[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  Else[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      .Font.Size = 7[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      .Font.Italic = True[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  End If[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  .Height = iHeightColA[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  .Left = 12[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  .Top = iTop[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  .Width = 150[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  .TextAlign = fmTextAlignCenter[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  .BackColor = &H8000000D[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  .ForeColor = &H8000000E[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              End With[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              Set NewLabel = Nothing[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]          End If[/SIZE][/FONT]
<o:p></o:p>
[FONT=Arial Narrow][SIZE=1]          If bTransparentLabelFlag = True Then[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              'Creating transparent label to give border look[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              Set NewLabel = Me.MultiPage1(iCounter).Controls.Add("Forms.Label.1")[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              With NewLabel[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  .Name = "LbCol2A" & i & "Page" & iCounter[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  .Height = iTransparentLabelHeight[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  .Left = 12[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  .Top = iTopTransparent     '24 + iTransparentLabelHeight * (iTransaparentLabelCounter - 1)[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  .Width = 150[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  If iInnerLoopCounter <> 0 Then[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      .BackStyle = fmBackStyleTransparent[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  End If[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  If iInnerLoopCounter = 0 Then[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      .Caption = "contd..."[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      .Font.Name = "Verdana"[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      .Font.Size = 7[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      .Font.Italic = True[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      .BackColor = &H8000000D[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      .ForeColor = &H8000000E[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      .TextAlign = fmTextAlignCenter[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  End If[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  .BorderStyle = fmBorderStyleSingle[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              End With[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              Set NewLabel = Nothing[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              iTransparentLabelHeight = 0[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]          End If[/SIZE][/FONT]
<o:p></o:p>
[FONT=Arial Narrow][SIZE=1]          Set NewLabel = Me.MultiPage1(iCounter).Controls.Add("Forms.Label.1")[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]          With NewLabel[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              .Name = "LbColB" & i & "Page" & iCounter[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              .Caption = sText[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              .Font.Name = "Arial"[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              .Font.Size = 8[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              .Height = iHeight[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              .Left = 162[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              .Top = 24 + 13 * iInnerLoopCounter[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              .Width = 312[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              .BackColor = &HFFFFC0[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              .BorderStyle = fmBorderStyleSingle[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]          End With[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]          Set NewLabel = Nothing[/SIZE][/FONT]
 
[FONT=Arial Narrow][SIZE=1]          j = 0   'Initializing j to 0[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]          For j = 1 To 3[/SIZE][/FONT]
 
[FONT=Arial Narrow][SIZE=1]'                sCell = Trim(ThisWorkbook.Sheets("PF").Range("AI" & (4 + j)).FormulaR1C1)[/SIZE][/FONT]
 
[FONT=Arial Narrow][SIZE=1]              Set NewCheckBox = Me.MultiPage1(iCounter).Controls.Add("Forms.CheckBox.1")[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              With NewCheckBox[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  .Name = "chkPg" & iCounter & "Row" & i & "Col" & j[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  .Caption = ""[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  .Height = iHeight[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  .Left = 485 + 30 * (j - 1)[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  .Top = 24 + 13 * iInnerLoopCounter[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  .Width = 18[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]'                    .Enabled = False[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]'                    If sCell = "" Then[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]'                        .Enabled = False[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]'                    Else[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]'                        .Enabled = True[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]'                    End If[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  If i = 178 Then[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      .Enabled = True[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  Else[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      .Enabled = False[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  End If[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  Select Case j[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  Case 1[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      If ThisWorkbook.Sheets("PF").Range("C" & i).FormulaR1C1 = "X" Then[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                          .Value = True[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      Else[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                          .Value = False[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      End If[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]'                        If sRetail <> "" Then[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]'                            .Enabled = True[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]'                        Else[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]'                            .Enabled = False[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]'                        End If[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  Case 2[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      If ThisWorkbook.Sheets("PF").Range("M" & i).FormulaR1C1 = "X" Then[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                          .Value = True[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      Else[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                          .Value = False[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      End If[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]'                        If sBroker <> "" Then[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]'                            .Enabled = True[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]'                        Else[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]'                            .Enabled = False[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]'                        End If[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  Case 3[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      If ThisWorkbook.Sheets("PF").Range("W" & i).FormulaR1C1 = "X" Then[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                          .Value = True[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      Else[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                          .Value = False[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                      End If[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]'                        If sCorr <> "" Then[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]'                            .Enabled = True[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]'                        Else[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]'                            .Enabled = False[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]'                        End If[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]                  End Select[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              End With[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]              Set NewCheckBox = Nothing[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]          Next[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]          iInnerLoopCounter = iInnerLoopCounter + 1 'This counter is used for the transparent label creation[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]      Next[/SIZE][/FONT]
 
[FONT=Arial Narrow][SIZE=1]      Me.MultiPage1(iCounter).ScrollBars = fmScrollBarsVertical[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]      If bLoopFlag = False Then[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]          iUsedRows = i - 1 'This will be the next row from where we need to capture the rows to populate next page.[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]          iTotalRowsCovered = i - 1 - 4[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]'            Me.MultiPage1(iCounter).ScrollHeight = iTotalRowsCovered * 13 + 24 + 5[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]          Me.MultiPage1(iCounter).ScrollHeight = iInnerLoopCounter * 13 + 24 + 5[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]      Else[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]          iUsedRows = iTotalRowsCovered + 1 'This will be the next row from where we need to capture the rows to populate next page.[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]'            Me.MultiPage1(iCounter).ScrollHeight = (iTotalRowsCovered - 2) * 13 + 5[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]          Me.MultiPage1(iCounter).ScrollHeight = iInnerLoopCounter * 13 + 24 + 5[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]      End If[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]      iCounter = iCounter + 1[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]  Next[/SIZE][/FONT]
[FONT=Arial Narrow][SIZE=1]'    MsgBox "Form Done"[/SIZE][/FONT]

now if you observe where i am creating checkboxes (3 consecutive checkboxes in a row), i want the checkboxes to be enabled or disabled based upon the value in certain cells. If you see, there are three variables which sRetail, sBroker, sCorr which if blank will disable the checkbox. Now it seems working fine perfectly until all the 3 cells from where i am deducing the enabled property are blank. When any of the 3 cells contain data userform is getting generated successfully but if all 3 of them are blank then i am stuck with this error. Hope i have explained the problem nicely.

Forgot to add - Now today when i was just trying to find the origin of the error, i tried commenting out the checkbox generation related code. and now it appears that is also of no help and i am still stuck in that error. I thought I should have got the userform with just labels in it

.

Automation Error in UserForm

vijayendra.shukla

New Member

krishhi

Active Member

vijayendra.shukla

New Member

Similar threads

Share this page

Automation Error in UserForm

vijayendra.shukla

New Member

krishhi

Active Member

vijayendra.shukla

New Member

Similar threads

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock