Posts by Excel Noob

    Re: Macro to print selected range of cells


    AAE,


    Thanks for answering this post. Your simple code works perfectly. Is there any way to make this resize the image to fit the paper (8.5x11) while maintaining it's aspect ratio?


    For the record your response was polite and concise.

    Re: Send To Back


    Rory,


    Here's the files. I'm using 2007.


    I was unable to attach the floorplan as it was too large even when it was in a .rar. Please try any .jpg that you have that wont be a chore to print.

    Images

    • Test Symbol.JPG

    Files

    Re: Send To Back


    Rory,


    Thanks.


    I would agree. The question is then why wont the symbol print? I've played around with it some more. I thought that it might be because the floor plan was a .jpg and the symbol was a .bmp. I saved the symbol as a .jpg and tried it with no success. I double checked the manual process again ensuring that they both were set as printable in the properties with the same results. Can anybody offer any suggestions or reasons as to why the symbol wont print?


    The floor plan is a .jpg that sits on A7:I44. The symbol only occupies 4 cells when it's first inserted onto the page. Both images are able to be sized and moved after they are inserted. When the floor plan is inserted it goes directly onto it's home. When the symbol is inserted it's placed onto cells that are outside of the page breaks. I then drag the symbol onto the floor plan and place and size it where/as needed.


    I tried setting the insertion point directly onto the floor plan into A7:B8 and manually sent it to the front the the plan to the back. I also did not move the symbol after it was inserted. Once again the symbol will not print.


    I'm perplexed. Any thoughts would be appreciated.

    Re: Send To Back


    Mike,


    Thanks I thought so too. I tried to enter it but I guess I don't know exactly how or where to put it.


    Here's what I entered after the "With pic" statement for the floorplan.


    Code
    1. While ZOrder >1
    2. .ZOrderSendBackward
    3. WEnd


    For the symbol I entered the same code with >2 and SendForward.


    I also tried >0 & >1


    Please let me know what I'm doing wrong here.

    Hello,


    How can I modify the code below so that the image that is inserted is sent to the back.


    The issue I have is that the image I'm inserting is a floor plan. I then run this macro again from a different button to insert symbols that I want to place on the floor plan. Everything works great except when I go to print it. The way it currently stands when I print I only have the floor plan printing. The only way I can get the symbols to print is if I delete the floor plan. I've tried manually sending the floor plan to the back and bringing the symbols to the front but they still wont print. I've looked at the properties of both and confirmed that they are set to print. So I'm not entirely sure if it's a simple "Send To Back" issue or if there's a larger problem here.


    Re: Insert PDF image using Macro


    Norie,


    Thanks for the reply. You're correct they are files. I need to make this as simple as possible which is why I want to automate the process. This will be part of a survey document that sales reps will be completing which is why I would like it to function from a macro.


    Is there a way to have the macro open the parent program and then take a copy of the image and paste it?


    The other solution would be to have the reps save the .pdf or .dwf file as a .jpg but I would like to keep the number of steps needed to a minimum or they will not use the form as willingly as they would if it was a click of a button.

    I have this code which works great. I can insert images and define the area that I want it to reside in. The issue is I would like it to work for PDF and DWF images as well. I've added the extensions into the code. When I run the code it will show me pdf and dwf files but when I select them I receive an error "An error occured while importing this file". When I debug this code line is highlighted

    Code
    1. Set pic = ActiveSheet.Pictures.Insert(sPicture)

    . The jpg, gif, bmp & tif files all import properly it's only the pdf and dwf files that fail.


    Can anybody please assist me in correcting this or advise me if this is possible or not?


    Here's the code I'm currently using.


    Re: Insert BMP images via a drop down list


    OK I've been playing and I believe I found a way to do it. Scratch that I did find a way to do it but then I moved the picture by dragging it and then it stopped working and I haven't been able to get it to work again.


    I've taken a .bmp that I have and pasted it onto Sheet 11. I then defined a name for it(I don't think I had to do this step but I thought I should mention it.) On my active sheet I created a Form Control button and then linked it to this macro.



    What I want is the value that is in Sheet2 H16 will define the number of copies of Picture 6 to place on the active worksheet. As I said I had it working perfectly. If I had 3 in H16 nd used the button I had 3 images pasted onto the active sheet. If I had 10 in H16 it pasted 10. Once I dragged the picture the macro only started pasting a copy of itself in text form.


    I wouldn't mind expanding on this to include a range of cells such as H16:H21 but it would have to correspond to Picture 6:Picture 10 meaning H16 would = Picture 6, H17 would = Picture 7 and so on. I'm not sure if this is possible. If not I'll have to enter a macro for each picture. The only issue with this is I will have a large amount of images.

    Re: Excel spreadsheet with insert images autofit help


    Sid,


    I doubt you still need this but I'll post it incase another person comes across this then they'll have a possible answer.


    Here's a code that I'm currently using that works very well. You'll have to adapt it for your purpose to suit the destination cells and size.


    Hello,


    I'm trying to create a drop down list that will allow me to insert BMP images stored on a worksheet within the same workbook into the active sheet.


    What I have is a floor plan. I would like a button on the top of the page that says CCTV. (I have no issues creating the button and linking it to the macro) When the user clicks that button I would like a list to be displayed that would list the names of all the different symbols that they would need to mark up the floor plan. These names would be linked to BMP images that would be stored on a separate sheet within the same work book. It can't be in a file on the PC as this will be sent to multiple users so they will not have the file.


    Here's an example.


    Click Button


    List displayed with the following text.


    "Fixed Camera"


    "PTZ"


    "APV"


    "Head End"


    and so on.....


    When the user chooses the name, a bmp image that's associated with that name will be placed onto the active worksheet in a specified location like A1. The user would then be able to drag that image to where they would like on the screen and resize it to the proper proportions for the floor plan.


    I currently have a code which will paste an image into the worksheet in a specified spot and you can drag and reporportion it.



    The issue is tying it into a list that's linked to an image on another worksheet.


    Hopefully that makes sense. Please let me know if this is even possible. Thanks in advance for your time.

    Hey guys,


    I bought the course and have learned a lot but I haven't learned enough yet that I can figure this one out. I've spent about a day and a half looking through the forums but I haven't found something that fits. If there is I apologize and ask that you please point me to it.


    Here's what I'm looking for.


    I need to calculate the percentage of cells in one column that have data in them compared to those that do not. This is easy enough to do by itself but here's where it gets tricky for me.


    I need this percentage calculated only when cells in a different column contain data.


    Here's the specifics. All of the row and column references are exactly what I'm looking to have in my worksheet.


    If A5:A200 contain any value then I want to know the % of the cells in F5:F200 that have a value in them compared to the same number in F5:F200 that contain no value. I would like the answer to show up in B2.


    Part of the issue is that cells A5:A200 already have a formula in them so I think that would count as a value. The one thing that might help is the fact that A5:A200 values that I'm looking for ALWAYS start with TN so that could be used as the search function.


    The reason why I need to base it on a value in A5:A200 is that the sheet is blank until I enter data in the rows. I can't have the blank rows below the last data that I've entered on the sheet count towards the percentage that I'm looking for.


    Basically it's a sheet that I enter installations and service calls on. The information always has a TN# associated with it. Which through a formula is automatically populated in the same row in the A column. I track the installation or service call and when it's completed I enter a completion date in the F column in the same row.


    I am trying to be able to keep track of the percentage of jobs completed (completion date entered in column F) compared to incomplete (cells in column F that have no completion date) but it has to only look at rows that actually have information entered in them which I think should be done by column A.


    column D5:D200 is where I enter the main information and there is no formula entered in the cells in column D so if it's easier to base it on that column then that's fine.


    Sorry for the long post but I wanted to be as clear as possible.


    Thanks in advance

    Re: Copy & Paste Specific Text From Cell


    LOL


    Well if you ever find yourself in my neck of the woods and it's summer time I'll take you out for a white knuckle ride :yikes: and then back to my place for booze and a BBQ.


    I'm taking it to the track this weekend for the first time to see what it can do.


    Hopefully it'll come home in one piece:rock:

    Re: Copy & Paste Specific Text From Cell


    Cringe2,


    There will always be a space after the TN or ON followed by usually text. The TN or ON are always in one string eg. TN12345 then there will be a space followed by either ON123456 followed by a space or text.


    The TN stands for Ticket Number and the ON stands for Order Number


    These are numbers that are assigned to service calls or sales orders placed for customers. the cell will contain either a TN or an ON or both followed by the customers information.


    Typically it will be entered in one of two formats.


    1. TN12345 Excel Noob 1234 Haven't got a clue lane. Dummyville Canada. Customer couldn't figure out a formula to save his life. 204-555-1234


    2. Install Excel Noob Office 2007 Dummyville ON123456 TN12345


    Hope that helps.

    I've been searching for the last hour or so and I can't seem to find something that fits the bill.


    I'm not even sure if it's possible but if it is I know I will find the answer here.


    I have a cell that contains various data but in the cell there will be a string of characters that will begin with either ON or TN followed by numbers.


    What I would like to do is have the sheet look at that cell and if there's a TN12345 (or any combination of numbers) or an ON123456 (same idea as the TN) I would like the sheet to pull just the ON or TN number out and paste it in a specific cell. The ON would have it's own cell to be placed in as would the TN.


    Here's an example. C5 contains various data, a mix of text and numbers but it will have either an ON or a TN or both. I would like the sheet to automatically pull the TN number out and place it in A5 and the ON in B5. I would like this process to repeat all the way through the sheet to the max amount of rows since I have no way of knowing how many rows I'll need.


    Can this be done with a simple IF formula or does it have to be done in VBA or is it not possible at all.


    Thanks

    Re: Show Message If Completion Date Not Met


    Cringe2,


    Thanks for the kind words. It was alot of work but well worth it


    Reafidy,


    Thanks for all of yuor help. I kinda thought that the problem was with what I was doing. I have to figure out when you place it in the worksheet or the workbook or in a module.


    It works great. The only thing I might change is to get it so it doesn't give you the message box when there are no rows that need to be entered. I'll play with it and see if I can get it.


    Thanks again for all of your help

    Re: Msgbox When Completion Date Is Not Met



    Cringe2,


    Yes it's amazing how much has changed Needless to say my car doesn't park itself


    Here's a link to some pictures of it.


    http://s180.photobucket.com/al…view&current=21970dbc.pbw


    Reafidy,


    I'm not sure if the exisitng code that I have might be messing with yours or if I'm just putting it together wrong.


    I've attached my sheet.


    You'll notice in the first row (5) that I use for a date in C there is a date prior to today. This is when I want the MsgBox to pop up because the expected date came and went without a Actual completion date being entered in D5.


    Yes I do want multiple MsgBox's to come up for every Sched Date thats past so I can see which jobs were expected to close but for whatever reason still remain open.


    This way I can track a job so it doesn't get forgotten about.


    Listing the rows that need attention would be great. If everytime the message box pops up it shows the row it's referring to that would be ideal. I have another code in a separate workbook that has something similar so I could work it out from that but first I need the basic function to work.

    Re: Msgbox When Completion Date Is Not Met


    Quote from ByTheCringe2

    What's the car, is it a T-bird?


    It's a 1969 Pontiac GTO[hr]*[/hr] Auto Merged Post;[dl]*[/dl]


    Please forgive me but I know very little about VBA


    When you say the code will exit every time does that mean that once it finds a problem it won't restart until the problem is fixed?
    I don't see it saying before close or open or save so I'm unsure how it would start again.


    I also don't see where it references todays date meaning the sheet every time it opens should check the estimated completion date (Column C ) against the current date when it's opened. If the current date is past the estimated date and there's no Actual Completion ddate entered in Column D it should bring up the MsgBox.


    It may be there but I just don't see it due to my lack of knowledge.


    I did try your code but I couldn't get it to do anything but I wouldn't be surprised if i didn't do something right there either.


    Thanks for the effort so far it really is appreciated