Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel Programming

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 12-15-2018, 12:59 PM
salimnore salimnore is offline Windows 7 32bit Office 2013
Novice
 
Join Date: May 2018
Posts: 8
salimnore is on a distinguished road
Default clicking on a cell to reveal a name

Hi all,

Thank you for taking the time to read my post, I am trying to click on a cell to reveal the names in a message box. I know this can be done using VBA. I have no experience in this and was wondering if there was an alternative or if someone can guide me with the vba with an explanation. My intention is to replicate this on a larger scale. I have attached an example of what i intend to achieve.


Thank you to all who help or comment.



Best wishes

Sam
Attached Files
File Type: xlsx names from a table.xlsx (11.3 KB, 12 views)
Reply With Quote
  #2  
Old 12-15-2018, 06:08 PM
Kenneth Hobson Kenneth Hobson is offline Windows 10 Office 2016
Novice
 
Join Date: Jun 2018
Posts: 25
Kenneth Hobson is on a distinguished road
Default

Right click the sheet's tab, View Code, and paste. Add the reference as commented.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim r As Range, c As Range, e
  'Tools > References > Microsoft Scripting Runtime
  Dim dic As Dictionary     'Early Binding metho
  
  Set c = Intersect(Target, [D4:G7])
  If c Is Nothing Then Exit Sub
  If c.Count <> 1 Then Exit Sub
  
  Set dic = New Dictionary
  For Each c In Range("K3", Range("K3").End(xlDown))
    If Cells(c.Row, "K") = Cells(Target.Row, "C") And _
      Cells(c.Row, "L") = Cells(3, Target.Column) Then
      e = Cells(c.Row, "J")
      If Not dic.Exists(e) Then dic.Add e, Nothing
      End If
  Next c
  
  If dic.Count = 0 Then
    MsgBox "None", , "Names Found"
    Else
    MsgBox Join(dic.Keys, vbLf), , "Names Found"
  End If
End Sub
Reply With Quote
  #3  
Old 12-16-2018, 02:11 PM
salimnore salimnore is offline Windows 7 32bit Office 2013
Novice
 
Join Date: May 2018
Posts: 8
salimnore is on a distinguished road
Default

Thank you Mr Kenneth Hobson.

I really appreciate your help.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
clicking on a cell to reveal a name salimnore Excel 1 12-16-2018 12:34 AM
Open an existing sheet by clicking on a cell in a master sheet darbybrown Excel 3 09-12-2016 05:12 PM
Help with a pop up window with relevant information upon clicking a value in cell Pvidya Excel 4 05-15-2014 04:58 PM
Help with a pop up window with relevant information upon clicking a value in cell Pvidya Excel Programming 2 05-15-2014 11:08 AM
Can't cancel Reveal Formatting dave7777 Word 2 05-15-2011 01:18 PM


All times are GMT -7. The time now is 05:49 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft