Well thanks again. I appreciate your help. I knew that I was so close to having it, but as they say "close only counts in horseshoes and hand grenades". 
Posts by Phillip2
-
-
Rory,
This is a rather large workbook and is just way too involved to post. I really appreciate your willingness to help, but I was able find what I needed. Thanks for your help,
Phillip
-
Thanks for looking at it Roy.
I have attached pictures of my form and a sample image.
Below is the beginning of my command button Sub.
my plans was to just add a macro inside it that would use the value of TBrecord to populate the second page with the sample image.
Thanks
Code
Display More'___________________________Add / Edit Record Button____________________________________________ Private Sub Add_Click() Dim cel As Range, rng As Range, v As Long, editRow As Long Set rng = Worksheets("Data").Range("A:A") On Error Resume Next v = TBrecord.Value If v > 0 Then editRow = rng.Find(v, lookat:=xlWhole).Row If editRow = 0 Then Set cel = rng.Find("*", searchdirection:=xlPrevious) editRow = cel.Row + 1 TBrecord.Value = cel.Value + 1 End If '____________Information Frame________________________ Worksheets("Data").Cells(editRow, 1).Value = TBrecord.Value Worksheets("Data").Cells(editRow, 2).Value = TBccxName.Text Worksheets("Data").Cells(editRow, 3).Value = TBlocation.Text Worksheets("Data").Cells(editRow, 4).Value = TBcontact.Text Worksheets("Data").Cells(editRow, 5).Value = TBemail.Text & "@childrensal.org" Worksheets("Data").Cells(editRow, 6).Value = Format(TBtelephone.Text, "000-000-0000") '____________Triggers Frame________________________ 'Triggers Worksheets("Data").Cells(editRow, 7).Value = TBtrigger1.Text Worksheets("Data").Cells(editRow, 8).Value = TBtrigger2.Text Worksheets("Data").Cells(editRow, 9).Value = TBtrigger3.Text Worksheets("Data").Cells(editRow, 10).Value = TBtrigger4.Text Worksheets("Data").Cells(editRow, 11).Value = TBtrigger5.Text Worksheets("Data").Cells(editRow, 12).Value = TBtrigger6.Text Worksheets("Data").Cells(editRow, 13).Value = TBtrigger7.Text Worksheets("Data").Cells(editRow, 14).Value = TBtrigger8.Text 'Sesssion Limits Worksheets("Data").Cells(editRow, 15).Value = CBsession1.Text Worksheets("Data").Cells(editRow, 16).Value = CBsession2.Text Worksheets("Data").Cells(editRow, 17).Value = CBsession3.Text Worksheets("Data").Cells(editRow, 18).Value = CBsession4.Text Worksheets("Data").Cells(editRow, 19).Value = CBsession5.Text Worksheets("Data").Cells(editRow, 20).Value = CBsession6.Text Worksheets("Data").Cells(editRow, 21).Value = CBsession7.Text Worksheets("Data").Cells(editRow, 22).Value = CBsession8.Text 'Call Control Groups Worksheets("Data").Cells(editRow, 23).Value = CBcallControl1.Text Worksheets("Data").Cells(editRow, 24).Value = CBcallControl2.Text Worksheets("Data").Cells(editRow, 25).Value = CBcallControl3.Text Worksheets("Data").Cells(editRow, 26).Value = CBcallControl4.Text Worksheets("Data").Cells(editRow, 27).Value = CBcallControl5.Text Worksheets("Data").Cells(editRow, 28).Value = CBcallControl6.Text Worksheets("Data").Cells(editRow, 29).Value = CBcallControl7.Text Worksheets("Data").Cells(editRow, 30).Value = CBcallControl8.Text '____________Scripts Frame________________________ 'Scripts If TBscripts1.Value <> "" And Left(TBscripts1.Value, 1) <> "[" Then Worksheets("Data").Cells(editRow, 31).Value = "[" & TBscripts1.Text & "IVR.aef]" Else Worksheets("Data").Cells(editRow, 31).Value = TBscripts1.Text End If If TBscripts1.Value <> "" And Left(TBscripts2.Value, 1) <> "[" Then Worksheets("Data").Cells(editRow, 32).Value = "[" & TBscripts1.Text & "IVR.aef]" Else Worksheets("Data").Cells(editRow, 32).Value = TBscripts1.Text End If ... -
This is in a Excel 365 Userform...
I have a command button (ADD) that looks at my customer's record number (TBrecord.value). If there isn't a number it assigns one, otherwise it edits the existing record.
The second page of my userform contains a picture insert. I'm wanting to change this picture to a jpg image of a call tree diagram. These images are stored in a folder titled Images and are named with the customer's record number 001, 002, 003 etc. (TBrecord.value).
I am trying to come up with a macro that I can add inside of my sub that will use the customer's record number (TBrecord.value) and insert it inside of the image's address when this sub is used.
CallTree.Picture = Load Picture ("Y:\CCX Applications\") & ("TBrecord.jpg")
Thanks in advance. I appreciate any help.
-
Thanks Jack for your comment. Our organization is around 5000 employees. Of course all of them would have a need for a product such as this. It is my understanding that there will still be some who will be allowed to keep Office 365. I'm hoping that we will be able to successfully make our case as one of them. If not, it won't be the end of the world.
-
RoyUK, thank you. While I most definitely understand the economics behind such decisions, I’m hoping that the benefits in our case will be seen. If not, then I be learning something new.
Rory, thanks for your input. I had not thought much about Javascript. However, It may be helpful to convert to it instead. That kind of brings up another thought. Can macros also be written in Python, and Javascript for Excel?
I would still be interested in hearing from anyone who may have had an experience with a converter. Do they work or they just do a hit or miss job?
-
I do appreciate your sentiments Carim. However, I just trying to learn what my options are so that I can either make a convincing case for our department to be allowed to keep using Excel or devise the best plan for making the conversion. Again, any advice would be greatly appreciated.
-
Our organization is planning to switch a large percentage of users from Microsoft Office to LibreOffice. Our department uses several Excel workbooks that include macros written in VBA. It is my understanding that these macros will not work in Calc and will need to be written using some other language.
First of all, I’m not a programmer. I do have a limited knowledge of Excel and VBA and have been successful in several of our departmental projects. However, if our department is affected by this change, I’ll be starting over with a new language. I’m a little confused about the available languages. I’ve have seen references to Basic, Star Basic, and Python as well as others. I don’t believe that any of my scripts are going to be very complicated in the grand scheme of things. What would be the preferred yet simplest language to use?
I have also seen several converters online. Just how good are these, and would anyone have a recommendation of one to use?
Thank you in advance for any advice that you can give me.
-
Thanks for the tip Roy. I'll be sure to look for it the next time.
-
That's a very good question Roy. I know I saw it. Anyway Let me try it again. Thanks for responding.
-
I have this macro working in a version with individual email buttons. However, I’d like to group everything together, so that I'm sending all of the customers emails with one macro. Currently, when I run this macro, it is sending out the first email but fail on the second. I also feel that this could be made simpler using a loop. Why is it failing?
In addition, I’d like for the macro to test each “customer mail” folder and skip it if there aren’t any files to be sent.
When the macro has finished sending, it cleans up by deleting all of the files in both the “Customer” and Customer mail” folders.
Code
Display More' ------ Email Button Emails all files in the assigned folders as attachments ----- '--------------------------------------------------------------------------------------------------- '--------------------------------------------------------------------------------------------------- '--------------------------------------------------------------------------------------------------- ' ------------------Emails all files in the assigned folder as attachments-------------------------- Sub MailAllReports() Dim OutApp As Object Dim OutMail As Object Set OutApp = CreateObject("Outlook.Application") OutApp.Session.Logon Set OutMail = OutApp.CreateItem(0) On Error Resume Next '----------------1 Sends Reports fort Customer Support to these Addresses-----------------------------Works With OutMail .To = "phillipxxxxxxxxxxxxxxx" '<---------------for testing '.To = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" '.CC = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" .BCC = "" .Subject = "Call Reports for the Customer Support Desk" .Body = "Attached are your Call Reports" '----------------Excel Files Attachments are located in this folder -------------------------------- strPath = "X:\Telecom\CallReports\CustomerSupportDesk Mail\" StrFile = Dir(strPath & "*.*") Do While Len(StrFile) > 0 .Attachments.Add strPath & StrFile StrFile = Dir Loop .display '--------------------------.Display, .Send or .Save Application.Wait (Now + TimeValue("0:00:02")) .Send '--------------------------.Display, .Send or .Save End With On Error GoTo 0 '----------------2 Sends Reports fort DeerFoot to these Addresses-------------------------------- With OutMail .To = "phillipxxxxxxxxxxxxxxx" '<---------------for testing<---------------------------------Fails '.To = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" '.CC = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" .BCC = "" .Subject = "Call Reports the DeerFoot Clinic" .Body = "Attached are your Call Reports" '----------------Excel Files Attachments are located in this folder -------------------------------- strPath = "X:\Telecom\CallReports\DeerFoot Mail\" StrFile = Dir(strPath & "*.*") Do While Len(StrFile) > 0 .Attachments.Add strPath & StrFile StrFile = Dir Loop .display '--------------------------.Display, .Send or .Save Application.Wait (Now + TimeValue("0:00:02")) .Send '--------------------------.Display, .Send or .Save End With On Error GoTo 0 '----------------3 Sends Reports fort Developmental Medicine to these Addresses-------------------------------- With OutMail To = "phillipxxxxxxxxxxxxxxx" '<---------------for testing '.To = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" '.CC = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" .BCC = "" .Subject = "Call Reports Developmental Medicine" .Body = "Attached are your Call Reports" '----------------Excel Files Attachments are located in this folder -------------------------------- strPath = "X:\Telecom\CallReports\DevelopmentalMedicine Mail\" StrFile = Dir(strPath & "*.*") Do While Len(StrFile) > 0 .Attachments.Add strPath & StrFile StrFile = Dir Loop .display '--------------------------.Display, .Send or .Save Application.Wait (Now + TimeValue("0:00:02")) .Send '--------------------------.Display, .Send or .Save End With On Error GoTo 0 '----------------4 Sends Reports fort Hospitality to these Addresses-------------------------------- With OutMail .To = "phillipxxxxxxxxxxxxxxx" '<---------------for testing '.To = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" '.CC = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" .BCC = "" .Subject = "Call Reports for Hospitality Services" .Body = "Attached are your Call Reports" '----------------Excel Files Attachments are located in this folder -------------------------------- strPath = "X:\Telecom\CallReports\HospitalityServices Mail\" StrFile = Dir(strPath & "*.*") Do While Len(StrFile) > 0 .Attachments.Add strPath & StrFile StrFile = Dir Loop .display '--------------------------.Display, .Send or .Save Application.Wait (Now + TimeValue("0:00:02")) .Send '--------------------------.Display, .Send or .Save End With On Error GoTo 0 '----------------5 Sends Reports fort Primary Care to these Addresses-------------------------------- With OutMail To = "phillipxxxxxxxxxxxxxxx" '<---------------for testing '.To = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" '.CC = "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" .BCC = "" .Subject = "Call Reports for Primary Care" .Body = "Attached are your Call Reports" '----------------Excel Files Attachments are located in this folder -------------------------------- strPath = "X:\Telecom\CallReports\PrimaryCare Mail\" StrFile = Dir(strPath & "*.*") Do While Len(StrFile) > 0 .Attachments.Add strPath & StrFile StrFile = Dir Loop .display '--------------------------.Display, .Send or .Save Application.Wait (Now + TimeValue("0:00:02")) .Send '--------------------------.Display, .Send or .Save End With On Error GoTo 0 'Clear Memory and Empty Mail Folder----------------------------------------------------------------- Kill "X:\Telecom\CallReports\CustomerSupportDesk\*.*" Kill "X:\Telecom\CallReports\CustomerSupportDesk Mail\*.*" Kill "X:\Telecom\CallReports\DeerFoot\*.*" Kill "X:\Telecom\CallReports\DeerFoot Mail\*.*" Kill "X:\Telecom\CallReports\DevelopmentalMedicine\*.*" Kill "X:\Telecom\CallReports\DevelopmentalMedicine Mail\*.*" Kill "X:\Telecom\CallReports\HospitalityServices\*.*" Kill "X:\Telecom\CallReports\HospitalityServices Mail\*.*" Kill "X:\Telecom\CallReports\PrimaryCare\*.*" Kill "X:\Telecom\CallReports\PrimaryCare Mail\*.*" Set OutMail = Nothing Set OutApp = Nothing 'Optimize Code-------------------------------------------------------------------------------------- ExitSub: Application.ScreenUpdating = True Application.EnableEvents = True Application.DisplayAlerts = True End Sub -
Thanks again Roy, I think that I found a solution. I appreciate your help.
-
Everything. However, that's fine Roy, I appreciate everything you have done on this. You already have done so much.
Thank you
-
Yes, I saw that. It worked great too. The problem was that it deleted the data in the report.
See the attached capture -
Roy, Thank you so much. I like your button idea. I can think of a project where this will be idea. The only issue is when I run the code on a file with data it doesn't keep the data. I just have a pretty formatted spreadsheet.
-
Trunten, That works perfectly. Thank you so much.
-
Roy, ok that sounds good.
-
Roy,
That is looking good. I can see places where I could use something like that. However, it seems to be formatting a new sheet.
Just so we understand each other. This is a report that has been generated by some other software and saved as a .xlsb file. Now, I am trying to add a little formatting to it to make it easier to read.
-
Trunten,
That work great. However, I've got another little problem. I realize that the code is supposed to go into a module. However, it will only work if I put it into "this workbook".
What am I doing wrong?
-
That's fine, Roy. Thanks again!