Error Message (Compile Error, Procedure too Large)

[dreicer_Jarr]

I am looking for a more efficient way to write a macro (a sample from the macro is below). This is just the first part of the macro. I need to repeat these same steps (seen for row 5 below) for rows 5 to 50. My script worked until I hit row 35 and then I got the "compile error.." message. There must be a way to use "loop" to write this more efficiently, no?

Please Login or Register  to view this content.

[stanleydgromjr]

dreicer_Jarr,

Try:


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Press the keys ALT+Q to exit the Editor, and return to Excel.

Please Login or Register  to view this content.

Then run the "Test" macro.

[shg]

dreicer, you know better . Please edit your post to add code tags.

[dreicer_Jarr]

I see the logic, but I am getting an error message '450' "Wrong number of arguments or invalid property assignment" after Case 1 (see bold below).

Please Login or Register  to view this content.

[shg]

Dreicer, please edit both of your posts to add code tags.

[dreicer_Jarr]

I thought I had. Maybe there was no comment on the second post? Can you assist with the problem?

And Let me know if everything is labeled OK. I generally don't have VBA questions, so I have not had to use tags.

[shg]

One tag goes before, and one goes after. I'll look at your code while you sort that out.

[dreicer_Jarr]

I see. Thanks for bringing this to my attention.

[shg]

This should get you there:

Please Login or Register  to view this content.

[dreicer_Jarr]

I don't see how that gets closer. Can you take a look at the original code I wrote; though repetitive and inefficient, you'll see what I am trying to accomplish. I believe Stanley was getting very close to finding a solution.

[shg]

Maybe you'll explain what you're trying to do, rather than me try to reverse-engineer your code?

[dreicer_Jarr]

I want to look at M5 on the "dashboard" sheet. If there is a 1, go to another sheet ("icon"), select picture 5, and paste the picture back on the "dashboard" sheet in cell H5. If M5 has a 2, another picture from the "icon" sheet (say "picture 2") will be selected and pasted in cell H5. If M5 is a 3, a third picture ("picture 3") from the "icon" sheet would be used in H5. This same process will need to repeat for many rows...next, the criteria in cell M6 will be "looked at" and depending on the # (1-3), the appropriate picture will go in H6 on the "dashboard" sheet...then row 7, row 8 and so on until about row 50.

[shg]

M5=1 => Picture 5 (?)
M5=2 => Picture 2
M5=2 => Picture 3
...

Can you not rename the pictures to agree with the value in M5? That would make life simpler -- the picture to be copied is "Picture M5-" & Range("M5").Value. So ...

M5=1 => Picture M5-1
M5=2 => Picture M5-2
M5=2 => Picture M5-3
...

And ditto for M6 and down the line.

[dreicer_Jarr]

There are only going to be three pictures to choose from on the "icon" sheet. So each row on the dashboard (H5, H6,...) will either get "picture 1", "picture 2" or "picture 3". Let me ask the question more clearly again.

How can I write AN efficient code to "look at" M5 (like below) and then M6, M7 and so on through M50. I did M5 and M6 the inefficient way so you can see a sample of code that works. We're always looking at M and pasting in H.

Please Login or Register  to view this content.

[Phil_V]

This is one of those posts where an example workbook attached would really help, most people that want to write and test code for you are now instead going to have to mimic and draw their own "pictures" based on their best guess, which won't neccasarily be what you want.

[Phil_V]

The code below should do what you want.
Perhaps by reading through this you will be able to see how Shg's code would have got you closer to your solution.
Stanley's code whilst good was written using the Copy / Paste method you would use for worksheet data, the alterations that Shg posted showed how to correctly use Copy & Paste for shapes to overcome the "Wrong number of arguments" error you had, (Copy for shapes takes no trailing arguments).
I have highlighted Shgs code section in red below for you to see.

As an aside you seem to have confused the issue here by 'changing your mind' mid post. In your original post you quite clearly show in the code you are copying "Picture 5", "Picture 6", "Picture 7", "Picture 8", and "Picture 9" but then later on you also state:

There are only going to be three pictures to choose from on the "icon" sheet.

It makes it very hard for people to help you if you post code that shows one thing, and then you say something completely different

In the code below I have assumed from your statement that there are only 3 pictures, but hopefully you can see how it would be expanded to accomodate more.

Please Login or Register  to view this content.

[dreicer_Jarr]

Here's the attachment. How do I write a more efficient (i.e. shorter) macro so the pictures or icons go down to row 35? The attachment has a sample macro that works, but will get too large when I continue through row 35. My gut tells me Stanley (see post above) was on the right track in figuring a way to loop the same methodology rather than repeating similar statements.

This should be very clear. Thanks for all your help.

D

[Phil_V]

Err. There is no attachment.

Did my post above not do as you required then? What was different to your requirements?

[dreicer_Jarr]

We crossed posts, though you (and others) may be interested in seeing a sample of what I am trying to accomplish. It appears your code worked. My only issue is trying to center the pictures in row H (or row_count, 8). It looks like your code directs the picture to the left (see below). How do I direct them to the center of the cells?

Please Login or Register  to view this content.

And thank you!!!

[dreicer_Jarr]

Something went wrong with the transfer. Do you see an attachment now? I do...

And yes, your code appears to work with one caviat. I have posted about it (the centering issue). Thanks!

[Phil_V]

Many thanks for the attachment, it often makes things clearer

As for centering the image I don't believe you can do it directly, but you can calculate where you want the left of the picture compared to the left of the cell.
Effectively to 'center' it, you want to find the Left of the cell, then add half of the cells width (which gives you it's center point). You then want to subtract from that half of the Pictures width, (so that you get half the picture each side of the center line, ie. centered), and the resulting figure is where you align the left of the picture.

The code change below performs this. Note that I have changed the 'With' statement for ease of writing:

Please Login or Register  to view this content.

[stanleydgromjr]

dreicer_Jarr,

Please attach your workbook - scroll down and see "Manage Attachments".

[Phil_V]

dreicer_Jarr,

Please attach your workbook - scroll down and see "Manage Attachments".

Stan, it's in post #17:
http://www.excelforum.com/2096889-post17.html

[dreicer_Jarr]

Err (like you say), I just realized that I need to skip a few rows. Specifically, when saying:

Please Login or Register  to view this content.

How could I change the code to skip rows 13, 22 and 27?

I am so close to marking SOLVED!!

[dreicer_Jarr]

Nevermind, I got it.

[Phil_V]

Out of interest why did you feel you need to skip those rows? If they were the blank rows the macro was coded to ignore them anyway.

[fmunirah2]

i hve problem with my vba coding..and i dont know how to reduce it..plz anyone help me..my interface have too many texboxt


Private Sub ComboBox1_Change()
If ComboBox1 = 1 And ComboBox2 = 1 Then
description.Visible = True
FlowRate.Visible = True
Contaminant1.Visible = True
Contaminant2.Visible = False
Contaminant3.Visible = False
Contaminant4.Visible = False
Contaminant5.Visible = False
Contaminant6.Visible = False
Contaminant7.Visible = False
Contaminant8.Visible = False
Contaminant9.Visible = False
Contaminant10.Visible = False
Label1.Visible = True
cont1.Visible = True
cont2.Visible = False
cont3.Visible = False
cont4.Visible = False
cont5.Visible = False
cont6.Visible = False
cont7.Visible = False
cont8.Visible = False
cont9.Visible = False
cont10.Visible = False
ppm1.Visible = True
ppm2.Visible = False
ppm3.Visible = False
ppm4.Visible = False
ppm5.Visible = False
ppm6.Visible = False
ppm7.Visible = False
ppm8.Visible = False
ppm9.Visible = False
ppm10.Visible = False
D1.Visible = True
D2.Visible = False
D3.Visible = False
D4.Visible = False
D5.Visible = False
D6.Visible = False
D7.Visible = False
D8.Visible = False
D9.Visible = False
D10.Visible = False
D11.Visible = False
D12.Visible = False
D13.Visible = False
D14.Visible = False
D15.Visible = False
D16.Visible = False
D17.Visible = False
D18.Visible = False
D19.Visible = False
D20.Visible = False
TD1.Visible = True
TD2.Visible = False
TD3.Visible = False
TD4.Visible = False
TD5.Visible = False
TD6.Visible = False
TD7.Visible = False
TD8.Visible = False
TD9.Visible = False
TD10.Visible = False
TD11.Visible = False
TD12.Visible = False
TD13.Visible = False
TD14.Visible = False
TD15.Visible = False
TD16.Visible = False
TD17.Visible = False
TD18.Visible = False
TD19.Visible = False
TD20.Visible = False
txtDemFlow1.Visible = True
txtDemFlow2.Visible = False
txtDemFlow3.Visible = False
txtDemFlow4.Visible = False
txtDemFlow5.Visible = False
txtDemFlow6.Visible = False
txtDemFlow7.Visible = False
txtDemFlow8.Visible = False
txtDemFlow9.Visible = False
txtDemFlow10.Visible = False
txtDemFlow11.Visible = False
txtDemFlow12.Visible = False
txtDemFlow13.Visible = False
txtDemFlow14.Visible = False
txtDemFlow15.Visible = False
txtDemFlow16.Visible = False
txtDemFlow17.Visible = False
txtDemFlow18.Visible = False
txtDemFlow19.Visible = False
txtDemFlow20.Visible = False
txtDem1Cont1.Visible = True
txtDem2Cont1.Visible = False
txtDem3Cont1.Visible = False
txtDem4Cont1.Visible = False
txtDem5Cont1.Visible = False
txtDem6Cont1.Visible = False
txtDem7Cont1.Visible = False
txtDem8Cont1.Visible = False
txtDem9Cont1.Visible = False
txtDem10Cont1.Visible = False
txtDem11Cont1.Visible = False
txtDem12Cont1.Visible = False
txtDem13Cont1.Visible = False
txtDem14Cont1.Visible = False
txtDem15Cont1.Visible = False
txtDem16Cont1.Visible = False
txtDem17Cont1.Visible = False
txtDem18Cont1.Visible = False
txtDem19Cont1.Visible = False
txtDem20Cont1.Visible = False
txtDem1Cont2.Visible = False
txtDem2Cont2.Visible = False
txtDem3Cont2.Visible = False
txtDem4Cont2.Visible = False
txtDem5Cont2.Visible = False
txtDem6Cont2.Visible = False
txtDem7Cont2.Visible = False
txtDem8Cont2.Visible = False
txtDem9Cont2.Visible = False
txtDem10Cont2.Visible = False
txtDem11Cont2.Visible = False
txtDem12Cont2.Visible = False
txtDem13Cont2.Visible = False
txtDem14Cont2.Visible = False
txtDem15Cont2.Visible = False
txtDem16Cont2.Visible = False
txtDem17Cont2.Visible = False
txtDem18Cont2.Visible = False
txtDem19Cont2.Visible = False
txtDem20Cont2.Visible = False
txtDem1Cont3.Visible = False
txtDem2Cont3.Visible = False
txtDem3Cont3.Visible = False
txtDem4Cont3.Visible = False
txtDem5Cont3.Visible = False
txtDem6Cont3.Visible = False
txtDem7Cont3.Visible = False
txtDem8Cont3.Visible = False
txtDem9Cont3.Visible = False
txtDem10Cont3.Visible = False
txtDem11Cont3.Visible = False
txtDem12Cont3.Visible = False
txtDem13Cont3.Visible = False
txtDem14Cont3.Visible = False
txtDem15Cont3.Visible = False
txtDem16Cont3.Visible = False
txtDem17Cont3.Visible = False
txtDem18Cont3.Visible = False
txtDem19Cont3.Visible = False
txtDem20Cont3.Visible = False
txtDem1Cont4.Visible = False
txtDem2Cont4.Visible = False
txtDem3Cont4.Visible = False
txtDem4Cont4.Visible = False
txtDem5Cont4.Visible = False
txtDem6Cont4.Visible = False
txtDem7Cont4.Visible = False
txtDem8Cont4.Visible = False
txtDem9Cont4.Visible = False
txtDem10Cont4.Visible = False
txtDem11Cont4.Visible = False
txtDem12Cont4.Visible = False
txtDem13Cont4.Visible = False
txtDem14Cont4.Visible = False
txtDem15Cont4.Visible = False
txtDem16Cont4.Visible = False
txtDem17Cont4.Visible = False
txtDem18Cont4.Visible = False
txtDem19Cont4.Visible = False
txtDem20Cont4.Visible = False
txtDem1Cont5.Visible = False
txtDem2Cont5.Visible = False
txtDem3Cont5.Visible = False
txtDem4Cont5.Visible = False
txtDem5Cont5.Visible = False
txtDem6Cont5.Visible = False
txtDem7Cont5.Visible = False
txtDem8Cont5.Visible = False
txtDem9Cont5.Visible = False
txtDem10Cont5.Visible = False
txtDem11Cont5.Visible = False
txtDem12Cont5.Visible = False
txtDem13Cont5.Visible = False
txtDem14Cont5.Visible = False
txtDem15Cont5.Visible = False
txtDem16Cont5.Visible = False
txtDem17Cont5.Visible = False
txtDem18Cont5.Visible = False
txtDem19Cont5.Visible = False
txtDem20Cont5.Visible = False
txtDem1Cont6.Visible = False
txtDem2Cont6.Visible = False
txtDem3Cont6.Visible = False
txtDem4Cont6.Visible = False
txtDem5Cont6.Visible = False
txtDem6Cont6.Visible = False
txtDem7Cont6.Visible = False
txtDem8Cont6.Visible = False
txtDem9Cont6.Visible = False
txtDem10Cont6.Visible = False
txtDem11Cont6.Visible = False
txtDem12Cont6.Visible = False
txtDem13Cont6.Visible = False
txtDem14Cont6.Visible = False
txtDem15Cont6.Visible = False
txtDem16Cont6.Visible = False
txtDem17Cont6.Visible = False
txtDem18Cont6.Visible = False
txtDem19Cont6.Visible = False
txtDem20Cont6.Visible = False
txtDem1Cont7.Visible = False
txtDem2Cont7.Visible = False
txtDem3Cont7.Visible = False
txtDem4Cont7.Visible = False
txtDem5Cont7.Visible = False
txtDem6Cont7.Visible = False
txtDem7Cont7.Visible = False
txtDem8Cont7.Visible = False
txtDem9Cont7.Visible = False
txtDem10Cont7.Visible = False
txtDem11Cont7.Visible = False
txtDem12Cont7.Visible = False
txtDem13Cont7.Visible = False
txtDem14Cont7.Visible = False
txtDem15Cont7.Visible = False
txtDem16Cont7.Visible = False
txtDem17Cont7.Visible = False
txtDem18Cont7.Visible = False
txtDem19Cont7.Visible = False
txtDem20Cont7.Visible = False
txtDem1Cont8.Visible = False
txtDem2Cont8.Visible = False
txtDem3Cont8.Visible = False
txtDem4Cont8.Visible = False
txtDem5Cont8.Visible = False
txtDem6Cont8.Visible = False
txtDem7Cont8.Visible = False
txtDem8Cont8.Visible = False
txtDem9Cont8.Visible = False
txtDem10Cont8.Visible = False
txtDem11Cont8.Visible = False
txtDem12Cont8.Visible = False
txtDem13Cont8.Visible = False
txtDem14Cont8.Visible = False
txtDem15Cont8.Visible = False
txtDem16Cont8.Visible = False
txtDem17Cont8.Visible = False
txtDem18Cont8.Visible = False
txtDem19Cont8.Visible = False
txtDem20Cont8.Visible = False
txtDem1Cont9.Visible = False
txtDem2Cont9.Visible = False
txtDem3Cont9.Visible = False
txtDem4Cont9.Visible = False
txtDem5Cont9.Visible = False
txtDem6Cont9.Visible = False
txtDem7Cont9.Visible = False
txtDem8Cont9.Visible = False
txtDem9Cont9.Visible = False
txtDem10Cont9.Visible = False
txtDem11Cont9.Visible = False
txtDem12Cont9.Visible = False
txtDem13Cont9.Visible = False
txtDem14Cont9.Visible = False
txtDem15Cont9.Visible = False
txtDem16Cont9.Visible = False
txtDem17Cont9.Visible = False
txtDem18Cont9.Visible = False
txtDem19Cont9.Visible = False
txtDem20Cont9.Visible = False
txtDem1Cont10.Visible = False
txtDem2Cont10.Visible = False
txtDem3Cont10.Visible = False
txtDem4Cont10.Visible = False
txtDem5Cont10.Visible = False
txtDem6Cont10.Visible = False
txtDem7Cont10.Visible = False
txtDem8Cont10.Visible = False
txtDem9Cont10.Visible = False
txtDem10Cont10.Visible = False
txtDem11Cont10.Visible = False
txtDem12Cont10.Visible = False
txtDem13Cont10.Visible = False
txtDem14Cont10.Visible = False
txtDem15Cont10.Visible = False
txtDem16Cont10.Visible = False
txtDem17Cont10.Visible = False
txtDem18Cont10.Visible = False
txtDem19Cont10.Visible = False
txtDem20Cont10.Visible = False

.....this repeat 20 time since i have 20 string in combobox

[romperstomper]

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread.

You have also failed to use code tags (rule 3)

Please read the rules and then start a new thread.