Help creating a Combo Macro to place data into another cell

**Darth269** · 02-05-2012, 06:13 AM

Hi folks,

I'm new here, new to Excel 2010, I know what a macro is, even though I'm pretty useless at creating them.

So far I've gotten through by looking at example code and modifying it. I've honestly got no idea how anyone got stuff done before Google!

Anyhow...

In Worksheet "Supply Calc" I've a combobox drop down list. The list pulls names from a "name range" called "Zone_Types" in a worksheet called "DATASHEET" which is a 2 column table with NAMES in column A, and a numeric value in column B.

What I would like is when I select an item from the drop down ComboBox (named "TOL" by the way) for the numeric value associated with it (1 cell over) to appear in cell B25 on the "Supply Calc" worksheet.

I know there may well be easier ways to do this, and if there are... by all means suggest them!

This is my first post, I'm sorry if I've missed something, I've done some basic searches, but without knowing some of the terminology to use, it's been tough on this one.

I also tend to ramble... so shutting up now. (thanks)

**Andrew-R** · 02-05-2012, 06:41 AM

If it's an ActiveX combo box you've embedded on your sheet then you'll need to use the change event for the object. Something like this, perhaps:

Private Sub TOL_Change()

Const sDATA_SHEET = "DATASHEET"
Const sTARGET_SHEET = "Supply Calc"
Const sTARGET_CELL = "B1"
Const lSEARCH_COL = 1
Const lOFFSET = 1

Dim rngMatchValue As Range

Set rngMatchValue = Sheets(sDATA_SHEET).Columns(lSEARCH_COL).Find(TOL.Value, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=True)

If Not rngMatchValue Is Nothing Then

  Sheets(sTARGET_SHEET).Range(sTARGET_CELL).Value = rngMatchValue.Offset(0, lOFFSET).Value

End If

End Sub

**Darth269** · 02-05-2012, 07:02 AM

Thanks Andrew-R, this seems great, but when I change the content of the dropdown box, i.e. make a selection, nothing appears in the specfied cell, I'm wondering...

Are any of the items dependant certain heading fields? Such as line 8 where it mentions "lSEARCH_COL"?

I've atached the sheet, hoping that this makes more sense...GM sheet.xlsm

**Andrew-R** · 02-05-2012, 07:04 AM

Sorry, the sTARGET_CELL value determines where the output goes.

Changing that line to:

sTARGET_CELL="B6"

Should sort you out.

**Darth269** · 02-05-2012, 07:10 AM

Andrew-R... You are Awesome!

**Andrew-R** · 02-05-2012, 08:15 AM

By the way, unless you've got a good reason for using an ActiveX control you'd be far better off using the Data Validation setting on cell B3 to show the drop down list, then in cell B6 you could use the formula:

=IF(B3="","",VLOOKUP(B3,DATASHEET!A:B,2,FALSE))

And that would get rid of the need for VB altogether (plus embedded ActiveX controls have a reputation for being a bit flaky, so are probably best avoided).