Call row data into userform with VBA

[simi_uk]

thanks for stopping by, can you help??

I'm using a worksheet as an ordering system with multiple command functions. One function of the system allows users to place a new demand, and this is carried out using a UserForm, the code for which is below:

Private Sub placeDMD_Click()
Worksheets("DEMANDS").Unprotect Password:="password"
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("DEMANDS")
 
'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row
'check for a part number
If Trim(Me.ptnum.Value) = "" Then
  Me.ptnum.SetFocus
  msgbox "Please enter a part number"
  Exit Sub
 
End If
'check for a description
If Trim(Me.desc.Value) = "" Then
  Me.desc.SetFocus
  msgbox "Please enter a description"
  Exit Sub
End If
 
'check for a section/sqn
If Trim(Me.ComboBox1.Value) = "" Then
  Me.ComboBox1.SetFocus
  msgbox "Please enter your Section Or Sqn"
  Exit Sub
End If
'check for a Name / Rank
If Trim(Me.Name_Rank.Value) = "" Then
  Me.Name_Rank.SetFocus
  msgbox "Please enter your Name..."
  Exit Sub
End If
 
'copy the data to the database
Dim rng As Range
Set rng = Cells(Rows.Count, 1).End(xlUp)
rng.Offset(1) = rng + 1
ws.Cells(iRow, 2).Value = Date
ws.Cells(iRow, 3).Value = Me.sectref.Value
ws.Cells(iRow, 4).Value = Me.ptnum.Value
ws.Cells(iRow, 5).Value = Me.desc.Value
ws.Cells(iRow, 6).Value = Me.qty.Value
ws.Cells(iRow, 7).Value = Me.rdd.Value
ws.Cells(iRow, 8).Value = Me.pty.Value
ws.Cells(iRow, 9).Value = Me.ACtailNo.Value
ws.Cells(iRow, 10).Value = Me.SNOW.Value
If ComboBox2.Value = "" Then
ws.Cells(iRow, 11).Value = Me.ComboBox1.Value
Else: ws.Cells(iRow, 11).Value = Me.ComboBox2.Value
End If
 
ws.Cells(iRow, 12).Value = Me.IPT.Value
ws.Cells(iRow, 13).Value = Me.IPT_contact.Value
ws.Cells(iRow, 14).Value = Me.remarks.Value
ws.Cells(iRow, 15).Value = Me.Name_Rank.Value
'clear the data
Me.sectref.Value = ""
Me.ptnum.Value = ""
Me.desc.Value = ""
Me.qty.Value = ""
Me.rdd.Value = ""
Me.pty.Value = ""
Me.ACtailNo.Value = ""
Me.SNOW.Value = ""
Me.ComboBox1.Value = ""
Me.ComboBox2.Value = ""
Me.IPT.Value = ""
Me.IPT_contact.Value = ""
Me.Name_Rank.Value = ""
Me.sectref.SetFocus
Worksheets("Demands").Protect Password:="password"
End Sub

The system is working very well and also has functions to search and cancel the demands as necessary. However my boss now wants it to be extended to allow amendments to a row should the user input, for example, an incorrect part number, or ajdust the quantity required.

Is it possible, using the search code below to locate the demand that is subject to amendment, and call the cell data from that row into a userform and allow data amendment before resubmitting it into the original row leaving 'Column A' (the demand number) unchanged?

Sub Find_Button()
Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer
On Error Resume Next
currentSheet = ActiveSheet.Index
datatoFind = InputBox("Please enter your search:")
If datatoFind = "" Then Exit Sub
sheetCount = ActiveWorkbook.Sheets.Count
If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)
For counter = 1 To sheetCount
Sheets(counter).Activate
Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlValues, LookAt _
:=xlWhole, SearchOrder:=xlColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate
If ActiveCell.Value = datatoFind Then Exit Sub
Next counter
If ActiveCell.Value <> datatoFind Then
msgbox ("The search returned no hits.")
Sheets(currentSheet).Activate
End If
End Sub

 

[simi_uk]

can anybody help with this please??

 

[simi_uk]

bump....

 

[simi_uk]

bump....

i guess i'm either not making sense, or there's nobody who can help...

if its the former, then allow me to reiterate what i'm trying to get a solution for.

Basically, a user has access to a protected sheet and is able only to place an order for an item using a userform. He/she is also able to cancel that same order, but not currently amend it. Is it, therefore, possible to get the data found in cells (row,3) thru (row, 13) - as found by the search code above - to populate a new userform, called "Amend_DMD" for arguments sake, with the same textboxes so that they can be amended as required and overwrite the original data when a command button is clicked?

i have found this example of code, but i'm not sure how to work it into the above...

WITH Worksheets("sheet1")

' get the row
myRow = WorksheetFunction.Match(txtFileNum, .Range("A:A"),False)
' note - raises an error if not found, so yuo may need to handle it
'now populate the userform controls...
for colIndex = 2 to n
cntrl(index) = .Cells(myRow, colIndex)
next
'
END WITH

using the above code, i also know that the for...next can be replaced by directly
coding in the text box content thus:

txtbox1 = .Cells(myrow,2)
txtbox2 = .Cells(myrow,3)

My problem is finding out how to amalgamate the search for the record to be amended,
the population of the userform with data from the row containing the search data,
and then using this to overwrite the amended data into the row it cam from...
</pre>