Solved

Part 10 to: Import more data into spreadsheet

Posted on 2013-06-17
46
225 Views
Last Modified: 2013-07-10
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
0
Comment
Question by:JaseSt
  • 23
  • 23
46 Comments
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Disn't you forgot to mention to save the proxy in Col W ??
gowflow
0
 

Author Comment

by:JaseSt
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility

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
 

Author Comment

by:JaseSt
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Did you hv a chance to try it ?
gowflow
0
 

Author Comment

by:JaseSt
Comment Utility
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
 

Author Comment

by:JaseSt
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 

Author Comment

by:JaseSt
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 

Author Comment

by:JaseSt
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 

Author Comment

by:JaseSt
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
it is working perfectly here.

I only hope it is not a 2010 issue !!!
gowflow
0
 

Author Comment

by:JaseSt
Comment Utility
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
 

Author Comment

by:JaseSt
Comment Utility
hold on, I think it worked. have to double check later
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
let me know. I am not touching the code before I get your feedback as here it is working fine.
gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Any news ?
gowflow
0
 

Author Comment

by:JaseSt
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 

Author Comment

by:JaseSt
Comment Utility
I agree. And I do that, just wanted to make sure it wasn't an easy fix.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:JaseSt
Comment Utility
still testing this, gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok
0
 

Author Comment

by:JaseSt
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 

Author Comment

by:JaseSt
Comment Utility
sorry gowflow, but could you point me to the code? doing a search on that number brings me back to this page.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 

Author Comment

by:JaseSt
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 

Author Comment

by:JaseSt
Comment Utility
just checked it again. the proxy number is put in by the code and is going in as scientific notation
0
 

Author Comment

by:JaseSt
Comment Utility
and hitting f2 shows the full number in the cell but then reverts back to scientific notation
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Let me look at this. Should revert in 2+ duty time
:)

gowflow
0
 

Author Comment

by:JaseSt
Comment Utility
this is to the system that says I need to pay attention to this question: I am!
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Any chance to hv tried it out ?
gowflow
0
 

Author Comment

by:JaseSt
Comment Utility
tried it but got error when I put a card number into Col T.error
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Ooops

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

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


gowflow
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Any news ?
gowflow
0
 

Author Comment

by:JaseSt
Comment Utility
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
 

Author Comment

by:JaseSt
Comment Utility
It seems to work, but the function also kicks in when I delete a value from Col Q.
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
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
 

Author Closing Comment

by:JaseSt
Comment Utility
Excellent! Thank you, Gowflow.

I have another if ready and willing
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
yes go ahead
gowflow
0
 

Author Comment

by:JaseSt
Comment Utility
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now