Display cell data in target cell upon single-click?

[hybridmoments82]

Hello Forum,

Not very well-versed in Excel but I figured as an IT professional I'd be able to wrap my head around the logic. Here's my question:

I created a nifty looking calendar in Excel for purposes of keeping track of tasks done per-day on a months-long project, however the formatting of the cell shapes/sizes in traditional calendar style only displays 4-6 words clearly before being cut off, and each day has quite a list of sentences within it. I did notice that when I click on the cell, I can see much more of the sentences within the formula bar. What I'd like to do is to create that same functionality but display the data of a "day" cell I click on in a "Notes" cell (D15) at the bottom of the calendar which I made relatively larger than the others. Exactly similar to how the formula bar functions, but inside of the workbook.

Is this possible?

I didn't search this first prior to asking and I am aware that's not very good forum etiquette, so if this has been asked before, please link me and accept my apologies. Just had no idea what keywords to use in this instance.

 

[Jeffrey Mahoney]

Welcome to the board. Maybe cell comments. I usually create a sheet event that when a cell is selected within the calendar, the sheet event places the cell text in another cell such as D15.

 

[Jeffrey Mahoney]

You have to place this code in a Sheet Module. You need to have two named ranges. The Calendar range is as it sounds. The Comment range is cell D15

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim i As Range
  
  Set i = Intersect(Target, Range("Calendar"))
  If Not i Is Nothing Then
    If i.Count = 1 Then
      Range("Comment") = i.Value
    Else
      Range("Comment") = ""
    End If
  Else
    Range("Comment") = ""
  End If
  
End Sub

 

[hybridmoments82]

You have to place this code in a Sheet Module. You need to have two named ranges. The Calendar range is as it sounds. The Comment range is cell D15

Thank you, Jeffrey! I appreciate your help on this matter. Thank you also for writing out the correct code to make this work.

Where do I define these ranges within this code?

 

[Jeffrey Mahoney]

Naming ranges is not a VBA thing. Highlight the area you want to name. From the Excel ribbon menu choose Formulas-Name Manager. Put in the name, and you're done.

The Scope of a named is sometimes important. A workbook level Scope Named Range can be referenced from any sheet. A Sheet level scope is usually only referenced from within the current sheet.

If you want I can explain it further. Simply put, named ranges can be used in any excel formula in the place of range of cells.

Jeff

 

[hybridmoments82]

Naming ranges is not a VBA thing. Highlight the area you want to name. From the Excel ribbon menu choose Formulas-Name Manager. Put in the name, and you're done.

The Scope of a named is sometimes important. A workbook level Scope Named Range can be referenced from any sheet. A Sheet level scope is usually only referenced from within the current sheet.

If you want I can explain it further. Simply put, named ranges can be used in any excel formula in the place of range of cells.

Jeff

Excatly what I was looking for! Works like a charm! Thank you for putting the time in to assist me with this. Greatly appreciated!

 

[hybridmoments82]

One more question about this. On the same spreadsheet I have multiple months (as if you were to separate the pages of a printed calendar and line up the months vertically). Can I just copy/paste the code from Private Sub... to ...End Sub, while renaming the range Calendar to the respective month range?

Each month has its own "Comment" section, so I guess I can just rename each "Comment" to "Comment1","Comment2", etc.

 

[Jeffrey Mahoney]

Yes you can. Make sure that you create named ranges that have a Sheet Scope instead of a Workbook Scope. Especially when the named ranges are named the same. When creating the named range, select the current sheet name.

Jeff

 

[hybridmoments82]

Hello Jeff,

Thanks again for being incredibly responsive! Rare to find such help on the internet these days.

Wasn't sure what you were referring to with this:

Make sure that you create named ranges that have a Sheet Scope instead of a Workbook Scope. Especially when the named ranges are named the same. When creating the named range, select the current sheet name.

Jeff

..which is likely why I'm getting this error:

Compile error: Ambiguous name detected: Worksheet_SelectionChange

Here's my code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)  Dim i As Range
  
  Set i = Intersect(Target, Range("March"))
  If Not i Is Nothing Then
    If i.Count = 1 Then
      Range("Comment1") = i.Value
    Else
      Range("Comment1") = ""
    End If
  Else
    Range("Comment1") = ""
  End If
  
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim i As Range
  
  Set i = Intersect(Target, Range("April"))
  If Not i Is Nothing Then
    If i.Count = 1 Then
      Range("Comment2") = i.Value
    Else
      Range("Comment2") = ""
    End If
  Else
    Range("Comment2") = ""
  End If
  
End Sub

 

[Jeffrey Mahoney]

Are those to Subs you listed above in the same sheet module? If they are you will need to move one of them to the other Month Sheet.