Solved

Excel user form with combo

Posted on 2013-12-10
8
220 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

729 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