Combobox With Variable ListFillRange

[redneck345]

I don't have any code experience. So I will attempt to explain as best I can.
I have a Combobox named "Combobox54" in a Excel 2003 Workbook on a worksheet named "Quote Worksheet".
I am trying to provide a ListFillRange (for Combobox54) which will have two possible Named Range Values RTFCLADRW or SIGDRW.
On another sheet (Sheet name = "Menu") I have a Named Range DRWBOXMENU that provides a value of either "1" or "0".
So if DRWBOXMENU equals 1, I want the ListFillRange value in Combobox54 to be SIGDRW otherwise (if the value is 0) I want the ListFillRange value to be RTFCLADRW.

I have tried this via formula in a cell on the menu sheet (to avoid programming) and I keep getting the #value error. I think I am going to need code to assign the value of the ListFillRange.

I have scoured the board looking for an example of my issue but, even if I found a match, I could not begin to generate the proper code. What I have realized is.....you people are absolutely AMAZING!
Can anyone help me out with the Code to do this?

Also....What are "Tags" referenced below this box?
Thanks

 

[Andrew Poulsom]

Try this in the module for the Worksheet that contains your ComboBox:

Private Sub ComboBox54_DropButt*******()
    With ComboBox54
        If Worksheets("Menu").Range("DRWBOXMENU").Value = 1 Then
            .ListFillRange = "SIGDRW"
        Else
            .ListFillRange = "RTFCLADRW"
        End If
    End With
End Sub

 

[redneck345]

I'll give it a shot Andrew, and let you know what happens.

Thank you so much!

 

[redneck345]

Hi Andrew,

Strange......I have to hit "run"...from the code window for this to "set" the menu.
Maybe I copied something in wrong?

Thanks

 

[Andrew Poulsom]

HTML has garbled the first line. It should read without spaces:

Private Sub ComboBox54_DropButton C l i c k()

 

[redneck345]

Worked like a charm Andrew!
Absolutely Amazing what you folks can do!

Thank you so very much.