I have an Excel sheet with column D (column 4) being a dropdown list for every row with 2 choices :<ul><li>Yes</li> <li>No</li> </ul>
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 :
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
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.Answer1:
You're changing cells <em>in the Worksheet_Change handler</em>, 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... <em>stack overflow</em>.
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 <em>doesn't</em> know - and right now, it's <em>assuming</em> it.
This is a problem, only because <em>the form is running the show</em>. 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 <em>and</em> toggling the
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:<ol><li>The cell that triggered the event is the
Target- use <em>that</em> over
ActiveCell.</li> <li>If the value of that cell is
#N/Aor any other cell error value, your code blows up. Use
IsErrorto verify whether it's safe to compare the cell's value with anything first.</li> <li>The form now needs
Contentsproperties, and can't be allowed to self-destruct.</li> <li>The calling code doesn't care about any textboxes: it doesn't know how
Contentsis getting populated - that's the form's concern.</li> </ol>
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. <em>Nothing more, nothing less</em>.