Using Class Module For Handling Events Of Dynamically Created Controls
Mar 4, 2009
I have a userform with a single blank multipage. At runtime additional pages are added, the number of pages depends on input from another userform. Six frames, containing labels, textboxes and comboboxes are then added to each page.
I need to be able to use the textbox and combobox change events of these dynamically created controls to perform lookups and calculations. Although I can name the controls at the time they are created, it is not feasible to write code specifically for each control (I can have over 1,600 text boxes distributed over 9 pages, for example).
Having searched for some time on how to achieve this I believe using a class module is the way to go. However, how to use a class module is just not sinking in I'm afraid.
HTML
Private WithEvents mpTextBox As MSForms.TextBox
Private WithEvents mpComboBox As MSForms.ComboBox
Private Sub Class_Initialize()
Set mpTextBox = MSForms.TextBox
Set mpComboBox = MSForms.ComboBox
End Sub
Private Sub mpComboBox_Change()
MsgBox "ComboBox value has been changed."
End Sub...........................................
View 2 Replies
ADVERTISEMENT
Oct 10, 2006
I have somecode that scan column "A", and add dynamically "TextBox" Control according to some condition.
How to add event on dynamically created controls?
Private Sub btnSubmit_Click()
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
For Each rng In Selection
View 9 Replies
View Related
Dec 14, 2009
I am having trouble working through the examples given on other posts for this topic. I have a userform that creates a number of controls at runtime. One group of controls is a series of textboxes and a label underneath that sums the values of the textboxes. When a user changes the value in a textbox the label needs to update the sum. I have read about created class modules to define the event but it is all going over my head.
A piece of the code that creates the controls is:
View 14 Replies
View Related
Apr 2, 2013
I have a userform that has one combobox at the top created manually. When the userform is opened, the user select an option in the combobox (these options are taken from a range on 1 worksheet). From the selection of the combobox, I use the comboxbox's change event to create and display 5 columns of textboxes and 2 columns of command buttons on the userform.
The number of rows of textboxes created depend on the option selected from the combobox since each option links to a different range of cells. Each of the 5 textboxes in each are set to be ".enabled = False" and display text as per the cell values within a range on another worksheet. 2 Columns of command buttons are created at the end of each row of textboxes - 1 is enabled and the other is not.
The creation of the textboxes and command buttons works as required. However, I am having problems with setting click events for each command buttons. When the 1st column of Command buttons are created, I need the click events to be created and filled out with 2 actions:
1. Enable all textboxes in the same row as the command button
2. Enable the other command button in the same row.
Here is the code I have so far that creates the textboxes and command buttons.
Each of the 5 textboxes and 2 command buttons have a unique name so the 1st row will have textbox and command button names of cTxtA1, cTxtB1, cTxtC1, cTxtD1, cTxtE1, CmdAmend1 and CmdConfirm1. The 2nd row will have the same names but with 2 on the end and so on. The bold sections is the code for the creation of the command buttons that I want click events for.
Code:
Private Sub CboTeamSelect_Change()
Application.ScreenUpdating = False
If CboGroupSelect.Value = "" Then Exit Sub
Dim cTxtA As Control, cTxtB As Control, cTxtC As Control, cTxtD As Control, cTxtE As Control
Dim CmdAmend As Control, CmdConfirm As Control
Dim iNum As Integer
Dim TxtTop As Long
[code]....
View 2 Replies
View Related
Apr 27, 2014
The following code dynamically creates a number of textboxes within a frame with a label right next to it. There are two classes.
[Code] ......
The two classes look like this:
[Code] .....
This works just fine to create the dynamic series of textboxes and corrosponding labels. I am having trouble with the reference between them. for example (within the class module):
[Code] .....
As shown, the reference to the corresponding lable from within the class doesn't work. How to reference the correct corresponding label.
View 13 Replies
View Related
Nov 20, 2006
I have a class module (MyCtrlEvents) with a sub (TxtGroup_Change) which I want to handle on a change event for some specific textboxes.
When the form is opening I don't get the correct sum for the textbox "TBSum601". It should be 200 but I get 14464
When I then also change a number in the form for any control like "TB7%", the change trigger event doesn't seem to occur....
View 9 Replies
View Related
May 24, 2006
I've created two modules, card and pack. Card has three variables (value, name, suit) and pack is made up of an array of 52 cards. Referencing it from a test module (NOT a class module) I expected to be able to use debug.print pack.card(32).suit to return the suit of the 32nd card. Instead I get an error message "Object or With variable not set". What am I doing wrong
Private p_strSuit As String
Private p_strName As String
Private p_iValue As Integer
Public Property Let Suit(strSuit As String)
p_strSuit = strSuit
End Property
Public Property Get Suit() As String
Suit = p_strSuit
End Property
Public Property Let Name(strName As String)
p_strName = strName
End Property......................................
View 7 Replies
View Related
Oct 10, 2011
In my current project, I have set up a worksheet with 15 comboboxes (dropdown lists).
To trigger change events, I use :
Private Sub ComboBox1_Change()
....
End Sub
So, I have 15 such routines defined to cover all comboboxes. By doing so, my code becomes quite large
I am wondering if there is not a more efficient way to handle this.
View 1 Replies
View Related
Jan 7, 2010
Can I place userform event handling within the code module where the rest of my program resides or does it have to go in the code module that is attached to the userform?
View 3 Replies
View Related
Jun 8, 2014
I am having a excel vba code that copies tab from different excel file and then i want to use in sql query. It is always giving me error saying that "The Microsoft Access database engine could not find the object 'rahul'. Make sure the object exists and that you spell its name and the path name correctly. If 'rahul' is not a local object, check your network connection or contact the server administrator."
Though, when i rerun the macro after reopening the excel file, it is working fine. Seems like tabs dynamically created cant be used. Is their any way to get it done at runtime? I want to delete new created tab once data from new tab is inserted to required tab.
my macro calls first copyAllSheets(filename) and then writeSheet().
Code:
Private Function copyAllSheets(ByVal fname)
Dim destSheetCount As Integer
Const FUNCNAME = "copyAllSheets"
On Error GoTo ErrorHandler
Set wb = Workbooks.Open(fname)
destSheetCount = ThisWorkbook.Worksheets.Count + 1
wb.Worksheets("Buyer").Copy after:=ThisWorkbook.Sheets(1)
ThisWorkbook.Worksheets(destSheetCount).Name = "impBuyer"
[code]........
This is part of code so probably it wont be easy to understand the flow. but mainly i want to know how can i have newly created tab as table_name.
View 7 Replies
View Related
Aug 19, 2008
Split from Determine Order Of Event Procedures
I realise that there's no set order for different types of event, because, as you say, it basically depends on what the user does. But what I'd like to control is what order like events fire in.
So let's say that object_1 handles the aplApp_WorkbookNewSheet event (an application-wide event).
Let's say that object_2 is an object of the same type as object_1, and therefore also handles the aplApp_WorkbookNewSheet event.
The objects are entirely independent; they know nothing about each other. However, I would like to be able to control whether the aplApp_WorkbookNewSheet event is fired first in object_1 or first in object_2 when the user triggers this event by adding a new worksheet somewhere.
View 4 Replies
View Related
Apr 27, 2014
Code to create buttons on a userform. I have adapted his code to do what I want. The code he gave me was:
[Code]....
In the Class Modules:
[Code] ........
All buttons when created are coloured yellow. When a button is clicked, it turns blue (to indicate that the button was clicked. However, my problem is that when another button is clicked, it also turns blue as coded. This means that both are showing blue colour. I want all other buttons to turn yellow and only the button clicked should turn blue.
View 5 Replies
View Related
Jun 3, 2014
Basically, I am taking user input to determine how many textboxes to create during runtime, and want to call the contents of those textboxes after the user has put something in them.
View 1 Replies
View Related
Apr 1, 2008
I have 100s of name ranges in my spreadsheet. I am using a series of drop down boxes for the user to select the info that they want to see. Based on the dropdown box, the macro "dynamically generates" a range name from which to pull the info.
For instance, if they Select Manager = Terry, Analyst 1, and Month = Jan, then the range name would be JanTA1
The if statement would dynamically create this name by concatenating the info
If manager = Terry
mnger = "TA"
if analyst = 1
anlyst = 1
if month = Jan
mnth = "Jan"
rangename = mnth & mnger & anlyst
So, I am dynamically creating the range name based on user selections...then to display it, I want to set the display cell = to the value
Range("display cell").Value = Range(rangename).Value
The problem is, I am getting an object error every time I try to refer to a range this way.
If I type:
Range("display cell").Value = Range("JanTA1").Value, it works
View 9 Replies
View Related
Jun 26, 2007
For a form that dynamically creates a series of text boxes and labels, does anyone know how to change the caption of a dynamically created label to display the value of a cell in a worksheet? The cell in the worksheet changes according to input on the text boxes. In the form's code, I have:
Dim newLblFreq As MSForms.Label
Set newLblFreq = Me.Controls.Add("Forms.Label.1", "lblfreq" & i, True)
newLblFreq.Caption = Range("V" & i).Text & " Hz"
But once the label is created, the caption won't change, even if Range("V" & i) changes...............
View 6 Replies
View Related
Feb 15, 2010
I would like to create a class module that it's the same as a connector shape (for example), but with additional custom properties defined by me. For example, I would like it to have a temperature property.
So, for example if I write in a Sub procedure:
Dim Test1 as EnhancedConnector
Set Test1 = New EnhancedConnector
A new connector appears in the activesheet, but this connector must have my custom defined properties available to VBA Code
View 9 Replies
View Related
Nov 20, 2013
I am using table driven forms controls on userforms. E.g.:
Excel 2010ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAG1UI NameMultiPage ParentPage
ParentControlNameTopLeftHeightWidthCaptionTagControlTipTextSpecialEffectWordWrap
MultiLineBorderStyleBorderColorBackColorBackStyleForeColorColumnCountColumnWidthsListStyle
[Code] .....
I use the following (work in progress) function to add the controls to the userform (usually added to page or frame).
Code:
Public Function AddControls(ByVal objTarget As Object, ByVal strUiName As String)
Dim rngControls As Excel.Range, rngProperties As Excel.Range
Dim rngControl As Excel.Range, rngProperty As Excel.Range
Dim objControl As Object
With shtFormUI
Set rngControls = .Range("D2:D" & .Range("D" & .Rows.Count).End(xlUp).Row)
[Code] .....
Now I need a means of trapping the controls events. I thought I could use a class, e.g.:
cFormEvents
Code:
Option Explicit
Public WithEvents lblLabel As MSForms.Label
Public WithEvents tbxTextBox As MSForms.TextBox
Public WithEvents cbxComboBox As MSForms.ComboBox
Public WithEvents lbxListBox As MSForms.ListBox
Public WithEvents cbtCommandButton As MSForms.CommandButton
Private Sub lblLabel_Click()
[Code] .....
It seems I cannot reference the controls because I they are added at runtime. For the given example, I want to run whatever procedure name appears for lblPrimaryContact in column AC (click event). So in my userform module I instantiate the class, but I get an error when I try and reference the control:
Code:
Set m_clsFormEvents.lblLabel = Me.lblPrimaryContact
Error is "method or data member not found".
Any alternative method to grab the click event for the control added at runtime?
View 9 Replies
View Related
Nov 29, 2007
I am working on a dynamically produced userform but the dynamically created Cancel and OK buttons don't work. I have been able to get round it by adding the buttons to the top of the form (non- dynamic) but I'd really like to be able to create them dynamically based on variable for other parts of the project. How can I produce dynamic command buttons that actually work?
Here's my code but I'd be happy with generic code I could use.
Private Sub UserForm_Activate()
' Define variables
Dim LabelV As MSForms.Label
Dim CheckboxV As MSForms.Checkbox
Dim rngFields As Excel.Range
Dim field As Excel.Range
Dim lngNextTop As Long
Dim lngTitleBarHeight As Long
Dim ColumnNum As Integer
' Set constants
' Choose height and width of Label box
Const cTextBoxHeight As Long = 12..............
View 9 Replies
View Related
Feb 15, 2013
I have a problem with a dynamic userform that I need to create.
I need a userform in which the number of controls are determined by the some values in the worksheet. The users of the workbook must be able to add new controls to the form, so I do not know the number of controls in advance. I therefore can not create the userform through the design module but must create it through code instead.
I need a userform with a number of comboboxes and a commandbutton, which when clicked sends the content of the comboboxes to the first empty row in a worksheet.
My problem is that I can't assign any commands to the comboboxes (which are created at runtime), nor can I use the values of the comboboxes in any commands assigned to other controls in the userform (e.g. the commandbutton).
I have read as far that I probably need some form of class module to create the controls at runtime, but since I am relatively new at VBA, I'm having trouble getting the class module to work in connection with the userform.
View 2 Replies
View Related
Apr 5, 2007
Is it possible to dynamically add or remove controls on an Excel userform using VBA? Doing this before the userform is displayed would be great.
View 9 Replies
View Related
Nov 23, 2008
What exactly is a Class Module? I've tried doing some searching on Google and haven't really found a straight answer as to what a class module is and what it is used for. How does it differ from a Normal Module?
View 2 Replies
View Related
Apr 9, 2008
I am trying to dynamically add controls to my user form based on some values in my cell. I am successfully able to create a text box dynamically but my label is not getting displayed. here is my code
Private Sub UserForm_Activate()
On Error Resume Next
If (ThisWorkbook. Sheets("Sheet2").Cells(1, 8) <> "FALSE") Then
Dim ctl As Control
Dim ctl1 As Control
Set ctl1 = Me.Controls.Add("Forms.Label.1", ctl1, True)
With ctl1
View 9 Replies
View Related
May 21, 2008
I have a code that adds a couple of ComboBoxes to a UserForm (the number of ComboBoxes is variable). Now that I want to control a certain ComboBox based on the selected value of another ComboBox (change or click event), how do I do that via VBA code?
View 3 Replies
View Related
Apr 22, 2014
I have two user forms, each with a checkbox for Jan-Dec. Also, I have a checkbox "All" to check/uncheck the Jan-Dec boxes. I also created a class module so that if any of the Jan-Dec boxes get unchecked, while the "All" box is still checked, it will uncheck the All box. Basically I just don't want the All checkbox to be able to be checked while any single month is unchecked. The only/easiest way I found to do this was with a class module, so I would not need to create individual Checkbox_Jan_Click() events for each month.
Now my issue is, I have 2 userforms like this. I'd like them to share the same class module, but in the class module I need to refer to UserForm1.CheckBox_All. So I need to dynamically refer to whichever userform was active, but as far as I can tell, this is not possible.
View 4 Replies
View Related
Jul 27, 2009
i would like to do is create a class module so that when the user double clicks on a label a msgbox will open saying the name of the double clicked label. If someone can get me to this point i can modify the code to suit my needs.
Some other things really quick though. I have 80 labels, but i only need this to work for labels 1 through 40 and the labels are on a multipage control within the userform.
View 5 Replies
View Related
May 25, 2006
I have written a public function inside a class module. Is there anyway I could call the public function in Excel? What is the exact syntax to do it?
View 5 Replies
View Related
Jan 9, 2007
I located combobox on userfom and don't see it in the module.
View 7 Replies
View Related
Oct 20, 2012
I have a UserForm with a Text Box, I populate that Text Box with a number (say 5) and then the following code runs:
Code:
Private Sub tbOverrideMokWh_Change()
Application.EnableEvents = False: Application.ScreenUpdating = False
With tbOverrideMokWh
[Code]....
After the Sub is run 1 time, it runs again. Why? I've disabled Events?
View 6 Replies
View Related
Mar 27, 2009
After all the awesome macros I've obtained with the help of all of you, I now have over 30 macros, each in its own module. I have tried without success to re-name the modules with no luck. How is everyone organizing these?
View 2 Replies
View Related
Jan 24, 2013
Let's say i have 2 Modules on my VBA forms, is it possible to Copy all the Codes in Module 1 and Paste it to Module 2 by using a Command Button?
View 1 Replies
View Related
