Populate a Userform Caption using a Named Range

price156

New Member
Joined
May 13, 2020
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
  2. MacOS
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
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

Rich (BB code):
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
 
Upvote 0
I would suggest using a listbox instead of a label & you can populate it like
VBA Code:
   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.
 
Upvote 0
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.
 
Upvote 0
Glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,557
Latest member
richa mishra

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top