0

I am trying to use a MsgBox upon selection of a cell. I need different message box's to appear depending on which cell is selected, so I'm trying to use and IF statement. Something is going wrong and I don't know what it is. Here is the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.Calculate

Dim ws1 As Worksheet, ws2 As Worksheet
Dim rngPH1, rngPH2, rngPH3, rngPH4, rngPH5, rngPH6, rngPH7, rngPH8 As Range

Set ws1 = Worksheets("Budget Hours")
Set ws2 = Worksheets("Schedule")

Set rngPH1 = ws2.Range("E7:E27")
Set rngPH2 = ws2.Range("E43:E63")
Set rngPH3 = ws2.Range("E79:E99")
Set rngPH4 = ws2.Range("E115:E135")
Set rngPH5 = ws2.Range("E151:E171")
Set rngPH6 = ws2.Range("E187:E207")
Set rngPH7 = ws2.Range("E222:E242")
Set rngPH8 = ws2.Range("E259:E279")

If Target.Address = rngPH1 Then
    Dim rng1 As Range, rng2 As Range, msg1, i1 As Long
    
    Set rng1 = ws1.Range("E6:E10")
    Set rng2 = rng1.Offset(0, Target.Row - 6)

    msg1 = rng2.EntireColumn.Cells(3).Value & vbNewLine
    For i1 = 1 To rng1.Cells.Count
        msg1 = msg1 & vbNewLine & rng1.Cells(i).Value & " - " & rng2.Cells(i).Value & " Hours"
    Next i1

MsgBox msg1, , ws1.Range("E5").Value

ElseIf Target.Address = rngPH2 Then
    Dim rng3 As Range, rng4 As Range, msg2, i2 As Long
    
    Set rng3 = ws1.Range("E15:E19")
    Set rng4 = rng3.Offset(0, Target.Row - 42)

    msg2 = rng4.EntireColumn.Cells(3).Value & vbNewLine
    For i2 = 1 To rng3.Cells.Count
        msg2 = msg2 & vbNewLine & rng3.Cells(i).Value & " - " & rng4.Cells(i).Value & " Hours"
    Next i2

MsgBox msg2, , ws1.Range("E14").Value

End If
End Sub

I get a "Type mismatch" error at the first IF statement. Why is that? What can I do to get around this?

Improve this question
2
  • Target.Address = rngPH1.Address? But normally you use Intersect to do this. Commented Jul 9, 2020 at 20:07
  • @BigBen I didn't get an error but it didn't do anything when selecting a cell in either rngPH1 or rngPH2. I know the code inside the IF statement works because I ran it with slightly different parameters before. Do you know what could be happening such that it isn't working? Thanks in advance. Commented Jul 9, 2020 at 20:11

2 Answers 2

Reset to default
1

If Target.Address = rngPH1

The Type Mismatch is because the left-hand side is a String and the right hand side is equivalent to rngPH1.Value, which is a 2D Variant array.

The normal approach is to use Intersect:

If Not Intersect(Target, rngPH1) Is Nothing Then
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

The answer to your question has been provided by @BigBen.

However, your code is full of patterns that you should think about using. Using available patterns in the flow of the code will greatly reduce your code lines and help you make your code more dynamic and managable. I suspect in the near future you will carry on adding "if" statements to rngPH3 through rngPH8. I used the patterns in your code and reduced it to the following:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.Calculate
    
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim rngPH As Range, rng1 As Range, rng2 As Range
    Dim msg As String, i As Long, j As Long
    
    Set ws1 = Worksheets("Budget Hours")
    Set ws2 = Worksheets("Schedule")
    
    For i = 1 To 8
    
        Set rngPH = ws2.Range("E" & 7 + (i - 1) * 36).Resize(21, 1)
        If Not Intersect(rngPH, Target) Is Nothing Then
            
            Set rng1 = rngPH.Cells(1, 1).Offset(-1, 0).Resize(5, 1)
            Set rng2 = rng1.Offset(0, Target.Row - rng1.Cells(1, 1).Row)
            
            msg = rng2.EntireColumn.Cells(3).Value & vbNewLine
            
            For j = 1 To rng1.Cells.Count
                msg = msg & vbNewLine & rng1.Cells(j).Value & " - " & rng2.Cells(j).Value & " Hours"
            Next j

            MsgBox msg, , rng1.Cells(1, 1).Offset(-1, 0).Value

            Exit For
        End If
    
    Next i
    
End Sub

Important notes:

  • This logic will only work if and only if rngPH7 in your original code should actually be set to ws2.Range("E223:E243"). If it doesn't you can simply add an empty row somewhere above to make it follow the patter.

  • This code has NOT been tested and possible bugs and errors may or may not exist. My only aim here is to show you the effort and time you will save in the long run if you spend a few extra minutes (even hours in some cases) in the beginning to analyse your problem and work out any patterns.

All the best

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.