• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 229
  • Last Modified:

Excel user form with combo

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
ExpExchHelp
Asked:
ExpExchHelp
  • 5
  • 3
2 Solutions
 
Saqib Husain, SyedEngineerCommented:
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
 
ExpExchHelpAuthor Commented:
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
 
Saqib Husain, SyedEngineerCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
ExpExchHelpAuthor Commented:
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
 
ExpExchHelpAuthor Commented:
Never mind... I got it!   Thanks for providing the solution!   ;)

EEH
0
 
ExpExchHelpAuthor Commented:
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
 
Saqib Husain, SyedEngineerCommented:
Try

 Me.cboDollarGallon_A.AddItem cel.text
0
 
ExpExchHelpAuthor Commented:
Excellent... works like a charm!

Thanks again!

EEH
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now