Enter & Exit Events In Control Classes
May 11, 2006
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,
View 4 Replies
ADVERTISEMENT
Oct 22, 2011
I'm having some trouble getting control ENTER & EXIT events to fire properly when having controls embedded on frames within a userform. I'm using Excel 2003, 2007, & 2010. Here's the userforms I'm working with:
With FRAME:
Without FRAME:
In both cases, the DESCRIPTION field is disabled. The selectable controls on both are a combo-box, textbox, listbox, & 2 buttons. On the FRAMED version, the combo-box & textbox are contained on a FRAME.
Here's the code, same on both userforms:
Code:
Option Explicit
Private Sub cmbRecipes_Enter()
ListBox1.AddItem "ENTER - " & cmbRecipes.Value
End Sub
Private Sub cmbRecipes_Exit(ByVal Cancel As MSForms.ReturnBoolean)
ListBox1.AddItem "EXIT - " & cmbRecipes.Value
End Sub
All this is doing is posting a message to the listbox when the combo-box ENTER & EXIT events fire. This works as expected without the FRAME, ENTER is shown when the combo-box is entered and EXIT is shown as focus is moved to another control. But when running it on the FRAMED version all I get is a single ENTER event recorded regardless of how I move the focus through the control set.
Another oddity is that if I have more than 1 control that can receive focus on the FRAMED version, it appears to work correctly.
View 6 Replies
View Related
May 28, 2012
I'm using a multipage control on a form that has the style set to fmTabStyleButtons. So it uses buttons instead of tabs. I named one of my buttons (tabs) to Close. Can I set this up to where just pressing the Close button will cause it to exit the app not just go to that page?
View 9 Replies
View Related
Jan 9, 2008
Within the ComboBox properties, is there anyway to control after "enter" his hit, you move to the right instead of down (similar to the edit under Tools/Options)?
View 9 Replies
View Related
Aug 10, 2013
I have a problem here regarding the combo box. The whole Column A has combobox. It would be easier if by just hitting the TAB or Enter it would go to the next cell just like what is usual in excel.
TAB = next cell to the right
Enter = Next cell below
View 1 Replies
View Related
Feb 3, 2006
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 ...........................................
View 9 Replies
View Related
Jul 7, 2009
I need to test what control will receive focus after the exit event
this test needs to be in the exit event so i am able to validate and cancel only if focus will be the enter button
Private Sub myTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If NextControl.Name = "EnterButton" Then
DoMyValidationTest 'and other stuff, such as set TabIndex
Else
'do nothing
End If
End Sub
so what should "NextControl" actualy be???
View 9 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
Sep 8, 2006
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)
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
Dec 11, 2008
For Excel 2007, does this key function only work after typing in a formula into a cell? I tried pasting the formula and am unable to get the {}.
Is there some setting in preferences to allow this?
View 2 Replies
View Related
Apr 21, 2006
If ur using a formula that uses Control Shift Enter to activate it in Excel,
How do u use that formula in VBA? ....
View 8 Replies
View Related
Sep 5, 2007
I need to change the way Exel move the focus when I press return in a cell. For example when I am in column 1 and press return, I want the focus to move to column 4. If I am on column 5 I need to go on the first column of the next line, etc ...
I think I am suppose to use ActiveCell.Offset(1,0), and ActiveCell.Offset(-4,1) for my 2 examples. But my question is what is the VBA code for: "do that when I press enter and I am in this column"?
View 5 Replies
View Related
Apr 23, 2008
I have a User Form that is used to collect data. CommandButton1 loads the User Form data into the worksheet and CommandButton2 clears all data from the Form in preparation for entering the next record. Immediately after a user first opens the Form, the very first time CommandButton1 is clicked, the Enter Key stops working. At this point data can be typed into any TextBox on the Form, but when the Enter Key is pressed the cursor remains in the TextBox. (The Enter Key will not move the focus to the next Textbox in the Tab Order. Also, if I alter the code to set the focus on a CommandButton as the active control instead of a TextBox, pressing Enter on the active CommandButton does not execute the CommandButton's macro... the same behavior as a TextBox; nothing at all happens when the Enter Key is pressed even though the CommandButton has focus).
At the point when the Enter Key stops functioning, if the user presses 'Alt-Tab' to leave the Form and then immediately uses 'Alt-Tab' to return back into the Form the Enter Key suddenly works again and continues to work correctly from that point on even after CommandButton1 is clicked. Again, the Enter Key stops working ONLY the first time CommandButton1 is run immediately following initially opening the workbook and Alt-Tabing into the Form immediately corrects it. I saw one other post in this forum with this same problem in 2003, but unfortunately it did not get answered.
View 2 Replies
View Related
Apr 24, 2009
I would like to call upon a function until a certain criterion is fullfilled. Then, I would like to have the result returned to me and exit (all) open functions. For value1 = 1 and value2 = 10 I expect value1*value2 = 100. Instead, the routine returnz zero. What is the logical flaw in the code below.
Function testfunction(value1, value2)
If value1 = value2 Then
'Calculating the difference
testfunction = value1 * value2
Exit Function
ElseIf value1 < value2 Then
value1 = value1 + 1
Call testfunction(matrix1, matrix2)
End If
End Function
View 9 Replies
View Related
May 26, 2009
VBA automation -
Excel added the formula - but they all return "Not Found". After the VBA automation - I visit each cell, see the formula is correct and then press Control +Shift + Enter; then the correct value displays! Of course, the curly brackets also appear in the formula bar. In VBA I tried to use the:
objXL.ActiveCell.FormulaArray = "=IF(ISERROR(INDEX(PositionDataSell!$T$2:$T$505,MATCH(" & lngContractID & "&" & strQuote & strConCat .....
The Excel Formula bar for the cell is just empty (blank)
Using only the Formula property:
objXL.ActiveCell.Formula = "=IF(ISERROR(INDEX(PositionDataSell!$T$2:$T$505,MATCH(" & lngContractID & "&" & strQuote & strConCat .....
The correct formula is created in each cell, but. Until I visit that cell and use the Control+Shift+Enter - the lookup will not work. It is proof that the right formula is there, without the curly brakcets.
In Excel - my vba code successfully constructs these formulas: In essence: It checks for an error and prints "Not Found" if no match is found in the check. It test for two values in a row - matches them to two columns on a row in another worksheet, and returns a third value for the matches of the same row.
View 5 Replies
View Related
Aug 4, 2006
User selects a date from a pop up calendar. The date and user ID is entered into the active cell of the active sheet. This I have. I would like at the same time to have it enter the value of just the date (without the user ID) to cell A1 on the sheet labeled final.
Also, while I'm on the topic, I have another workbook where I would like to do the exact same thing with the exception that the user will only be allowed to select a date equal to today or up to 60 days from today?
View 7 Replies
View Related
Jan 4, 2007
Following are the VBA Object in excel file :
1.TextBox1
2.TextBox2
3. CommandButton1
4. CommandButton2
5. CommandButton3
i want to do following items :
1. when i am in textbox1 and press TAB button from keyboard then cursor go to Textbox2
2. when i am in textbox2 and press TAB (keyboard) then select commandButton1
3. when i am in textbox2 and press enter button (Keyboard) then run the macro of commandButton1 click event.
Actully above VBA object in excel sheet and i am not useing any fram for them .
how to possible above items.
View 9 Replies
View Related
Jul 17, 2009
I have a worksheet that has a Calendar on a worksheet from Calendar Control 8.0.
I want to be able to pick a month from the calendar and press a button to convert the selected month into a worksheet. I have a custom made calendar worksheet that I would like for it to be converted into.
I have uploaded my workbook. It contains the custom calendar worksheet as well as the calendar control.
The file is called "calendar.xls".
View 9 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
Dec 4, 2013
If MonthView control can be set to allow users to select multiple ranges and enter different dates into those ranges? I know I was able to do with with the previous Datepicker control and I thought it was as easy as setting the Show Modal property to true.
View 1 Replies
View Related
Dec 23, 2008
I'd like to create a number of classes in a VB.NET DLL and then use them from VBA in Excel 2003+. Is this possible? I wouldn't actually expose any of .NET. These would be simply be classes defined by me (which may wrap elements of .NET but never require VBA to have knowledge of .NET or access to .NET assemblies).
If this is not possible, would it be possible to create a DLL in VB.NET that exports functions and subroutines that can then be linked into VBA by importing them (the same way you would with Win32 API routines)?
View 3 Replies
View Related
Sep 21, 2008
I am interested to get some opinions/input on where one might obtain Excel training/classes, etc. Our local college infrequently offers classes and when they do, the class is most always entry level which I have already taken. Online or correspondence would be acceptable.
View 2 Replies
View Related
Apr 14, 2014
Why it seems not possible to declare variables in Class modules like so:
[Code] ....
View 3 Replies
View Related
Sep 22, 2008
I am trying to replicate the class example on Chip Pearson's site:
[url]
In particular I am interested in having a class that can be accessed by multiple workbooks.
View 12 Replies
View Related
Feb 20, 2009
I have a spreadsheet with ~350 people tracking 24 dates for classes each. On a seperate tab I have a macro that copies and pastes a pre-defined macro to display the due dates. My question is, can I have a macro run the calculation and put the result in the cell rather than have around 8,400 calculations on a sheet?
View 9 Replies
View Related
May 2, 2014
I can create events in my custom classes. When do I even want to raise an event like this instead of calling the corresponding Sub?
I imagine that I can create an event called OnColorChange or I can create a sub called OnColorChange.
Why would I want to create an event?
View 1 Replies
View Related
Mar 12, 2007
This is a double IF() to me. I just can't figure it out.
I download a list of classes taught by a number of teachers. I want to summarize how many of classes taught by each teacher.
I have tried many variations of countif() but can't seem to figure out how to do both.
Example copied in below:
ClassesTeacher Classes Taught Angela Barbara Bob
mathAngela math 2 0 1
englishBarbara english 0 2 0
musicBob music 1 0 1
mathBob
englishBarbara
musicAngela
mathAngela
englishAngela
musicBarbara
mathBob
englishBarbara
musicAngela
mathAngela
englishBarbara
View 9 Replies
View Related
Dec 13, 2012
I am aware that I can use single changing events in worksheet change events. For instance, if column 1, or A is changed, do something. This is only a single If statement, i.e. either the condition is true, or not. What I am not sure is if I can use two changing events, i.e. two conditions. For e.g. I would like if Column A value is X and Column B is "Active", action it, but only if two conditions are true.
For.e,g. The below syntax does not work. If it is only column A, it does work, but I want both A and B to be true, then copy and paste the target does not anything.
VB:
If Target.Column = 1 Then
If Target.Column = 2 Then
If Not Intersect(Target, Range("A2:A" & Rows.Count)) Is Nothing Then
If Not Intersect(Target, Range("B2:B" & Rows.Count)) Is Nothing Then
If Target.Value = "X" And Target.Value = "Active" Then
View 4 Replies
View Related
Jun 1, 2014
I really know nothing about vba so here goes. I would like to enter data in a row with 4 cells of info. then hit enter and return to the first cell and move the row down. all four cells must have data entered. and all four must move down. i tried some code as below i found and i modified but it did not work as expected. this moved the row down when returning the cursor to A2. It also should not copy the data style of the top row.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column <> 1 Then Exit Sub
Application.EnableEvents = False
[Code].....
View 4 Replies
View Related
