Solved

Excel user form with combo

Posted on 2013-12-10
8
214 Views
Last Modified: 2013-12-10
Experts:

Please see attached XLS.    I need to figure out how to apply two combo boxes (currently available through Validation List in worksheet) to a user form.

Additional information is included in the attached XLS.

Thank you in advance,
EEH
UserForm-with-Combo.xls
0
Comment
Question by:ExpExchHelp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
8 Comments
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 500 total points
ID: 39708327
Add this to the userform module of the project

Private Sub UserForm_Activate()
    Dim cel As Range
    Me.cboField1.Clear
    For Each cel In Range("Lookupfield1").Cells
        Me.cboField1.AddItem cel.Value
    Next cel
    Me.cboField2.Clear
    For Each cel In Range("Lookupfield2").Cells
        Me.cboField2.AddItem cel.Value
    Next cel
End Sub
0
 

Author Comment

by:ExpExchHelp
ID: 39708357
ssaqibh:

Thanks for the prompt reply...

Right now, there appears to be a conflict.   When adding the function, the form shows but doesn't unload when clicking "Ok".

Envisioned process:
- Open XLS
- Form automatically opens
- I select values from the two drop-downs
- I click "Ok"... the form closes and values are automatically entered into the designated cells (i.e., B2 and C2).

What am I missing?

EEH
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 500 total points
ID: 39708434
Before adding code to the file you uploaded; when I clicked Ok it broke at

    ActiveWorkbook.Sheets("GenericModel").Activate

because that sheet was not available. When I commented out that line the unload was ok and remained the same way even after adding my code.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ExpExchHelp
ID: 39708445
ssaqibh:

Sorry... I noticed afterwards that the naming convention between form and worksheet were different.

Once I synchronized the naming convention, the selected values were not unloaded into the worksheet.  

Something is missed on my end?    Would you mind posting your working solution?

Thanks,
EEH
0
 

Author Comment

by:ExpExchHelp
ID: 39708455
Never mind... I got it!   Thanks for providing the solution!   ;)

EEH
0
 

Author Comment

by:ExpExchHelp
ID: 39708956
ssaqibh:

One quick follow-up question... for one of the combos, the lookup values in in "Currency" format.

However, in the combos on the form, a $3.00 is shown as "3".   How can I change the format of the combo on the form to $3.00?

I tried the following two but they only show 0.00 values for all lookups.



*************

    Me.cboDollarGallon_A.Clear
        For Each cel In Range("LookupDollarGallon_A").Cells
            Me.cboDollarGallon_A.AddItem cel.Value = Format(cboDollarGallon_A.Value, "0.00")
    Next cel
   
   
        Me.cboDollarGallon_A.Clear
        For Each cel In Range("LookupDollarGallon_A").Cells
            Me.cboDollarGallon_A.AddItem cel.Value = Format(cboDollarGallon_A.Value, "#.##")
    Next cel
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39709096
Try

 Me.cboDollarGallon_A.AddItem cel.text
0
 

Author Comment

by:ExpExchHelp
ID: 39709235
Excellent... works like a charm!

Thanks again!

EEH
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question