Need VBA for making a action when clicking on a cell

[bamaisgreat]

I have a application on a spreadsheet and what I would like is when they click on a few specific cells it will run this speech code I found

Sub State()
Application.Speech.Speak "Enter the state you live in!"
End Sub

I will be doing this on the whole application
Example: Name,City,State,race etc.....

 

[Gary McMaster]

Maybe the "selection Change" event?

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Dim sText As String

Select Case Target.Column

    Case 1 'Change to appropriate column numbers
        sText = "Name"
        
    Case 2
        sText = "City"
        
    Case 3
        sText = "State"
        
    Case 4
        sText = "Race"
        
    Case Else
        Exit Sub
        
End Select

Application.Speech.Speak sText

End Sub

 

[bamaisgreat]

Thanks for the reply Gary, Is there away to have the speech code work only when a specific cell is clicked? Example: A5 would say "State", D10 would say "Race"etc...
also could this only apply to a specific sheet instead of the entire workbook.. Thanks again for your help,your getting me in the right direction.

 

[Gary McMaster]

The variable "Target" that is passed to this event is a reference to the selected cell. You can get just about anything you could possibly want to know about the cell from "Target". including the cell address. Something like the sample below would work though it would get very cumbersome if you had a lot of cells to check. That's why I originally had it checking for entire columns.

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Dim sText As String

Select Case Target.Address

    Case "$D$10" 'Change to appropriate cell addresses
        sText = "Name"
        
    Case "$A$2"
        sText = "City"
        
    Case "$B$7"
        sText = "State"
        
    Case "$C$8"
        sText = "Race"
        
    Case Else
        sText = "Unknown selection"
        
End Select

Application.Speech.Speak sText

End Sub

 

[bamaisgreat]

Thanks again Gary thats what I needed. Is there away to have check just a specific sheet ?

 

[Gary McMaster]

You could put the code in the "Selection Change" event for the sheet you are interested in or check the name of the sheet in the "Sh" variable that is passed into the workbook level event (as I posted it)

Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

MsgBox Sh.Name

If Sh.Name = "My Target Sheet" Then

'Do stuff

End If