Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Excel user form with combo

Posted on 2013-12-10
8
Medium Priority
?
227 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 2000 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 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

636 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