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

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Dan

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

Forum statistics

Threads
1,223,889
Messages
6,175,226
Members
452,620
Latest member
dsubash

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