Solved

Excel user form with combo

Posted on 2013-12-10
8
184 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
  • 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
 

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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 article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now