Help with a dropdown list

[malik641]

In a dialog sheet I made a dropdown list of certain cells from another worksheet called "Total Inventory" and I was wondering if I can create a code so that whatever product is selected from the dropdown list, it would show the current inventory (which would be found in the total inventory worksheet) of that product inside a group box

If this is too vague let me know, and I'll be more specific

[malik641]

maybe i do need to be more specific

whatever is selected from the dropdown list, I want to display certain text from other cells in a label, thats all

Here's what I have:

Sub DropDown26_Change()
'R1 Inventory
If Reagent_Drop_Down = "='Total Inventory'!A5:A62" Then
R1 = "='Total Inventory'!C5:C62"
Else
R1 = ""
End If




End Sub

R1 is the Label name
I'm sure this is way off, but I haven't programmed in VB since high school

so any help is greatly appreciated

[malik641]

Am I asking a dumb question?

[Dave Peterson]

I think that you haven't posted enough details.

If your inventory sheet has one row per part number, maybe just using =vlookup()
would be sufficient.

If your inventory has multiple rows per part, maybe =sumproduct() would work.

I assumed that you have one row per part number.

I put a button on the dialog and assigned it this macro:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim res As Variant
Dim myDD As DropDown
Dim myItem As String

Set wks = ThisWorkbook.Worksheets("Total Inventory")

Set myDD = ThisWorkbook.DialogSheets("dialog1").DropDowns("drop down 1")

With myDD
If .ListIndex < 0 Then
Beep 'nothing selected
Else
myItem = .List(.ListIndex)
End If
End With

res = Application.VLookup(myItem, wks.Range("a:B"), 2, False)

If IsError(res) Then
res = 0
End If

ThisWorkbook.DialogSheets("dialog1").Labels("Label 1").Caption = res

End Sub

The other thing is that most people aren't familiar with dialogsheets (I'm
not!). If you're using xl97+, you may want to consider using UserForms.

malik641 wrote:
>
> Am I asking a dumb question?
>
> --
> malik641
> ------------------------------------------------------------------------
> malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
> View this thread: http://www.excelforum.com/showthread...hreadid=378607

--

Dave Peterson