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 07/22/2018 in Posts

  1. 1 point
    Ah so simple! Thanks for your assistance. Now all I have to do is make the rest of the suite work and we'll be flying! Again, many thanks.
  2. 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
  3. 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
  4. 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
  5. 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 😎
  6. 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
  7. 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
  8. 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.
  9. 1 point
    File sent earlier today. Thank you in advance!
  10. 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 !
  11. 1 point
    Thank you Caleeco! I will give it a try and let you know!
  12. 1 point
    No Problem, I'll leave it with you. Give me a shout if it needs any tweaks when you do get it tested. Many Thanks Caleeco