Edit Text boxes Without Combobox

[adam]

Hi Anyone,

Based upon the code given at Post=19110 my final code is as follows.

Code: Select all

Private Sub cmdEdit_Click()
  Dim r As Long
  Select Case Me.cboSelection
  Case "Orders"
  For r = 1 To Range("Serial").Count
    If Range("Serial").Cells(r) = Val(Me.txtSerial.Value) And _
        Range("Code").Cells(r) = Val(Me.txtCode.Value) Then
      Exit For
    End If
    
  Next r
  If r > Range("Serial").Count Then
    MsgBox "Serial Number/Code combination not found", vbExclamation
    Exit Sub
  End If
  Range("Date").Cells(r).Value = CDate(txtDate.Value)
  Range("Code").Cells(r).Value = txtCode.Value
   MakeList
Case "Memos"
  On Error Resume Next
r = Application.WorksheetFunction.Match(CLng(Me.txtSerialNo.Value), _
    Range("Serials"), 0)
  If Err Then
    MsgBox "Serial Number not found", vbExclamation
    Exit Sub
  End If
  On Error GoTo 0
  Range("CustomerID").Cells(r).Value = txtCustomerID.Value
  Range("ReceiptNo").Cells(r).Value = txtReceiptNo.Value
  End Select
  End Sub

Since use of the combo box adds additional tasks to the user; my question of concern is that how I could make the edit button work without having the user to select either Orders 1 or Memos 2 from the combo box?

Any help would be kindly appreciated.

[HansV]

But the code does different things depending on the item selected in the combo box. What would you want to happen if there is no combo box?

[adam]

The combo box allows selecting the sheet where the edited values from the textbox need to be copied to.

Suppose if the sheet Orders is selected and the edit button is clicked it copies the edited values from the text boxes to the sheet Orders as in Here
And if Memos is selected and the text box values the edit button copies the values in the text boxes to the sheet memos.
But to do this the user has to select the sheet from the combo box.

My intension is to remove the combo box and make the code work, same as above; without having the user to select the sheet.

[HansV]

Do you mean that the code should always act on the currently active sheet?

[adam]

Suppose If I edit the text box values related to the sheet Orders and press the edit button, I need the ranges related to the sheet Orders to be edited. And likewise If I edit the text box values related to the sheet Memos I need the ranges related to the sheet Memos to gets edited and copied to the sheet Memos.

Furthermore, If both the text boxes related to the sheet Orders & Memos are edited simultaneously I need the values in both sheets to get edited & get copied to the related sheets.

I hope I have made my question clear.

[HansV]

Does this work?

Code: Select all

Private Sub cmdEdit_Click()
  Dim r As Long
  For r = 1 To Range("Serial").Count
    If Range("Serial").Cells(r) = Val(Me.txtSerial.Value) And _
        Range("Code").Cells(r) = Val(Me.txtCode.Value) Then
      Range("Date").Cells(r).Value = CDate(txtDate.Value)
      Range("Code").Cells(r).Value = txtCode.Value
      MakeList
      Exit For
    End If
  Next r
  On Error Resume Next
  r = Application.WorksheetFunction.Match(CLng(Me.txtSerialNo.Value), _
    Range("Serials"), 0)
  If Err = 0 Then
    Range("CustomerID").Cells(r).Value = txtCustomerID.Value
    Range("ReceiptNo").Cells(r).Value = txtReceiptNo.Value
  End If
End Sub

[adam]

Thanks Hans. It worked.