PDA

View Full Version : Solved: How to Identify Worksheet Name for Each Range



brorick
05-25-2008, 11:14 AM
Can anyone tell me how to identify the worksheet name for each range name in a workbook? I know how to find the range names, but I need to refer to the worksheet name of each range name I find in a workbook. Does anyone have any ideas? :doh: Thanks in advance.

Bob Phillips
05-25-2008, 12:02 PM
This seems to work



Dim nme As Name

For Each nme In ActiveWorkbook.Names

Debug.Print nme.Name & " - "; Range(nme.RefersTo).Parent.Name
Next nme

brorick
05-25-2008, 12:29 PM
XLD, thanks. I will give this a try.

brorick
05-25-2008, 01:43 PM
XLD, the code you provided works perfectly. I am experimenting with another piece of code. Using the code XLD provided, could someone please tell me how to set the nme value. Here is what I attempted but of course it is not working. Any thoughts?

Example:

Dim nme as name
Dim rngstrg as string

rngstrg = "EmpInfo"
set nme = range(rngstrg)

Debug.Print nme.Name & " - "; Range(nme.RefersTo).Parent.Name

Bob Phillips
05-25-2008, 03:14 PM
It is not clear what yoou are trying to do in that example.

ARe you tring to create a VBA variable pointing to a range with that name, or set that name to refer to a particular range?

brorick
05-25-2008, 05:25 PM
My goal is to populate a combo box list with all the range names in my workbook. Based on my selection from the list I want to populate a text box with the worksheet name of the selected range. In the following example I am able to pull the worksheet name using the wsnm as based on your previous code. I am not sure how to populate the text box with the worksheet name based on the selection from cboCategory.

Dim nme As Name
Dim wsnm As String

For Each nme In ActiveWorkbook.Names
wsnm = Range(nme.RefersTo).Parent.Name
cboCategory.AddItem nme.Name
Next nme

sai_golden
05-25-2008, 07:08 PM
Hi ,

I think this should work fine:

Private Sub cboCategory_Change()
TextBox1.Text = cboCategory.Value
End Sub

Replace the texbox1 in the code with the textbox object name in your form

brorick
05-25-2008, 08:54 PM
Sai_golden, thank you for your response. Your response is correct in how I would place the value of the selected item from cboCategory into a text box. But, I am still faced with the issue of identifying the name of the worksheet for the selected range from cboCategory. If I have five ranges and each range is on a different worksheet, I need to identify what the name of the worksheet is for the selected range. That is currently the biggest problem I am having at this time.

sai_golden
05-27-2008, 09:15 PM
Hi Brorick,

Apologise for the late response.
From your query above i understand the following:
(1) You are selecting a named range in a combo box
(2) When you select the named range the corresponding worksheet's name should be displayed in a text box.

Kindly clarify if my above understanding is right.

sai_golden
05-27-2008, 09:34 PM
Hi,

Please find sample code in the attached file.

Hope it answers your query. Any further clarifications please ...

Regards
Sairaj P

brorick
05-27-2008, 10:15 PM
sai_golden, thank you for your response and sample workbook. I will gladly take a look at it.

brorick
05-27-2008, 10:18 PM
Sai_golden, thank you. It works perfectly. The code is very clean and just what I needed. I appreciate your help. :friends: