UserForm and Range

Question

I have an Excel sheet with column D (column 4) being a dropdown list for every row with 2 choices :

• Yes
• No

When I click No, I have a Userform pop up with a simple "text zone" asking to enter a value and a "Submit button" to validate.

When the "Submit button" is clicked, I want the value from the "text zone" to be implemented into the cell to the right : offset(0,1).

Example : D5 : "No" -> "Enters 5 in Userform" -> E5: "5"

Here is my code so far :

Worksheet :

Private Sub Worksheet_Change(ByVal Target As Range)
    If ActiveCell.Column = 4 Then
        If ActiveCell.Value = "no" Then
            UserForm1.Show
        End If
    End If
End Sub

UserForm :

Private Sub CommandButton1_Click()
    ActiveCell.Offset(0, 1).Value = TextBox1.Value
    UserForm1.Hide
End Sub

If I put the UserForm1.Hide before the ActiveCell it does what I want but the UserForm won't close. If I take out the ActiveCell the UserForm closes but I can't seem to make both work at once.

Answer 1

You're changing cells in the Worksheet_Change handler, which means if you didn't have a form to block the UI, you'd quickly blow the call stack and run into an "Out of stack space" error, also known as a... stack overflow.

You need to prevent your Worksheet_Change handler from calling itself recursively.

And this can be done by turning off Application.EnableEvents before you make the change, and toggling it back on afterwards:

Application.EnableEvents = False
ActiveCell.Offset(0, 1).Value = TextBox1.Value
Application.EnableEvents = True

Now, see what the problem is with that? How does the form know that it's being invoked from a Worksheet_Change handler and so that it needs to toggle Application.EnableEvents? It doesn't know - and right now, it's assuming it.

This is a problem, only because the form is running the show. Flip things around, and leave the form as stupid as it can possibly be, and make the Worksheet_Change handler responsible for making the sheet changes and toggling theApplication.EnableEvents state:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 4 And Not IsError(Target.Value) Then
        If Target.Value = "no" Then
            With New UserForm1
                .Show
                If .Proceed Then
                    Application.EnableEvents = False
                    Target.Offset(0, 1).Value = .Contents
                    Application.EnableEvents = True
                End If
            End With
        End If
    End If
End Sub

Several things:

1. The cell that triggered the event is the Target - use that over ActiveCell.
2. If the value of that cell is #N/A or any other cell error value, your code blows up. Use IsError to verify whether it's safe to compare the cell's value with anything first.
3. The form now needs Proceed and Contents properties, and can't be allowed to self-destruct.
4. The calling code doesn't care about any textboxes: it doesn't know how Contents is getting populated - that's the form's concern.

So what would the form's code-behind look like now?

Option Explicit
Private mProceed As Boolean
Private mContents As String

Public Property Get Proceed() As Boolean
    Proceed = mProceed
End Property

Public Property Get Contents() As String
    Contents = mContents
End Property

Private Sub TextBox1_Change()
    mContents = TextBox1.value
End Sub

Private Sub CommandButton1_Click()
    mProceed = True
    Me.Hide
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = VbQueryClose.vbFormControlMenu Then
        Cancel = True
        Me.Hide
    End If
End Sub

Now all the form does, is collect data, and expose it for the calling code to see: it doesn't know or care about any ActiveCell or worksheet - it collects data, and exposes it for the calling code to see. Nothing more, nothing less.