send data to other worksheet from user form

[CampbellC]

hi
newuser to vba i have a user form that is populated from one worksheet ( placed Orders) and 10 rows of 10 text boxes for each row selected data from list box
and then from cmd button save to sheet ( completed orders) that working fine
my Question is how do update the selected row in Placed orders sheet not to replace the row just to add Received at the end of the row with text box called txtrec value once i press the cmd button save
my vba in a mess with bits of code that dont work is there any way to remove unused code

 

[dmt32]

Hi welcome to forum
Always helpful to forum if, no matter how bad you think it is, you post the code you already have - plenty here to offer guidance

If you are new to userforms then worthwhile visiting sites like this one: How to Create Excel UserForm for Data Entry

which provides free guidance.

Dave

 

[CampbellC]

this is the form Frmreceiving this is the code to send it to the completeOrder sheet
what am try to do is to send the data ( received) to the PlaceOrder sheet so that status on the place ordersheet changes to received
that way if i try to and a received order then ill get msg telling me that it been done
thank in advance Campbell C
'save it to complete Order sheet (sheet3)
Dim x As Integer
Dim nextrow As Range
Dim nextrow1 As Range
Dim DateCus As Range
Dim Sh As Worksheet
Dim irow As Long
Unprotect_All
On Error GoTo cmdOrders_Click_Error
Sheet3.Activate
Me.AOrd8.Value = Format(Me.AOrd8.Value, "£##,###.00")
Me.BOrd8.Value = Format(Me.BOrd8.Value, "£##,###.00")
Me.COrd8.Value = Format(Me.COrd8.Value, "£##,###.00")
Me.DOrd8.Value = Format(Me.DOrd8.Value, "£##,###.00")
Me.EOrd8.Value = Format(Me.EOrd8.Value, "£##,###.00")
Me.FOrd6.Value = Format(Me.FOrd8.Value, "£##,###.00")

If Me.AOrd1.Value = "" Or Me.AOrd2.Value = "" Or Me.AOrd3.Value = "" Or Me.AOrd4.Value = "" Or Me.txtRecDate.Value = "" Then
MsgBox "There is insufficient data, Please return and add the needed information" & vbNewLine & "Eg: Order Information or Date "
Exit Sub
Else
Set nextrow = Sheet3.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0)
'Set nextrow = Sheet8.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

End If

If AOrd2.Value > "" Then
For x = 1 To 10
nextrow = Me.Controls("Aord" & x).Value
Set nextrow = nextrow.Offset(0, 1)

Next



End If

 

[CampbellC]

ive just changed the code to this on a second button so as not to over right the first code
If Me.AOrd1.Value = "" Or Me.AOrd2.Value = "" Or Me.AOrd3.Value = "" Or Me.AOrd4.Value = "" Or Me.txtRecDate.Value = "" Then
MsgBox "There is insufficient data, Please return and add the needed information" & vbNewLine & "Eg: Order Information or Date "
Exit Sub
Else
Set Sh = ThisWorkbook.Sheets("CompletedOrders")
If FrmReceiving.txtrowA.Value = "" Then
irow = [counta(CompletedOrders!b:b)] + 1
Else
irow = FrmReceiving.txtrowA.Value
End If
With Sh
.Cells(irow, 2) = irow - 1
.Cells(irow, 3) = FrmReceiving.TxtOnum.Value
.Cells(irow, 4) = FrmReceiving.txtRecDate.Value
.Cells(irow, 5) = FrmReceiving.AOrd3
.Cells(irow, 6) = FrmReceiving.AOrd2.Value '=supplier 'B
.Cells(irow, 7) = FrmReceiving.AOrd3.Value '=catergory 'C
.Cells(irow, 8) = FrmReceiving.AOrd4.Value '=product 'D
.Cells(irow, 9) = FrmReceiving.AOrd5.Value '= code 'E
.Cells(irow, 10) = FrmReceiving.AOrd6.Value ' =unit price 'F
.Cells(irow, 11) = FrmReceiving.AOrd7.Value '= sale price 'G
.Cells(irow, 12) = FrmReceiving.TxtRec.Value '= min qty 'H


End With
End If

 

[CampbellC]

both work great at sending datato the completeOrder sheet but am stuck on how to update status on Placed order sheet
any help would be appreciated

 

[dmt32]

Hi,

Just for future info, when posting code you will see on toolbar, VBA button, press it & it will produce code tags – place your code between the tags.

Now Seeing what you are trying to achieve my personal view is you are using the wrong application - have you considered using MS Access which I think would be better suited to your project? There are plenty of ready-made templates you can download & a help forum here should you need it.

The problem with Excel Userforms is that they require you to do all the coding & judging by yours, likely prove to be very complex – If you still want to continue using Excel then suggest place copy of your workbook with dummy data in a file sharing site like dropbox & maybe someone can take time to review your project & assist solving your issue.

Dave

 

[CampbellC]

thank you for you reply ill carry on with and see if i can get it working

 

[CampbellC]

I managed to get it sorted re wrote it different

 

[smozgur]

Glad to hear you got the solution.

If you would like to post the solution as you rewrote it different then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

 

[CampbellC]

Can i post a example of my inventory here as it quite big and lot of code most of it from other codes
am not that clever yet with plenty of defunct code on it