abhijeets1991

New Member
Joined
Mar 12, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
I have 25 text boxes named in the following manner on a UserForm
Name: id_[X]_box 1<= x <= 25
I am trying to write a program which can register a change event for all 25 boxes and populate the corresponding [DESCRIPTION] Labels.
Naming scheme for Description Labels Name: desc_[X]_label 1 <= X <= 25

When I program for a change event for just one box (i.e id_box_1), the functionality works fine.
When I try to implement for the 25 boxes with WithEvents and ClassModules, I am getting an error "Can't compile Module"

The form's Name: links

Please see relevant code snippets below

Code in the UserForm_Initialize function
VBA Code:
Private Sub UserForm_Initialize()
'Code to make single change event subroutine register for all id_[INT]_textboxes on links form
Dim ctrl As MSForms.Control
Dim text_box_handler As text_boxes_change

Set textBox_collection = New Collection

For Each ctrl In Me.controls

    If TypeOf ctrl Is MSForms.TextBox Then
        If Split(ctrl.Name, "_")(0) = "id" Then
            Set text_box_handler = New text_boxes_change
            Set text_box_handler.control_text_box = ctrl
            textBox_collection.Add text_box_handler
        End If

    End If

Next ctrl


End Sub

Custom Class Module Code
Class Module Name: text_boxes_change
VBA Code:
Option Explicit

'This class assists in validating multiple text boxes on forms without having to define event funtions for each text box separately

'Global Constants
Const CASHFLOW As String = "Chart"
Const SETUP As String = "Settings"
Const INVOICE_STATUSES As String = "K13:K18"
Const TIME_UNITS As String = "L21:L24"
Const RELATION_TYPES As String = "M21:M25"
Const ACTIVITIES_COL As String = "T"
Const PROJ_START_ROW As Integer = 6

Public WithEvents MyTextBox As MSForms.TextBox

Public Property Set control_text_box(ByVal tb As MSForms.TextBox)
    Set MyTextBox = tb
End Property

Public Sub BoxesGroup_Change()
    'Setting default background color for the box
    Me.MyTextBox.BackColor = RGB(255, 255, 255)
    
    'Setting up Cashflow Worksheet Object
    Dim cashflow_sheet As Worksheet
    Set cashflow_sheet = Sheets("Chart")
    
    'Finding lastrow with text inside the Sub-Activites column in Chart sheet
    Dim lastrow As Integer
    lastrow = cashflow_sheet.Cells(Rows.Count, ACTIVITIES_COL).End(xlUp).Row
    
    'Range to represent the activities column in Chart worksheet
    Dim activities_range As Range
    Set activities_range = cashflow_sheet.Range(ACTIVITIES_COL & CStr(PROJ_START_ROW) & ":" & ACTIVITIES_COL & CStr(lastrow))
    
    'A variable to store the user inputed value for id_box
    Dim row_id As String
    MsgBox ("salar")
    row_id = Me.MyTextBox.value
    
    If IsNumeric(row_id) = True Then
        If CInt(row_id) >= PROJ_START_ROW And CInt(row_id) <= lastrow Then
            Dim desc_caption As String
            'SheetFunctions is a Module ;  links_description is a Function that returns a string representing a cell address based on the rules of the workbook; functionality is tested and verified for this part
            desc_caption = SheetFunctions.links_description(row_id)
            If desc_caption <> "" Then
                Me.MyTextBox.BackColor = RGB(255, 255, 255)
                Me.desc_1_label.Caption = desc_caption
            Else
                Me.MyTextBox.BackColor = RGB(140, 39, 30)
            End If
        Else
            Me.MyTextBox.BackColor = RGB(140, 39, 30)
        End If
        
    Else
        Me.MyTextBox.BackColor = RGB(140, 39, 30)
    End If
End Sub

Kindly provide any insights.
Thank You
 

Attachments

  • links_form_screenshot.png
    links_form_screenshot.png
    35.5 KB · Views: 8

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,469
When I try running your code, I don't get a "Can't compile Module" error. Maybe the error has something to do with some other module? In any case, I do see some issues which will prevent it from working.

UserForm Module While you're creating an instance of textBox_collection within the Initialize event, you'll need to declare it at the userform module level (at the top of the userform module, before any procedure) so that it can be available after the Initialize event and while the userform is running...

VBA Code:
Dim textBox_collection As Collection

Class Module Since you've named your textbox variable MyTextBox, the change event should be MyTextBox_Change, not BoxesGroup_Change. If you select MyTextBox from the left drop down menu for Objects, the correct change event will automatically be added to your code...

VBA Code:
Private Sub MyTextBox_Change()
    'your code here
    '
    '
End Sub

Also, if you click on the right drop down menu for Procedure, you see all of the available events for your textbox.

One other point, since you're using Property Set to assign a textbox to MyTextBox, you can declare it as Private, instead of Public...

VBA Code:
Private WithEvents MyTextBox As MSForms.TextBox

Hope this helps!
 
Last edited:

abhijeets1991

New Member
Joined
Mar 12, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
When I try running your code, I don't get a "Can't compile Module" error. Maybe the error has something to do with some other module? In any case, I do see some issues which will prevent it from working.

UserForm Module While you're creating an instance of textBox_collection within the Initialize event, you'll need to declare it at the userform module level (at the top of the userform module, before any procedure) so that it can be available after the Initialize event and while the userform is running...

VBA Code:
Dim textBox_collection As Collection

Class Module Since you've named your textbox variable MyTextBox, the change event should be MyTextBox_Change, not BoxesGroup_Change. If you select MyTextBox from the left drop down menu for Objects, the correct change event will automatically be added to your code...

VBA Code:
Private Sub MyTextBox_Change()
    'your code here
    '
    '
End Sub

Also, if you click on the right drop down menu for Procedure, you see all of the available events for your textbox.

One other point, since you're using Property Set to assign a textbox to MyTextBox, you can declare it as Private, instead of Public...

VBA Code:
Private WithEvents MyTextBox As MSForms.TextBox

Hope this helps!
Thank you Domenic! The solution worked just fine :)
 

Watch MrExcel Video

Forum statistics

Threads
1,128,017
Messages
5,628,163
Members
416,296
Latest member
smartua

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top