Variant not appearing in MsgBox?

ciapul12

New Member
Joined
Nov 3, 2013
Messages
13
Hi There,
Could someone take a look at my code and tell me how to fix it please?
It's a simple code that was given to me by one of the forum members and after trying to implement it into my workbook I have come across an issue and have not been able to resolve it since.
Basically I have an input form which users uses to answer all questions and once they're ready to submit their answers they click on save button which then copies the range into the sheet called "Tracker" all of this works smoothly.
What doesn't work is the following:
On input form there are 3 columns called "Warning" "Minor" and "Major", upon selecting one of the cells in those 3 columns the code checks if there are any historical records in the tracker and if match is found it then displays the message to inform user that basically this issues was flagged up in the past and that they should upgrade to next stage.
Currently code fires up every time any of those cells is activated and even when mach is not found in the tracked..
Also the message is missing the piece of info whether a Warning, Minor or Major was found.

610743d1549882511-variant-not-appearing-in-msgbox-capture.jpg


Hopefully I've explained this correctly.
Code:
Option ExplicitPrivate Sub Worksheet_SelectionChange(ByVal Target As Range)


If Target.Column >= 12 And Target.Column <= 14 And Target.Row > 2 Then
'If Target.Column >= 12 And Target.Column <= 14 And Target.Row > 3 And Target.Row < 22 Then
Dim c, ky, st, sh1, sh2 As Variant
Dim j As Integer
Set sh1 = ThisWorkbook.Sheets("Input Form")
Set sh2 = ThisWorkbook.Sheets("Tracker")
ky = sh1.Cells(Target.Row, 4) & "." & sh1.Cells(Target.Row, 6) & "." & sh1.Cells(Target.Row, 5)
Set c = sh2.Range("T:T").Find(ky, LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
  If sh2.Cells(c.Row, "M") = 1 Then st = "Warning"
  If sh2.Cells(c.Row, "N") = 1 Then st = "Minor"
  If sh2.Cells(c.Row, "O") = 1 Then st = "Major"


  MsgBox ("Attention!A match was found with " & st & " in line " & c.Row & "... We strongly recommend to upgrade")
End If
End If
End Sub

611018d1550011920-variant-not-appearing-in-msgbox-capture1.jpg

611019d1550011934-variant-not-appearing-in-msgbox-capture2.jpg

Would appreciate your help.
Thank You
Dan
 
Last edited:

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Dan

I don't see any 1s in columns M, N or O.
 
Upvote 0

Forum statistics

Threads
1,223,214
Messages
6,170,771
Members
452,353
Latest member
strainu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top