Part 10 to: Import more data into spreadsheet
Continuing on from: https://www.experts-exchange.com/questions/28159139/Part-9-to-Import-more-data-into-spreadsheet.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\S
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
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\S
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
ASKER
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!
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!
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
ASKER
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!
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!
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)
5) SAVE the workbook.
6) Doubleclick on Module1 and after any End sub Paste the below code:
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
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 = True5) 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 Sub7) 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
Did you hv a chance to try it ?
gowflow
gowflow
ASKER
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 the
attached.
The location of this file is actually here:
2. there is another problem as well, but let's deal with this one first
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 the
attached.The location of this file is actually here:
2. there is another problem as well, but let's deal with this one first
ASKER
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.
For the location of the proxy file:
It seems that you made a mistake and it should be:
C:\Users\Michael\Dropbox\S
??? 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\S
Maybe you changed it as well.
check and let me know so I post the changes.
gowflow
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\S
??? 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\S
Maybe you changed it as well.
check and let me know so I post the changes.
gowflow
ASKER
1. Yes, my mistake. I'm sorry. The correct path to Proxy_Numbers.xls is:
C:\Users\Michael\Dropbox\S
2. The path is correct as I have it too.
C:\Users\Michael\Dropbox\S
2. The path is correct as I have it too.
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
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
8) SAVE and Exit the workbook
9) open it and try it.
gowflow
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 Function4) 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 = True8) SAVE and Exit the workbook
9) open it and try it.
gowflow
ASKER
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.
You never mentioned anything about value in Q and Value in K !!!
No K in the whole question !!!! where did you get this from ? or I am mistaken ??
gowflow
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
ASKER
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.
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.
it is working perfectly here.
I only hope it is not a 2010 issue !!!
gowflow
I only hope it is not a 2010 issue !!!
gowflow
ASKER
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
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
ASKER
hold on, I think it worked. have to double check later
let me know. I am not touching the code before I get your feedback as here it is working fine.
gowflow
gowflow
Any news ?
gowflow
gowflow
ASKER
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.
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
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
ASKER
I agree. And I do that, just wanted to make sure it wasn't an easy fix.
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
go safe and enforce the rule !
good luck
gowflow
ASKER
still testing this, gowflow
ASKER
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
https://www.experts-exchange.com/questions/28157691/Part-8-to-Import-more-data-into-spreadsheet.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.
https://www.experts-exchange.com/questions/28157691/Part-8-to-Import-more-data-into-spreadsheet.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.
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
gowflow
ASKER
sorry gowflow, but could you point me to the code? doing a search on that number brings me back to this page.
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
change this line
MaxRow = WS.UsedRange.Rows.Count + 1
to be
MaxRow = WS.UsedRange.Rows.Count
gowflow
ASKER
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?
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?
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
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
ASKER
just checked it again. the proxy number is put in by the code and is going in as scientific notation
ASKER
and hitting f2 shows the full number in the cell but then reverts back to scientific notation
Let me look at this. Should revert in 2+ duty time
:)
gowflow
:)
gowflow
ASKER
this is to the system that says I need to pay attention to this question: I am!
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
save and close the workbook. Open it and try it.
gowflow
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 = Truesave and close the workbook. Open it and try it.
gowflow
Any chance to hv tried it out ?
gowflow
gowflow
ASKER
tried it but got error when I put a card number into Col T.
Ooops
replace this
WS.Range("W" & Target.Row).NumberFormat = "@"
by this
Range("W" & Target.Row).NumberFormat = "@"
gowflow
replace this
WS.Range("W" & Target.Row).NumberFormat = "@"
by this
Range("W" & Target.Row).NumberFormat = "@"
gowflow
Any news ?
gowflow
gowflow
ASKER
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.
ASKER
It seems to work, but the function also kicks in when I delete a value from Col Q.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent! Thank you, Gowflow.
I have another if ready and willing
I have another if ready and willing
yes go ahead
gowflow
gowflow
ASKER
Here is is gowflow. Thank you!
https://www.experts-exchange.com/questions/28180801/Part-11-to-Import-more-data-into-spreadsheet.html
https://www.experts-exchange.com/questions/28180801/Part-11-to-Import-more-data-into-spreadsheet.html
gowflow