Calculate worksheet when combobox or textbox data is changed by user

[cvrband]

I have a worksheet that has many comboboxes (drop-down lists) and many textboxes for user input. The workbook is set to manual calculation but I have the following VBA on this sheet to calculate the sheet on change:

Private Sub Worksheet_Change(ByVal Target As Range)        
    Worksheets("DATA").Calculate
End Sub

When a new item is selected in one of the many comboboxes or new data is input in any of the textboxes, that code does not seem to recognize a change on the sheet and does not calculate. Can someone help expand that code to recognize combobox or textbox changes to fire the sheet calculate?

Thank you.

 

[JustynaMK]

Hi,

I suspect this will require some work with Class Modules. Before I even begin, could you please confirm if you are using "Form Controls" or "ActiveX Controls" for your comboboxes and textboxes?

 

[cvrband]

Hello JustynaMK - thank you for your assistance.

I'm not sure which type was used as I'm not the original creator of this worksheet. In Design Mode, I clicked on the boxes and review their properties but the properties section does not appear to provide the type (Form / ActiveX). Can you direct me on how to determine this for you?

 

[JustynaMK]

No worries! Once you're in the Design Mode, right-click on any Combo Box and check if you have access to "Properties" (drop-down list). If yes, it means you are looking at ActiveX object.
ActiveX object allows you to modify "Properties" or "View Code".
Form object allows you to "Assign Macro" and there is no option for "Properties".

 

[cvrband]

Interesting - that's good information! It appears they are ActiveX since the drop-down allows me to see properties and code.

 

[JustynaMK]

Awesome, thanks for confirming. I will come back to you in a few hours with an update.

 

[JustynaMK]

Hi,

We need to follow a few steps in order to complete this task. I really recommend checking this blog post - it has been written for Form Controls but the principle is the same:
https://www.jkp-ads.com/Articles/ControlHandler00.asp

Step 1
Go to VBA > Insert > Class Module
It will be named as "Class1" by default but let's change it to cCtlEvents (hit F4 to call "Properties" window and change (Name) from "Class1" to "cCtlEvents").

Step 2
Paste the following code into your Class Module code window. This class will handle the Control Events.

Option Explicit


Public WithEvents ctlCombo  As MSForms.ComboBox
Public WithEvents ctlText   As MSForms.TextBox


Private Sub ctlCombo_Change()
    Application.Calculate
End Sub


Private Sub ctlText_Change()
    Application.Calculate
End Sub


Private Sub Class_Terminate()
    Set ctlCombo = Nothing
    Set ctlText = Nothing
End Sub

Step 3
Go to VBA > Insert > Module

Option Explicit
Option Private Module


Private m_colCTLEVENTS      As Collection


Public Sub EnableControlsEvents()
    Dim clsCtlEvents        As cCtlEvents
    Dim ctlTarget           As OLEObject
    
    If m_colCTLEVENTS Is Nothing Then
        Set m_colCTLEVENTS = New Collection
    End If
    
    For Each ctlTarget In [COLOR=#ff0000]Sheet1[/COLOR].OLEObjects
        Debug.Print TypeName(ctlTarget.Object)
        If TypeName(ctlTarget.Object) = "TextBox" Then
            Set clsCtlEvents = New cCtlEvents
            Set clsCtlEvents.ctlText = ctlTarget.Object
            Call m_colCTLEVENTS.Add(clsCtlEvents)
        End If
        If TypeName(ctlTarget.Object) = "ComboBox" Then
            Set clsCtlEvents = New cCtlEvents
            Set clsCtlEvents.ctlCombo = ctlTarget.Object
            Call m_colCTLEVENTS.Add(clsCtlEvents)
        End If
    Next ctlTarget
End Sub


Public Sub DisableControlEvents()
    Set m_colCTLEVENTS = Nothing
End Sub

This will instantiate the class from Step 2 and pair the controls with the control objects in the class, thus allowing us to hook the control events.
Note - this code currently works only for Sheet1 (highlighted).

Step 4
Go to VBA > double-click on "ThisWorkbook"

Option Explicit


Private Sub Workbook_Open()
    Call EnableControlsEvents
End Sub


Private Sub Workbook_Activate()
    Call EnableControlsEvents
End Sub


Private Sub Workbook_Deactivate()
    Call DisableControlEvents
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call DisableControlEvents
End Sub

This will trigger the process whenever you Open / Activate your Workbook and it will terminate the class again on Close / Deactivate.

Hope it helps!

 

[cvrband]

Wow, that was a lot of effort on your part! I was not expecting the code to be so elaborate. I thought there would be a little tweak to my original code to make it work.

With the changes made/added to the spreadsheet code as directed, the worksheet still does not calculate on a change of the combobox and textboxes. I do not get any errors with the added code, but it still does not calculate the sheet on change of any of the text/comboboxes. I feel like I've done so already, but to not waste any more of your time, I think it will be best to rework this sheet to make it less cumbersome rather than fight with the coding.

I truly appreciate your time and effort. Thank you.

 

[JustynaMK]

No worries at all! If it helps you can upload your file somewhere on the server, paste the link here on the forum and I can take a look later today.

 

[JustynaMK]

+ alternatively, if you do not have too many Combo Box / Text Box objects, you can simply go to Design Mode > Right-click on an object > View Code, select "Change" event and use the following code:

Private Sub ComboBox1_Change()
    Application.Calculate
End Sub

This will simply re-calculate the whole workbook whenever you make any change to ComboBox1. However, the code works only for this one object, so you would need to repeat the process manually for each ActiveX control.