Part 10 to: Import more data into spreadsheet

Continuing on from: http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28159139.html

Now need the following function:

WHEN a card number  beginning with 5116 is inserted in Col T of Applicant Status AND when I insert a value into Col Q (Approved - note: its value turns to a date when I put an x in that column) do the following:

1. Locate the 5116 full card number (from Applicant Status.xls - Col T) in the spreadsheet Proxy_Numbers.xls Col C (Cardnumber - path: C:\Users\Michael\Dropbox\Sovereign)

2. Copy the Proxy Number from that spreadsheet (from Col B - Proxy_Numbers.xls )

3. Create a email, addressing it to the email addresses (plural if more than one) found in Col D of Applicant Status.xls

4. Subject: "Proxy Number for Sovereign USD Mastercard"

5.  Body (without quotes):

"Dear {first name from Col A, Applicant Status.xls}

Your card must be activated first before you can access your online account.
To activate it I must first tell the bank to do so  which I have just done.  However,
it often takes them till the end of the day to activate your account.

If after you follow the below instructions and you still can't access your account,
please try again later in the day.

Below are the instructions for setting up your online Mastercard bank account.

Please click on the following link: https://www.pcbmyaccount.com/index.cfm.

When there for the first time please click 'Sign Up' for establishing your online account management. You will then be taken to a screen and asked for a 4 digit number.

First try 4524. If that doesn’t work use the last 4 telephone digits you provided.

Then enter your proxy number which is: {enter proxy number from Col B, Proxy_number.xls here}

You are then ready to choose your username, password and security questions.

If you have any problems setting up your online account please feel free to contact me.

In a subsequent email I will send you card loading instructions.


Michael
Sovereign Goldcard Support Services"

6. After the sentence above where it says: "Then enter your proxy number which is:"
enter the Proxy Number from Col B, Proxy_Numbers.xls

Scrubbed example of Proxy Numbers attached.

Scrubbed-Proxy-Numbers.xls
JaseStAsked:
Who is Participating?
 
gowflowConnect With a Mentor Commented:
ok this should fix the blank cell issue or not to activate when you delete something in Col Q.

replace the code in worksheet_Change event of sheet1 by the below code. Paste the below code after this line
Private Sub Worksheet_Change(ByVal Target As Range)


Dim cCell As Range
Dim fName As String

'---> disable all events while in this procedure to prevent from looping
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False

'---> Update Date in Col O,P,Q,R,S when x is inputed.
For Each cCell In Target
    If (Not Intersect(cCell, Columns("O")) Is Nothing Or _
        Not Intersect(cCell, Columns("P")) Is Nothing Or _
        Not Intersect(cCell, Columns("Q")) Is Nothing Or _
        Not Intersect(cCell, Columns("R")) Is Nothing Or _
        Not Intersect(cCell, Columns("S")) Is Nothing) _
        And LCase(cCell.Value) = "x" Then
        cCell = Format(Now, "mm/dd/yyyy")
    End If
Next cCell


'---> Send Email if Cell in Col N has a value and Cell in Col K
If Not Intersect(Target, Columns("N")) Is Nothing Or Not Intersect(Target, Columns("K")) Is Nothing Then
    If Range("N" & Target.Row) <> "" And _
        Range("K" & Target.Row) <> "" Then
        If MsgBox("Send Mail for " & Cells(Target.Row, "C") & ", " & Cells(Target.Row, "B") & " ?", vbQuestion + vbYesNo, "Send Email") = vbYes Then
            fName = CreateNewCardLoad(Range(Cells(Target.Row, "A"), Cells(Target.Row, "T")))
            SendEmail Range(Cells(Target.Row, "A"), Cells(Target.Row, "T")), fName
            Exit Sub
        End If
    End If

Else
    
    If Not Intersect(Target, Columns("Q")) Is Nothing And InStr(1, Cells(Target.Row, "T"), "5116") <> 0 And Range("Q" & Target.Row) <> "" Then
        Range("W" & Target.Row).NumberFormat = "@"
        Cells(Target.Row, "W") = GetProxy(Cells(Target.Row, "T"))
        If Cells(Target.Row, "W") <> "" Then
            SendActivationEmail Range(Cells(Target.Row, "A"), Cells(Target.Row, "T"))
        End If
    Else
        If Not Intersect(Target, Columns("Q")) Is Nothing And InStr(1, Cells(Target.Row, "T"), "5116") = 0 And Cells(Target.Row, "T") <> "Not Yet Assigned" And Range("Q" & Target.Row) <> "" Then
            MsgBox ("It seems that the credit card number entered " & Cells(Target.Row, "T") & " does not start with '5116' ")
        End If
    End If
    
End If

'---> Re-activate all events prior exit
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True

Open in new window


gowflow
0
 
gowflowCommented:
Disn't you forgot to mention to save the proxy in Col W ??
gowflow
0
 
JaseStAuthor Commented:
Oops! You're beating me at my own game!! You're right. Thanks
for noticing. Yes, please do that.

Now, just to have you be prepared, I'm going to ask for a similar function so that when I put in a value into the column Activated (Col R) it will initiate another email. But for now, yes please import that Proxy number into Col W. And if it can't find the proxy number (because the card number is wrong) then do what? I guess bring up an error message and allow me to find the proxy number and insert it manually.

Thanks gowflow!
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
gowflowCommented:

column Activated (Col R) it will initiate another email


Thought so when I read the email content Iguessed that there was an other one coming down the line.

No problem



And if it can't find the proxy number (because the card number is wrong) then do what? I guess bring up an error message and allow me to find the proxy number and insert it manually.




Well here you have 2 scenarios:
1) the card is Not yet Assigned ...> then presume do nothing
2) there is a card and it start by 5116 then prefer to just state Proxy not found pls check file so and so. As if we keep the file opened and then you need to update it then it will be a mess in the macro. So when you update the file and run it again it should find it.

Pls confirm ok
gowflow
0
 
JaseStAuthor Commented:
1.  I suppose that is okay. However on the other hand, it may be true that I get notification from the bank the card is approved before I know what the card number is. And I would then put a value under Approved (Col Q). When the customer gets the card they then inform me the card number.

So I guess the best scenario is that initiate the function as long as both parameters are met:
a card number beginning with 5116 in Col T AND a value inserted into Col Q. If both those parameters are not met then give me some notification.

2. Yes, if the card number is wrong and proxy number can't be found, yes just state the proxy number cannot be found. And Yes, when I input the correct number the function runs and finds the proxy number, etc.

Thank you!
0
 
gowflowCommented:
ok here it is it took some intensive testing as we have several options here.

1) Make a copy of your latest file and give it a new name
2) Goto VBA and doublclick on Sheet1 and locate the Sub Worksheet_Change event and delete all the code that is there.
3) Select again this event and it should be like this
=========
Private Sub Worksheet_Change(ByVal Target As Range)

End Sub
=========

4) Paste the below code after
Private Sub Worksheet_Change(ByVal Target As Range)


Dim cCell As Range
Dim fName As String

'---> disable all events while in this procedure to prevent from looping
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False

'---> Update Date in Col O,P,Q,R,S when x is inputed.
For Each cCell In Target
    If (Not Intersect(cCell, Columns("O")) Is Nothing Or _
        Not Intersect(cCell, Columns("P")) Is Nothing Or _
        Not Intersect(cCell, Columns("Q")) Is Nothing Or _
        Not Intersect(cCell, Columns("R")) Is Nothing Or _
        Not Intersect(cCell, Columns("S")) Is Nothing) _
        And LCase(cCell.Value) = "x" Then
        cCell = Format(Now, "mm/dd/yyyy")
    End If
Next cCell


'---> Send Email if Cell in Col N has a value and Cell in Col K
If Not Intersect(Target, Columns("N")) Is Nothing Or Not Intersect(Target, Columns("K")) Is Nothing Then
    If Range("N" & Target.Row) <> "" And _
        Range("K" & Target.Row) <> "" Then
        If MsgBox("Send Mail for " & Cells(Target.Row, "C") & ", " & Cells(Target.Row, "B") & " ?", vbQuestion + vbYesNo, "Send Email") = vbYes Then
            fName = CreateNewCardLoad(Range(Cells(Target.Row, "A"), Cells(Target.Row, "T")))
            SendEmail Range(Cells(Target.Row, "A"), Cells(Target.Row, "T")), fName
            Exit Sub
        End If
    End If

Else
    
    If Not Intersect(Target, Columns("Q")) Is Nothing And InStr(1, Cells(Target.Row, "T"), "5116") <> 0 Then
        Cells(Target.Row, "W") = GetProxy(Cells(Target.Row, "T"))
        If Cells(Target.Row, "W") <> "" Then
            SendActivationEmail Range(Cells(Target.Row, "A"), Cells(Target.Row, "T"))
        End If
    Else
        If Not Intersect(Target, Columns("Q")) Is Nothing And InStr(1, Cells(Target.Row, "T"), "5116") = 0 And Cells(Target.Row, "T") <> "Not Yet Assigned" Then
            MsgBox ("It seems that the credit card number entered " & Cells(Target.Row, "T") & " does not start with '5116' ")
        End If
    End If
    
End If

'---> Re-activate all events prior exit
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True

Open in new window


5) SAVE the workbook.
6) Doubleclick on Module1 and after any End sub Paste the below code:

Function GetProxy(CC As String) As String
Dim sProxyFile As String
Dim WS As Worksheet
Dim WB As Workbook
Dim cCell As Range

sProxyFile = "C:\Users\Michael\Dropbox\Sovereign\Proxy-Numbers.xls"
'sProxyFile = ActiveWorkbook.Path & "\Proxy-Numbers.xls"

Set WB = Workbooks.Open(Filename:=sProxyFile)
Set WS = ActiveSheet

Set cCell = WS.UsedRange.Find(what:=CC, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not cCell Is Nothing Then
    GetProxy = cCell.Offset(, -1)
Else
    MsgBox ("This Card Number " & CC & " was not found in Proxy-Numbers.xls")
End If

WB.Close savechanges:=False

'---> Clean Variables
Set WB = Nothing
Set WS = Nothing

DoEvents

End Function



Sub SendActivationEmail(Rng As Range)
Dim SendTo As String
Dim sTo
Dim OutlookApp As Object
Dim MItem As Object
Dim subject_ As String
Dim attach_ As String
Dim omail As Outlook.MailItem


'---> Create Outlook
Set OutlookApp = CreateObject("Outlook.Application")

'---> Fill in Subject Details'
subject_ = "Proxy Number for Sovereign USD Mastercard" ' (" & Rng.Cells(1, "B") & " " & Rng.Cells(1, "C") & ")"

'---> Fill in email addresses
sTo = Split(Rng.Cells(1, "D"), "/")

For I = 0 To UBound(sTo)
    If SendTo <> "" Then SendTo = SendTo & "; "
    SendTo = SendTo & LTrim(RTrim(sTo(I)))
Next I


'---> Create the Email
Set MItem = OutlookApp.CreateItem(0)
With MItem
  .To = SendTo
  .Subject = subject_
  
  .Body = "Dear " & Rng.Cells(1, "B") & Chr(10) & Chr(10) _
    & "Your card must be activated first before you can access your online account." & Chr(10) _
    & "To activate it I must first tell the bank to do so  which I have just done.  However," & Chr(10) _
    & "it often takes them till the end of the day to activate your account." & Chr(10) & Chr(10) _
    & "If after you follow the below instructions and you still can't access your account," & Chr(10) _
    & "please try again later in the day." & Chr(10) & Chr(10) _
    & "Below are the instructions for setting up your online Mastercard bank account." & Chr(10) & Chr(10) _
    & "Please click on the following link: https://www.pcbmyaccount.com/index.cfm." & Chr(10) & Chr(10) _
    & "When there for the first time please click 'Sign Up' for establishing your online account" & Chr(10) _
    & "management. You will then be taken to a screen and asked for a 4 digit number. " & Chr(10) & Chr(10) _
    & "First try 4524. If that doesn’t work use the last 4 telephone digits you provided. " & Chr(10) & Chr(10) _
    & "Then enter your proxy number which is: " & Rng.Cells(1, "W") & Chr(10) & Chr(10) _
    & "You are then ready to choose your username, password and security questions." & Chr(10) & Chr(10) _
    & "If you have any problems setting up your online account please feel free to contact me." & Chr(10) & Chr(10) _
    & "In a subsequent email I will send you card loading instructions." & Chr(10) & Chr(10) _
    & "Michael" & Chr(10) _
    & "Sovereign Gold Card Support Services"
  'Send the Email
  .Display
End With

'Clear Resources
Set MItem = Nothing
Set OutlookApp = Nothing

End Sub

Open in new window


7) SAVE and Exit the workbook.
8) try it with situation where there is a CC that is in proxy file, CC that is not there, a CC that does not start with 5116 and CC that Not Yet Assigned.

Let me know
gowflow
0
 
gowflowCommented:
Did you hv a chance to try it ?
gowflow
0
 
JaseStAuthor Commented:
well, a few other problems popped up:

1. when putting an x into Col Q it pops up a Run-time error 1004. It seems it's looking for proxy_numbers.xls in the wrong location. See the1004 attached.

The location of this file is actually here:location
2. there is another problem as well, but let's deal with this one first
0
 
JaseStAuthor Commented:
Also Part 8 to: Import more data into spreadsheet function is not working. Putting a value into Col N with a value in K does nothing.
0
 
gowflowCommented:
For the location of the proxy file:


1. Locate the 5116 full card number (from Applicant Status.xls - Col T) in the spreadsheet Proxy_Numbers.xls Col C (Cardnumber - path: C:\Users\Michael\Dropbox\Sovereign)

It seems that you made a mistake and it should be:
C:\Users\Michael\Dropbox\Sovereign\MasterCard

??? Please confirm.


2. There is no problem for this function on my side maybe you changed the file name it should be:
Blank - New Card Load.xls  (not underscore here like proxy but hyphen)

and the path is:
C:\Users\Michael\Dropbox\Sovereign\MasterCard\MasterCardLoadRequests

Maybe you changed it as well.

check and let me know so I post the changes.
gowflow
0
 
JaseStAuthor Commented:
1. Yes, my mistake. I'm sorry.  The correct path to Proxy_Numbers.xls is:
C:\Users\Michael\Dropbox\Sovereign\MasterCard

2.  The path is correct as I have it too.blank
0
 
gowflowCommented:
ok then lets try to refresh your code maybe it got ruined with the different versions

1) Make a copy of your latest file and give it a new name
2) Open VBA doubleclick on module1 and delete ALL the code that is there.
3) Paste the below code in Module1

Sub SendActivationEmail(Rng As Range)
Dim SendTo As String
Dim sTo
Dim OutlookApp As Object
Dim MItem As Object
Dim subject_ As String
Dim attach_ As String
Dim omail As Outlook.MailItem


'---> Create Outlook
Set OutlookApp = CreateObject("Outlook.Application")

'---> Fill in Subject Details'
subject_ = "Proxy Number for Sovereign USD Mastercard" ' (" & Rng.Cells(1, "B") & " " & Rng.Cells(1, "C") & ")"

'---> Fill in email addresses
sTo = Split(Rng.Cells(1, "D"), "/")

For I = 0 To UBound(sTo)
    If SendTo <> "" Then SendTo = SendTo & "; "
    SendTo = SendTo & LTrim(RTrim(sTo(I)))
Next I


'---> Create the Email
Set MItem = OutlookApp.CreateItem(0)
With MItem
  .To = SendTo
  .Subject = subject_
  
  .Body = "Dear " & Rng.Cells(1, "B") & Chr(10) & Chr(10) _
    & "Your card must be activated first before you can access your online account." & Chr(10) _
    & "To activate it I must first tell the bank to do so  which I have just done.  However," & Chr(10) _
    & "it often takes them till the end of the day to activate your account." & Chr(10) & Chr(10) _
    & "If after you follow the below instructions and you still can't access your account," & Chr(10) _
    & "please try again later in the day." & Chr(10) & Chr(10) _
    & "Below are the instructions for setting up your online Mastercard bank account." & Chr(10) & Chr(10) _
    & "Please click on the following link: https://www.pcbmyaccount.com/index.cfm." & Chr(10) & Chr(10) _
    & "When there for the first time please click 'Sign Up' for establishing your online account" & Chr(10) _
    & "management. You will then be taken to a screen and asked for a 4 digit number. " & Chr(10) & Chr(10) _
    & "First try 4524. If that doesn’t work use the last 4 telephone digits you provided. " & Chr(10) & Chr(10) _
    & "Then enter your proxy number which is: " & Rng.Cells(1, "W") & Chr(10) & Chr(10) _
    & "You are then ready to choose your username, password and security questions." & Chr(10) & Chr(10) _
    & "If you have any problems setting up your online account please feel free to contact me." & Chr(10) & Chr(10) _
    & "In a subsequent email I will send you card loading instructions." & Chr(10) & Chr(10) _
    & "Michael" & Chr(10) _
    & "Sovereign Gold Card Support Services"
  'Send the Email
  .Display
End With

'Clear Resources
Set MItem = Nothing
Set OutlookApp = Nothing

End Sub

Sub SendEmail(Rng As Range, fName As String)
Dim WB As Workbook
Dim WS As Worksheet


Dim SendTo As String
Dim Blindcc As String
Dim OutlookApp As Object
Dim MItem As Object
Dim subject_ As String
Dim attach_ As String
Dim fFile
Dim omail As Outlook.MailItem

Application.DisplayAlerts = False

'Create Outlook
Set OutlookApp = CreateObject("Outlook.Application")

'Fill in Subject Details'
subject_ = "New Mastercard Application for (" & Rng.Cells(1, "B") & " " & Rng.Cells(1, "C") & ")"
SendTo = "nmai@banking.bz"
Blindcc = "david@offshorelawcenter.com"

'Create the Email
Set MItem = OutlookApp.CreateItem(0)
With MItem
  .To = SendTo
  .BCC = Blindcc
  .Subject = subject_
  
  '---> Attach files
  For Each fFile In Rng.SpecialCells(xlCellTypeFormulas)
        If InStr(1, fFile.Formula, "HYPERLINK") <> 0 Then
            fpos = InStr(1, fFile.Formula, "HYPERLINK") + 11
            attach_ = Mid(fFile.Formula, fpos, InStr(fpos, fFile.Formula, Chr(34)) - fpos)
            .Attachments.Add (attach_)
        End If
  Next fFile
  
  .Attachments.Add (fName)
  .Body = "Hi Nalleli," & Chr(10) & Chr(10) _
    & "Attached are the documents and load request for (" & Rng.Cells(1, "B") & " " & Rng.Cells(1, "C") & ")" & Chr(10) _
    & "Please have his card shipped to address indicated on spreadsheet." & Chr(10) _
    & "PIC:99554Freedom" & Chr(10) & Chr(10) _
    & "Please let me know you received this email." & Chr(10) & Chr(10) _
    & "Thank you." & Chr(10) & Chr(10) _
    & "Michael" & Chr(10) _
    & "Sovereign Gold Card Support" & Chr(10) _
    & "www.sovereigngoldcard.com"

  'Send the Email
  .Display
End With

'Clear Resources
Set MItem = Nothing
Set OutlookApp = Nothing

Application.DisplayAlerts = True
End Sub


Function CreateNewCardLoad(Rng As Range) As String
Dim sPathName As String
Dim sFileName As String
Dim sBlankCardLoad As String
Dim MaxRow As Long
Dim WS As Worksheet
Dim WB As Workbook

sBlankCardLoad = "C:\Users\Michael\Dropbox\Sovereign\MasterCard\MasterCardLoadRequests\Blank - New Card Load.xls"
sPathName = "C:\Users\Michael\Dropbox\Sovereign\MasterCard\MasterCardLoadRequests\"

'sBlankCardLoad = ActiveWorkbook.Path & "\Blank - New Card Load.xls"
'sPathName = ActiveWorkbook.Path & "\"

Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False

Set WB = Workbooks.Open(Filename:=sBlankCardLoad)
Set WS = ActiveSheet
MaxRow = WS.UsedRange.Rows.Count + 1

sFileName = sPathName & Rng.Cells(1, "B") & " " & Rng.Cells(1, "C") & " - New Card Load.xls"

'---> Affect Values to Card Load.xls
WS.Range("D" & MaxRow) = Rng.Cells(1, "T")
WS.Range("H" & MaxRow) = Rng.Cells(1, "B")
WS.Range("I" & MaxRow) = Rng.Cells(1, "C")
WB.SaveAs Filename:=sFileName
CreateNewCardLoad = sFileName
WB.Close savechanges:=True

'---> Clean Variables
Set WB = Nothing
Set WS = Nothing

Application.EnableEvents = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
DoEvents


End Function

Function GetProxy(CC As String) As String
Dim sProxyFile As String
Dim WS As Worksheet
Dim WB As Workbook
Dim cCell As Range

sProxyFile = "C:\Users\Michael\Dropbox\Sovereign\MasterCard\Proxy_Numbers.xls"
'sProxyFile = ActiveWorkbook.Path & "\Proxy_Numbers.xls"

Set WB = Workbooks.Open(Filename:=sProxyFile)
Set WS = ActiveSheet

Set cCell = WS.UsedRange.Find(what:=CC, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False)
If Not cCell Is Nothing Then
    GetProxy = cCell.Offset(, -1)
Else
    MsgBox ("This Card Number " & CC & " was not found in Proxy_Numbers.xls")
End If

WB.Close savechanges:=False

'---> Clean Variables
Set WB = Nothing
Set WS = Nothing

DoEvents

End Function



Function ProcessCC(CC As String) As String
   
Dim ILength As Integer, I As Integer
Dim CCFormat As String
   

ILength = Len(CC)
For I = 1 To ILength
    If IsNumeric(Mid(CC, I, 1)) And Mid(CC, I, 1) <> " " Then
        CCFormat = CCFormat & Mid(CC, I, 1)
    End If
Next I

ProcessCC = CCFormat

End Function

Open in new window


4) SAVE the workbook.
5) doubleclick on Sheet1 and select to view 1 sub at a time by clicking on the button left icon
6) Select Worksheet_change event and delete all the code that is there
7) Select again worksheet change and paste the below code after the header Worksheet_change

Dim cCell As Range
Dim fName As String

'---> disable all events while in this procedure to prevent from looping
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False

'---> Update Date in Col O,P,Q,R,S when x is inputed.
For Each cCell In Target
    If (Not Intersect(cCell, Columns("O")) Is Nothing Or _
        Not Intersect(cCell, Columns("P")) Is Nothing Or _
        Not Intersect(cCell, Columns("Q")) Is Nothing Or _
        Not Intersect(cCell, Columns("R")) Is Nothing Or _
        Not Intersect(cCell, Columns("S")) Is Nothing) _
        And LCase(cCell.Value) = "x" Then
        cCell = Format(Now, "mm/dd/yyyy")
    End If
Next cCell


'---> Send Email if Cell in Col N has a value and Cell in Col K
If Not Intersect(Target, Columns("N")) Is Nothing Or Not Intersect(Target, Columns("K")) Is Nothing Then
    If Range("N" & Target.Row) <> "" And _
        Range("K" & Target.Row) <> "" Then
        If MsgBox("Send Mail for " & Cells(Target.Row, "C") & ", " & Cells(Target.Row, "B") & " ?", vbQuestion + vbYesNo, "Send Email") = vbYes Then
            fName = CreateNewCardLoad(Range(Cells(Target.Row, "A"), Cells(Target.Row, "T")))
            SendEmail Range(Cells(Target.Row, "A"), Cells(Target.Row, "T")), fName
            Exit Sub
        End If
    End If

Else
    
    If Not Intersect(Target, Columns("Q")) Is Nothing And InStr(1, Cells(Target.Row, "T"), "5116") <> 0 Then
        Cells(Target.Row, "W") = GetProxy(Cells(Target.Row, "T"))
        If Cells(Target.Row, "W") <> "" Then
            SendActivationEmail Range(Cells(Target.Row, "A"), Cells(Target.Row, "T"))
        End If
    Else
        If Not Intersect(Target, Columns("Q")) Is Nothing And InStr(1, Cells(Target.Row, "T"), "5116") = 0 And Cells(Target.Row, "T") <> "Not Yet Assigned" Then
            MsgBox ("It seems that the credit card number entered " & Cells(Target.Row, "T") & " does not start with '5116' ")
        End If
    End If
    
End If

'---> Re-activate all events prior exit
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True

Open in new window


8) SAVE and Exit the workbook
9) open it and try it.

gowflow
0
 
JaseStAuthor Commented:
ok, now I don't get an error, but it doesn't do anything when I put a value in Q with a value in K.
0
 
gowflowCommented:
You never mentioned anything about value in Q and Value in K !!!


WHEN a card number  beginning with 5116 is inserted in Col T of Applicant Status AND when I insert a value into Col Q (Approved - note: its value turns to a date when I put an x in that column) do the following:

No K in the whole question !!!! where did you get this from ? or I am mistaken ??
gowflow
0
 
JaseStAuthor Commented:
Ok, you're right. Tthe value in Col K corresponds to the 5116 number in col T. The 5116 number means that there is a value in Col K.

In any case when I do put a Value in Col Q, nothing happens even though I do have a 5116 number it T and a Value in K.

Let me try reinserting your code and try again.
0
 
gowflowCommented:
it is working perfectly here.

I only hope it is not a 2010 issue !!!
gowflow
0
 
JaseStAuthor Commented:
re-inserted your code and putting a value in Col Q does nothing.
I've attached a scrubbed version of Applicant Status for you to take a look at if that will help.scrubbed-APPLICANT-STATUS-6-20-1.xls
0
 
JaseStAuthor Commented:
hold on, I think it worked. have to double check later
0
 
gowflowCommented:
let me know. I am not touching the code before I get your feedback as here it is working fine.
gowflow
0
 
gowflowCommented:
Any news ?
gowflow
0
 
JaseStAuthor Commented:
not sure where to start - it's not doing some things it did before. Not capturing email address or card number when there is no column headers. Got an email with such an xls spreadsheet.
0
 
gowflowCommented:
can't do miracles !!!
No header = no retrieval

you need to decide on a system if you want automation. You got to get your clients to 'behave'

My suggestion don't fool around or try to twist code to cater for all scenarios, simply stick to your format and go back to client saying: Sorry fill this form and send it back our system only accept THIS FILE !!!

:)
gowflow
0
 
JaseStAuthor Commented:
I agree. And I do that, just wanted to make sure it wasn't an easy fix.
0
 
gowflowCommented:
no forget it no fix for that as you will never finish when you start with this and it will easily backfire on you as you may get conflicting info.

go safe and enforce the rule !
good luck
gowflow
0
 
JaseStAuthor Commented:
still testing this, gowflow
0
 
gowflowCommented:
ok
0
 
JaseStAuthor Commented:
when creating the first_name last_name - New Card Load.xls file it is putting the card number and the first and last name on row 3 instead of row 2. This is from the function created in

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28157691.html

Row 2 DOES have data in that row but not in Col D and Col H and Col i which is where the card number (Col D), last name (Col H) and first name (Col i) are supposed to go.
0
 
gowflowCommented:
as usual you are messed-up with version in that link I gave you the correction to do in ID: 39249580 check it again.
gowflow
0
 
JaseStAuthor Commented:
sorry gowflow, but could you point me to the code? doing a search on that number brings me back to this page.
0
 
gowflowCommented:
In Module1 there is a Sub Called CreateNewCardLoad
change this line
MaxRow = WS.UsedRange.Rows.Count + 1

to be
MaxRow = WS.UsedRange.Rows.Count


gowflow
0
 
JaseStAuthor Commented:
that was it. thank you!

Still testing the proxy number function and it seems to work perfectly however for Col W where it puts the proxy number it formats it to scientific notation. Can that be fixed in this question or do you want me to submit another?
0
 
gowflowCommented:
well it should not put it as scientific unless you put it manually there then yes it turn it to scientific. If you input manually then just click on the cell press F2 then enter and it should fix the problem for that cell.

If the  macro is writing the value of the card it should write it correctly.

Now that you are testing I presume you are entering the values manually or copy/paste
then F2 Enter should do it.

gowflow
0
 
JaseStAuthor Commented:
just checked it again. the proxy number is put in by the code and is going in as scientific notation
0
 
JaseStAuthor Commented:
and hitting f2 shows the full number in the cell but then reverts back to scientific notation
0
 
gowflowCommented:
Let me look at this. Should revert in 2+ duty time
:)

gowflow
0
 
JaseStAuthor Commented:
this is to the system that says I need to pay attention to this question: I am!
0
 
gowflowCommented:
Sorry totally overlooked this question.

open your file goto vba and doubleclick on sheet1 and display worksheet_change event. Delete all the code that is there. locate worksheet_ change event again and paste below code after the first line Private Worksheet_change

Dim cCell As Range
Dim fName As String

'---> disable all events while in this procedure to prevent from looping
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False

'---> Update Date in Col O,P,Q,R,S when x is inputed.
For Each cCell In Target
    If (Not Intersect(cCell, Columns("O")) Is Nothing Or _
        Not Intersect(cCell, Columns("P")) Is Nothing Or _
        Not Intersect(cCell, Columns("Q")) Is Nothing Or _
        Not Intersect(cCell, Columns("R")) Is Nothing Or _
        Not Intersect(cCell, Columns("S")) Is Nothing) _
        And LCase(cCell.Value) = "x" Then
        cCell = Format(Now, "mm/dd/yyyy")
    End If
Next cCell


'---> Send Email if Cell in Col N has a value and Cell in Col K
If Not Intersect(Target, Columns("N")) Is Nothing Or Not Intersect(Target, Columns("K")) Is Nothing Then
    If Range("N" & Target.Row) <> "" And _
        Range("K" & Target.Row) <> "" Then
        If MsgBox("Send Mail for " & Cells(Target.Row, "C") & ", " & Cells(Target.Row, "B") & " ?", vbQuestion + vbYesNo, "Send Email") = vbYes Then
            fName = CreateNewCardLoad(Range(Cells(Target.Row, "A"), Cells(Target.Row, "T")))
            SendEmail Range(Cells(Target.Row, "A"), Cells(Target.Row, "T")), fName
            Exit Sub
        End If
    End If

Else
    
    If Not Intersect(Target, Columns("Q")) Is Nothing And InStr(1, Cells(Target.Row, "T"), "5116") <> 0 Then
        WS.Range("W" & Target.Row).NumberFormat = "@"
        Cells(Target.Row, "W") = GetProxy(Cells(Target.Row, "T"))
        If Cells(Target.Row, "W") <> "" Then
            SendActivationEmail Range(Cells(Target.Row, "A"), Cells(Target.Row, "T"))
        End If
    Else
        If Not Intersect(Target, Columns("Q")) Is Nothing And InStr(1, Cells(Target.Row, "T"), "5116") = 0 And Cells(Target.Row, "T") <> "Not Yet Assigned" Then
            MsgBox ("It seems that the credit card number entered " & Cells(Target.Row, "T") & " does not start with '5116' ")
        End If
    End If
    
End If

'---> Re-activate all events prior exit
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True

Open in new window


save and close the workbook. Open it and try it.

gowflow
0
 
gowflowCommented:
Any chance to hv tried it out ?
gowflow
0
 
JaseStAuthor Commented:
tried it but got error when I put a card number into Col T.error
0
 
gowflowCommented:
Ooops

replace this
WS.Range("W" & Target.Row).NumberFormat = "@"

by this
Range("W" & Target.Row).NumberFormat = "@"


gowflow
0
 
gowflowCommented:
Any news ?
gowflow
0
 
JaseStAuthor Commented:
sorry, gowflow. 4th of July events and celebrations have kept me from attending this. I'll get back to you in a few minutes after testing it.
0
 
JaseStAuthor Commented:
It seems to work, but the function also kicks in when I delete a value from Col Q.
0
 
JaseStAuthor Commented:
Excellent! Thank you, Gowflow.

I have another if ready and willing
0
 
gowflowCommented:
yes go ahead
gowflow
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.