Hi Freinds,
I have two UserForms 1 and 2. In a userForm "frmCottonPurchase" i have a combobox. The Source data from sheet "Party". I used a vlookup formula to retrieve data in next textbox i.e "txtregistration". When i click the combobox the compile error appears at code "party.activate" with a message "Method or Data member not found".
Please solve the Problem. I am using follwing codes.
1st userform "frmCottonPurchase"
Option Explicit
Private Sub cmdCancel_Click()
End
End Sub
Private Sub cmdExit_Click()
Unload Me
End Sub
Private Sub cmdSave_Click()
Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("CottonPurchase")
'find empty records
irow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'check for inv no
If Trim(Me.txtVoucher.Value) = "" Then
Me.txtVoucher.SetFocus
MsgBox "Please Enter Voucher No."
Exit Sub
End If
' copy the data in database
ws.Cells(irow, 1).Value = Me.txtVoucher
ws.Cells(irow, 2).Value = Me.txtVdate.Value
ws.Cells(irow, 3).Value = Me.txtInv
ws.Cells(irow, 4).Value = Me.txtDate.Value
ws.Cells(irow, 5).Value = Me.ComboBox1
ws.Cells(irow, 6).Value = Me.txtRegistration
ws.Cells(irow, 7).Value = Me.txtBales.Value
ws.Cells(irow, 8).Value = Me.txtKgs.Value
ws.Cells(irow, 9).Value = Me.txtRate.Value
ws.Cells(irow, 10).Value = Me.txtValue.Value
ws.Cells(irow, 11).Value = Me.txtStax.Value
ws.Cells(irow, 12).Value = Me.txtTotal.Value
ws.Cells(irow, 13).Value = Me.txtWtax.Value
ws.Cells(irow, 14).Value = Me.txtNet.Value
Set ws = Worksheets("Trial")
'1st Entry
irow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
ws.Cells(irow, 1).Value = Me.TextBox14.Value
ws.Cells(irow, 2).Value = Me.txtdebit1.Value
'2nd Entry
irow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
ws.Cells(irow, 1).Value = Me.TextBox13.Value
ws.Cells(irow, 2).Value = Me.TextBox11.Value
'3rd Entry
irow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
ws.Cells(irow, 1).Value = Me.TextBox3.Value
ws.Cells(irow, 3).Value = Me.TextBox8.Value
'4th Entry
irow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
ws.Cells(irow, 1).Value = Me.TextBox4.Value
ws.Cells(irow, 3).Value = Me.TextBox6.Value
'clear the data
Me.txtVoucher.Value = ""
Me.txtVdate.Value = ""
Me.txtInv.Value = ""
Me.txtDate.Value = ""
Me.ComboBox1.Value = ""
Me.txtRegistration.Value = ""
Me.txtBales.Value = ""
Me.txtKgs.Value = ""
Me.txtRate.Value = ""
Me.txtValue.Value = ""
Me.txtStax.Value = ""
Me.txtTotal.Value = ""
Me.txtWtax.Value = ""
Me.txtNet.Value = ""
Me.TextBox14.Value = ""
Me.TextBox13.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox11.Value = ""
Me.TextBox6.Value = ""
Me.TextBox8.Value = ""
Me.txtdebit1.Value = ""
End Sub
Private Sub ComboBox1_afterupdate()
TextBox1 = Application.WorksheetFunction.VLookup(ComboBox1, Sheet2.Range("a2:b1000"), 2)
TextBox4 = ComboBox1
End Sub
Private Sub Party_Click()
frmCottonPurchase.Hide
frmParty.Show
End Sub
Private Sub TextBox11_Change()
End Sub
Private Sub TextBox14_Change()
End Sub
Private Sub txtDate_afterupdate()
With txtDate
.Value = Format(.Value, "dd/mm/yyyy")
End With
End Sub
Private Sub txtdebit1_Change()
End Sub
Private Sub txtKgs_afterupdate()
With txtKgs
.Value = Format(.Value, "###,##0.00")
End With
End Sub
Private Sub txtNet_Change()
With txtNet
.Value = Format(.Value, "###,##0.00")
TextBox6 = txtNet
End With
End Sub
Private Sub txtRate_afterupdate()
With txtRate
.Value = Format(.Value, "###,##0.00")
End With
txtValue = Round((CDbl(txtKgs) / CDbl(37.324) * CDbl(txtRate)), 0)
End Sub
Private Sub txtRegistration_Change()
End Sub
Private Sub txtStax_enter()
With txtStax
.Value = Format(.Value, "###,##0.00")
TextBox11 = txtStax
End With
txtTotal = CDbl(txtValue * 1) + CDbl(txtStax * 1)
TextBox13 = "SALES TAX ON COTTON"
End Sub
Private Sub txtTotal_enter()
With txtTotal
.Value = Format(.Value, "###,##0.00")
End With
txtWtax = Round(CDbl(txtValue) / CDbl(100) * 1, 0)
End Sub
Private Sub txtValue_afterupdate()
With txtValue
.Value = Format(.Value, "###,##0.00")
txtStax = Round(CDbl(txtValue) / CDbl(100) * 15, 0)
txtdebit1 = txtValue
End With
TextBox14 = "COTTON PURCHASE"
End Sub
Private Sub txtVdate_afterupdate()
With txtVdate
.Value = Format(.Value, "dd/mm/yyyy")
End With
End Sub
Private Sub txtWtax_enter()
With txtWtax
.Value = Format(.Value, "###,##0.00")
TextBox8 = txtWtax
End With
txtNet = txtTotal - txtWtax
TextBox3 = "GINNER'S WITHHOLDING TAX PAYABLE"
End Sub
Private Sub UserForm_activate()
Party.Activate
ComboBox1.ColumnCount = 1
ComboBox1.RowSource = "a2:a1000"
End Sub
Private Sub UserForm_queryclose(cancel As Integer, closemode As Integer)
If closemode = vbFormControlMenu Then
cancel = True
MsgBox "Dear Syed Haider Ali Please click Exit Botton to Close the Form"
End If
End Sub
2 nd UserForm "frmParty"
Option Explicit
Private Sub CommandButton1_Click()
Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("Party")
irow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
If Trim(Me.TextBox1.Value) = "" Then
Me.TextBox1.SetFocus
MsgBox "Please Enter the Party Name"
Exit Sub
End If
ws.Cells(irow, 1).Value = Me.TextBox1
ws.Cells(irow, 2).Value = Me.TextBox2
ws.Cells(irow, 3).Value = Me.TextBox3
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
'sort the data
Columns("a:c").Select
Selection.Sort key1:=Range("A2"), order1:=xlAscending, Header:=xlYes, _
ordercustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
dataoption1:=xlSortNormal
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub CommandButton3_Click()
End
End Sub
Private Sub Cotton_Click()
frmParty.Hide
frmCottonPurchase.Show
End Sub
Private Sub TextBox1_Change()
TextBox1 = UCase(TextBox1)
End Sub
Private Sub TextBox2_Change()
End Sub
Private Sub TextBox3_Change()
TextBox3 = UCase(TextBox3)
End Sub
Private Sub UserForm_Click()
End Sub
Bookmarks