Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 12-15-2018, 12:59 PM
salimnore salimnore is offline clicking on a cell to reveal a name Windows 7 32bit clicking on a cell to reveal a name Office 2013
Novice
clicking on a cell to reveal a name
 
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, 19 views)
Reply With Quote
  #2  
Old 12-15-2018, 06:08 PM
Kenneth Hobson Kenneth Hobson is offline clicking on a cell to reveal a name Windows 10 clicking on a cell to reveal a name Office 2016
Advanced Beginner
 
Join Date: Jun 2018
Posts: 37
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 clicking on a cell to reveal a name Windows 7 32bit clicking on a cell to reveal a name Office 2013
Novice
clicking on a cell to reveal a name
 
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
clicking on a cell to reveal a name Open an existing sheet by clicking on a cell in a master sheet darbybrown Excel 3 09-12-2016 05:12 PM
clicking on a cell to reveal a name 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
clicking on a cell to reveal a name Can't cancel Reveal Formatting dave7777 Word 2 05-15-2011 01:18 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 02:55 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft