 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2006Can combobox selection place another value in spreadsheet?
I have a combobox where the user selects a city. The cities come from a spreadsheet where all the cities are listed in one column, and all of their zip codes are in the column next to it. How can I allow the user to pick a city, while putting that city's zip code into the spreadsheet? Astello If this is a combobox from the control toolbox (ActiveX) then set the ListFillRange property to the address of the list (e.g Sheet3!$A$1:$A$100), then set the LinkedCell property to a cell that will return the value of the selected item in the combobox. I usually set this to the cell 'under' the combobox as it will not be seen. Now set up a formula in the cell next to the combobox that references the cell with the LinkedCell range (e.g A1) =VLOOKUP(A1,'YourCityZipRange',2,FALSE)  SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS www.nickhodge.co.uk >I have a combobox where the user selects a city. The cities come from > a spreadsheet where all the cities are listed in one column, and all of > their zip codes are in the column next to it. How can I allow the user > to pick a city, while putting that city's zip code into the spreadsheet? It's actually a ComboBox in a UserForm, can I still do the same thing, or something similar? I'm trying to make it so that the end user never actually touches the data. Thanks. > Astello > [quoted text clipped - 22 lines] > > their zip codes are in the column next to it. How can I allow the user > > to pick a city, while putting that city's zip code into the spreadsheet? Astello I have a combo box called ComboBox1, a label called Label1 and the data to load the combobox is in A1:A25 on Sheet1 and the 'lookup' value is alongside in B1:B25. The event I am using is the ComboBox's _Change event below, after I have set the ComboBox's ControlSource property at design time to Sheet1!$A$1:$A$25. Private Sub ComboBox1_Change() Me.Label1.Caption = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, Worksheets("Sheet1").Range("Data"), 2, False) End Sub (The code from me.label... to ...False) should be on one line) This will load the associated value for the item selected in the combobox SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS www.nickhodge.co.uk > It's actually a ComboBox in a UserForm, can I still do the same thing, > or something similar? I'm trying to make it so that the end user never [quoted text clipped - 29 lines] >> > to pick a city, while putting that city's zip code into the >> > spreadsheet? Your logic makes perfect sense to me, but it won't work, probably because of how I'm inputting the values from the UserForm in the first place. I have a CommandButton that adds the values entered in the UserForm to certain cells in the spreadsheet all at once. But when I insert the code for ComboBox1_Change() it crashes when something in the ComboBox is chosen. Here is my code: Private Sub CloseButton_Click() Unload Me End Sub ______________ Private Sub cmdAdd_Click() Dim iRow As Long Dim ws As Worksheet Set ws = Worksheets("CostModelData") 'find first empty row in database iRow = 2 'check for a destination city If Trim(Me.ComboBox1.Value) = "" Then Me.ComboBox1.SetFocus Exit Sub End If 'copy the data to the database ws.Cells(iRow, 15).Value = Me.ComboBox1.Value ws.Cells(iRow, 16).Value = Me.txtMCFloor.Value ws.Cells(iRow, 17).Value = Me.txtDiscount.Value 'clear the data Me.ComboBox1.Value = "" Me.txtMCFloor.Value = "" Me.txtDiscount.Value = "" Me.ComboBox1.SetFocus End Sub _____________ Private Sub ComboBox1_Change() Me.Label3.Caption = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, Worksheets("Sheet1").Range("Data"), 2, False) End Sub _____________ Private Sub UserForm_Click() End Sub > Astello > [quoted text clipped - 56 lines] > >> > to pick a city, while putting that city's zip code into the > >> > spreadsheet? Astello It should work. If you have data in a combobox called combobox1 and you have a table set up on a sheet called Sheet1 with a range name of data (2 columns). If you then change the combobox, the value selected will look up the value in the data table and display in a label called label3 SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS www.nickhodge.co.uk > Your logic makes perfect sense to me, but it won't work, probably > because of how I'm inputting the values from the UserForm in the first [quoted text clipped - 118 lines] >> >> > to pick a city, while putting that city's zip code into the >> >> > spreadsheet? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.