code doesn't work using userform but just work inside the sheet

[Mussa]

Hi guys
this code should work when I copy data from userform to sheet
when copy data from userform to sheet and fill column B should populate date in column A and should populate calculation in column H= column F* column G, but doesn't work totally , just when I write the values inside the cell from inside the sheet will be work !
can any body help please?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Intersect(Target, [B:B]) Is Nothing Or Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, [B:B]) Is Nothing Then
    Target.Offset(, -1) = Date
   Range("H2", Range("F" & Rows.Count).End(xlUp).Offset(, 2)).Formula = "=F2*G2"
   Range("H2", Range("F" & Rows.Count).End(xlUp).Offset(, 2)).Value = Range("H2", Range("F" & Rows.Count).End(xlUp).Offset(, 2)).Value
  End If
End Sub

 

[Jimmypop]

Could you maybe provide some sample data via XL2BB?

 

[Mussa]

Hi,
I'm not sure why you want data ?
any way here is the data should be after copy data from userform to sheet . as I said the problem in column A(DATE), , column H (calculation between columns F,G)

v04 a.xlsm
	A	B	C	D	E	F	G	H
1	DATE	CODE	BRAND	BATCH	DEL N	QTY	PRICE	AMOUNT
2	26/04/2023	CCS-001	CTR-001	BTR-001	CDF-00	200.00	10.00	2,000.00
3	26/04/2023	CCS-002	CTR-002	BTR-002	CDF-01	200.00	10.00	2,000.00
4	26/04/2023	CCS-003	CTR-003	BTR-003	CDF-02	200.00	10.00	2,000.00
5	26/04/2023	CCS-004	CTR-004	BTR-004	CDF-03	200.00	10.00	2,000.00
BRANDS

 

[Jimmypop]

I am of the thought that you might need to write this code into the userform code itself when sending to the sheet.. I have taken the data and yes it does work on manual input but seeing as this is in a Worksheet Change event it will only work when you change the sheet manually... Might be wrong... I am just not sure what your userform code looks like to send the info to the sheet ..

 

[Mussa]

but I tested this procedure with another tab for multipage surprisingly works !
I no know why this tab doesn't work with this sheet .
the code is

Private Sub CommandButton1_Click()
 
Dim i As Long, j As Long
Dim LstRw As Long, CurrCon As String, ws As Worksheet
Dim va, a As Long, b As Long, h As Long

Set ws = sheet2
'find last used row
LstRw = ws.Range("B" & Rows.Count).End(xlUp).Row

    h = 11 * 7    'columns count on userform are 7,rows are 11
    a = 1
    ReDim va(1 To 11, 1 To 7)

    For i = 1 To h 'loop userform textbox
          b = b + 1
            va(a, b) = Me.Controls("Textbox" & i + 65).Value   'textboxes number start at 66

              If b = 7 Then a = a + 1: b = 0
    Next i

ws.Range("B" & LstRw + 1).Resize(UBound(va, 1), UBound(va, 2)) = va
MsgBox "  ok"
Dim m, n As Long
For m = 66 To 145
For n = 99 To 101

Me.Controls("textbox" & m) = ""
Me.Controls("label" & n) = ""
Next
Next
End Sub

 

[Jimmypop]

Strange... Will have another look and advise

 

[Peter_SSs]

Hello

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: why when copy data from user form don't change by using worksheet change event ?
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.