Auto fit function

[wedzmer]

I have a macros enabled worksheet. The codes were basically compiled together from the different posts here in the forum. Now I wanted to use an "auto-fit" function for the rows that should be set into 46 height as default and adjusts if the texts it is using as reference/copied is longer. I used wrap-up text but it doesn't seem to work. A code I found here in the site is used for that:

Please Login or Register  to view this content.

But I have a problem inserting this code simply because:

1. The code does not set it to 46 height as standard/default if the text it copies is shorter.
2. Everytime I insert it on the codes/macros I am using, an error 400 is popping up. here's the macros that I use:

Please Login or Register  to view this content.

badly need help here...

[Pauleyb]

400 errors are notoriously mysterious. So these are just some thoughts...
I don't see where you are trying to add the AutoFit code, so it is hard to answer.
You seem to be mixing around your sheets. Note that ActiveSheet may not be the same as Worksheets("SF 1"). If that is planned, then okay, else you may want to clarify your code.
Trace through your code. Is the 400 error happening at the AutoFit line or somewhere else?
As for the 46 height (width?), AutoFit does just what it says. It fits the cells to their smallest size, yet showing all of the data. It will overwrite any 'default' height/width setting as it does its magic. I see no easy way around this other than to perform the AutoFit and then go through each row, check its height, and modify it if <46.

[wedzmer]

400 errors are notoriously mysterious. So these are just some thoughts...
I don't see where you are trying to add the AutoFit code, so it is hard to answer.
You seem to be mixing around your sheets. Note that ActiveSheet may not be the same as Worksheets("SF 1"). If that is planned, then okay, else you may want to clarify your code.
Trace through your code. Is the 400 error happening at the AutoFit line or somewhere else?
As for the 46 height (width?), AutoFit does just what it says. It fits the cells to their smallest size, yet showing all of the data. It will overwrite any 'default' height/width setting as it does its magic. I see no easy way around this other than to perform the AutoFit and then go through each row, check its height, and modify it if <46.

How do I exactly do that?

I already tried using

Please Login or Register  to view this content.

below the Target.Rows.AutoFit part which I inserted under the Private Sub Worksheet_Change...

[Pauleyb]

Not sure what part of my post you asking help for. I'll assume it is to trace through your code. Put a breakpoint on the first line of your worksheet_change event. Then step through (F5) your code to see where the error occurs.

As for your code test snippet, I just tried it in my immediate window (using activecell instead of target) and it worked, so you are on the right track.

How/Where exactly are you including the cells.rows.autofit?

[wedzmer]

Unfortunately it doesn't work... Here's the whole code I was working on which I was hoping to adjust the whole rows as it checks on the cells on column AA.

Please Login or Register  to view this content.

However, when the main file is now open and where this second file (which is the presentation file that has these codes) are open too, it doesn't change the row of the cells on column AA that should autofit if the text are longer.

[Pauleyb]

I don't think I'm getting the whole picture. I used this code:

Please Login or Register  to view this content.

And, anytime I added something in a cell, it changed the rowheight to 46 as expected. What is the interaction between your main file and the macro file? Is it changing AA?

[wedzmer]

I don't think I'm getting the whole picture. I used this code:

Please Login or Register  to view this content.

And, anytime I added something in a cell, it changed the rowheight to 46 as expected. What is the interaction between your main file and the macro file? Is it changing AA?

it still won't work..

here are the two sheets. The MY SF1 sheet is the original sheet and the Integrated School Forms V.3 is the template sheet that copies the data within the MY SF1 sheet.

Both files are saved on my desktop within a folder named/titled "Integrated School Forms Version 3 by Sir Wedzmer Munjilul". If that would make any difference.

MY SF1.xls

and for some reason I could not upload the template sheet so I used an external link for the file which is located here Click Here

[Pauleyb]

Please explain the chain of events you think should be happening. I don't see any code which is pulling data from the template sheet. How do you think the data is being input to the sheet?

I don't see any unprotect of the sheet in the change event. When I manually unprotect the sheet, and change data in a row, then your macro seems to work fine. Is that the problem?

[wedzmer]

Please explain the chain of events you think should be happening. I don't see any code which is pulling data from the template sheet. How do you think the data is being input to the sheet?

That is maybe because both files were saved INSIDE A FOLDER on the desktop titled "Integrated School Forms Version 3 by Sir Wedzmer Munjilul".

[Pauleyb]

You are not answering my question. I have the links working fine. I am asking you how you think the data is being pulled and a worksheet change event is occurring.

the template sheet that copies the data within the MY SF1 sheet

Are you saying the formulas (such as in B10) that reference the MY SF1.xls sheet is how you are pulling the data? If so, then that is pulling the data but it will not generate a worksheet change event. However, you started this thread by saying you are getting a 400 error, so what are you doing to get your change event code to run?

[wedzmer]

In respect to pulling the data to MY SF1, there's nothing wrong with it and the error 400 is now gone. However the auto fit function that should change the height of a row where the texts within column AA are longer isn't working.

[Pauleyb]

Okay, so it is my belief that the worksheet change event (where you are trying to do the auto-fit) is not being called. Pulling in data through a formula does not cause that event to be triggered, so no autofit. You way want to consider the worksheet_calculate event. There is no Target in the calculate event, so you would have to go through row by row every time it is called to ensure the minimum 46 height.

[wedzmer]

That sounded like an alien dialect...
How do I do that? I'm really not good in macros, I merely copy the codes posted on the forum and use it on the macro sheet cos I'm no programmer.