Click on a blank cell and have text appear in another cell

[Davespil]

I want to be able to click on a blank cell and have text inserted in another cell. When they click on say cell D4-D8 (merged cells) I want instructions to be displayed in cell A20. Is there a macro that I can use to make that happen?

[offthelip]

the answer is yes there is! Rather than just answering the question I will explain how to do it:

you need to use the the worksheet selectionchange event. Open the visual basic window and right click on the worksheet that you want to monitor, and select view code. then in the window that opens select worksheet inthe left hand wondow and selection change in the right hand windos then post this code , This put the instructions in for D4 . copy and modify for other cells. The application.enablevents is turned off when changing the cell A20 to prevent the worksheet recalculating unnessarily


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range("$D$4"), Target) Is Nothing Then
Application.EnableEvents = False
Cells(1, 20) = " these are the instructions"
Application.EnableEvents = True
End If

[snb]

This suffices:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$D$4" Then cells(20,1) = "my instructions"
End Sub

But for this purpose I'd suggest to use a comment, that can be made visible/invisible


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
cells(20,1).comment.visible=target.Address = "$D$4"
End Sub
And refrain from using 'merged cells' in VBA.

[mdmackillop]

Hi SNB,
You missed "D4-D8 (merged cells) "

[snb]

@MD

No I didn't, I abhorr them (last sentence).
I don't adapt code to clumsiness/malpractice. ;)

[Davespil]

What would $D$4 be if it was merged cells like D4-L4?

[mdmackillop]

What would $D$4 be if it was merged cells like D4-L4?
See post #2

[snb]

Private sub Workbook_open()
sheets(1).cells.unmerge
End Sub

[Davespil]

OK, so I tried the two solutions below but neither worked. Also, Cells(1, 20) would put the message in cell A20 or T1?:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range("$A$4"), Target) Is Nothing Then
Application.EnableEvents = False
Cells(20, 20) = "these are the instructions"
Application.EnableEvents = True
End If

And:

[Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Cells(20, 20).Comment.Visible = Target.Address = "$A$4"
End Sub

Yes, I switched the cell to A4 for now. I want to get it working before I add it to a merged cell.

[snb]

well.... cell A20 should contain a comment in the first place.

And then you should check for spelling errors:

Cells(20, 1).Comment.Visible = Target.Address = "$A$4"

[Davespil]

well.... cell A20 should contain a comment in the first place.

And then you should check for spelling errors:

Cells(20, 1).Comment.Visible = Target.Address = "$A$4"

So (20, 1) is A 20. Thank you. I switched it to 20, 20 because I have something in A20.

[offthelip]

Hi ,
My mistake the references Cells (1,20) refers to row 1 and column 20, which puts it is T1 so if you change the reference to cells(20,1) it will put the reference into A20.

[Davespil]

Hi ,
My mistake the references Cells (1,20) refers to row 1 and column 20, which puts it is T1 so if you change the reference to cells(20,1) it will put the reference into A20.

Thank you. But the message "these are the instructions" is not appearing when I click in A4 (I changed it to $A$4). Could the fact that I have several sheets be causing a problem. When I click A4 I want the message to appear on the same sheet. So I don't think that it should affect it.

[Davespil]

To better explain what I'm looking for I have uploaded a censored version of the Sheet. I changed the field names to Xs. What I want to happen is when someone clicks on one of the white boxes (for example: B4:G4) the Help Field (B18:AS22) will be populated with an explanation of what kind of data will be required in that field. Some of these boxes have a drop down list in them and one will have 100% already selected for them (though they can select a different value from the drop down menu).

16482

[snb]

That isn't a sheet, it's a picture.
A picture doesn't contain cells, nor comments.

[offthelip]

One possible problem I can think is: you need to put the code as above into the worksheet selection change for every
sheet. The code works perfectly in my test sheet.
the other possibility is that the "Help" field looks like it is merged cells and so you need to put the instructions in the top left corner which looks like it might A or B 18 ( cells(18,1) or cells (18,2)

[mdmackillop]

This will work for all sheets. If you wish to exclude some sheets, the code can be modified by reference to Sh.Name

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Sh.Range("$A$4"), Target) Is Nothing Then
Application.EnableEvents = False
Sh.Cells(20, 1) = "these are the instructions"
Application.EnableEvents = True
End If
End Sub