VBA [sheet code] vs macro module?

[dwarnimont]

I have the below code embedded within my sheet. my need is to repeat the same type of process but with several different targets like (C4), and have a different MsgBox for each cell and. Not sure if this is still sheet code or macros?

Also, this code was given to me and the LCase is not helpful. I'd like to remove any logic about case. Thanks for this help. Dave
=======================================================================

Private Sub Worksheet_Change(ByVal Target As Range)
Dim msg As String

msg = "Dave doesn't like to do this work, will you do it?"


If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("c4")) Is Nothing Then
If LCase(Target.Value) = "grind-flat" Then
If MsgBox(msg, vbYesNo) = vbNo Then
MsgBox "This will cost you!"
Else
MsgBox "Good girl..."
End If
End If
End If
End Sub

 

[Scott Huish]

It's still in sheet code. The purpose of the lcase, however is that VBA is case-sensitive when comparing text strings. So if your cell read GRIND-FLAT it wouldn't evaluate as True.

Can you be a little more specific in your requirements? Do you always need a Msgbox?

 

[dwarnimont]

Thanks. I understand Lcase now.

The objective is to interrogate the result from a list drop down data validation and return a message depending on selection. The real question is, how do i create code for several cells, each with a different expected cell result and an unique msgbox for each cell. This example here doesn not work but gives the idea.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim msg As String

msg = "Dave doesn't like to do this work, will you do it?"


If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("c4")) Is Nothing Then
If LCase(Target.Value) = "grind-flat" Then
If MsgBox(msg, vbYesNo) = vbNo Then
MsgBox "This will cost you!"
Else
MsgBox "Good girl..."
End If
End If
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim msg As String

msg = "Dave likes this work"


If Target.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("c10")) Is Nothing Then
If LCase(Target.Value) = "grind-no" Then
If MsgBox(msg, vbYesNo) = vbNo Then
MsgBox "This will cost you!"
Else
MsgBox "Good girl..."
End If
End If
End If
End Sub

 

[Joe4]

You cannot have multiple procedures by the same name within a single module. So, you just need to create separate code blocks for each one, i.e.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim msg As String

    msg = "Dave doesn't like to do this work, will you do it?"

    If Target.CountLarge > 1 Then Exit Sub

'   *** BLOCK1 ***
    If Not Intersect(Target, Range("c4")) Is Nothing Then
        If LCase(Target.Value) = "grind-flat" Then
            If MsgBox(msg, vbYesNo) = vbNo Then
                MsgBox "This will cost you!"
            Else
                MsgBox "Good girl..."
            End If
        End If
    End If
    
'   *** BLOCK 2 ***
    If Not Intersect(Target, Range("c10")) Is Nothing Then
        If LCase(Target.Value) = "grind-no" Then
            If MsgBox(msg, vbYesNo) = vbNo Then
                MsgBox "This will cost you!"
            Else
                MsgBox "Good girl..."
            End If
        End If
    End If
    
End Sub

If there is enough similarity between all the blocks, it may be possible to store the ranges and associated values in an array, and have a single block of code loop through the array.

 

[My Aswer Is This]

Are you saying this Message will change:

msg = "Dave doesn't like to do this work, will you do it?"​

So tell us what causes this message to change.

And can this script just apply to all cells in column C

And what is the relationship between

grind-flat​

and

grind-no​

The script needs to see some sort of logic and association or you will need to have numerous if statements