Next Button Clicked Get Run-Time Error -'2147024809(80070057)

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
604
Hello I get the following error when NextButton Clicked. Can anyone help me to reoslve the same
FYI : both forms are VbModeless

Could not Find the specific object
Run-Time Error -'2147024809(80070057)

at this line in userform2 CmdNext_click coding :
Ws.Cells(curRow, i).Value = frm2.Controls("txtFrm2" & i).Value

also shared the file:
https://www.dropbox.com/s/55j2wdwjdg3bqlf/Userform-workSheets-TxtBxsFrmload.xlsm?dl=0

in Module1
Code:
Option Explicit

Public Const StartRow As Long = 2
Public row As Long
Public curRow As Long
Public Ws As Worksheet
Public uf_frmTarget As UserForm1
Public frm2 As UserForm2
Public curRec As Integer

in Userform1
Code:
Option Explicit

Private Sub cmdNext_Click()
Dim Ws As Worksheet
Set Ws = Worksheets("Sheet2")
Dim i As Integer
    i = 1

If curRow < 5 Then 'lastRow Then
 For i = 1 To 2
        Ws.Cells(curRow, i).Value = frm2.Controls("txtFrm2" & i).Value
        'Ws.Cells(curRow, i).Value = frm2.txtFrm2(i).Value
 Next i

       curRec = curRec + 1
       curRow = curRow + 1
      UserForm1.lblSrNo.Caption = Format$(curRec)
      GetRecord curRow
    End If
 Rows(curRow).Select
End Sub

Private Sub cmdPrevious_Click()
Dim Ws As Worksheet
Set Ws = Worksheets("Sheet2")
    If curRec > 1 Then 'lastRow Then
       curRec = curRec - 1
       curRow = curRow - 1
      UserForm1.lblSrNo.Caption = Format$(curRec)
      GetRecord curRow
    End If
      Rows(curRow).Select
End Sub

Private Sub cmdUF2_Click()
Dim Ws As Worksheet

Set frm2 = New UserForm2
Load frm2
frm2.Show vbModeless
frm2.Caption = "Trial"
Set Ws = Worksheets("Sheet2")
Ws.Activate

 curRow = 2
 GetRecord curRow
End Sub

Private Sub UserForm_Initialize()
   curRec = 1
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
On Local Error Resume Next
frm2.Hide
End Sub

Sub GetRecord(ByVal row As Long)
Dim Ws As Worksheet
Set Ws = Worksheets("Sheet2")
    UserForm1.Tag = xlOff
    Ws.Activate
     If row < StartRow Then row = StartRow
    Rows(row).Select
    curRec = curRow - 1
    UserForm1.lblSrNo.Caption = Format$(curRec)
End Sub

in Userform2
Code:
Private Sub UserForm_Activate()
  Set frm2 = New UserForm2
      frm2.Top = 210
      frm2.Left = 0
End Sub

Private Sub UserForm_Initialize()

Dim Ws As Worksheet
Set Ws = Worksheets("Sheet2")
Ws.Activate


Dim txtBxFrm2 As Control
Dim lablFrm2 As Control

Dim i As Integer
Dim x As Integer
Dim y As Integer

y = 10
x = 10

For i = 1 To 2
Set txtBxFrm2 = Controls.Add("Forms.TextBox.1")
Set lablFrm2 = Controls.Add("Forms.Label.1")

 With lablFrm2
        .Name = "lblfrm2" 
        .Height = 15.75
        .Width = 15 * 5
        .Left = x
        .Top = y
        .BackStyle = 0
        .Caption = Sheet2.Cells(1, i).Value
    End With

    With txtBxFrm2
        .Name = "txtFrm2" 
        .Height = 18
        .Width = 116
        .Left = x
        .Top = y + 10
        .Value = Ws.Cells(StartRow, i).Value 
        .Font.Name = "Calibri"
        .Font.Size = "11"
    End With
    x = x + 142
Next i

End Sub
Thankx NimishK
 
Last edited:

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
604
Steve

Thanks for your Query.
i = 1 already mentioned in cmdNext_click of Userform1 and in Userform2 UF_initialize.

txtFrm21 and txtFrm22 automatically created in Frm2. Pl check the File attached in the link

It is just that from Userform1 when pressed Next Button want to read records in Frm2 of txtFrm21 and txfrm22 from Sheet 2.

Merry Christmas to you and all :)

Thanks NimishK
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,137
Office Version
  1. 365
Platform
  1. Windows
I did download it. Using the immediate window when that line is reached txtFrm21 doesnt exist hence the error. txtFrm2 does.
 

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
604
MERRY CHRISTMAS

i corrected the following to avoid the error
Code:
With txtBxFrm2
[COLOR=#ff0000][B]        .Name = "txtFrm2" & i[/B][/COLOR]
        .Height = 18
        .Width = 116
        .Left = x
        .Top = y + 10
        .Value = Ws.Cells(StartRow, i).Value '& "  " & .Name & Val(i)
        .Font.Name = "Calibri"
        .Font.Size = "11"
    End With

but i require correct looping in cmdNext_click of userform1
because no one would be willing to code individually of respective cells values of (Row) in respective textboxes.
At Present 2 cols, 2 textboxes what if in future more than 20

Code:
Private Sub cmdNext_Click()

Dim Ws As Worksheet
Set Ws = Worksheets("Sheet2")
Dim i As Integer, shtcols As Integer
    i = 1

If curRow < 5 Then 'lastRow Then
  For i = 1 to 1 to 2 
     Ws.Cells(curRow, i).Value = frm2.Controls("txtFrm2" & i).Value
 Next i
       curRec = curRec + 1
       curRow = curRow + 1
       i = i + 1
       
[COLOR=#ff0000][B]       frm2.Controls("txtFrm2" & 1).Value = Ws.Cells(curRow, 1).Value[/B]
[B]       frm2.Controls("txtFrm2" & 2).Value = Ws.Cells(curRow, 2).Value[/B]
[/COLOR][B][COLOR=#ff0000]       
      'Correct Looping required for above [/COLOR]
[/B]
       Rows(curRow).Select
      UserForm1.lblSrNo.Caption = Format$(curRec)
    End If
End Sub
 
Last edited:

NimishK

Well-known Member
Joined
Sep 4, 2015
Messages
604
No Success with below :banghead:
Code:
Private Sub cmdNext_Click()
Dim Ws As Worksheet
Set Ws = Worksheets("Sheet2")
Dim i As Integer 
curRow = 2


If curRow < 5 Then 
 
For i = 1 to 2 
       Ws.Cells(curRow, i).Value = frm2.Controls("txtFrm2" & i).Value  ' This only reads the 1st value of Col A and B
Next i 


[B][COLOR=#ff0000]tried the above looping  for below but getting wrong values ie it just reads the first record[/COLOR][/B]
'''''   Ws.Cells(curRow, 1).Value = frm2.Controls("txtFrm2" & 1).Value
'''''   Ws.Cells(curRow, 2).Value = frm2.Controls("txtFrm2" & 2).Value
     
       curRec = curRec + 1
       curRow = curRow + 1


For i = 1 To 2
        frm2.Controls("txtFrm2" & i).Value = Ws.Cells(curRow, i).Value
Next i
[COLOR=#ff0000][B]tried the above looping  for below but getting wrong values ie it overwrites the Previous row values with NextRow values in worksheet Kindly check[/B][/COLOR]
''''       frm2.Controls("txtFrm2" & 1).Value = Ws.Cells(curRow, 1).Value
''''       frm2.Controls("txtFrm2" & 2).Value = Ws.Cells(curRow, 2).Value
     
       Rows(curRow).Select
      UserForm1.lblSrNo.Caption = Format$(curRec)
    End If
End Sub
Any other Method to achieve the same
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,114,535
Messages
5,548,623
Members
410,858
Latest member
RamIndia
Top