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,
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.
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?
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)?
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
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 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???
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.
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 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?
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"?
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.
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
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.
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?
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 .
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.
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.
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)?
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.
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?
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
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
