Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Unsolvable - Displaying Named Ranges based on Clicking a Cell

Posted on 2004-08-11
Medium Priority
134 Views
EE,

How do I move only the names into a another sheet but I want them to be in a 3 columns instead of one long column?
And then when you click on a name, it will display it's range a few columns over.  Picture this.. a list of names and when you click on the name, all of the person's data is displayed, which IS already defined.  I have the named ranges defined (insert menu > names > define). Any sample code on how to do this?

A          ..       J
John Smith        data
data
data
Jane Doe           data
Mike K. Jordan   data
data
data
data
.
.
.
<<Points refunded (500) because user answered question himself
byundt--Excel TA Page Editor>>
0
Question by:efarhat
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 4

Author Comment

ID: 11779150
i'll figure out how to add the color later. have to make it aesthetically pleasing.
0

Author Comment

ID: 11782895
can anyone please provide feedback on this? I am really struggling.
0

Author Comment

ID: 11784145
right now this is what i have displaying a list of names, except it skips the first name and i haven't been able to figure out the associate of ranges...

i also figured it would be easier to display 5 columsn of 10 names in each.

Dim r, c, d As Range, sTraderNames As String
Dim i As Long
i = 0
Set r = Sheets("layout").Range("A4")
Set d = Sheets("report").Range("A1")

While Not (IsEmpty(r) And IsEmpty(r.Offset(0, 1)))
Set c = r.Offset(1, 0)
While IsEmpty(c) And (Not IsEmpty(c.Offset(0, 1)))
Set c = c.Offset(1, 0)
If c.Offset(-1, 0).value = r.Offset(0, 0).value Then d.Offset(i, 0).value = c.value
Wend
d.Offset(i, 0).value = c.value
Set r = c
i = i + 1
Wend

End Sub

0

Accepted Solution

efarhat earned 0 total points
ID: 11785704
ok, i was able to generate the columns:

Dim Trader As Range
Dim Traders As Range
Dim ColCount As Integer

Sheets("Report").Select
Range("A1").Select

Set Traders = Range(ActiveCell, ActiveCell.End(xlDown))

Sheets("Layout").Select
Range("A1").Select

ColCount = 1
If ColCount > 3 Then  ' set this value to the number of columns you require
ColCount = 1
ActiveCell.Offset(1, 0).Select
End If
ActiveCell.Offset(0, (ColCount - 1) * 2).Value = Trader
ColCount = ColCount + 1

End Sub

BUT: how do i associate a Traders name with a defined region?  and for it to display when i click on the name...
0

LVL 81

Expert Comment

ID: 11817095
Essa,
I am going to refund your points on this question since you have solved the first part yourself, and have asked the second part in http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21091484.html

I also notice that you have asked another question in two technical areas. Experts-Exchange policy is for you to ask a question in only one forum. That said, you can use Pointer questions to alert experts in other technical areas about your problem. The title of this question might be "50 point question on "Object defined or application defined error in Excel VBA". The body of the question would then describe a few more details and ask experts in the Visual Basic TA to post comments in the following link:
http://www.experts-exchange.com/Applications/MS_Office/Excel/Q_21095236.html

I have converted this link into a Pointer question: http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_21095174.html

byundt--Excel TA Page Editor
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
###### Suggested Courses
Course of the Month6 days, 11 hours left to enroll

#### 705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.