9434

UserForm and Range

Question:

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 :

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.

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 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:

<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/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.</li> <li>The form now needs Proceed and Contents properties, and can't be allowed to self-destruct.</li> <li>The calling code doesn't care about any textboxes: it doesn't know how Contents is 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>.

Recommend

  • VBA Excel sorting on multiple columns
  • Excel - VBA fill in cells between 1st and Last value
  • Show data after POST with AngularJS
  • Excel VBA QueryTable Parameter Fails with Null Value
  • Django - Form bind data after initialization
  • VBA: Howto open a file, that has # -characters in it's name?
  • Angular 1.5 Components with Form and $setPristine
  • VBA USERFORM: PREVENT COMBOBOX ESCAPE ON KEYDOWN
  • Using triggers to implement referential integrity actions (SQL Server)
  • asp.net GridView IF empty show message
  • java - memory usage
  • VB.NET Iterating through objects of a structure
  • PHP Queue System with Codeigniter. HOW? [closed]
  • Excel VBA Intersect
  • System call time out?
  • Primefaces lazy datascroller calling load twice
  • cell spacing in div table
  • How to access meteor package name inside package?
  • Rest Services conventions
  • what makes a request a new request in asp.net C#
  • SonarQube: Cannot deactivate rule with missing quality profile
  • Suppressing passwd when calling sqlplus from shell script
  • Tamper-proof configuration files in .NET?
  • Excel's Macro-Recorder usage
  • JQuery Internet Explorer and ajaxstop
  • Android full screen on only one activity?
  • Java: can you cast Class into a specific interface?
  • Optimizing database types to compact database (SQLite)
  • AES padding and writing the ciphertext to a disk file
  • Excel - Autoshape get it's name from cell (value)
  • TFS: Get latest causes slow project reloading
  • Updating server-side rendering client-side
  • How to extract text from Word files using C#?
  • Running a C# exe file
  • vba code to select only visible cells in specific column except heading
  • R: gsub and capture
  • jqPlot EnhancedLegendRenderer plugin does not toggle series for Pie charts
  • Comma separated Values
  • Reading document lines to the user (python)
  • How to load view controller without button in storyboard?