How to trigger a macro on cell *selection*

[Larry_1]

I am slowly learning VBA.

I have an Excel spreadsheet and between 25 and 100 MS Word documents in the same hard drive folder. The spreadsheet uses 4 sheets. The first sheet, “Available Documents,” has a table with 2 columns:

Document number (for example, “Gardening-0017” – The associated Word document uses the “document number” as the file name.)

Document title (for example, “pruning roses”)

All of the sheet’s cells are protected from edits, and selections are limited to the cells in the document number column. When the user selects a cell in that column, I want to open the associated Word file and then return when the user finishes reviewing that file. I have created a Word template with a command button that minimizes and then closes the Word file, so that should return control to the Excel table. But how to call the document? I could create a command button for each cell in the column, but that seems heavy.

What commands or functions could I use to trigger a macro based on selecting a cell from the document number column (the cells are protected against changes) and call an external document?

And how do I prevent calls based on a multi-cell selection?

I would sincerely appreciate any suggestions that you can offer.

Very respectfully,
Larry

[NoSparks]

What you're looking for is the Worksheet_SelectionChange event.

Right click the sheet tab then View Code.
Select Worksheet from the drop down where it probably says (General).
All the different worksheet events will now appear in the other drop down.

This assumes that what you are referring to as a table is in fact an Excel table, not a standard range.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'limit to single cell selection
If Target.Count > 1 Then Exit Sub

'limit to the first column of data in the first table on the sheet
If Intersect(Target, Me.ListObjects(1).ListColumns(1).DataBodyRange) Is Nothing Then
    Exit Sub
Else
    ' do what's required
    ' for example
    MsgBox "The sheet address of the cell you just entered is " & Target.Address
    '
    '
End If
    
End Sub

Hope this helps.

[Larry_1]

Oops. Sorry, NoSparks,



I was a very proficient Word user, but, having retired, I use it less and thus have fallen to being only reasonably proficient. In the mid 1980s I was a power user in Lotus 123, but I’ve not used it since. Although I can work in Excel somewhat, I’m a rank beginner in Excel VBA. In Word, you have to actually insert a table; I didn’t have to insert anything into Excel, it all looked like a “table,” and that was how I had been considering it. So that was an inaccurate description: I’ll have to read up on Excel tables.


What I have is a worksheet with data arranged in several columns (but I’ve not established it as a table – would this be easier if I did?), all the cells other than those in the document-number column are protected against selection or editing. The cells in that column permit selection but not editing. What does that do to your suggested solution?


Very respectfully,
an apologetic Larry

[NoSparks]

No problem.

You can use a standard range in the Worksheet_SelectionChange macro by restricting things to specific column and rows,
for example column E and rows 4 to 20.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'limit to single cell selection
If Target.Count > 1 Then Exit Sub
'limit to specific column
If Target.Column <> 5 Then Exit Sub
'limit to specific rows
If Target.Row < 4 Or Target.Row > 20 Then Exit Sub

    ' do what's required, example
    MsgBox "The sheet address of the cell you just entered is " & Target.Address
    '
    
End Sub

[Larry_1]

Thank you, NoSparks!

I’d thought that selection change as a worksheet function and I was looking for a cell function . . . so skipped over it. Following your suggestion, I’ve now got the selection change working properly: When I select a cell it assigns the cell’s value to a variable. That’s a great step forward.

I've now concatenated the value with the Word extension to get the file that I want to open:
Dim CellContents as string
CellContents = Target.Value
DocumentID = CellContents & “.docx”

If, as in my example, the cell contains “Gardening-0017”, that concatenation would yield “Gardening-0017.docx”.



What Excel command do I look at to open that Word document?

Very respectfully,
Larry

[NoSparks]

Don't know, never done that, but Google is your friend.

Try a search for "Open word document from Excel VBA"
or "VBA in Excel to open Word document"
or something similar

[Larry_1]

Thank you, NoSparks!

Long, Long ago in the ancient archives of time, I supervised several new hires that needed to learn Lotus 123. I told them that their first task was to read the manual's index in the morning and again in the afternoon before they left for the day. Same the 2nd day. They only had to do it once in the morning of the 3rd day and the rest of the week. They didn't think that taught teach them anything, but it made them familiar with the terminology that Lotus used to describe the software. That familiarity made it much easier for them to then look up information that they needed to answer their "how do I" questions.

One of the main reasons that I so dislike the, now established, trend away from manuals is that I no longer have an assembled list of important terms that the vendor uses. Oh man, am I an old fart.

Very respectfully,
Larry