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

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,515
Office Version
  1. 2019
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
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.
 

price156

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,562
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,587
Messages
5,637,248
Members
416,963
Latest member
samfuge

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
Top