Jump to content
News Ticker
  • Welcome to the Community
  • Use the forum to troubleshoot your Excel & VBA Problems
  • We will be launching a new website soon! www.ExcelWTF.com/Learn ! Join us to learn VBA from the ground up!


Popular Content

Showing content with the highest reputation since 02/21/2018 in all areas

  1. 1 point
    Hi again Caleeco Sorry to be a pain but I've just been simulating the effect of the requested coding and I've found a small issue. There are times when not every symbol in 'Summary' col B will have a corresponding .csv file. I can readily overcome this by increasing the amount of data in each .csv file, but it complicates the coding slightly because instead of reading data from a specific cell in the .csv file it will now need to read the last value in the column. So, in items c) & h) above, please replace "Read the value in the .csv file cell B2" with "Read the last value in col B of the .csv file". That should ensure there is always a .csv file to correspond with each symbol in 'Summary' col B and hence each symbol would have a value in col F, although for safety it might be sensible to include some error trapping to allow for a situation where there is no corresponding .csv file. And of course the first line in d) of the upper block is no longer valid; there will now be multiple cells of data and different numbers of cells in each .csv file. In text mode, it's always the last value in the last line that has to be read. Thanks
  2. 1 point
    Hey Caleeco, I'm upgrading my dashboard with some new charts, however, space is a premium as most people in the office work on small laptop screens! 😶 Would you be able to help me with something i've dreamt up? Basically... i want to stack 2 charts one on top of the other. Then be able to click on Cell B3 to bring the first chart to the front (hiding the other one behind it), or click cell B4 to bring the second chart to the front. So it will look like they're coming out of nowhere 😁 My Chart names are 'Chart 1' and 'Chart 2' if you need them. Can you write some VBA magic? Z
  3. 1 point
    Ah, that's not a VITAL bit of code.. it's just generally good practice when using the SelectionChange Event. It basically prevents the code running if you select more than one cell with your mouse. Eg... if you selected both B3 and B4 at the same time, the code will get confused and not trigger either event! 😵 Glad it worked 😊 Caleeco
  4. 1 point
    Hello Ziya Good to see you again. That's an awesome idea! Certainly something we can use the SelectionChange Event to implement. Try this code out and see if it does what you need. Obviously you'll need to align your charts up first & past the code in the relevant SHEET object and not a VBA module. Private Sub Worksheet_SelectionChange(ByVal Target As Range) '//--- Source: www.ExcelWTF.com '//--- Purpose: Quick Switch between charts If Target.Count > 1 Or Intersect(Target, Range("B3:B4")) Is Nothing Then Exit Sub If Target.Address = "$B$3" Then ActiveSheet.Shapes("Chart 1").ZOrder msoBringToFront If Target.Address = "$B$4" Then ActiveSheet.Shapes("Chart 2").ZOrder msoBringToFront End Sub Hope That Helps Caleeco 😎
  5. 1 point
    Hey Cal, Just checked out your other side... looks pretty slick I will be browsing! Hopefully I'll be less of a Excel Newbie soon enough Z
  6. 1 point
    Hi Caleeco, you rock man !!! I can't test the printing yet (next week), but the rest is exactly what I need. 1) Indeed the time stamp, very important! I assume this is the code you will use after the pdf or word is saved : .Range("Z" & CustRow).Value = TemplName .Range("AA" & CustRow).Value = Now 2) Printer I will test next week 3) I simply would say "Kill (wordFilename)" and "Kill (pdfFilename)" somewhere at the end :-) 4) Only code for closing Word, not Outlook, because I always have to review before I definitely send. So the email must be ready to send, and not immediately, so please set it on ".Display" . And Outlook is always running on my pc, so no need to close that. 5) I tested the outmail.send and works like a charm, but as said, must be set to .display 6) When you tidy up, can you adjust the variables for print/email and pdf/word. I didn't realize those 2 are just the headers for making a choice, so it's either print or email, and either word or pdf, so no doubles. Sorry for missing that earlier. This simplifies the code a bit. Again sorry !! Can you also put in this code when you are going to open Word ? On Error Resume Next Set WordApp = GetObject("Word.Application") If Err.Number <> 0 Then Err.Clear Just to avoid problems when Word already running. Probably not...but just to be sure :-) Thanks again !!! Sapron
  7. 1 point
    Greetings, I have a report ran for me which dumps into a .xls into several different sections. Each section has a name in column B, a route in the following row in column C, and data that begins to dump in the next row Columns D through F which are time planned, time actual, and location. Each section goes about 200 +/- rows of these times and locations. After each section, there are about 5 blank rows before the next name and route section information. I got a friend to help me with a macro to count each time a certain timed event happens. These Counts occur in columns H, I , and J. One count is for early, one count for late, and one count for each stop. But now I want to add up the number of times it happens for each name and route. I am using SUM function to add up each section. The total is in the same row as the Route. I have learned about the INDEX to Next Blank, but am not sure how to write the macro for the formula to go instead of the SUM function I am typing in each cell. I want to continue this report to run and sum up each section (for each driver and route that day). B C D E F H I J K John Doe Early Late Stops Pct Route 1 1 3 5 20.00% 10:00 AM 10:01 AM 123 Main Street 1 1 10:15 AM 10:14 AM 246 Main Street 1 1 10:30 AM 10:30 AM 123 3rd Avenue 1 10:45 AM 10:50 AM 247 3rd Avenue 1 1 11:00 AM 11:10 AM 395 3rd Avenue 1 1 About Five Lines of Blank Rows Jane Done Early Late Stops Pct Route 2 2 1 5 40.00% 10:00 AM 10:01 AM 100 Cherry St 1 1 10:15 AM 10:14 AM 210 Cherry St 1 1 10:30 AM 10:30 AM 330 Cherry St 1 10:45 AM 10:44 AM 1000 3rd Avenue 1 1 11:00 AM 11:00 AM 1100 3rd Avenue 1 Thanks in advance for all help.
  8. 1 point
    File sent earlier today. Thank you in advance!
  9. 1 point
    I think you are on the right track :-) Thanks so far and looking forward to see it running perfectly. If you have questions please let me know !
  10. 1 point
    Thank you Caleeco! I will give it a try and let you know!
  11. 1 point
    Recieved! Thanks for the source files, I will get started on this now.. hopefully I can crack it this evening and report back Caleeco
  12. 1 point
    Hi Caleeco, thank you for your answer. That wouldn't be an option, because the recipient can alter the data afterwards in excel and that's just the issue. For audit purposes the data must be data which can not be altered afterwards. So we will leave it like it is..no password thusfar, thank you for your input though !!
  13. 1 point
    Hi, I'm busy with converting excel files to pdf and send them out with Outlook. The converting is okay, but I want to add a password protection. The password will be a general one, so it doesn't have to put in manually each time the excel file will be converted. Can you help me with this. This is a part of the vba code : If .Range("F2").Value = "PDF" Then FileName = ThisWorkbook.Path & "\" & .Range("C" & CustRow).Value & " " & .Range("G" & CustRow).Value & ".pdf" 'creeert file met persnr en achternaam WordDoc.ExportAsFixedFormat OutputFileName:=FileName, ExportFormat:=wdExportFormatPDF WordDoc.Close False Else 'Als het in Word moet FileName = ThisWorkbook.Path & "\" & .Range("C" & CustRow).Value & " " & .Range("G" & CustRow).Value & ".docx" WordDoc.SaveAs FileName End If If .Range("J2").Value = "Email" Then Set OutApp = CreateObject("Outlook.Application") Set OutMail = OutApp.Createitem(0) With OutMail .To = Blad16.Range("U" & CustRow).Value .Subject = "Beste " & Blad16.Range("E" & CustRow).Value & "ADV en ziek" .Body = "Beste " & Blad16.Range("E" & CustRow).Value & "Hier de brief mbt adv en ziek" .Attachments.Add FileName .Display 'Als je zonder van tevoren wilt zien dan .Display to.Send End With Else: WordDoc.PrintOut WordDoc.Close End If Kill (FileName) 'Gooit de pdf of word document weg wat je hebt aangemaakt Can you help me with this one ? Thanks Ron
  14. 1 point
    Hey Kirabo, I thought that may have been the case! It's always a good idea to rename your charts however, as this makes things easier when modifying them via VBA. You can make use of the CEILING function to round up to the nearest 10, and I have modified the code slightly to just look at the entire columns of B:C. Sub ChangeAxisScales_1Line() '// Source: www.ExcelWTF.com '// Purpose: Set Max Y-Axis using MAX of 2 x datasets Dim Max1 As Long, Max2 As Long Dim ws As Worksheet Set ws = Sheets("Utilization") With ws Max1 = WorksheetFunction.Max(.Range("B:B")) Max2 = WorksheetFunction.Max(.Range("C:C")) If Max1 > Max2 Then ' Change the chart names below! .ChartObjects("Chart1").Chart.Axes(xlValue).MaximumScale = WorksheetFunction.Ceiling(Max1, 10) .ChartObjects("Chart2").Chart.Axes(xlValue).MaximumScale = WorksheetFunction.Ceiling(Max1, 10) Else .ChartObjects("Chart1").Chart.Axes(xlValue).MaximumScale = WorksheetFunction.Ceiling(Max2, 10) .ChartObjects("Chart2").Chart.Axes(xlValue).MaximumScale = WorksheetFunction.Ceiling(Max2, 10) End If End With End Sub Give it a try and let me know how you get on Thanks Caleeco
  15. 1 point
    haha turns out my charts ARE called Chart1 & Chart2 anyway That's cool, seems to work great! However, is it possible to change it so that the axis find the biggest number in my dataset and then rounds up to the nearest 10? Also how can i change it so that columns B:C can be dynamically filled with more data and I wont have to keep editing the code?
  16. 1 point
    Hey Kirabo, If you are using Excel 2013 or greater, you can just click on the chart and then the Chart Name will be displayed in the Top Left of the screen (Next to the formula bar). You can then edit the code below to suit, in my example i have called them 'Chart1' and 'Chart2'. Sub ChangeAxisScales_1Line() '// Source: www.ExcelWTF.com '// Purpose: Set Max Y-Axis using MAX of 2 x datasets Dim Max1 As Long, Max2 As Long Dim ws As Worksheet Set ws = Sheets("Utilization") With ws Max1 = WorksheetFunction.Max(.Range("B2:B8")) Max2 = WorksheetFunction.Max(.Range("C2:C15")) If Max1 > Max2 Then ' Change the chart names below! .ChartObjects("Chart1").Chart.Axes(xlValue).MaximumScale = Max1 .ChartObjects("Chart2").Chart.Axes(xlValue).MaximumScale = Max1 Else .ChartObjects("Chart1").Chart.Axes(xlValue).MaximumScale = Max2 .ChartObjects("Chart2").Chart.Axes(xlValue).MaximumScale = Max2 End If End With End Sub Let me know how you get on Thanks Caleeco
  17. 1 point
    Hi Caleeco Looks like you've been having it pretty easy with me not pestering you with my problems. However, I have a new problem. Still on the same spreadsheet as previous, but a little enhancement has stopped working. I've been using =LOOKUP(2,1/(1-ISBLANK(S:S)),S:S) to find the last value in a column that consists of blanks and manually entered numbers. It worked fine until I decided to use a formula to automatically enter new values in the column instead of me entering them manually. There are now about a hundred blank cells in the lower section of the column, each with an underlying formula. Now my =LOOKUP cell is blank; presumably it's showing the value of the last formula in the column - a blank. How do I resolve this? I searched the 'net and tried an assortment of similar formulae, but nothing worked. Now I need proper help. As always, I'm open to using a formula or VBA, whichever is the more efficient. Many thanks.
  18. 1 point
    Hi Caleeco For a "stab in the dark", that was pretty well aimed! As expected, it works perfectly - but I've learned by now to expect nothing less Thank you so much for that, now the sheet is performing as normal again. I'll try to come up with something that taxes your brain a bit better next time. Cheers