Object Required Error VBA Userform

T

Tamiz1982

New Member
Joined
Sep 11, 2020
Messages
17
Office Version
  1. 2007
Platform
  1. Windows
Hi,
This is my first try. I am developing mini project in Excel VBA. I have get some errors (object required) while calling function.. Kindly solve this problem.



VBA Code: 
Sub recentrecord()
    Dim lastrow, i As Long
    'Dim ws As Worksheet
    Call Activate_Cluster
    
    lastrow = Application.WorksheetFunction.CountA(ws.Range("A:A")) - 1
    UserForm1.Label8.Caption = lastrow
    If lastrow <> 0 Then
        'lastrow = Worksheets("Cluster-1").Cells(Rows.Count, 1).End(xlUp).Row
        lastrow = Application.WorksheetFunction.CountA(ws.Range("A:A"))
        UserForm1.Label4.WordWrap = False
        UserForm1.Label4.FontSize = 10
        UserForm1.Label4.Caption = ws.Cells(lastrow, 1) & "-A " & ws.Cells(lastrow, 5)
        UserForm1.Label12.Caption = ws.Cells(lastrow, 11)
        UserForm1.cmdUpdateRec.Enabled = True
        UserForm1.cmdDelRec.Enabled = True
    Else
        UserForm1.Label4.Caption = "No Record Entered"
        UserForm1.cmdUpdateRec.Enabled = False
         UserForm1.cmdDelRec.Enabled = False
    End If
End Sub
'calling function
Public Sub Activate_Cluster()
    Dim SName As String
    Dim ws As Worksheet
    
    If Cluster1.Value = True Then
       clrsno = 1
        Set ws = ThisWorkbook.Sheets("Cluster-1")
        ws.Activate
        ws.Select
        SName = ws.Name
        Call lb2
    ElseIf Cluster2.Value = True Then
        clrsno = 2
        Set ws = ThisWorkbook.Sheets("Cluster-2")
        ws.Activate
        ws.Select
        SName = ws.Name
        Call lb2
    Else
        clrsno = 3
        Set ws = ThisWorkbook.Sheets("Cluster-3")
        ws.Activate
        ws.Select
        SName = ws.Name
        Call lb2
    End If
    
End Sub


Thanks & Regards.
 

Attachments

  • Object Required..jpg
    Object Required..jpg
    245.1 KB · Views: 28

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
Try changing this
VBA Code: 
Sub recentrecord()
    Dim lastrow, i As Long
    'Dim ws As Worksheet
    Call Activate_Cluster
    lastrow = Application.WorksheetFunction.CountA(ws.Range("A:A")) - 1


to


VBA Code: 
Sub recentrecord()
    Dim lastrow as long, i As Long, ws As Worksheet
    Call Activate_Cluster
    lastrow = cells(rows.count,"A").end(xlup).row-1
 
Upvote 0
Thank U for reply.
I have changed . But another error shown "Run Time Error 91" "Object Variable or With block variable not set"
VBA Code: 
UserForm1.Label4.Caption = ws.Cells(lastrow, 1) & "-A " & ws.Cells(lastrow, 5)
UserForm1.Label12.Caption = ws.Cells(lastrow, 11)
 
Upvote 0
Ok. I got the point.
Code: 
UserForm1.Label4.Caption = Cells(lastrow, 1) & "-A " & Cells(lastrow, 5)
UserForm1.Label12.Caption = Cells(lastrow, 11)
Thank U
 
Upvote 0
I need more help. I have 3 option button and 3 working sheets. Using option button I want input record from text to relevant sheets. I could try only 1 option which I developed. Could you guide me regarding?. and also explain me how it is work.
VBA Code: 
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

'Input data in Textbox
Dim rw, jcn, lastrow As Long
Dim ws, ws1 As Worksheet
Set ws = ThisWorkbook.Worksheets("Cluster-1")
Set ws1 = ThisWorkbook.Worksheets("NLB")
Sheets("NLB").Activate
    
    If KeyCode = 13 Then
        Sheets("NLB").Range("b1") = Trim(UserForm1.TextBox1.Value)
        If (ws1.Cells(2, 3)) = "" Then
            MsgBox "Unavailable Job Card No...Try Again...!!!"
            Call reset
        End If
        
        rw = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
        ws.Cells(rw, 1).Value = Trim(UserForm1.TextBox1.Value)
        UserForm1.TextBox1.TextAlign = fmTextAlignLeft
        UserForm1.TextBox1.FontSize = 10
        UserForm1.TextBox1.Text = ws.Cells(rw, 4)
        jcn = UserForm1.TextBox1.Text
          
        UserForm1.ListBox1.SetFocus
        UserForm1.ListBox1.Selected(0) = True
                
        'Add item into Listbox depends on Registration Number
        Sheets("NLB").Select
        For i = 2 To Application.WorksheetFunction.CountIf(Sheet12.Range("D2:D11"), "><")
            UserForm1.ListBox1.Value = Cells(i, 4)
        Next i
        Sheets("Cluster-1").Activate
        'Sheets("NLB").Select
    End If
End Sub

Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim rw As Long
Dim ws, NLB As Worksheet
Set ws = ThisWorkbook.Worksheets("Cluster-1")
Call Activate_Cluster
'Sheets("Cluster-1").Activate

'Sheets("NLB").Select
If KeyCode = 13 Then
     rw = ws.Cells(Rows.Count, 1).End(xlUp).Offset(0, 2).Row
     ws.Cells(rw, 2) = Me.ListBox1.ListIndex + 1
    Call reset
End If
Call recentrecord
End Sub

Private Sub ListBox2_Click()
Dim i, j, lastrow As Long
Dim ws, ws1 As Worksheet
Set ws = ThisWorkbook.Worksheets("Cluster-1")
Set ws1 = ThisWorkbook.Worksheets("NLB")
Sheets("NLB").Select

Set ws = ThisWorkbook.Worksheets("Cluster-1")
lastrow = ThisWorkbook.Worksheets("Cluster-1").Cells(Rows.Count, 1).End(xlUp).Row

    For i = 0 To lastrow - 1
        If ListBox2.Selected(i) = True Then
            UserForm1.TextBox1.FontSize = 10
            UserForm1.TextBox1.Text = UserForm1.ListBox2.List(UserForm1.ListBox2.ListIndex, 0)
            
            If ws.Cells(i + 2, 4) = UserForm1.TextBox1.Text Then
                Sheets("NLB").Range("b1") = ws.Cells(i + 2, 1)
                UserForm1.ListBox1.SetFocus
                UserForm1.ListBox1.Selected(0) = True
            End If
            
         End If
        
         Next i
End Sub


Sub recentrecord()
    Dim lastrow, rw As Long, i As Long, ws As Worksheet
    Call Activate_Cluster
    
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row - 1
    UserForm1.Label8.Caption = lastrow
    If lastrow <> 0 Then
        'lastrow = Worksheets("Cluster-1").Cells(Rows.Count, 1).End(xlUp).Row
        lastrow = Cells(Rows.Count, "A").End(xlUp).Row
        UserForm1.Label4.WordWrap = False
        UserForm1.Label4.FontSize = 10
        UserForm1.Label4.Caption = Cells(lastrow, 1) & "-A " & Cells(lastrow, 5)
        'UserForm1.Label4.Caption = ws.Cells(lastrow, 1) & "-A " & ws.Cells(lastrow, 5)
                If Cells(lastrow, 11) > 80 Then
            UserForm1.Label11.ForeColor = &HFF&
            UserForm1.Label12.ForeColor = &HFF&
        Else

            UserForm1.Label11.ForeColor = &HFF00&
            UserForm1.Label12.ForeColor = &HFF00&
        End If
  
        
        UserForm1.Label12.Caption = Cells(lastrow, 11)
        'UserForm1.Label12.Caption = ws.Cells(lastrow, 11)
        UserForm1.cmdUpdateRec.Enabled = True
        UserForm1.cmdDelRec.Enabled = True
    Else
        UserForm1.Label4.Caption = "No Record Entered"
        UserForm1.cmdUpdateRec.Enabled = False
         UserForm1.cmdDelRec.Enabled = False
    End If
End Sub

Public Sub Activate_Cluster()

    Dim lastrow, i As Long, ws As Worksheet
    
    If Cluster1.Value = True Then
      
        ThisWorkbook.Sheets("Cluster-1").Activate
        
    ElseIf Cluster2.Value = True Then
        
        ThisWorkbook.Sheets("Cluster-2").Activate
        
    Else
        
        ThisWorkbook.Sheets("Cluster-3").Activate
        
    End If
    
End Sub

Public Sub lb2()
With Me.ListBox2
ListBox2.ColumnHeads = True
ListBox2.ColumnCount = 4
.RowSource = Range("D2:G200").Address
End With

End Sub

Private Sub Cluster1_Click()
        Cluster1.Value = True
        Cluster2.Value = False
        Cluster3.Value = False
        clrsno = 1
        Call Activate_Cluster
        Call lb2
        Call recentrecord
        Call reset
End Sub

Private Sub Cluster2_Click()
        Cluster1.Value = False
        Cluster2.Value = True
        Cluster3.Value = False
        clrsno = 2
        Call Activate_Cluster
        Call lb2
        Call recentrecord
        Call reset
End Sub

Private Sub Cluster3_Click()
        Cluster1.Value = False
        Cluster2.Value = False
        Cluster3.Value = True
        clrsno = 3
        Call Activate_Cluster
        Call lb2
        Call recentrecord
        Call reset
End Sub

Private Sub cmdNewEntry_Click()
Call Activate_Cluster
confirm = MsgBox("Do You Want to Clear Existing Data  in Cluster " & clrsno, vbInformation + vbYesNo, "Clear Data")

    If confirm = vbYes Then
        Sheets("Cluster-1").Activate
        'Call Activate_Cluster
        ThisWorkbook.Sheets("Cluster-1").Range("A2:B800").ClearContents
        Call reset
        Call recentrecord
    Else
        Call reset
        Call recentrecord
    End If
End Sub
 

Attachments

  • User-Form.jpg
    User-Form.jpg
    217.9 KB · Views: 8
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Force Excel 2019 VBA to import multiple txt file to existing sheet & not seperate wk.book / wk.sheets
Replies
1
Views
600
Domenic
D
L
  • Question Question
VBA to export recurring meeting data
Replies
1
Views
1K
John_w
J
R
  • Question Question
[VBA] Trying to use sheet names in listbox as source to run code - can't figure out reference
Replies
1
Views
888
rossross
R
M
  • Question Question
Object Required Error
Replies
4
Views
741
NdNoviceHlp
NdNoviceHlp
O
  • Question Question
Run time error 424 (Object Required)
Replies
3
Views
707
omairhe
O

Forum statistics

Threads
1,223,909
Messages
6,175,313
Members
452,634
Latest member
cpostell

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top