UserForm - Navigating fields when changing visibie property

Greetings,

This is quite complex to explain but here goes, below is a mock up of my 
user form:-

Label Title    <Value>  *Populated from result of combo box1
Combo Box 1    <Value select>
Text Box <Value = 'Todays date"> *Populated from result of combo box1
Combo Box 2 <Value select>

The form is launched and ONLY Combo Box 1 is set to visible (others on 
intilisation of form have visible set to FALSE)

The user selects a value in Combo box 1 and label and text box are populated 
and Combo box 1 is then set to disabled (ENABLED = FALSE).
The user can either change the Date value in the text box or press ENTER/TAB 
etc.., to leave the text box ...

However the code behind Text Box_Exit  event sets Combo Box 2 to visible.
VBA knows though whilst in the Text Box there is no where else on the form 
to go, no other control are visible at that point so TAB/ENTER become 
invalid options as they result in no action??

Other than adding an EXTRA button to validate the text (i.e. User is saying 
I have finished my edit) can I make the Combo Box 2 appear somehow?

Hope this makes sense.
Rob W




0
Whllrob (18)
11/29/2007 6:14:10 PM
excel 39879 articles. 2 followers. Follow

2 Replies
572 Views

Similar Articles

[PageSpeed] 47

If I understand correctly, you could use code like the following

Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal 
Shift As Integer)
    If KeyCode = vbKeyTab Then
        With Me.ComboBox2
            .Visible = True
            .SetFocus
            .SelLength = Len(.Text)
            .SelStart = 0
            If .ListCount > 0 Then
                .ListIndex = 0
            End If
        End With
        KeyCode = 0
    End If
End Sub


-- 
Cordially,
Chip Pearson
Microsoft MVP  - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)


"Rob W" <Whllrob@aol.com> wrote in message 
news:OXZpmQrMIHA.4948@TK2MSFTNGP02.phx.gbl...
> Greetings,
>
> This is quite complex to explain but here goes, below is a mock up of my 
> user form:-
>
> Label Title    <Value>  *Populated from result of combo box1
> Combo Box 1    <Value select>
> Text Box <Value = 'Todays date"> *Populated from result of combo box1
> Combo Box 2 <Value select>
>
> The form is launched and ONLY Combo Box 1 is set to visible (others on 
> intilisation of form have visible set to FALSE)
>
> The user selects a value in Combo box 1 and label and text box are 
> populated and Combo box 1 is then set to disabled (ENABLED = FALSE).
> The user can either change the Date value in the text box or press 
> ENTER/TAB etc.., to leave the text box ...
>
> However the code behind Text Box_Exit  event sets Combo Box 2 to visible.
> VBA knows though whilst in the Text Box there is no where else on the form 
> to go, no other control are visible at that point so TAB/ENTER become 
> invalid options as they result in no action??
>
> Other than adding an EXTRA button to validate the text (i.e. User is 
> saying I have finished my edit) can I make the Combo Box 2 appear somehow?
>
> Hope this makes sense.
> Rob W
>
>
>
> 

0
chip1 (1821)
11/29/2007 11:58:21 PM
Thanks very much, worked a treat.

When I get a free minute I will look up the commands Im unfamilar with.

Thanks
Rob


"Chip Pearson" <chip@cpearson.com> wrote in message 
news:1D498627-0E51-424C-A332-9898A878BAA7@microsoft.com...
> If I understand correctly, you could use code like the following
>
> Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal 
> Shift As Integer)
>    If KeyCode = vbKeyTab Then
>        With Me.ComboBox2
>            .Visible = True
>            .SetFocus
>            .SelLength = Len(.Text)
>            .SelStart = 0
>            If .ListCount > 0 Then
>                .ListIndex = 0
>            End If
>        End With
>        KeyCode = 0
>    End If
> End Sub
>
>
> -- 
> Cordially,
> Chip Pearson
> Microsoft MVP  - Excel, 10 Years
> Pearson Software Consulting
> www.cpearson.com
> (email on the web site)
>
>
> "Rob W" <Whllrob@aol.com> wrote in message 
> news:OXZpmQrMIHA.4948@TK2MSFTNGP02.phx.gbl...
>> Greetings,
>>
>> This is quite complex to explain but here goes, below is a mock up of my 
>> user form:-
>>
>> Label Title    <Value>  *Populated from result of combo box1
>> Combo Box 1    <Value select>
>> Text Box <Value = 'Todays date"> *Populated from result of combo box1
>> Combo Box 2 <Value select>
>>
>> The form is launched and ONLY Combo Box 1 is set to visible (others on 
>> intilisation of form have visible set to FALSE)
>>
>> The user selects a value in Combo box 1 and label and text box are 
>> populated and Combo box 1 is then set to disabled (ENABLED = FALSE).
>> The user can either change the Date value in the text box or press 
>> ENTER/TAB etc.., to leave the text box ...
>>
>> However the code behind Text Box_Exit  event sets Combo Box 2 to visible.
>> VBA knows though whilst in the Text Box there is no where else on the 
>> form to go, no other control are visible at that point so TAB/ENTER 
>> become invalid options as they result in no action??
>>
>> Other than adding an EXTRA button to validate the text (i.e. User is 
>> saying I have finished my edit) can I make the Combo Box 2 appear 
>> somehow?
>>
>> Hope this makes sense.
>> Rob W
>>
>>
>>
>>
> 


0
Whllrob (18)
12/2/2007 12:57:25 PM
Reply:

Similar Artilces:

Pivot Table Field Settings Default value
Hi, When you drag an item from the field list into the pivot table, how does EXCEL decide what field setting ( Count, Avg, Stdev) to use. Sometimes it choses Sum and sometimes it choses Count, even in the the same pivot table. I would like to set it to always use average or stdev as a default. Thanks. -- Mark Mesarch School of Natural Resources University of Nebraska-Lincoln I think it was Deborah D. that answered this same question for me. If the data contains blanks, is assumes that those are text, and does a count. If it is all numbers, then it uses sum. "MMesarch" w...

Why are my fields protected?
I have built an Access database that has as its primary table tblAlumnus. The form frmPersonal is used to add and edit the record. It provides a command button to link to the form frmContact to maintain the contact information; however, in frmContact, all the fields are protected. The form properties are set as follows: Recordset Type = Dynaset, Allow Edits = Yes, Allow Deletions = Yes, Allow Additions = Yes, Data Entry = No, and Record Locks = No Locks. I have tried opening frmContact without frmPersonal and by using frmPersonal and then closing frmPersonal after I am in frmContact, but ...

Custom Product fields
Has anyone tried to add a custom field to Products and then have it copied to a field in the Quote Product area (i.e. viewable from the product form for each product in a quote)? I'd like to do this to happen when I add products to a quote. On Wed, 6 Apr 2005 23:15:01 -0400, "Brian" <bdemoe@atriosystems.com> wrote: >Has anyone tried to add a custom field to Products and then have it copied >to a field in the Quote Product area (i.e. viewable from the product form >for each product in a quote)? I'd like to do this to happen when I add >products to a quo...

Changing order of series in line-column chart
Using Excel 2003. Plotting a line-column chart using two columns of data "Actual" & "Target". How can I change the order of the series, so that the bar becomes the line and the line becomes the bar, without changing the location of the data? Excel help has me select a data series, click on "Format", "Selected Data Series", "Series Order" and use the "Move Up"/"Move Down" buttons. However, these buttons are grayed out (since I can only select one series there is only one series showing in the "Series Order&quo...

Field Level Security with SOP
I'm trying to restrict some key fields on the SOP transaction entry window for our shipping department. Specifically I want to be sure they cannot change the order quantity, unit price or extended price. I'm able to lock the quantity field on the SOP_Entry Line_Scroll window and that works perfectly. When I do the same for the Unit Price and Extended Price fields on the same window nothing happens. Same if I try to disable or password those fields. Any ideas why this would be? I'm open to other suggestions to solve the basic problem, but it seems that field level sec...

clearing field "Bank Balance"
Is there any way to clear the field "Bank Balance" since I no longer use on line updates, this field has no meaning and is a pain! HELP!!! Thanks In Account List? No. But you can make the column width near zero. "Joe F" <JoeF@discussions.microsoft.com> wrote in message news:C5C90FEC-3822-4E1E-90D5-93A48C0C7044@microsoft.com... > Is there any way to clear the field "Bank Balance" since I no longer use > on > line updates, this field has no meaning and is a pain! HELP!!! Thanks In microsoft.public.money, Joe F wrote: >Is there ...

Open Userforms from a Dropdown list
Hello, and thanks for the help, I have several userforms and would like to place the names of the forms into a dropdown list to select the form i wish to use is this possible. again thanks ...

Inserting a full file path and name into a field
Hello, I have seen Arvin's Document Management database from here http://www.datastrat.com/DataStrat2.html but the browse doesnt include the file names, only the directories. The following API can search for files: http://www.mvps.org/access/api/api0001.htm but won't allow me to insert the full path into a field (including file). Unless I have missed something. How can I do it? cheers! ...

how change default set for email client
when we first time use outlook , it will ask to setup as default email client. so during access interenet , some webpage has contact us link to email, when click it will pop to outlook, but fo some reason, some use click cntact us in web , it will pop a outlook express. which is not my expected, how can i set it back to outlook Thank gary ...

Creating a userform?
I would like to enter data to a speadsheet with a userform. I want enter the data in a form that will automatically insert the data to the correct cells in my spreadsheet. And then allow me to enter new data on the next line below. Is this possible? I have just put up a file for you at:- http://www.pierrefondes.com/ It is item number 42 towards the top of my home page. In here I have tried to explain, as clearly as possible, what you need to do to set up and use a Form in EXCEL 2007. There is a simple worked example for you to work through if you want to. Please d...

opening a userform from a userform
Is there a way to open and use a userform from another userform? I am using userforms for data entry and would like the option to open a second form whilst entering data into a form Can anyone help? [excel 2003] Just Show it. Call is like this... UserForm2.Show or like this... UserForm2.Show vbModeless depending on whether you want UserForm2 to be modal or non-modal. Remember to change my example UserForm name from UserForm2 to whatever the name your second UserForm is. -- Rick (MVP - Excel) "Roger on Excel" <RogeronExcel@discussions.microsof...

How do i change what opens when i start Microsoft word?
when i open word one of my final exam papers from last semester automatically opens. I've tried changing my "normal" file, deleting macros, nothing works. How do i set the default document to open up to be blank? Take a look here: http://word.mvps.org/fAQs/AppErrors/BlankDocNotBlank.htm If editing Normal.dot/Normal.dotm doesn't fix the problem, make sure you're editing the correct one. Some people have multiple copies in different folders. -- Herb Tyson MS MVP Author of the Word 2007 Bible Blog: http://word2007bible.herbtyson.com Web: http://www.he...

Quote Fields
In creating a modified version of the CRM SFO Quote.dot file the custom fields we created in CRM show up with standard (merge) fields when added to the template, but don’t merge along with the standard fields when the template is sent to merge. What could we be doing wrong? What might we look for? -- Darryl - dh@mtccrm.com -www.mtccrm.com - Only Microsoft CRM ...

Format for text field
Hi- I have a text field, the format of this field is ##.## (ex: 09.10) How can I check this field if it have a value then it must be in a format like above? (null is OK) Thank for any replpy. >>How can I check this field if it have a value then it must be in a format like above? (null is OK) Null is not a value. Are you wanting to check for a numerical value or that the text is formated? -- Build a little, test a little. "MN" wrote: > Hi- I have a text field, the format of this field is ##.## (ex: 09.10) > How can I check this field if it have a va...

change font of all data labels at once
I have excel 2003. How do you change the font/font size of all the labels at one time? I can click on each one and do them one at a time but surely there's a way to do them all at once? :) Thanks Hi, You should be able to select all the data labels from 1 series at a time and apply formatting. Either you can click a label twice which in effect selects that individual data label or you have multiple series each containing 1 data point. Cheers Andy Meenie wrote: > I have excel 2003. How do you change the font/font size of all the labels at > one time? I can click on each one...

passwords - userforms
[Excel 2003] I have a spreadsheet which uses forms to enter and display data. The Spreadsheet uses a main page form "frmDashboard" from which other userforms are activated using command buttons. The form frmDashoboard remains open in the background as a desktop while the user activates the other forms. I am looking at various methods to password protect the opening of other forms from the main useform frmDashboard and I use the following code to do this: Private Sub CommandButton1_Click() Dim i_pwd As String i_pwd = InputBox("Please Enter Password&q...

Type property of XmlAttributeAttribute
Where and how to use Type property of XmlAttributeAttribute? Thanks for any samples that describe it ...

userform
hello everybody, is it possible to see the content of a cell (i.e. worksheet 1 cell y32) (and all it's changes) in a userform (which is also used to input data) while it is still open? Hi You will probably need some code for that, like Label1.Caption = Sheets(1).Range("Y32").Text trigged by some relevant events, like the Exit events of your input boxes. -- HTH. Best wishes Harald Followup to newsgroup only please. "Mauro" <mlauro@fastwebnet.it> wrote in message news:WlKQb.4922$VS6.4580@tornado.fastwebnet.it... > hello everybody, is it possible to see the ...

OWA problems after changing e-mail address
I am in the process of evaluating Exchange 2003 and have come across a problem authenticating to Outlook Web Access. Lets say that my test domain is called test-domain.com Our e-mail addressing scheme however will be different, lets say test.com So, in our environment, my e-mail address would be jrkallas@test.com despite the fact that our AD domain is test-domain.com When we make this change within AD Users and Computers, we are no longer able to authenticate to Outlook Web Access. We can still access e-mail through the Outlook Client, but get "Authorization failed" on OW...

Outlook Mail Merge STANDARD fields
Hi We are having a problem with producing a mailmerged quote document from the outlook client. I know that you cannot add any custom fields into a mailmerged word document, but I cannot seem to add the product line description into the document (the long, memo field). Steps, open a quote and choose file->Print Quote for Customer. Pick the installed quote template and open the document. When you go to 'add more fields' the product line description is not available. Any one any ideas as to why this standard field is not available to mail merge or any work around? Regards Peter...

Change from Transfer to Withdrawal
I would like to change some transactions from Transfers to plain old withdrawals. (Not sure how I ended up with the transfers in the first place.) How do I do that? In microsoft.public.money, Kathy wrote: >I would like to change some transactions from Transfers to plain old >withdrawals. >(Not sure how I ended up with the transfers in the first place.) >How do I do that? Go to the register where the withdrawal should be. If you are in single-line mode, I would press Ctrl+T to change to multi-line. Double-click where it says Transfer... and change the Category as appropriate....

Disabled field greyed out
Hi Folks We are using GO10 sp4. In VBA we are disabling the editing of the item description field using description.enabled = FALSE or description.locked = true. Both give the same result i.e. on the screen the field is greyed out and non editable to a user. Users need the field to be non editable but would like the grey background to rather be white or very light grey – they need to see the text on this disabled field easier. Does anyone know how we can do that please? Thanks in advance Sheila Have you thought about using Field Level Security instead? If all you want to do is disable...

Adding a field based on printed currency for an invoice?
I am trying to add a comment at the bottom of an invoice that says, "This invoice stated in US Dollars" or "This invoice stated in Canadian Dollars" based on which currency it is printed I was able to use the "Currency ID" field from the Sales work table, and this worked unless I changed the report from printing in the "originating currency" to the "functional currency". Any thoughts on another field to base the conditional statement? Thanks, KJ ...

Change field name programically
I have a table, MyTable It has a field called Memory (MB) This plays havoc with trying to do things because it has a space and ( ) brakets. How do I programically rename the field to say MemoryMB This is data that automatically gets imported so i have to get it this way. Thanks. On Apr 9, 12:30 pm, "steve" <stevesem...@lycos.com> wrote: > I have a table, MyTable > It has a field called Memory (MB) > This plays havoc with trying to do things because it has a space and > ( ) brakets. How do I programically rename the field to say MemoryMB > This is data that aut...

Fields in a query
I am trying to create a query but when I run it I get the message - query too complex. Am I limited to the number of fields I can incorporate into a query or is there a way round this. At present I have over 150 fields. I want to create a query that illustrates how pupils are progressing and applying themselves from when they join school until they leave. Any and all advice greatly received. Many thanks. Most of us old timers think anything more that 25-30 fields is too many. There are lots of reasons for the "too complex" message. I would prefer to not attempt ...