Solved

Cell Value Equal Combo Box in Excel

Posted on 2013-06-25
5
7,010 Views
Last Modified: 2013-06-25
Hi,

I have goolged this and seen there are several ways to accomplish this, but I can 't seem to make it work. Not sure if I use VBA or just have the cell value equal the combo box contents.

In my Print Report! worksheet I am using a bunch of vlookups from my DataEntry! worksheet. All of these lookups are currently based off the cell in PrintReport! E3 and they work just fine. But, I would like to use the combo box properties of autocomplete (there will be about 600 entries by the end of the year) to make it easier picking the name. I created a combo box (activeX) and set the properties. I am using quite a bit of Index, Match and vlookups throughout the entire workbook. I have done this all on my own, so I am certain there are better ways, but this is what I know how to do.

Question: How to I set the value in E3 (PrintReport!) to the contents of my combo box?  The combo box will be moved over E3, so the user will only see the combo box.

My workflow will be entering the data into DataEntry! worksheet, then choose from the combo box which will populate the results. Then they will click on EMAIL REPORT which will save as a PDF and email the report (Ok, still working on that part :)  )

Thanks for any help!
EE-Question.xlsx
0
Comment
Question by:bvanscoy678
[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
  • 3
  • 2
5 Comments
 
LVL 12

Accepted Solution

by:
telyni19 earned 500 total points
ID: 39275793
I think the simplest way is to just use the Change event on the combobox. Open the code for the combobox (enter design mode, right-click on the combobox, and choose "View Code"). Replace any existing Change event with the following:

Private Sub TempCombo_Change()
ActiveSheet.Range("E3").Value = TempCombo.Value
End Sub

Open in new window

0
 

Author Comment

by:bvanscoy678
ID: 39275887
Yes, that did the trick! Thanks for the help
thanks
0
 

Author Closing Comment

by:bvanscoy678
ID: 39275896
Thank you. Brente
0
 
LVL 12

Expert Comment

by:telyni19
ID: 39275901
(Answering the question about defining the tempcombo variable:) Nope. tempcombo appears to be the name of your combo box already. If you're within the tempcombo_Change event, the context of the code ensures that the reference to tempcombo will be understood. You can even use Me.Value instead, as the keyword Me will refer to whatever object owns the current event. But I tested what I gave you in the file you attached, so if you paste in exactly what I gave you, you should get the desired results.
0
 

Author Comment

by:bvanscoy678
ID: 39275921
I must have done something wrong the first time, then I posted the reply. Once I tested again, it worked just fine. I wasn't exactly sure where the tempcombo_Change event was located, but after a quick read, I can see that the drop down arrow in the upper right corner was set to change.

Great information about me.value. I will keep that in mind.

Thanks! Brent
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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.

739 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