Userform Controls Behaviour When Moving From One Control To Another

Feb 27, 2013

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.

View 5 Replies


ADVERTISEMENT

Force UserForm Controls On Top Of Image Control

Sep 14, 2006

i have a textbox on top of a picture box but i cannot replicate it with a new textbox or combobox.
when i place the control on the picture box it disapears?

View 9 Replies View Related

Excel2010 :: Userform Control Events For Controls Added During Runtime

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

Access Control Properties Of Controls Within UserForm, MultiPage & Frame

Jan 30, 2009

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?

Validate UserForm MultiPage and Frame Controls

View 3 Replies View Related

Stock Control With Controls On Worksheet

May 3, 2008

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

View 5 Replies View Related

Counting Selections Made With Control Toolbox Controls

Apr 12, 2007

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.

View 2 Replies View Related

Moving Control Between Userforms And Worksheets

Aug 10, 2006

"how do you move control/focus between Userforms and a worksheet that has Control objects on it?"

Here's what I am trying to do... I have created a Workbook that is basically a database and uses several Userforms to allow users to enter data into the database. I then have various CommandButtons in the Userforms that are intended to extract information from the database (which is stored in a worksheet) and display that information on a newly created worksheet. What I want to do is have a CommandButton on the new worksheet (the new worksheet is called "Results") that when pressed will delete the Results worksheet and return control to the Userform that created it.

So, the basic steps are:

* Display the Userform with the show method
* On the _click event of a particular Command button, Add a new worksheet
* Fill the worksheet with the pertinent data
* Place a Command button on the new worksheet and call it "Close"
* Somehow transfer "focus" or control to the new worksheet so the user can move around, print, do whatever on the new worksheet and then when done press "Close"
* When "Close" is pressed, delete the newly created worksheet and then return control to the original Userform.

View 3 Replies View Related

Prevent Down Arrow Moving To Next Form Control

Mar 13, 2008

I have multiple dropdown lists (forms combobox, NOT activex) on a worksheet -- once the user selects them, a keypress on the downarrow will move to the next dropdown list.

is there a way of preventing this? instead, i would like to move focus to the next cell (same column, 1 row beneath the combobox)

View 9 Replies View Related

Excel 2010 :: Microsoft Date And Time Picker Control Not Appearing On Controls List

Jun 14, 2013

I was trying to use this guide to add a date picker in:

[URL]

and noticed that the date picker does not appear in my active x controls list.

I am definitely on Excel 2010 so it should be there right?

View 1 Replies View Related

Access UserForm Controls From Another UserForm

Jul 2, 2007

I am trying to change the property value of all optionbuttons on userform1 via userform2's deactivate event

Private Sub UserForm2_Deactivate()
If userform1.Controls = OptionButton Then
Value = False
End If
End Sub

View 5 Replies View Related

Macro That Deletes Sheet With Control & Shows UserForm Causes UserForm To Disappear

Jun 15, 2009

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?

View 9 Replies View Related

Referencing Userform Control From Another Userform / Class Via Variable

May 3, 2012

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:

Code:

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

View 5 Replies View Related

Replicate Controls Within Userform

Aug 15, 2012

I have a userform that has nested multipages (5 in the outer page, 4 in the inner page).

On each of these multipages, I want to have the same controls (sliders) laid out in the same order - but with unique names, named after their tab location, for each control so I can use their value property later in the code.

I've designed the layout and named all the controls on my first sheet (e.g. Slider1Outer1Inner1). I now need a way of automating the replication of these across the other 19 sheets (including the nested inner multipage!); so that equivalent slider for example would be called Slider1Outer1Inner2, Slider1Outer1Inner3 etc.

I'm not sure about coding VBA to act on items within VBA...

VB:
" For each multipage in outer
For Each multipage In inner
For Each Object In current multipage
Copy inner.object -> Next multipage
Inner.object.name = CurrentOuter & CurrentInner & CurrentSlider "

View 2 Replies View Related

Hide Controls On A Userform

Nov 17, 2008

What's the best way to hide controls on a userform? I have a userfrom with 2 datepicker one for start date and one for end date. I want them hidden until I use checkbox and check it to appear. I am using this code but nothing is work.

View 2 Replies View Related

Loop Through Controls In UserForm

Jan 23, 2009

I have taken this code from Access and trying to use it in Excel to lock all the TextBox, ComboBox & CheckBox on the form.

View 2 Replies View Related

Disable Controls On Userform?

Jul 18, 2012

How to disable different parts of a userform?

I dont really want to go through each control as I reckon it will be handy code to have in the future if I need to add more but so far my code is:

Private Sub UserForm_Activate()
With Sheet2
n = 3

[Code].....

View 2 Replies View Related

Does A Group Of Userform Controls Have A Name

Jul 11, 2008

I have a number of TextBoxes across the page, all set to Visible=False.

If these get filled with data then I need to set Visible=True.

Rather than hard code this individually for each one (which is not a problem, it just looks untidy), can I select each row as a Group (whilst designing the form, not in the code) and then set that Group to Visible=True ?

View 9 Replies View Related

Loop Through Controls On A Userform

Nov 4, 2009

I have a userform that contains several textboxes, checkboxes, and comboboxes. There are also some command buttons, one of which says clear all. What I want to do is loop through the controls and clear the contents or change the value to false, depending on the type. I can do this by type the name.value = "", but there are alot of controls. If possible, a loop would be much more effecient.

View 9 Replies View Related

Create Controls Of A Userform On The Fly

May 18, 2006

I am trying to create some controls in a userform on the fly based on the data in a sheet. The part of my code is the following:

summaryForm.Controls.Add bstrProgID:="forms.label.1", Name:="LAWts", Visible:=True
With summaryForm.Controls("LAWts")
.Top = 120
.Left = 20
.Height = 18
.Width = 300
.Caption = "Weights: "
End With

Question I is that how can I connet this textbox to a click event or just to a procedure when it is clicked? I tried to use OnAction but it does not work for textbox in a userform. Question II is that how can I find the all properties of a textbox or any other controls? Is there a manuel or reference available on the internet?

View 3 Replies View Related

Pro/Cons Of Userform Controls?

Aug 4, 2006

What are the pros/cons of using Userforms with the various objects (comboboxes, textboxes,etc) compared to putting the controls at the worksheet level? Are there things you can do in one and not the other?

View 2 Replies View Related

Add Controls To UserForm At Run-Time

Oct 25, 2006

I am trying to transfer data from a worksheet to a user form, so that the end users can edit the data on the user form, save it, and the revised data is sent back to the worksheet. since the worksheet data is dynamic, i am trying to dynamically add controls in the user form. but the form displays only one data.

Set sdel = Sheets("Deliverables")
Set rStartCell = sdel.Range("A65536").End(xlUp).Offset(0, 0)
counter = Mid(rStartCell.Address, 4)
dummy = 0
cnt = 1
'copy data from sheet to the user form
With sdel
'checking if deliverables sheet has any data
' If .Range("A3") <> " " Then Exit Sub
' MyTextBox.Caption = .Range("A3").Value
For r = 3 To counter
If .Cells(r, 1) <> "" Then

Set MyTextBox = Controls.Add("Forms.Label.1", "lbl" & cnt, Visible)
MyTextBox.Top = topadd + 30
MyTextBox.Left = 20
MyTextBox.Width = 150
MyTextBox = .Range("A" & r).Value
cnt = cnt + 1
Else
dummy = dummy + 1
End If
Next r

End With

View 3 Replies View Related

Looping Through Userform Controls

Feb 24, 2007

I have a userform with a series of textboxes used for data entry (numeric values) specifically, 3 groups with 5 text boxes per group. If the value of any text box within a group is either >= the value of a cell in a worksheet range (Benchmark, i.e. BM), then the BackColor of that particular text box changes to green.

The challenge I have is trying to loop through each set of five controls to determine if the control's value is >= to the cell's value in the worksheet range, and if that condition is met, than changing the BackColor to green.

I know there must be a more efficient way to make this happen than to write an 'If - Then' for each text box (see code below)


Private Sub Benchmarks()
BM1 = Worksheets("Sheet 1").Range("A1").Value
BM2 = Worksheets("Sheet 1").Range("A2").Value

If txtGroup1Box1.Value <> "X" And txtGroup1Box1.Value <> "" And txtGroup1Box1.Value <= BM1 Then
txtGroup1Box1.BackColor = &HFF00&
Else
txtGroup1Box1.BackColor = &H80000005 ............................

View 9 Replies View Related

Userform Controls Empty When Add-in

May 9, 2007

i build a userform that includes 6 columns, and use named range to to be shown in the userform.

i use simple code to call this userform.
excuting the code, the userform appears ok.

i saved my file as an addin to be able to call my userform

the problem that the userform appears empty, no data and no columns, when calling the userform from the addin file whearas the userform works fine with the named range when calling from an xls file.

any idea how to solve the problem ?
pls find the attached file

File deleted as it of no help.

View 9 Replies View Related

Add Controls To UserForm With Code

Dec 14, 2007

way to allow a user to add a control to a userform without going into the VBE. Here is the situation, I am developing a userform to calculate a projected budget. I want the user to be able to select the number of controls to add, and click a command button to add them. Based on other selections that the user makes, different controls would automatically be added. Is this possible? As a follow up, will I be able to atttach code to these new controls?

View 9 Replies View Related

Reset UserForm Controls

Mar 4, 2008

On the event that a combobox is changed the objects on my userform that relate to the same information as the combobox need to be disabled (and made invisible)... however if another choice is made then they need to be enabled again and then the objects relating to the new selection need to be disabled.

Private Sub cbo1_Change()
Dim DisableIndex As Integer
Dim CellAddress As String
Worksheets("Matrix").activate
ActiveSheet. Range("B1").Select
Do
If ActiveCell.Value <> cboVarNamEn.Value Then
ActiveCell.Offset(0, 1).Select
End If
Loop While ActiveCell.Value <> cboVarNamEn.Value
CellAddress = ActiveCell.Address(ReferenceStyle:=xlA1)
ActiveSheet.Range("B1:" & CellAddress).Select
DisableIndex = Selection.Cells.Count
Me.FrameAddEn.Controls("label" & DisableIndex).Enabled = False
Me.FrameAddEn.Controls("label" & DisableIndex).Visible = False
'there a few other objects here that will also be disabled... text boxes, check boxes etc.

Putting an unload/load command in won't work for obvious reasons and I can't use a command button because that would require my clients to actually think of pressing it.

View 3 Replies View Related

Multi-Page Userform Controls

Dec 15, 2008

I have a MultiPage userform and I'm adding (4) control buttons - CancelButton, BackButton, ForwardButton and FinishButton. On the first page, those names are fine but when I get to subsequent pages, I get an ambiguous error if I try to name the controls the same. But the code will all be the same. What am I missing? In an example that I have, the user was able to name all the controls with the same name. What setting would control that?

View 5 Replies View Related

Userform Controls Suddenly Vanished...

Jun 19, 2009

My userform has a group of controls, four navigation buttons and a textbox. The nav. buttons have accelerator keys, Alt+N for next record and so on. I was moving the group about on a userform, when hey presto! it suddenly disappeared. When I run the form, the navigation buttons do not appear, but I can still navigate using Alt+N, Alt+P. Here's what I've done so far, trying to find out what's going on.

* I selected all controls with Ctrl+A, but there was no sign of any object in the place where the missing group should have been.

* I looked on the Object Browser (which is something I don't understand too well yet) and the navigation buttons are there.

* I hid all the controls, and showed them again, didn't help.

* The group I was moving was near the bottom of the form. I set the zoom property of the form to 50, so all controls in design view were bunched up near the top left corner; the missing group didn't appear

View 2 Replies View Related

Do Userform Controls Have An Index Number

Dec 18, 2009

The form that I have created has a number of controls that are created at runtime. If a user triggers the event that originally created the controls, I want to be able to delete all of the controls before recreating them.

Is there a loop that can delete all of the newly created controls? I have 9 controls on the form that need to stay put but everything after those 9 controls I want to delete. Is there something like:

View 5 Replies View Related

Controlling Colors Of Controls In Userform

Nov 17, 2003

I have a userform with 24 text and combo boxes. Aside from using the enter and exit events, is there an easy way to have the active text/combo box be highlighted in a color?

View 9 Replies View Related

Calculate 2 UserForm TextBox Controls

Jun 3, 2009

I am trying to calculate the result of two text boxes in a third textbox on a userform.

Box1 = DirectorHrs = a textbox that the user enters the number of hours
Box2= DirectorRate = a textbox that retrieves the rate from a sheet called "Rates" within the workbook upon initialise of userform.
Box3 = DirectorTotal = Box1*Box2. I want this to update when the user enters or changes Box1.

I have the following code on the change event of Box1

Private Sub DirectorHrs_Change()
DirectorTotal.Value = CStr(Val(DirectorRate.Value) * Val(DirectorHrs.Value))
End Sub

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved