# Posts by Excel Noob

• ## Macro protection error 2003

Hello to all,

I have developed a survey sheet using 2007 and saved a copy for use with 2003. When end users try to utilize the protected sheet in 2003 they get a runtime error whenever they click the button linked to this macro. This also occured with 2010 version. I was able to load the version created with the 2007 onto a machine I have with 2010 on it and eliminated the error by unprotecting the sheet, re-protecting it making sure the Edit Objects box was checked. I tried this with 2003 on a machine running 2003 but I still receive the same error. I went as far as to check every box in the protection screen to no avail.

The error goes away if you unprotect the sheet. Both the 2007 and 2010 versions work perfectly now. Can anybody help to determine if I'm simply unable to run this macro within a protected 2003 version?

The code is to insert a picture

• ## Combining IF and SUMPRODUCT

Re: Combining IF and SUMPRODUCT

NBVC,

It's amazing how the simplest of things can be the hardest to see sometimes. I just had to add the Contact! into your formula and it worked perfectly. I probably should have stepped away, had a drink, watched a mindless show and it would have came to me. Thanks for the quick and accurate response.

• ## Combining IF and SUMPRODUCT

Hello to all,

I'm trying to incorporate an IF into this formula based on the value of cells on another sheet within the same workbook.

=SUMPRODUCT(((D\$2:D\$2999)-E\$2:E\$2999)*(G\$2:G\$2999>=N2)*(G\$2:G\$2999<=O2)) This formula works perfectly.

D2:D2999 are values of jobs. E2:E2999 is the value of products returned from the job. G2:2999 are completion dates. N2 & O2 are Month start and end dates respectively.

I want to be able to run this formula based on the value in cells L2:L2999 on a separate sheet titled Contact.

Basically if the value in Contact! L\$2:L\$2999 equals CA then I want to total all of the corresponding values in D & E 2:2999. by incorporating the SUMPRODUCT formula above. If not then I want the cell to remain blank.

I've tried =IF(L\$2:L\$2999="CA",(SUMPRODUCT(((D\$2:D\$2999)-E\$2:E\$2999)*(G\$2:G\$2999>=N2)*(G\$2:G\$2999<=O2))),("")) but it doesn't work.

• ## Macro to print selected range of cells

Re: Macro to print selected range of cells

AAE,

Thanks for the response. I have decided to use the PrintPreview command rather than the PrintOut and included instructions on how to configure the page layout to the end user.

• ## Macro to print selected range of cells

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.

• ## Send To Back

Re: Send To Back

Rory,

• ## Send To Back

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.

• ## Send To Back

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.

• ## Send To Back

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
``````While ZOrder >1
.ZOrderSendBackward
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.

• ## Send To Back

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.

• ## Insert PDF image using Macro

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.

• ## Insert PDF image using Macro

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
``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.

• ## Insert BMP images via a drop down list

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.

• ## Excel spreadsheet with insert images autofit help

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.

• ## Insert BMP images via a drop down list

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"

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.

• ## Percentage Of Cells That Start With X If Corresponding Cells Have Data

Re: Percentage Of Cells With Data If Other Cells Contain Data

Mike,

That's perfect. All I had to do was format B2 for a percentage with no decimal places and I got exactly what I needed. I didn't even think about using an array.

I guess I need to keep going through that course.

This place and you Rock!!!!

• ## Percentage Of Cells That Start With X If Corresponding Cells Have Data

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.

• ## Copy & Paste Specific Text From Cell

Re: Copy &amp; 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:

• ## Copy & Paste Specific Text From Cell

Re: Copy &amp; Paste Specific Text From Cell

Cringe2,

YOU ROCK!!!!:music:

That's perfect!

I thought it could be done with a IF formula but there's no way I could have done that.

Thanks a whole bunch.

• ## Copy & Paste Specific Text From Cell

Re: Copy &amp; 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.