Populate a Userform Caption using a Named Range

[price156]

Hi All,

I'm new to VBA so hope this question isn't too dumb!

I'd like to add a "Key" to a user form that can dynamically update should it need to.

Currently the user form has a number of comboboxes which have shortened codes in them to make entering the data faster, and so the resultant database/worksheet, the selection is entered onto, isn't unnecessarily wide. The downside of this is the user must know the coding (ie A = Apple, B = Banana, etc) otherwise they could enter the wrong item. I plan to add a key at the bottom of the form so any user can refer to it should they need to, but rather than hardcoding the "Key" I'd like to populate it by using the same Named Ranges the comboboxes are populated by.

Is there away to make a Userform Label equal to a named range??

I've tried
* setting the caption in the label properties as '=Named_Range
* using the code Userform_Name.Label_Name.Caption = Sheets("Sheet_Name").Range("Named_Range").Value in the Private Sub Userform_Initialize ()
* as well as defining the variables like
Dim Label_Name_Variable As String
Label_Name_Variable = Sheets("Sheet_Name").Range("Named_Range").Value
Userform_Name.Label_Name.Caption = Label_Name_Variable

 

[dmt32]

Hi
welcome to forum

Returning a range to a userform label is not something I am aware (but happy to be told otherwise) that can be achieved in manner you are attempting - I suspect you probably are getting type mismatch errors?

Maybe something like following will help you

Private Sub UserForm_Initialize()
    Dim Text As String
    Dim Cell As Range
    For Each Cell In Sheets("Sheet1").Range("mynamedrange").Cells
        Text = Text & Cell.Text & " "
    Next
        Me.Label1.Caption = Text
End Sub

Change items shown in BOLD to meet your specific project need as required

Others here may have alternate suggestions

Hope Helpful

Dave

 

[Fluff]

I would suggest using a listbox instead of a label & you can populate it like

   Me.ListBox1.List = Sheets("Sheet_Name").Range("Named_Range").Value

You can also set the SpecialEffect property to flat & the Locked property to True, this will make it look more like a label & prevent users from doing anything with it.

 

[price156]

Thank you both for your suggestions, I went for the using a listbox instead of a label option which didn't quite work, but gave me the idea to get there in the end

Using the VBA code in the initialise sub was giving me errors, however, I could set the "Row Source" property of the ListBox "=Named_Range" and that works perfectly.

 

[Fluff]

Glad we could help & thanks for the feedback.