Passing variables from a User Form

[steveh8204]

I have created a user form that displays the contents of a selected row (via a 'find' function) and then displays in editable fields in the form. I now want to be able to send the new contents back to another function to update the row but I don't know how to send the contents of the updated control to the 'button click' of the control that initiates the function.

How do I send the contents of the updated controls to the 'button click :function? As far as I know button click functions don't take arguments?

Hope I've explained the issue well.

 

[Akuini]

Could you post your code?

 

[steveh8204]

The code to find and display a record is as follows. I've attached a screenshot of the userform (findRecord) too.

When I click the 'Update Record' button (UpdateRecordBtn) I want the code in 'DateInputBox' and 'IssueTextBox' *(which are both TextBox controls) to be updated to the row (foundRow):

Private Sub FindBtn_Click() ' sub to display contents of the requested row in the dialog box
    Dim ws As Worksheet
    Dim searchColumn As Range
    Dim cell As Range
  
    Dim searchValue As String
  
    ' Set the worksheet and search column
    Set ws = ThisWorkbook.Sheets("Action register") ' ---*** Change to your sheet name if sheet name changed ***---
    Set searchColumn = ws.Range("B:B") ' ** Change to your search column **
  
    ' Get the value from SelectedID control
    searchValue = Me.SelectedID.Value
  
    ' Find the row containing the search value
    On Error Resume Next
    Set cell = searchColumn.Find(What:=searchValue, LookIn:=xlValues, LookAt:=xlWhole)
    On Error GoTo 0
  
    If Not cell Is Nothing Then
        foundRow = cell.row
    Else
        foundRow = -1 ' Indicate not found
    End If
  
        Set ws = ThisWorkbook.Sheets("Action register") ' ---*** change to name of spreadsheet (unless using default) ***---
  
    ' Assign the values from the found row to dialog box labels
    DateInputBox = ws.Cells(foundRow, 1).Value
    IssueTextBox = ws.Cells(foundRow, 3).Value

End Sub

 

[steveh8204]

Could you post your code?

Sorry should have posted below as a 'Reply'.

 

[Akuini]

' Assign the values from the found row to dialog box labels
DateInputBox = ws.Cells(foundRow, 1).Value
IssueTextBox = ws.Cells(foundRow, 3).Value

DateInputBox & IssueTextBox are textbox?
After you run "Sub FindBtn_Click", DateInputBox & IssueTextBox get the found cells value. And then what? you will manually change both textboxes value & then you want to send the values back to the found cells?

 

[steveh8204]

DateInputBox & IssueTextBox are textbox?
After you run "Sub FindBtn_Click", DateInputBox & IssueTextBox get the found cells value. And then what? you will manually change both textboxes value & then you want to send the values back to the found cells?

Yes, exactly that.

 

[Akuini]

Here's an option:
1. Declare foundRow as Long at the top of Userform module (outside any procedure)

2. In "UpdateRecordBtn_Click" you use foundRow like this:

Dim foundRow As Long ' <-- at the top of Userform module (outside any procedure)

Private Sub UpdateRecordBtn_Click()
With ThisWorkbook.Sheets("Action register")
    .Cells(foundRow, 1).Value = DateInputBox
    .Cells(foundRow, 3).Value = IssueTextBox
End With
End Sub

 

[steveh8204]

Here's an option:
1. Declare foundRow as Long at the top of Userform module (outside any procedure)

2. In "UpdateRecordBtn_Click" you use foundRow like this:

Dim foundRow As Long ' <-- at the top of Userform module (outside any procedure)

Private Sub UpdateRecordBtn_Click()
With ThisWorkbook.Sheets("Action register")
    .Cells(foundRow, 1).Value = DateInputBox
    .Cells(foundRow, 3).Value = IssueTextBox
End With
End Sub

Works like a charm, thanks very much!

 

[Akuini]

Works like a charm, thanks very much!

You're welcome, glad to help & thanks for the feedback.
One thing though:
I think your code in post #3 can be simplified. When you open the userform, do you only deal with data in one specific sheet, in this case Sheets("Action register")?
If so, then you can just activate it in Sub UserForm_Initialize(), then you don't need to qualify the sheet of any cells or range. It will be easier to maintain the code.

 

[steveh8204]

You're welcome, glad to help & thanks for the feedback.
One thing though:
I think your code in post #3 can be simplified. When you open the userform, do you only deal with data in one specific sheet, in this case Sheets("Action register")?
If so, then you can just activate it in Sub UserForm_Initialize(), then you don't need to qualify the sheet of any cells or range. It will be easier to maintain the code.

Good shout, didn't really think (or know how) to do that but just had a think about how to do it (try not to just go straight to ChatGPT as normal) and figured it out in a couple of minutes. Now the sheet name is just declared in the initialize and I've been able to get rid of all the declarations/ws. etc. Will come in very handy, thanks again!