I use the following (work in progress) function to add the controls to the userform (usually added to page or frame).
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
Set rngControls = .Range("D2:D" & .Range("D" & .Rows.Count).End(xlUp).Row)
Now I need a means of trapping the controls events. I thought I could use a class, e.g.:
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()
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:
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?
I have been working on large project using Excel VBA for several days. My code seems to be working correctly, but I have more to do and now, when I drag an object from the toolbox onto a UserForm, the object is not added to the list of objects on the form. If I go back to versions of the project that I was working on several days ago, there is no problem. If I run "Workbook Rebuilder", the objects that I have dragged onto the form are then added to the object list, but I still can't add new objects to forms from within the VBA editor. Is the project corrupted, or is there some other explanation, and are there any fixes? The code runs about 50 pages, and there are over 20 forms, so redoing from scratch is only a last resort option.
I have built a quoting tool and Userforms to act as a user interface to this tool. The userforms basically just feed user input back to relevant sheets. I have a number of textboxes, listboxes and radio buttons on multipages. Now the issue is that each control has an Afterupdate or Change event associated with it. Consider this scenario:
I have 4 product categories (represented by radio buttons) : Cat A, Cat B, Cat C, Cat D
Each category can have up to 10 line items which are represented by combo boxes.
Now lets say the user has selected Cat A radio button, he selects a product from the combo box (the cursor or focus is still on the combo box) and clicks on Cat B radio button. Here is the issue: on the first click, Afterupdate or Change event of Combo box fires but radio button is still unselected. The user has to click Cat B radio button AGAIN to select Cat B radio button. So essentially it takes two clicks with a slight pause between them to change categories.
One way to avoid this is to press Enter or 'tab through' each field. But this is not very intuitive. I have tried using Exit, Afterupdate, Exit, Change events without success. I can't use ControlSource option because the cell to input info to is dynamically selected. I have used xlCalculationManual for some text boxes but can't use this since some inputs on the userforms depend on the right value being calculated on the sheet.
I want to access the Properties of a number of controls in a running form, and these controls may or may not be contained in a Frame or a MultiPage.
In particular I want the Top and Left for these controls, which means I have to first find out if the control is contained in a Frame or MultiPage so I can get the reference for Top and Left. I'm ok with doing this for controls inside a Frame, but the MultiPage is eluding me. I get an error when I try to access these controls and it looks like they are actually owned by the individual Pages of the MultiPage.
how do I find out if a given control is contained in a given MultiPage?
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...........................................
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.
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
I have been working for 8-1/2 hours to get the focus to move to a specified control on my user form if criteria are met in two other controls on the same user form. It's not responding as I expect. I think I know what is happening; I just don't know why.
Situation: I have a user form with the following relevant fields in this tab order - LMonthDay, tbMonthDay, SBMonthDay, LYear, tbYear, SBYear, LTime, and tbTime. I used the designations L, tb, and SB in my control names to represent label, text box, and spin button respectively.
Problem: Once the SBMonthDay control has the focus, if I press TAB without changing the control's value, it does not execute the Exit event statement the way I anticipated. I want the focus to move to the tbTime control when the tbMonthDay & tbYear controls are already properly populated. (They will already be populated for 99% of the entries in this case.)
Private Sub SBMonthDay_Exit(ByVal Cancel As MSForms.ReturnBoolean) Debug.Print "SBMonthDay_Exit Begin" If ISLIKE(tbMonthDay.text, "####") _ And ISLIKE(tbYear.text, "####") Then tbTime.SetFocus Else: tbYear.SetFocus End If Debug.Print "SBMonthDay_Exit End" End Sub ...........................................
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.
A lot of people seem to have been having troubles with the fact that if you try to use controls in a class with the "withevents" keyword, some events (like Enter & Exit) are not included in the tracked events. I have been having some troubles with this myself & have not yet seen any particularly satisfying workarounds to this problem.
So here is an idea I just had, I have not made any attempt yet to implement it & so have no idea how complicated doing so would be, but I thought I would just put it out here & get some feedback on the idea before I spend any real time working on it.
The idea is to create a blank, transparent label that covers the entire form, monitor the Click event of this label, & pass it back to the normal controls on the form as necessary. Does this sound doable, or do you all think it would be merely an exercise in futility? If anyone has any better suggestions for how to work around this lack of events,
Imagine I have a form with a few controls on it. And the controls properties are set up at runtime.
Is there any way possible to save the property changes that were made at runtime to the controls themselves? I mean - short of manually editing all the controls at Design time?
This might be easier to explain by example. See dummy code below. Using this example - I want to find something that will save the Caption of CommandButton1 as "TestMe" (rather than have it only temporarily set at runtime).
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.
I've several check boxes on a worksheet. Some of the check boxes control the values of other through the _click event. However, when I try to change the value the event attached to the control in questions fires.
application.enableevents = False
would prevent events being fire from within the macro but this only seems to work when I step through the code.
I understand that I could set a global variable to do this and will probably go down this route anyway - but I am interested to know how/when I should be using the EnableEvent method (as I am sure this should be the way that I use it)
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?
I would like to know if it's possibale to use VB, to auto manage these levels IE, 1 box asks for the part number 2nd asks you how many 3rd/4th boxes are "+", "-" When + or - is pressed the part number will automatically add's/subtracts that part level. I had 10x abcde's I enter 2 in "How Many", then press "-" it will then adjust the level from 10 to 8
I have code that creates a row of controls on an MSForms.Userform at runtime.
I would like the user to be able to save these controls so that they are available the next time he opens the form. The user would be able to add or delete a row of controls and save them AND the values that he has set. This allows great flexibility for each session using the form.
The values aren't a real problem - I've been using the SaveSettings function for the design time controls values. The runtime controls are combobox, textbox, listbox, checkbox.
I don't want to design-time build the controls and just toggle the visible property (not a good solution for my app).
I am attempting to put in some dropdown boxes and text boxes and maybe some other items using the control toolbox in Excel. After a selection is made I would like to count that particular item. I was using the validation method in which all I needed was the following to sum up the selections made: =SUMIF(G:G,"Monday",I:I)
Now I realize this will not work due to the fact the control toolbox controls are embeded and merley sit on the cells. How do I sum selections made using drop down boxes that are derived from the control toolbox? If someone can give me an example to try or point me to a place to find such information that would be great.
I have several similar statements in different events in my UserForm code. Is it possible to separate these statements in a procedure and call it each it when I need it? The similar statements are:
With Sheets("Knowledge") For Each rw In .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row) If ListBox5.List(, 4) = Trim(rw.Value) Then Label1.Caption = rw.Offset(, 1).Value Label2.Caption = rw.Offset(, 4).Value Label3.Caption = rw.Offset(, 5).Value Exit For End If Next rw End With
If it's not possible to make my code shorter with a procedure, can I optimize somehow this repeated code?
This is weird - if you delete a sheet that contained a control then
a. showing a modeless userform resluts in a userofrm that goes invisible at subroutine End b. public variables lose their value
These things do not happen if the sheet did not contain a control. Attached is an example file - put the inputfile.xls in your default file location (or add a path in the code) then open the ProblemDemo.xls and run the main macro to see it fal - isthis another Excelbug I've found?
I have several non-modal userforms in my App, some of them have date-fields that require manual entry typing of dd/mm/yy etc (No single userform has more than one date-box in it, this I think may be pivotally useful)
Now the Userform 'Calendar' that is built on the class of the same (cCalendar) name, has the write value line 'ActiveCell.value = theCal.value'
I'm looking to change this to refer to the correct userform.Textbox value, depending on which form is open.
I would imagine I could simply have a global string, whose value is set (or re-set) whenever a Userform is initialized (some sort of 'ActiveUF.value = Me.Name), where I get lost is referring to the components by name, so as to have a case statement by where I go:
Private Sub theCal_AfterUpdate() Select Case ActiveUF Case "AddForm" application.vbe.components("AddForm").controls("AddFormDatePicker").value = theCal.value Case "EditForm" '.... etc end select end sub
better way of doing this (instead of passing around the userform name as a variable) - or proper syntax for referring to controls outside of the 'active' userform (but an open userform nonetheless)?
Every time I have to do this particular thing with userforms, I completely forget how, and the object browser always leads me on an infinite loop of Application.vbe.activevbproject.vbcomponents.vbe.active....
PS - there may be one slight complication to the process - one of the forms, has a 2-tab page in it, each page having similar (but named differently) fields. So I may need to be able to throw in 'Activepage' or whatnot
I have created a userform to add a new user and his/her password to a list of usernames and passwords (which i use for login procedure). Now, as the login name must be unique, I would like the userform to disallow existent usernames from being added. How do I go about doing this?