Auto Size Cells across two sheets

[Padthelad]

I have two sheets that have data entered. Sheet 2 gets text from a particular cell on sheet 1.

When I enter data on sheet 1 the cell auto fits to show the text. This does not automatically happen on the corresponding cell on sheet 2.

Is there a way to have the cells on sheet 2 automatically auto fit to show the text entered on sheet 1?

All help is gratefully received.

Thanks,

Pad

 

[My Aswer Is This]

How is the text being entered into sheet 2?
Is this the result of a formula or a auto sheet event script?

 

[Padthelad]

Thanks for your response. Text is entered as a formula. example: =+Quote!D17

 

[My Aswer Is This]

Try this:
This is a auto sheet event script
Your Workbook must be Macro enabled
To install this code:

Right-click on the sheet2 tab
Select View Code from the pop-up context menu
Paste the below code in the VBA edit window

Private Sub Worksheet_Activate()
ActiveSheet.UsedRange.Columns.AutoFit
End Sub

 

[Padthelad]

Try this:
This is a auto sheet event script
Your Workbook must be Macro enabled
To install this code:

Right-click on the sheet2 tab
Select View Code from the pop-up context menu
Paste the below code in the VBA edit window

Private Sub Worksheet_Activate()
ActiveSheet.UsedRange.Columns.AutoFit
End Sub

Thank you for your response. That works really well!

I now have a new problem. I would like to protect the sheets from editing as they will be used company wide and I would like to prevent any unauthorized changes being made. The Autofit doesn't work when the sheets are protected. Is there a way to write VBA that would autofit with the sheets when they are protected?

Thanks

Pad

 

[My Aswer Is This]

I'm not sure how to do that. Maybe someone else here at Mr. Excel will have an answer for this.

 

[Padthelad]

I'm not sure how to do that. Maybe someone else here at Mr. Excel will have an answer for this.

Hi,

I was able to overcome this by changing the setting for the 'protect worksheet'. I ticked the box for allowing 'row height' change.

Thank you for your help on this.

Pad

 

[My Aswer Is This]

That's great. I never deal with protected sheets.

Hi,

I was able to overcome this by changing the setting for the 'protect worksheet'. I ticked the box for allowing 'row height' change.

Thank you for your help on this.

Pad