Solved If cell is selected, display paragraph or image in new cells

Microsoft Office excel 2007
August 28, 2012 at 12:48:57
Specs: Windows XP
Hello and thanks in advance for taking the time to read.

I am wonder if it is possible to create a dynamic display of sorts in excel. My goal is to have a list of items (for example in column A). When the user selects A1, cells C1-10 (range is variable) will populate with a paragraph's worth of information about the item selected from A1. A2 will bring it's own paragraph and so on.

To explain further or in different wording, I would like a column to display different information each time a user selects a cell in column A.

I am comfortable working backwards with VB, meaning if I have a basecode I will be able to change it to fit all of the other columns/cells.

It looks like I might be able to accomplish some of what I am hoping for using "comments." However I cannot make it so the comment appears and stays on the screen when the cell is clicked.

I appreciate your time. Thanks!


See More: If cell is selected, display paragraph or image in new cells

Report •


#1
August 29, 2012 at 04:34:42
✔ Best Answer
How about this:

1 - Put the desired data in columns in a different sheet, e.g. Sheet2

The data you want to appear when Sheet1!A1 is selected should go in Sheet2!Column A, which is Column 1.

The data you want to appear when Sheet1!A2 is selected should go in Sheet2!Column B, which is Column 2.

etc.

2 - Right click the sheet tab for Sheet1, choose View Code and paste this code into the pane that opens:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Determine if a cell in Column A was selected
  If Target.Column = 1 Then
'If Yes...
'Disable Events while this code runs
    Application.EnableEvents = False
'Copy data from Sheet2 based on which Row was selected in Sheet1
'The selected Row will determine which Column is copied
      Sheets(2).Columns(Target.Row).Copy _
        Destination:=Range("C1")
'Re-enable Events
     Application.EnableEvents = True
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
August 29, 2012 at 06:28:03
Code works very well, thank you!

I am having trouble with it when I attempt to edit as I change some formatting around. I'm now using the original A1 as B1. I switched the Target Column to B, "if arget.Column = 2 Then"

Could not get that to work. Afterwards I reverted to your original code and could not get A1 to populate C1 until I restarted the program.

By changing the target to column 2, do I have change Sheet2's target copy?

Thanks!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Determine if a cell in Column B was selected
  If Target.Column = 2 Then
'If Yes...
'Disable Events while this code runs
    Application.EnableEvents = False
'Copy data from Sheet2 based on which Row was selected in Sheet1
'The selected Row will determine which Column is copied
      Sheets(2).Columns(Target.Row).Copy _
        Destination:=Range("C1")
'Re-enable Events
     Application.EnableEvents = True
  End If
End Sub


Report •

#3
August 29, 2012 at 06:48:52
I'm not sure what problem you are having.

I pasted the code you posted into the VBA editor, then selected cells in Column B and it worked fine.

The only thing that I can think of this:

If you stopped the code (or if an error stopped it) after this line:

Application.EnableEvents = False

and before this line:

Application.EnableEvents = True

then Events were disabled and no Event code would have run until you either restarted the Application or ran a macro with the instruction:

Application.EnableEvents = True

Let me know if you have any further problems and I'll do some more testing

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
August 29, 2012 at 07:19:37
I started a new Workbook and began with pasting the code into VB before inputting any data. Tested and worked. After that, I formatted Sheet1 and tested again with success. It looks like the breakdown starts when I change the width of a column on Sheet2. Even if I return the colmuns to the defaults and try again, the Sheet2 columns no longer paste in.

If I resize C on Sheet1, C resizes as the contents are pasted in but no data is input.
The easy solution is just don't resize! But I will try too see if creating a new default column widths in sheet2 before I put in the code will help.

Thanks for all your help so far Derbydad! I sincerely appreciate it.


Report •

#5
August 29, 2012 at 07:58:56
I resized columns on Sheets 1 & 2 numerous times and have no problems running the code.

I'm using 2003 currently, but can try it 2010 this evening.

I see no reason why a change in the column width would prevent the Copy/Paste function or the code itself from working.

You said you could work "backwards with VB" so I would suggest doing some testing to see if the data is actually being copied but not pasted or if the code itself isn't even running once you make the formatting changes.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
August 29, 2012 at 08:03:31
The VB code itself appeared unchanged after formatting changes. Truthfully I have no idea why it would stop. Suppose I could blame a 10 year old work computer. I will keep toying with it but you have helped tremendously. Thank you!

Report •

#7
August 29, 2012 at 08:47:09
re: "The VB code itself appeared unchanged after formatting changes."

I'm not surprised that the code wasn't changed since you can't edit a macro in that manner.

The only way a macro can be edited is by manually typing code into the VBA editor.

You can't even edit an existing macro with the VBA recorder since the recorder won't accept an existing macro name without asking the user if (s)he wants to replace it.

Again, the only thing that I can think of is that events are somehow being disabled by your actions.

One way to check that would be to put this code in the same sheet module and then, when the Copy/Paste code doesn't appear to work, enter a value in A1. If the message doesn't pop up, then events are disabled.

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = "$A$1" Then _
     MsgBox "Events are Enabled"
End Sub

A learning moment:

Note that the Copy/Paste macro is named:

Worksheet_SelectionChange

while the one in this post is named:

Worksheet_Change

The key is that the SelectionChange code fires as soon as a range is selected while the Change code fires after a range is changed.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question