how to retrieve a value from an userform

Question

I'm trying to write a code to trace every change made by the user on any worksheet. The user will input data and from time to time will erase said data and/or correct the original value they inserted. If the change is either deletion or modification, an Userform will pop up and the user will include a reason for that change. Right now I'm able to display the form everytime the user makes one of the changes mentioned before, but I'm not able to retrieve the reason, could you guys help me?

This is what I have for the UserForm

Private Sub CommandButton1_Click()
Dim msgvalue As VbMsgBoxResult
Dim value As String
msgvalue = MsgBox("Do you wish to save the change?", vbYesNo + vbInformation, "Confirm")
    If msgvalue = vbNo Then GoTo Command 
    If msgvalue = vbYes Then
        value = UserForm1.txtCmmt.Value
        If value = "" Then GoTo Command
        End
    End If
Command:
MsgBox ("A reason must be provided")
    With UserForm1
        .txtCmmt.Value = ""
    End With
End Sub

So if a user tries to delete a value, the code is the following:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Dim sLastAction As String
Dim Cell As Range
sLastAction = Application.CommandBars("Standard").Controls("&Undo").List(1)
For Each Cell In Target
    If sLastAction = "Clear" Or sLastAction = "Delete" Or Left(sLastAction, 9) = "Typing ''" Then
       
        UserForm1.Show 'this is where I'm stuck, I'm not sure how to retrieve the value from the form
    End If
'the code continues to retrieve other info from the changes made, including the "reason"

Thanks for the help!

Answer 1

Try the next way, please:

1. Let us say that your text box where the comment will be written is named "txtComment".
2. Put this code in its Exit event:

Private Sub txtComment_Exit(ByVal Cancel As MSForms.ReturnBoolean)
 If Me.txtComment.text <> "" Then
    If ActiveSheet.Name <> "LogDetails" Then
        Application.EnableEvents = False
         Sheets("LogDetails").Range("A" & rows.count).End(xlUp).Offset(0, 5).Value = Me.txtComment.text
        Application.EnableEvents = True
        Unload Me
    End If
 End If
End Sub

Let the existing Worksheet_Change event as it is, only launching the form and maybe making a Public boolean variable (from a standard module) True (something boolStop) which will not allow changing anything in any worksheet until it is not False.

Then fill the text you need in the text box ("txtComment", or however you named it) and press Enter. If my above suggestion looks interesting for you, the last line of the text box event will be boolStop = False.

If you understand how to implement the above simple solution, you maybe will forget about a user form and use a simple InputBox, as in the next example:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) <> "E1" Then Exit Sub
    Dim sLastAction As String, comValue As String
    Dim Cell As Range
    sLastAction = Application.CommandBars("Standard").Controls("&Undo").list(1)
    For Each Cell In Target
        If sLastAction = "Clear" Or sLastAction = "Delete" Or left(sLastAction, 9) = "Typing ''" Then
WritePlease:
            comValue = InputBox("Please write the reason for cell """ & Cell.Address & """ (" & sLastAction & ").", "Reason, please")
              If comValue = "" Then GoTo WritePlease
              If ActiveSheet.Name <> "LogDetails" Then
                  Application.EnableEvents = False
                    'Stop
                   Sheets("LogDetails").Range("A" & rows.count).End(xlUp).Offset(0, 5).Value = comValue
                  Application.EnableEvents = True
              End If
        End If
    Next
End Sub